Results 1 to 9 of 9

DLookUp Error When Updating Record It Calls

  1. #1
    spartyon is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2019
    Posts
    4

    DLookUp Error When Updating Record It Calls


    I will try to be as descriptive as possible. I have two tables right now "Casework" and "Employee Information". I have two forms "Casework" and "Employee Information Search Form". The main form which will be used is the "Casework" form. This form contains a section labeled employee information and contains the fields Employee ID (EID), Last & First Name, Email, Business Group, Company, Region, and Country. When a user is on the form and enters an EID that matches a record in the Employee Information table, the other fields will DLookUP the corresponding data for that Employee ID (EID). All is great so far.

    If the user inputs an EID that has not been entered before, a dialog pops up and says "employee not found". Once you click "ok" the "Employee Information Search Form" opens in a dialog box for the user to input a new Employee record for that EID. Once all the information is entered into that dialog box (employee information search form), you hit a button on the top that saves the record and closes the employee form, returning you back to the new entry in the "casework" form you were originally at. All works perfect, until this next part.

    Now that there IS a record in the "Employee Information" table that matches that new EID, I need the DLookUp fields to populate. I have tried putting a "refresh" command button that essentially just requeries the EID field and that works. It will populate the other employee fields with the new information. The issue is when you go to save the record, Access still thinks there is not a record matching the EID you just created and throws an error for the DLookUP fields. The only way I can seem to get around this error is this: After the new employee information is entered and the dialog box is closed, you have to delete the EID field and re-enter the EID to populate the DLookUp fields.

    Is there a way to do this without having the user delete the characters and re-type them in? Why wont the "refresh" button work?

    Please bear with me as I am fairly new to Access and my abilities are really limited. I appreciate all the patience and help!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,476
    Advise not to use spaces nor punctuation/special characters in naming convention. Name parts should be in separate fields.

    Post your code.

    You select EID with a combobox? You use NotInList event code to allow creating new employee record 'on the fly'? https://blueclawdatabase.com/notinlist-event-code/
    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
    spartyon is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2019
    Posts
    4
    Quote Originally Posted by June7 View Post
    Advise not to use spaces nor punctuation/special characters in naming convention. Name parts should be in separate fields.

    Post your code.

    You select EID with a combobox? You use NotInList event code to allow creating new employee record 'on the fly'? https://blueclawdatabase.com/notinlist-event-code/
    The EID is not a combobox. There are too many options to list in a combobox. Sorry but I only use macros as I do not know VBA. Not sure if that will help since I dont really have code that I did.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,135
    Post 3 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,884
    you hit a button on the top that saves the record and closes the employee form
    then there must be a macro event for that action? Can you not just add the requery action to the set of events? FWIW, I don't use macros either so I may be off base with that suggestion.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,476
    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  7. #7
    spartyon is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2019
    Posts
    4
    Quote Originally Posted by June7 View Post
    If you want to provide db for analysis, follow instructions at bottom of my post.
    DB Attached

    DBBackup.zip

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,476
    Usually a form does data entry/edit for only 1 table. You are including Employee table in form RecordSource and then you also do DLookup for employee data. Don't need to do both. If you include Employee table then use RIGHT instead of INNER join in query. Bind textboxes to Employee fields and set them Locked Yes and TabStop No - DO NOT ALLOW EDITS OF EMPLOYEE DATA ON THIS FORM.

    The form must be requeried, not just the EID textbox. Unfortunately, this will set focus to first record. Combobox NotInList event code would deal with this.

    Why do you say there are too many options for a combobox? You can still type EID into box but the list will help in validating the input. If input does not match item in list then it must be a new employee and user can be given option to create new employee record. (Or it clues them that they have mistyped input.) I have only ever seen VBA code used to facilitate entering new lookup record 'on the fly' during data entry. Review https://blueclawdatabase.com/notinlist-event-code. Make it multi-column and employee can be selected by name instead of EID. Textboxes can refer to columns of combobox to display associated info. This means Employee table would NOT BE INCLUDED IN RecordSource.

    Region cannot be both a combobox and have expression as ControlSource and since it is Employee table field, should NOT be edited anyway.

    Might explore use of Tab control to organize the groups of Casework data fields on 'pages' to make the form more compact.

    Don't see need for vertical scrollbar on all textboxes.

    Strongly advise not to use spaces nor punctuation/special characters in naming convention.
    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.

  9. #9
    spartyon is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2019
    Posts
    4
    I solved it! Not sure if it was the easy or right way but it works now.

    On the refresh button I set one SetValue macro to set the value of the EID text box to exactly what is already in the box. There are then separate SetValue macros that follow setting the values of the other fields to the DLookUp commands.

    Now when a user enters a new employee information and returns to the main form, they can hit the refresh button and all the data will populate and they can save successfully with no errors!

    I think im partially bald now from sitting here for days trying to get it to work.

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

Similar Threads

  1. Updating a table with a DLookUp text box
    By adrianf44 in forum Forms
    Replies: 1
    Last Post: 06-10-2018, 11:18 AM
  2. error in updating the record in table
    By moises in forum Access
    Replies: 6
    Last Post: 01-06-2015, 02:52 AM
  3. Replies: 2
    Last Post: 01-04-2013, 09:04 PM
  4. Replies: 3
    Last Post: 08-22-2012, 03:51 PM
  5. Replies: 4
    Last Post: 03-05-2010, 09:56 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
  •  
Tech Forums: Microsoft Office Forums