Results 1 to 3 of 3
  1. #1
    pthom is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    6

    Form before_update VBA for records not previously entered - if yes open another form

    I am working on a simple database that only I will be using. I have a table for Invoices and a table for Payees. I have a form that opens to enter in new invoices as we get them with basic invoice specific information that then prints a check request based off the tempvar (Invoice number) I want to set up on my form a way to get a message box if the Payee is not currently in the database that says "New Payee, do you want to enter information?" If yes then open the "Payee details" form, complete their contact information, close that form and continue entering their invoice specific information. If no - then me.undo. I currently have this on the "Payee" field beforeupdate of the form:

    Code:
    [Code][Private Sub Payee_BeforeUpdate(Cancel As Integer)
    Dim Answer As Variant
    Answer 
    DLookup("[Payee]""Payees Extended""[Payee] <> '" Me.Payee "'")
    If 
    Not IsNull(AnswerThen
    MsgBox 
    "Payee Not found" vbCrLf "Please enter information."vbInformation vbyesno vbDefaultButton1"New Payee"
    DoCmd.OpenForm"Payee Details"

     
    Cancel False
    Me
    .Undo

     
     
    Else:
    End If


    End Sub][/Code
    I am still learning Access and VBA so any clear cut instructions or help would be GREATLY appreciated!

    Thank you so much!

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Payee must be a combobox. The code above will be in the NotInList event.

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I'm don't think your DLookup is correct - it seems to me that there will always be records in [Payees Extended] that don't match; all of them in fact if the one you are looking for isn't there, so Answer will never be Null.

    Your DLookup should look for a match on Payee:

    Answer = DLookup("[Payee]", "[Payees Extended]", "[Payee] = '" & Me.Payee & "'")

    so you will get the Null if it NOT found, and your If statement should be If: isnull(Answer) then...

    Note: [Payees Extended] must be in square brackets (because it contains a blank), otherwise the DLookup will give you an error.
    Note: Else should not have the colon after it, otherwise Access will treat it as a label.

    Depending on how big your organization is, and how many different Payees there are, you might want to look at changing the Payee control on the form to a combo box where you could list the Payees in alphabetical order and select from it. You could then have a command button to open the Payee data form if the one you want is not listed. That approach minimizes the risk of entering the same payee twice but with slightly different spelling of the name.

    Does your [Payee] table contain a Payee_ID field (numeric, as the table PK) to identify the Payees? If not it should, because you would then use the Payee_ID value in the Invoices table, to "point to" the address record in the Payees table.
    Last edited by John_G; 11-17-2016 at 10:07 AM. Reason: text was mixed up

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to recall information previously entered
    By Accu-Grind in forum Forms
    Replies: 4
    Last Post: 11-07-2015, 06:34 AM
  2. Form won't show entered records
    By FMAlanbrooke in forum Forms
    Replies: 4
    Last Post: 09-16-2014, 08:38 PM
  3. Replies: 1
    Last Post: 07-26-2012, 10:51 AM
  4. Replies: 4
    Last Post: 05-28-2011, 01:20 AM
  5. Replies: 3
    Last Post: 05-26-2011, 12:52 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums