X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Dlookup To Search In Two Tables, Access 2010    
post Jan 10 2018, 11:35 AM

Posts: 1
Joined: 10-January 18


I had this code in different buttons to search record on each table and when does not exist to add the new records on each tables using forms

How can I search two tables at the same time with one single button instead of two button for each table?

Table: tbl_CDS_DRA_Key_Legend

sExists = Nz(DLookup("[ExtractType]", "tbl_CDS_DRA_Key_Legend", "[KeyType]='" & Me.txtART & "' AND [ExtractType]='" & Me.txtExtractType & "' AND [Prefix]='" & Me.txtPrefix & "'"))


sExist = Nz(DLookup("[Vendor]", "tbl_CDS_DRA_Key_Master_Codes", "[Vendor]='" & Me.txtExtractType & "' AND [Code]='" & Me.txtCode & "'"))

I was trying to combine but it gave me an error:

sExists = Nz(DLookup("[Vendor]", "tbl_CDS_DRA_Key_Master_Codes", "[Code] = " & DLookup("[Code]", "tbl_CDS_DRA_Key_Legend", "[Code]='" & Me.txtCode & "' And [KeyType]='" & Me.txtART & "' AND [ExtractType]='" & Me.txtExtractType & "' AND [Prefix]='" & Me.txtPrefix & "' AND [Vendor]='" & Me.txtExtractType & "'")))

Please help, thank you
Go to the top of the page
post Jan 10 2018, 11:44 AM

Access Wiki and Forums Moderator
Posts: 72,428
Joined: 19-June 07
From: SunnySandyEggo


Welcome to UtterAccess!

What was the error and how are the two tables related?

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
post Jan 10 2018, 04:00 PM

UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


It looks like you have a table of Key Types and a table of Vendors. Can you explain how Key Types and Vendors are related to one another in the real world/your database? Does one of these two tables have a foreign key field that links to the other? If so, what is it? And what is this dual DLookup trying to find, in real-world terms?

For example, is every vendor supposed to have one or more corresponding key type? Or vice versa? Or, if there are six vendors and seven key types, are there 42 records in one of these tables, implying that for every vendor there should be seven records with unique key types?


(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    21st June 2018 - 02:34 PM