Results 1 to 8 of 8
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    record doesn't exist


    All, using access 2010. I have a data entry form. right now I have it on load to new record. I have an unbound field based on the cust# field from a table. When the user enters a cust number; the corresponding record comes up. But, if the number isn't in the table, nothing happens and the data for the current record exist. I need to write some code where as if a user enters a cust# that doesn't exitst; the form goes blank letting them know that number is not in the table to prevent overwriting data of the current record. Thanks

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Why not use a simple Search Combo?

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    I agree with Dave...Record retrieval using a Combobox (the Combobox Wizard will walk you through process) means that the user will realize that the customer number doesn't exist, when AutoExpand doesn't go find the entered number .

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I agree. But I've taken over this database and the user may not pay attention and overwrite the current fields trying to update info for the cust# not realizing that's not the associated record. Need to put fail safes in place.

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    If the entered number is not in the Combobox then no Record will be retrieved! How do you overwrite a Record that isn't there?

    But if you insist on going down your current path we'll need to see what code you're using to do the Record retrieval, in order to advise you on how to accomplish your goal. Either post it or (better) ZIP up the file and post it, here, removing confidential data, if necessary.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks for replying. For some reason the code was used like:

    Code:
    Sub Combo188_AfterUpdate()
        ' Find the record that matches the control.
        Me.RecordsetClone.FindFirst "[Cust #] = '" & Me![Combo188] & "'"
        Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub
    This allows the user to input a cust#. If the number is not in the list it does nothing and all the data from the current record remainsL So if not paying attention changes can be made. Smh. So I need to change this to where if the record does not exist; a msgbox appears telling them the cust# doesn’t exist and make it so they cannot move on. Even possibly allow them to enter a new cust# by moving to a blank record and adding. I’m pretty sure I’ve done this before but not in a long time. Trying to remember.

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    I see...so if you retrieve one Record, then try to retrieve another Record, which is not found, the previous Record is still displayed...is that correct? See if this does it for you:
    Code:
    Sub Combo188_AfterUpdate()
     
     Me.RecordsetClone.FindFirst "[Cust #] = '" & Me![Combo188] & "'"
      
     If Me.RecordsetClone.NoMatch Then
       MsgBox "No Match to Customer Number Found", vbOKOnly + vbInformation, "Sorry"
       DoCmd.GoToRecord , , acNewRec
      Else
       Me.Bookmark = Me.RecordsetClone.Bookmark
     End If
    
    End Sub

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    You are exactly correct. I'll try this. Thanks

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

Similar Threads

  1. Replies: 4
    Last Post: 12-14-2012, 06:33 PM
  2. Calling for parameter that doesn't exist anymore
    By rankhornjp in forum Programming
    Replies: 3
    Last Post: 12-22-2011, 02:14 PM
  3. VBA to create PDF and folder if doesn't exist!
    By crxftw in forum Programming
    Replies: 2
    Last Post: 08-08-2011, 08:53 AM
  4. Append if record doesn't exist
    By Lorlai in forum Queries
    Replies: 1
    Last Post: 06-14-2011, 06:38 PM
  5. Form doesn't exist
    By Back2Basics in forum Access
    Replies: 1
    Last Post: 02-01-2010, 11:39 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