Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Stephan,
    The reply to your

  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    Star Lounger
    Join Date
    Dec 2000
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  6. #6
    Star Lounger
    Join Date
    Dec 2000
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Claus,

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

    Stephan

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    and as a P.S. you can always save a macro as a module to get the code <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Bat17

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •