Results 1 to 5 of 5
  1. #1
    gg091869 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    3

    IFF record not in list allow user to enter fields

    I need to add to this code = IFF record I type in is not part of the drop down menu
    I'm selecting a record from "[ASN_Appointment]!PO= if record I'm looking for is not there I need to continue by adding the data manually to all fields


    Private Sub PO_Click()


    AHVNAM = DLookup("AHVNAM", "AHASNF00", "AHSHMT=" & Forms![ASN_Appointment]!PO)
    AHSHDT = DLookup("AHSHDT", "AHASNF00", "AHSHMT=" & Forms![ASN_Appointment]!PO)
    AHSERD = DLookup("AHSERD", "AHASNF00", "AHSHMT=" & Forms![ASN_Appointment]!PO)
    Vendor_No = DLookup("AHRCFR", "AHASNF00", "AHSHMT=" & Forms![ASN_Appointment]!PO)
    End Sub


    Sample of when record is found data defaults from other table

    When record is not part of the drop down menu I get an error = Run Time "you must enter a value in the ASN_Appointment.AHVNAM" field.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Here is how I understand your requirements:

    1) There is a listbox called PO on Form [ASN_Appointment], loaded with information from table AHASNF00 and bound to a column with the AHSHMT field values.

    2) When the user selects a PO number that is in the dropdown list, the form must load the related information from the AHASNF00 record with that AHSHMT value.

    3) When the user enters a PO number that is NOT in the dropdown list, the form must force the user to enter the required information for that new PO, which must then be added to that table.

    If this is correct, then there are a few things I would suggest.

    First, move your code to the AfterUpdate Event for the listbox, not the Click event.

    Second, if the four variable names used in your code in PO_Click are the names of other controls on the same form, please standardize your code to use Me.controlname to refer to the controls.

    Third, error trap your Dlookups by using the NZ(,) function around them, and take appropriate action.

    Fourth, check for the existence of the PO before doing all your Dlookups.

    Code:
    Private Sub PO_AfterUpdate()
      Dim POTest As String
    
    ' Read for existence of the requested PO
       POTest = NZ(DLookup("AHSHMT", "AHASNF00", "AHSHMT=" & Forms![ASN_Appointment]!PO),"")
    
    ' If the PO exists, use the values
       If POTest = Forms![ASN_Appointment]!PO Then
          Me.AHVNAM = DLookup("AHVNAM", "AHASNF00", "AHSHMT=" & Forms![ASN_Appointment]!PO)
          Me.AHSHDT = DLookup("AHSHDT", "AHASNF00", "AHSHMT=" & Forms![ASN_Appointment]!PO)
          Me.AHSERD = DLookup("AHSERD", "AHASNF00", "AHSHMT=" & Forms![ASN_Appointment]!PO)
          Me.Vendor_No = DLookup("AHRCFR", "AHASNF00", "AHSHMT=" & Forms![ASN_Appointment]!PO)
       Else
          ' otherwise, pop up a modal data entry form to get all the new information
          ' using any of various ways to do this
       End If
    End Sub
    I would tend to use a popup modal data entry form that collects and validates all the required fields for the new PO, then saves them to the AHASNF00 table, requeries the listbox and loads the main form with the newly entered values.

  4. #4
    gg091869 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    3
    You're the best! I took your modified code and pasted it over mine and it worked..

  5. #5
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Hopefully, you did something in this spot
    Code:
          ' otherwise, pop up a modal data entry form to get all the new information
          ' using any of various ways to do this
    in order to allow/force the user to enter the required fields.

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

Similar Threads

  1. Replies: 8
    Last Post: 02-06-2013, 11:21 AM
  2. Clearing Fields for on User Form for New Record
    By dccjr in forum Programming
    Replies: 1
    Last Post: 01-31-2013, 06:40 PM
  3. Replies: 1
    Last Post: 11-08-2012, 02:55 PM
  4. VBA code to require user to enter a Value
    By rlsublime in forum Access
    Replies: 1
    Last Post: 03-14-2012, 04:34 PM
  5. Replies: 2
    Last Post: 03-02-2010, 01:58 AM

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