Use DLOOKUP to Validate User's Entry? Pls Help...

[huffjd]

I am extremely new to MS Access and have reached a barrier that I hope someone can help me with. I am trying to validate a user’s entry(s) into a form’s text fields against a table. I believe this can be accomplished with a DLOOKUP but nothing I have tried seems to work. Any help would be greatly appreciated! Below are the details (using Access 2007)

Two Tables

1. TblOvrStock

Fields

OvrLoc1 (Format: ## EX: 10)



OvrLoc2 (Format: ## EX: 01)

OvrLoc3 (Format: ## EX: 05)

OvrLoc4 (Format: ## EX: 12)

PckLoc1 (Format: ## EX: 20)

PckLoc2 (Format: ## EX: 05)

PckLoc3 (Format: ## EX: 04)

PckLoc4 (Format: ## EX: 01)

2. TblValidLoc

Fields

ValidOvrLoc (Format: ######## EX: 10010512)

ValidPckLoc (Format: ######## EX: 20050401)



One Form:

· The first four fields (two digits each) represent a warehouse ‘overstock’ location

§ TxtOvrLoc1 (EX: 10)

§ TxtOvrLoc2 (EX: 01)

§ TxtOvrLoc3 (EX: 05)

§ TxtOvrLoc4 (EX: 12)

· The other four fields represent a warehouse ‘pick’ location

§ TxtPckLoc1 (EX: 20)

§ TxtPckLoc2 (EX: 05)

§ TxtPckLoc3 (EX: 04)

§ TxtPckLoc4 (EX: 01)

The form is intended to insert records into a table (TblOvrStock)

Before the records are inserted, I need to validate that the entries made in The TxtOvrLoc1,2,3,4 fields match a location in the ValidOvrLoc column of TblValidLoc

If match, then proceed with new record insert

If no match, cancel new record insert and error msg “Not a valid overstock location”

AND validate that the entries made in The TxtPckLoc1,2,3,4 fields match a location in the ValidPckLoc column of TblValidLoc

If match, then proceed with new record insert

If no match, cancel new record insert and error msg “Not a valid pick location”

[pbaldy]

You haven't posted your code attempt so we can fix it. That said, a simpler solution might be to use combo boxes with limit to list set to yes. That way a user can't select an incorrect location.

By the way, numbered fields like that is almost always a mistake. You should probably have a related table where each location is a record. Sooner or later somebody is going to decide that they need 5 locations, and your whole application will have to be revised.

[huffjd]

Paul

Thanks for the feedback. I thought about the Combo Box option but there will be over 10,000 location options. The users will be using a 10-key pad so I need to keep this as simple as possible for the users.

I haven't even gotten as far as to develop any code. Would you happen to have example code that I could learn from and try to fit to my needs?

[pbaldy]

Oh; you said "nothing I have tried seems to work", so I thought you had tried it. Here's a good reference on DLookup syntax:

DLookup Usage Samples

I might still consider a combo box. The user can still type in their input; they don't have to scroll/select.