VBA to compare variable to data in a field

[MikeLiberty]

Hi,
I know a little VBA from my work with Excel, but I'm having trouble moving over into Access. I'm trying to find a way to determine if the value of a variable exists within a table. How do I compare a variable to all of the rows in a field. If I was in excel, I'd just loop through with for each or something similar but I'm not sure what the syntax is for doing it in Access.

Can anyone help me? I'm envisioning something like this.

For each ? in table.field
if ? = myvariable then
execute code
end if
Next ?



Thanks,
mike

 

[MikeLiberty]

Let's take a step back and confirm a few things first.

Is "Check" the name of a field in your table?
Also, is your "PIN" field numeric or text?

Check is not a field in the table. Its just Username and PIN. the PIN field is text as is the value of the UserPin variable.

 

[Joe4]

Not sure why you had "Check" in your formula then.

Anyway, this is what your DCOUNT code would look like:

    UserPin = "1234"
    LTotal = DCount("UserName", "UserVerification", "PIN = '" & UserPin & "'")
    MsgBox LTotal

To lookup and return the UserName associated with it, the code would look something like this, though you might need to add error handling code to it to tell it what to do in the event that the id is not found.

    UserPin = "1234"
    LUserName = DLookup("UserName", "UserVerification", "PIN = '" & UserPin & "'")
    MsgBox LUserName

 

[MikeLiberty]

That worked. Thanks for walking me through all of that. I appreciate it. I also used the Lookup code you added to generate a welcome message.

Thanks Again!

 

[Joe4]

Your welcome! Glad we got it all worked out.