Confusing problem, simple answer

[Noreene Patrick]

I have attached following code to a command button to enter data in a form for my calendar. My only problem is the last line:

When I click the command button, code runs fine until empname.setfocus. Then the line is highlighted in yellow and wont take focus of the command button. What am I doing wrong?

I only want focus to go on empname when next record appears. Otherwise, the focus is still on command button on the new record.
Can anyone help?

Private Sub Command10_Click()

'capture the values of the date & number of days

Dim numOfDays As Long
numOfDays = Me.Duration

Dim StartDate As Date
StartDate = Me.StartDate

'create records in the same table for each day
' incrementing the date for each record

Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection

Dim sqlStr As String

Dim i As Integer
For i = 1 To numOfDays
sqlStr = "INSERT INTO tbltimeoffrequests(EmpName,Shift,StartDate,Duration) VALUES('" & Me![EmpName] & "', '" & Me.Shift & "',"
sqlStr = sqlStr & "#" & StartDate + i & "#," & Me.Duration & ")"
conn.Execute sqlStr
Next

DoCmd.GoToRecord , , acNewRec
EmpName.SetFocus

 

[The_Doc_Man]

In order to accept focus, a control has to be enabled. I also think it cannot be locked, though I won't swear to that one. When you take the breakpoint, open the Debug Locals window and check the button's properties.

 

[Pat Hartman]

Try moving the SetFocus to the Current event. I think that you'll have more luck with it there.

 

[Noreene Patrick]

I have checked the properties for both controls, i.e. empname and command10. They are set to enabled - yes, locked - no.

I also tried to put empname.setfocus in the on current event of form. It gives me the same problem. The phrase empname.setfocus is always highlighted in yellow no matter where I put it, even in the on open event of the form.
It says "method or data member not found". I have checked the spelling and everything I can think of.

Also, when I got to the Command10_on click and below the line Next, I type
docmd.gotorecord,,acNewrec
empname.setfocus
Then the locals window tells me no value for empname and variant empty.

If I leave out empname.setfocus it will go to a new record but the focus is still on the command button.

What do you suggest?

 

[Fizzio]

Does me.empname.setfocus make a difference?

 

[Noreene Patrick]

It doesnt seem to matter whether I put in me.empname.setfocus or empname.setfocus.

It just will not recognize this code anywhere in the form.

 

[Fizzio]

What is empname?. I would try renaming the control different to the fieldname. If it is a txtbox, change it to txtempname (or whatever). I seem to remember access getting its knickers in a twist when a control is the same name as a fieldname. Worth a try!

 

[Noreene Patrick]

Okay, guys...I think I got it!!!

I was trying to put me.empname.setfocus

and by chance I typed me!empname.setfocus
and It Worked.....

So, why did it work with me- bang and not me-period? I know that I should know the answer, but I dont.

And thanks to all of you again for being so helpful!!

 

[Pat Hartman]

You must have changed the form's recordsource query after the form was built. The columns of the recordsource are added to the field collection of the form at the time the recordsource is entered. This is what enables the dot to work in the first place. To get the form to recognize the new columns as life forms, delete the form's recordsource, save the form, and just add the query name back into the recordsource.

 

[Noreene Patrick]

yes, I did so many things trying to figure out what was wrong that I do believe I changed the recordsource at one time.


Thank you for your help

 

[Rich]

Why not just, empname.setfocus ?