Set Focus to Subform

[iseman22]

Main Form is called frm_pickups tied to table Pickups
Sub Form is called DODAAC_subform tied to table DODAAC

DODAAC_subform is hidden until the user enters data in the control on the main form which doesn't exist in the table DODAAC.

Private Sub DODAACCS_LostFocus()
If IsNull(Me.City1) Then
Me.DODAAC_subform.Visible = True
Me.DODAAC_subform.SetFocus
DoCmd.GoToRecord acDataForm, "DODAAC_subform", acNewRec
Else
Me.DODAAC_subform.Visible = False
End If
End Sub

As you can see, I'm trying to SetFocus to DODAAC_subform and create a new record on it. Unfortunately, I keep getting a debug error 2110 which indicates DODAAC_subform is still invisible.

I'd like when user enters DODAAC that doesn't exist in the table,

1) force a new record in DODAAC table,

2) set the DODAAC field on the subform equal to the user-entered data from the main form,

3) once the user finishes entering the new data in DODAAC, return focus to the next field on the main form and make DODAAC_subform invisible again.

Thanks in advance for helping an old excel junkie learn Access

 

[bob fitz]

Your code is currently in the Lost Focus event of the control DODAACCS.
Have you tried putting it in the Before Update or AfterUpdate events.

 

[iseman22]

Bob,

Thanks for the idea. I moved the SetFocus and Command to BeforeUpdate:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.City1) Then
Me.DODAAC_subform.SetFocus
DoCmd.GoToRecord acDataForm, "DODAAC_subform", acNewRec
End If
End Sub

Debugging wouldn't allow the SetFocus; so, I took it out. Then it gave me a RT error 2489 "The object DODAAC_subform isn't open."

I can see the subform on the screen; but, apparently access can't.

I don't think I want to put this code in AfterUpdate; I'm concerned the data on the main form will change before I can use it on the subform.

 

[bob fitz]

I don't think I want to put this code in AfterUpdate; I'm concerned the data on the main form will change before I can use it on the subform.

Well that's where I think it needs to be, but it's your db.
I'm sorry but I just don't understand what it is that you are concerned about.

 

[iseman22]

Sorry for the confusion; I really need help. I'll try to clarify.

My sub form, DODAAC_subform, is set to hide/show based on whether my user enters data that is found in my DODAAC table.

What happens now: if user enters data not found in the DODAAC table, my subform appears; however, it won't allow the user to input the missing data (read: fields don't appear on the sub form).

What I'd like: when the user enters data not found in DODAAC table, my sub form appears with a new record allowing the user to input the missing data.

Thanks,
Paul.

 

[bob fitz]

Can you try this:

Private Sub DODAACCS_LostFocus()
If IsNull(Me.City1) Then
Me.DODAAC_subform.Visible = True
Me.DODAAC_subform.SetFocus
[COLOR=red][B]Me.DODAAC_subform.ReQuery[/B][/COLOR]
DoCmd.GoToRecord acDataForm, "DODAAC_subform", acNewRec
Else
Me.DODAAC_subform.Visible = False
End If
End Sub