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

 

[Joe4]

What is it, ultimately, that you want to do with this?
It sounds like something that you might possibly be able to do with a Query (in a query, you can select records where a certain field meets certain criteria).

If you are then looking to update the data, you could use an Update Query to do this.

 

[MikeLiberty]

Thanks for the response. I'm actually looking to validate the current user's ID against a list of approved people. I have the code to get the username but I don't know how to check it against the table.

 

[Joe4]

It is a little too abstract, I think we need a bit more details.
Can you lay out a simple example, of what you data tables look like, and what you are trying to return?

 

[MikeLiberty]

I'm trying to compare the value i have stored in a variable to the data in a specific field within a table.

Table Name: UserVerification

PIN UserName
1234 MikeLiberty


I have the variable UserPin populated in my VBA code, not from another table in the database. I'm trying to see if it exists in [UserVerification].[PIN] and if it does, I will execute code A. If not, execute Code B.

Does that make more sense?

 

[Joe4]

You could possibly use the DCOUNT function to count how many records meet your criteria. If it is returns 0 records, then you have no matches. If it returns 1 or more, you know you do.

Here is some info on that function: http://www.techonthenet.com/access/functions/domain/dcount.php

 

[MikeLiberty]

Thanks. Any idea on the syntax for adding in a variable to it?

From the link:


VBA Code
The DCount function can be used in VBA code. For example:

Dim LTotal As Long

LTotal = DCount("UnitPrice", "Order Details", "OrderID = 10248")

In this example, the variable called LTotal would now contain the number of records in the Order Details table where the OrderID is 10248.


When I do that, i get an error message of whatever I put in the "Unit Price" spot.

Example:

Dim UserPin As String
Dim LTotal As Long

UserPin = 1234

LTotal = DCount("Check", "UserVerification", "PIN = UserPin")

MsgBox(Check)

I get the following error:

The expression you entered as a query parameter produced this error: 'Check'



Can DCount be used inside a Form_Load event procedure?

 

[Joe4]

In programming, anything placed between text qualifiers (like double-quotes) are treated as literal text. Therefore, you do not want to ever put your variable names between these text qualifiers.

Try:

LTotal = DCount("Check", "UserVerification", "PIN = " & UserPin)
 
MsgBox(LTotal)

Note that this just returns a record count (how many records meet your criteria), not a field value. If you want to return a field value, take a look at DLOOKUP (http://www.techonthenet.com/access/functions/domain/dlookup.php).

 

[MikeLiberty]

In programming, anything placed between text qualifiers (like double-quotes) are treated as literal text. Therefore, you do not want to ever put your variable names between these text qualifiers.

Try:

LTotal = DCount("Check", "UserVerification", "PIN = " & UserPin)
 
MsgBox(LTotal)

Note that this just returns a record count (how many records meet your criteria), not a field value. If you want to return a field value, take a look at DLOOKUP (http://www.techonthenet.com/access/functions/domain/dlookup.php).

I'm still getting the same error with that change. I also tried subbing in the field name "PIN" and I get the error only with the value that is in the PIN field instead of the word Check. I can't get it to return just a count and store it in the variable.

 

[Joe4]

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?