NotInList event / add new record in a subform (97/SR-2)

Topic

New Reply

Hi all,

I routinely use the combo box’s NotInList event to open a different data entry form in add mode, ready for the user to input the entry.

What I can’t seem to figure out is the code to do the same thing in a subform. In the code below, the user enters a matter number that’s not in the list. If he responds yes to the prompt, the frmClients form (which has a fsubMatters subform) opens up and goes to a specific Client number record. The fsubMatters subform however is still at its first record for that Client number. What I want to do is go to a new record (add a record) in the subform. This is the part I can’t figure out.

‘********************** START CODE **********************
Private Sub cboMatterNum_NotInList(NewData As String, Response As Integer)

Dim iAnswer As Integer

iAnswer = MsgBox(“Matter number does not exist. Add (Yes/No)?”, vbYesNo + vbQuestion)
If iAnswer = vbYes Then
If IsLoaded(“frmClients”) Then
DoCmd.Close acForm, “frmClients”
End If
DoCmd.OpenForm “frmClients”, acNormal, , “[ClientID] = ‘” & cboClientNum.Value & “‘”, acEdit, acDialog
‘*** Now, need to go to new record (add new record) in fsubMatters subform
‘*** How do I do this???
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub
‘********************** END CODE **********************

Thanks,

Stephan

Reply | Quote

Replies

Stephan,
The reply to your

Reply | Quote

Claus,

Thanks for your response.

In my case, I could only consider your possible solution #2 since Matters Clients is a one-to-many relationship, so having a fsubMatters subform in the frmClients form makes more sense than using a separate frmMatters form. If I did have a separate frmMatters form, I wouldn’t have a problem.

Essentially, I would have a line a code:
DoCmd.OpenForm “frmMatters”, acNormal, , , acAdd, acDialog

in place of the line:
DoCmd.OpenForm “frmClients”, etc., etc.

But since I’m using a subform, I do have a problem. My difficulty remains going to a new record in the subform. However, I am a little puzzled why you say I have to do this in the frmClients’ Open or Load event. In other words, I don’t see why I can’t also do this if frmClients is already open. For example, if frmClients is open and I want to go to a new record, this simple line of code does the trick:

DoCmd.GoToRecord acDataForm, “frmClients”, acNewRec

Again, what is infuriating is that I can’t figure out the syntax to go to a new record in a subform within the main form. This is all I need to do, and you’d think that this should also be pretty easy to do in Access.

Thanks again,

Stephan

Reply | Quote

Claus,

I did some more tinkering and found the answer using the approach in your solution #2, and I did wind up using the OpenArgs property after all. The answer turned out to be relatively simple, but I couldn’t do it all through VBA code. I also had to create a simple macro.

In the frmClients Load event, I put the following code:

Private Sub Form_Load()
If Me.OpenArgs = “GotoNewSubform” Then
DoCmd.RunMacro “MacroNewSubformRecord”
End If
End Sub

The macro MacroNewSubformRecord only has 2 actions:
GoToControl: [fsubMatters]
GoToRecord: New

Incredibly simple — however it’s just annoying I couldn’t do this 100% through code and had to resort to using a macro.

In any event, thanks again for your help.

Stephan

Reply | Quote

Stephan,
If you would like to, you can do the
Docmd.GotoControl “subformname” and the
DoCmd.GoToRecord acActiveDataObject, , acNewRec
in your code replacing the call to the macro.

Reply | Quote

Claus,

Holy cow, of course! Now why didn’t I think of that?

Stephan

Reply | Quote

and as a P.S. you can always save a macro as a module to get the code

Bat17

Reply | Quote