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: 73,941
Joined: 19-June 07
From: SunnySandyEggo


Welcome to UtterAccess!

What was the error and how are the two tables related?
Go to the top of the page
post Jan 10 2018, 04:00 PM

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?

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th December 2018 - 09:37 AM