Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding Records to Lookup Tables on the Fly (2000)

    I have some fields in a Main form that have combo boxes looking up data from another Table (i.e. Phone Type - home, work, mobile). I would like the user to be able to add another phone type to that look up table while in the main form . Since I'm not good at VB, I have created one NotOnList Event macro that pops up a msgbox that says the phone type is not in the list, double click to add. Then I have another macro on the dblclick Event that opens the Add phone type form.

    When closing the Add phone type form, I would like the new entry to fill the the phone type field in the Main form.

    Can I do this through macros? I tried modifying some code without luck.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Records to Lookup Tables on the Fly (2000)

    If the Phone Type table has just the Type field, and perhaps an AutoNumber ID, you don't need to use a form to add a new type. You can add the new entry on the fly. The value entered by the user is available in the NewData argument of the NotInList event procedure; you can use the Response argument to tell Access what you have done.

    You can put VBA code like the following in the NotInList event procedure:

    Private Sub cboPhoneType_NotInList(NewData As String, Response As Integer)
    If MsgBox("The type " & NewData & " does not occur not in the list." & vbCrLf & _
    "Do you want to add it?@", vbYesNo + vbQuestion) = vbYes Then
    Dim strSQL As String
    strSQL = "INSERT INTO tblPhoneTypes (PhoneType) VALUES (" & _
    Chr$(34) & NewData & Chr$(34) & ")"
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
    Else
    cboPhoneType.Undo
    Response = acDataErrContinue
    End If
    End Sub

    If the user confirms adding a new type, the Response argument of the NotInList event procedure is set to acDataErrAdded. This tells Access that the new value should be accepted. If the user cancels, the entry is removed and the Response is set to acDataErrContinue. This tells Access that the new value is to be ignored without putting up an error message.

    If the user needs to enter additional information when adding a new phone type, you can use a form.

    Private Sub cboPhoneType_NotInList(NewData As String, Response As Integer)
    If MsgBox("The type " & NewData & " does not occur not in the list." & vbCrLf & _
    "Do you want to add it?@", vbYesNo + vbQuestion) = vbYes Then
    DoCmd.OpenForm FormName:="frmAddPhoneType", DataMode:=acFormAdd, _
    WindowMode:=acDialog, OpenArgs:=NewData
    Response = acDataErrAdded
    Else
    cboPhoneType.Undo
    Response = acDataErrContinue
    End If
    End Sub

    The type entered by the user is passed to the Add phone type form in the OpenArgs argument. You can pick this up in the Open or Load event of this form to fill the corresponding text box:

    Private Sub Form_Load()
    If OpenArgs <> "" Then
    txtPhoneType = OpenArgs
    End If
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Records to Lookup Tables on the Fly (2000)

    In my db the form (called Contact Address) is based on a tbl called Contact Addresses. The combo box name is cmboAddressDesc. There is a field in the Contact Addresses tbl and the LUAddress Description tbl called AddressDescription.

    When I put the code in the OnNotInList Event Procedure I get a message box...The text you entered isn't an item in the list. Here is how I modified the code. Can you see any errors...

    Private Sub cmboAddressDesc_NotInList(NewData As String, Response As Integer)
    If MsgBox("The type " & NewData & " does not occur not in the list." & vbCrLf & _
    "Do you want to add it?@", vbYesNo + vbQuestion) = vbYes Then
    Dim strSQL As String
    strSQL = "INSERT INTO tblLUAddressDescription (AddressDescription) VALUES (" & _
    Chr$(34) & NewData & Chr$(34) & ")"
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
    Else
    cmboAddressDesc.Undo
    Response = acDataErrContinue
    End If
    End Sub

    I don't need a separate form - adding to the actual table would be fine! Thanks.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Records to Lookup Tables on the Fly (2000)

    Change the line
    CurrentDb.Execute strSQL, dbFailOnError
    to
    CurrentDb.Execute strSQL

    dbFailOnError is an argument only used by DAO, witch is not standard in Access 2000
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Records to Lookup Tables on the Fly (2000)

    Francois already pointed out a mistake I made in my example. I still use Access 97.

    You can also remove the @ in the MsgBox text. It is a typo that also stems from Access 97. The @ character adds formatting to a message box in A97, but not any more in A2K.

    Regards,
    Hans

  6. #6
    Lounger
    Join Date
    Sep 2002
    Location
    Brookings, South Dakota, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Records to Lookup Tables on the Fly (2000)

    Hans, I also used your code to add a record but did want to add more information. Everything works great, thank you, except the newdata doesn't appear in the text box on the form that pops up, the user has to retype the data (with no errors) or it gives the error message that the text isn't in the list. I have your code

    Private Sub Form_Load()
    If OpenArgs <> "" Then
    txtAdvisor = OpenArgs
    End If
    End Sub

    in the on load event and tried the on open event of the pop up form. I'm working in access 02, on a secured database with a front end and back end. Do I need to do something different?
    Thanks,

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Adding Records to Lookup Tables on the Fly (2000)

    Is the text box called txtAdvisor? Put a msgbox just after the statement txtAdvisor = OpenArgs and see what value is in txtAdvisor.
    What Hans has suggested should work fine.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Adding Records to Lookup Tables on the Fly (2000)

    I just wanted to be sure I understood what you are attempting to do. You are storing your data used as the RowSource for the combo box in a table, and you are using the NotInList event to trigger opening a form where the data can be added. If that's the case, and your text box on the pop-up form is named txtAdvisor, then you code should read
    Me!txtAdvisor = OpenArgs, but that means you need to set that as an input parameter in the Sub statement.
    The other way to do this is to set the value of the control in the NotInList event procedure after you have opened the form but before you exit it.
    Wendell

  9. #9
    Lounger
    Join Date
    Sep 2002
    Location
    Brookings, South Dakota, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Records to Lookup Tables on the Fly (2000)

    Thanks for responding. Pat was right ,I just hadn't put the correct name in, now everything works! I learn so much from this group and really appreciate the effort you all put into helping people. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Tammy

Posting Permissions

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