Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save Record & Open Form (2000)

    I have a form with a vendor field. If the vendor is not in the list, I want to to bring up a vendor form and add the vendor. My problem is when I close the vendor form, it's still giving me an error because the vendor field has not requeried. I thought that I would try to save the record before it brought up the vendor form. I've done this with other forms when the user clicks on a button, and it worked, but I can't get it to work with Not On List.

    The field is a combo box that shows the vendors' names. In the properties, the control source is the vendor number, the row source type is Table/Query, the Row Source is SELECT [Vendor].[Vendor Number], [Vendor].[Vendor] FROM Vendor ORDER BY [Vendor].[Vendor], and the bound column is 1. It puts the Vendor Number in my Transactions table. I created a program for Not On List:

    Private Sub Combo76_NotInList(NewData As String, Response As Integer)
    Dim strMsg As String
    Dim db As Database
    Dim stDocName As String
    Dim stLinkCriteria As String

    Const MB_YESNO = 4
    Const MB_QUESTION = 32
    Const IDNO = 7

    strMsg = "'" & NewData & "' is not in the list."
    strMsg = strMsg & "Would you like to add it?"
    If MsgBox(strMsg, vbExclamation Or vbYesNo) = IDNO Then

    Response = DATA_ERRDISPLAY

    Else

    stDocName = "Vendor"
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    stLinkCriteria = "Vendor Number=" & Me![Vendor Number]

    DoCmd.OpenForm stDocName, , , stLinkCriteria

    End If
    End Sub

    What is wrong with this? It keeps bringing back the question if I want to add - stuck in a loop.

    Thanks for your help!

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

    Re: Save Record & Open Form (2000)

    You must open the form as a dialog form, otherwise the code will continue, and you must tell Access that you added the new value by setting Response to acDataErrAdded.

    Private Sub Combo76_NotInList(NewData As String, Response As Integer)
    Dim strMsg As String
    Dim stDocName As String
    Dim stLinkCriteria As String

    strMsg = "'" & NewData & "' is not in the list."
    strMsg = strMsg & "Would you like to add it?"
    If MsgBox(strMsg, vbExclamation Or vbYesNo) = vbNo Then
    Response = acDataErrDisplay
    Else
    stDocName = "Vendor"
    RunCommand acCmdSaveRecord
    stLinkCriteria = "Vendor Number=" & Me![Vendor Number]
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
    Response = acDataErrAdded
    End If
    End Sub

    (I changed some AccessBasic items to their VBA equivalent)

Posting Permissions

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