	AllExperts > Using MS Access
	Search

About Richard Rost
Expertise

I am happy to answer any kinds of questions about Microsoft Access - from basic table design to advanced VBA programming. Also, please feel free to check the Access Tips & Tricks section of my web site.

Experience
I am the president of AccessLearningZone.com and 599CD.com. I specialize in Microsoft Access Tutorials. I have been teaching Access in the classroom since 1994, and online since 2002.

Education/Credentials
I am a self-taught Access expert. I have been building databases for clients since the early 90s. You can see a sample of my Access Tutorials on my web site at 599CD.com

You are here: Experts > Computing/Technology > Business Software > Using MS Access > Setfocus

Using MS Access - Setfocus

Expert: Richard Rost - 7/8/2009

Question
QUESTION: I have a subform with the emplid as the first feild. when they tab it does the Dlookup. If it can't find the first name it sets the value of emplid to spaces. I want it to go back to the emplid so they can start over. Right now it goes to the feild i tabbed to.

Private Sub EMPLID_AfterUpdate()

EMPLID.SetFocus

If IsNull(DLookup("[FNAME]", "candidates", "emplid = LMPeople")) Then
MsgBox "No records were found. Please try again."
LMPeople = " "

Else
Me.[First Name] = DLookup("[FNAME]", "candidates", "emplid = LMPeople")
Me.[Last Name] = DLookup("[LNAME]", "candidates", "emplid = LMPeople")
Me.[short Desc] = DLookup("[dept_descr_short]", "candidates", "emplid = LMPeople")
End If

End Sub

ANSWER: First, don't set your field to spaces. That's just bad practice. Set it to EMPTY: ""

Of course, you'll have to set the "Allow Zero Length Strings" property of that field in the table to TRUE.

Now, all you really have to do to fix your problem is just issue a DoCmd.GoToControl command after your MsgBox.

DoCmd.GoToControl "FNAME"

If that's the name of your text box.

Let me know if you have any other questions or comments.

Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT ACCESS VIDEO TUTORIAL. Go to http://www.599cd.com/Access/AllExperts

Cordially,

Richard Rost
599CD Computer Training
http://www.599cd.com

P.S. Also, don't forget to visit my FREE Microsoft Access Tips & Tricks Page at http://www.599cd.com/Tips/AllExperts

P.P.S. I volunteer my time at AllExperts to help people, and I get a LOT of questions, so I can't take an hour to answer each question. If you need more DETAILED HELP, come to my TechHelp web site at http://www.599cd.com/TechHelp/AllExperts and I'll take as much time as you need to answer your question.

http://www.599cd.com/Microsoft_Access_Tutorial?key=AllExperts

Out of work? Know someone who is? Get a FREE copy of my Computer Job Skills CD at http://www.599cd.com/JobSkills?key=AllExperts

---------- FOLLOW-UP ----------

QUESTION: I tried DoCmd.GoToControl "LMPeople" it doesn't appear to work The field is named LMPeople on the subform. In the field preferences the control is LMPeople, but I get an error runtime 2109 stating that LMPeople is not a field in the current record. In the Dlookup I am using that field name to find my value.

Answer
If you want to refer to a value on a Form, you can use the notation:

Forms!FormName!FieldName

You can use this notation inside of queries, other forms, or even reports. I have a free tutorial that explains how to use this:

http://599cd.com/tips/access/forms-formname-field-notation?key=AllExperts

Note that if you're refering to a value on a SUBFORM, the correct notation is:

Forms!FormName!SubForm.Form!FieldName

Crazy, I know. Enjoy! Hope this helps.

Let me know if you have any other questions or comments.

Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT ACCESS VIDEO TUTORIAL. Go to http://www.599cd.com/Access/AllExperts

Cordially,

Richard Rost
599CD Computer Training
http://www.599cd.com

P.S. Also, don't forget to visit my FREE Microsoft Access Tips & Tricks Page at http://www.599cd.com/Tips/AllExperts

P.P.S. I volunteer my time at AllExperts to help people, and I get a LOT of questions, so I can't take an hour to answer each question. If you need more DETAILED HELP, come to my TechHelp web site at http://www.599cd.com/TechHelp/AllExperts and I'll take as much time as you need to answer your question.

http://www.599cd.com/Microsoft_Access_Tutorial?key=AllExperts

Out of work? Know someone who is? Get a FREE copy of my Computer Job Skills CD at http://www.599cd.com/JobSkills?key=AllExperts

Add to this Answer Ask a Question