UtterAccess.com
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    
 
   
ivonsurf123
post Jan 10 2018, 11:35 AM
Post#1



Posts: 1
Joined: 10-January 18



Hello,

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 & "'"))

Table:tbl_CDS_DRA_Key_Master_Codes

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
 
theDBguy
post Jan 10 2018, 11:44 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,941
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

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


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


ivonsurf123,

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?

Dennis
Go to the top of the page
 


Custom Search


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