Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Lookup Record in Database 1

Status
Not open for further replies.
perfectchaoss

perfectchaoss

Programmer
Nov 6, 2002
44
US
Hi,
I am currently trying to verify if a current Primary Key (UPC) is already in a database then return the status(1,2 or 3). I am using the Dlookup and the code looks like this.

UPCf = Me!txtUPC.Text
CheckUPC = DLookup("UPC", "dbo_NewItemList", UPC = UPCf)

I thought that this would set CheckUPC = UPC where UPC = my variable from the text box. However CheckUPC is always = to the first record in my database. Does anyone know why this is? Or if there is a better way to check the Database.
Thanks,
JF
 
Sort by date Sort by votes
Hello JF,
This is happenning because you are not calling DLookup correctly. Because you are leaving the " " quotes off of the condition parameter, the function is being told to return the first row. Here is the syntax that will correct the problem:
Code: 
CheckUPC = DLookup("UPC", "dbo_NewItemList", "UPC = " & Me!txtUPC.Text)
I removed the variable UPCf, as it was not required here.

Good Luck, Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance °•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°
 
Upvote 0 Downvote
Robert,
Thanks for the quick responce, i originally had that format,
CheckUPC = DLookup("UPC", "dbo_NewItemList", "UPC = " & Me!txtUPC.Text)
but if i have the "UPC = " & Me!txtUPC.Text it gives me the following error. "Data type mismatch in criteria expression" any sugestions?
thanks again,
JF
 
Upvote 0 Downvote
im newbie but i'd try this:

"UPC = Me!txtUPC.text"
 
Upvote 0 Downvote
It appears that you have a string value, not a number as I thought. Here is the syntax for string values:
Code: 
CheckUPC = DLookup("UPC", "dbo_NewItemList", "UPC = '" & Me!txtUPC.Text & "'")
Enjoy, Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance °•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Xavierparkash
  • Locked
  • Question
MS Access Slow Search in Linked Database
Replies
0
Views
151
Xavierparkash
Xavierparkash
ameedoo3000
  • Locked
  • Question
Rename current database
Replies
0
Views
174
ameedoo3000
ameedoo3000
ameedoo3000
  • Locked
  • Question
rename current database 2
2
Replies
31
Views
555
SkipVought
S
caykamanj1966
  • Locked
  • Question
Duplicate Records in DataSheet View
Replies
5
Views
263
Andrzejek
Andrzejek
PrimoSteve
  • Locked
  • Question
Fill in 2nd Textbox2 from Textbox1
Replies
3
Views
234
PrimoSteve
PrimoSteve

Part and Inventory Search

Sponsor

Back
Top