Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    gar is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Nov 2011
    Posts
    42

    Display values being misinterpreted as bound values in a lookup field.

    I have a VBA routine which reads several data items from a text file and writes them to fields in an Access table; two of the fields involved are "lookup". Until recently this has worked without problems but a few days ago it started to try to write the display values instead of the bound values to the lookup fields. Can anyone help?

  2. #2
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What changed? Was the VBA edited? Were the tables edited/changed? Did the structure of the text file change?

    Hard to troubleshoot without seeing the VBA/dB.

    The first thing I would suggest is to remove any and all look up FIELDS. The are evil...
    See The Evils of Lookup Fields in Tables
    Might also look at The Ten Commandments of Access

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I concur with Steve. However, you may find some helpful info in the Similar Threads area at the bottom of the page.

  4. #4
    gar is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2011
    Posts
    42
    Thanks for your help. The existing lookup fields, according to design view, already have a data type of "Number" but they also have a lot of lookup-specific "baggage" as detailed under the Lookup tab of the Field Properties. Can they be converted to pure/simple Number fields?

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post some code or attach a copy of the database (or part of it) with instructions showing some of the data you are reading? Post as a zip.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by gar View Post
    Thanks for your help. The existing lookup fields, according to design view, already have a data type of "Number" but they also have a lot of lookup-specific "baggage" as detailed under the Lookup tab of the Field Properties. Can they be converted to pure/simple Number fields?
    You can test this by making a COPY of your dB, opening a table in design view, select the look up field, click on the "Lookup" tab and change the "Display Control" property from "Combo Box" to "Text Box".
    Save the table and test the dB. If everything works correctly, apply the change(s) to the real dB.


    Whenever I look at someone's dB, one of the first things I do is to check if there are look up FIELDS. If I find any, I change the "Display Control" to Text Box.

  7. #7
    gar is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2011
    Posts
    42
    Thanks for all your help. Lookup Fields don't appear to have any supporters; I wonder why Microsoft persists with them. I have succeeded in eliminating them from my database, as advised, but, as a result the data which used to appear in a table now requires a query and I am being confronted with error messages when I attempt to edit the data. E.g. "Cannot add record(s): join key of table<table name> not in recordset.". If this this should be submitted as a separate thread, please advise accordingly.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Seems to be same issue so I'd suggest staying in this thread. However, providing a copy of the database or part of it to show what you are working with will help get advice/solution. Nothing like having an actual database to get context and streamline communications.

  9. #9
    gar is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2011
    Posts
    42
    Zipped database attached, I hope.
    Attached Files Attached Files

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    More info needed.

    I have a VBA routine which reads several data items from a text file and writes them to fields in an Access table;
    Need some sample data and
    instructions of what data should populate what fields

    What fields in what tables were/are lookup fields?

    t_cheques has no PK

    Can you describe the application/logic of the database and the "business" it supports?

  11. #11
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Agree with orange. Cannot troubleshoot the VBA code without a sample of the CSV text file. How do you run the "Sub statement_update()"? A button?
    Would you post the CSV file and say what two fields are having problems?


    After looking at your table designs/relationships, I changed some field names and the relationships. You have 23 checks (cheques) without a Payee, so I couldn't enforce RI between t_payees and t_Cheques.
    I created a form "frmChequesWithoutPayee" to see the checks w/missing payees. You can assign a payee to the checks from this form.

    "Name" and "Key" are reserved words, so I changes the names.
    I like to have suffixes for the PK/FK fields.... I added those also.
    I created new a new form/sub form and hid yours. (looks like you also deleted a few tables?)
    Attached Files Attached Files
    Last edited by ssanfu; 12-04-2020 at 04:20 PM. Reason: added request for info

  12. #12
    gar is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2011
    Posts
    42
    Thanks for bringing the primary key issue to my attention. The primary key field in t_cheques was serial_number. A duplicate value (012426) had found it's way into that field and, presumably, invalidated the primary key. However, when I eliminated the duplicate and tried to enter a new record, the error message was still issued. - In t_cheques "payee_code" used to be a lookup field, keyed to ID in t_payees with the result that when t_cheques was displyed in datasheet view, the name from t_payees was displayed rather than the numerical payee code. Now, payee_code is an ordinary number field related to ID in t_payees and in order to see the payees name rather than the payee code, together with the other cheque data one has to view q_cheques_payee. - The error message was elicited as soon as I tried to enter the first digit of the serial number of a new record.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Again, Would you post the CSV file and say what two fields are having problems?

  14. #14
    gar is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2011
    Posts
    42
    Here is a typical example:

    Transaction Date,Transaction Type,Sort Code,Account Number,Transaction Description,Debit Amount,Credit Amount,Balance
    02/12/2020,DEB,'80-45-06,01020328,SAINSBURYS PETROL,51.45,,15459.74
    02/12/2020,DEB,'80-45-06,01020328,THE DUCK AND DOG,11.00,,15511.19
    02/12/2020,DEB,'80-45-06,01020328,SAINSBURY'S S/MKT,7.40,,15522.19

    The lookup fields are "Transaction Type" and "Transaction Description"

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Seems there are some things missing from the database??

    Click image for larger version. 

Name:	Missing.PNG 
Views:	12 
Size:	39.7 KB 
ID:	43559

    Consider an import specification for your transactions, then a validation/verification process to ensure the data values are appropriate. If not, then identify specific errors and specific records. Since we don't know your process either return for correction and resubmit or other approved process.
    You can use your lookup tables to ensure incoming values are correct/acceptable.

    Possible import spec
    Click image for larger version. 

Name:	ImportSpecForExtract.jpg 
Views:	11 
Size:	90.2 KB 
ID:	43560

    I used this with a sample table (of your csv values) to create a table ExtractJ

    Click image for larger version. 

Name:	ExtractJ_Design.PNG 
Views:	11 
Size:	13.3 KB 
ID:	43561

    ID TransactionDate TransactionType SortCode AccountNumber TransactionDescription DebitAmount CreditAmount Balance
    1 02-Dec-20 DEB '80-45-06 01020328 SAINSBURYS PETROL 51.45
    15459.74
    2 02-Dec-20 DEB '80-45-06 01020328 THE DUCK AND DOG 11
    15511.19
    3 02-Dec-20 DEB '80-45-06 01020328 SAINSBURY'S S/MKT 7.4
    15522.19

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-07-2018, 05:04 AM
  2. Replies: 1
    Last Post: 08-01-2017, 12:14 PM
  3. Lookup values not appearing in Lookup field?
    By dominover in forum Access
    Replies: 4
    Last Post: 03-05-2016, 05:01 PM
  4. Display all the lookup values found
    By boboivan in forum Access
    Replies: 2
    Last Post: 02-22-2016, 07:40 AM
  5. Replies: 14
    Last Post: 07-13-2015, 12:47 PM

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