User Input Validation/DLookup Question(s) 1

[marcus101]

Couple of hopefully quick form/DLookup questions:

On a form, I'm attempting to handle user input for an ID table value using Access 2000.

Here's the thing:

I have a query that goes and looks up a valid ID number that can be input from the form.

I've bound this query to a combo box, and I've used the "Limit to List" option and it essentially works great.

I need to reconsider this option, though, and see if I can get the same, or similar functionality using just a single text box INSTEAD of the combo box.

My original idea was to hang on to my combo box or query and then somehow use a lookup or DLookup function (albeit based on a query which links the two required tables I need to check for a valid non-matching ID, which I don't think is possible to reference using Dlookup...)

I suppose I could use a MakeTable query to create a temp table, then use Dlookup to get info from that, but my critical $10000 question is:

1. Is it at all POSSIBLE to do a Dlookup from a QUERY instead of a TABLE?

A simple yes or no will do.

If yes, well, I'd like to know better how to go about doing that with some terse examples, because I get a 2471 error when I use a query name within the Dlookup code, which seems to suggest that that option isn't available.

2. Also, what does DLookup OUTPUT? Is it a value, a t/f, a variant, a string, what?

I can provide examples if necessary, but I've already gone through the syntax checking and read the FAQs here locally on Dlookup syntax.

If no, well, no worries - I'll just have to try and figure something else.

Thanks in advance.

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[hneal98]

1. Is it at all POSSIBLE to do a Dlookup from a QUERY instead of a TABLE? YES.

Example:
DLookUp("[PRODUCT]","Query4","[IPANO]='050'")


2. Also, what does DLookup OUTPUT? Is it a value, a t/f, a variant, a string, what?

That depends on what the data type of your field is that you are trying to query.

 

[jksmi]

Hi, 1. yes and 2. returns the same type as the field you lookup. DLookup can return null but you can catch this with the NZ function... do I get my $10000?

HTH, Jamie
FAQ219-2884

 

[jksmi]

oh, it seems I took to long...

HTH, Jamie
FAQ219-2884

 

[randy700]

1. Yes

Dim x as string
x = dlookup("LastName","qryEmployees","EmpID=" & me.txtEmpID)

Dim y as integer
y = dlookup("EmpID","qryEmployees","LastName = '" & me.txtLastName & "' and FirstName = '" & me.txtFirstName & "'")

2. It can return ANY data type or null.

Dim x as Integer
x = me.txtEmpID
If IsNull(dlookup("LastName","qryEmployees","EmpID=" & x) Then
msgbox "Invalid employee id"
End If

3. Do you need an address to send the $10000?



Randy

 

[marcus101]

Randy700:

Thanks a lot for the post. You nailed my problem on the head perfectly, though it wasn't for the reason I thought.

The problem was due to my handling of the criteria portion at the END of the DLookup string, which I need to handle as text, but obviously, depending on what criteria field type I use, that would change if numerics were in play.

Here is the solution that worked for me:

Dim string1 as String
string1 = Me.NewFileID.Value

DLookup("FileID", "qryCheckID", "FileID='" & string1 & "'")

Randy also added some neat tips for getting related records from associated query fields and also some ideas on if/then usage, which is great as a "package" solution to leverage the whole thing.

Thanks again.

PS: You realize I was only kidding about the $10000, right?

(grin)

marcus101
Access/SQL/XML Developer
Ottawa, Canada