How can I refer to a temp variable?

[Muneer Mikel]

Hi,

The following are a couple of lines from a code in my form:

Dim TableTemp As Integer
Dim varX As Variant

TableTemp = Int((100 - 1 + 1) * Rnd + 1)
varX = DLookup("[NoOfSeats]", "Thursday", "[TableNo] =!TableTemp")

The fields [NoOfSeats] and [TableNo] are from "Thursday" table. I found out
that when I run the code, the "varX" is Null. I believe that the way I refer
to "TableTemp" in the DLookup line is wrong!
I tried to use "&TableTemp", "TableTemp", etc. yet nothing worked.
Could you please tell me how can I refer to a temp variable on a form in
another table.

Thank you,
Muneer Mikel

 

[Wayne Morgan]

You need to concatenate in the variable.

varX = DLookup("[NoOfSeats]", "Thursday", "[TableNo] =" & TableTemp)

 

[Muneer Mikel]

Thank you Wayne, it worked like a charm!

One more thing, after doing some calculations and conditions, I have this
line in my code:

dbs.Execute "UPDATE Thursday SET [NoOfSeats] = &varX WHERE [TableNo]=
&TableTemp;"

I used your method to refer to the temp variable. However this time I'm
getting this message: (Syntax error (missing operator) in query expression
'&varX')

What I'm doing wrong here?!

Thank you,
Muneer

You need to concatenate in the variable.

varX = DLookup("[NoOfSeats]", "Thursday", "[TableNo] =" & TableTemp)

--
Wayne Morgan
MS Access MVP

Hi,

The following are a couple of lines from a code in my form:

Dim TableTemp As Integer
Dim varX As Variant

TableTemp = Int((100 - 1 + 1) * Rnd + 1)
varX = DLookup("[NoOfSeats]", "Thursday", "[TableNo] =!TableTemp")

The fields [NoOfSeats] and [TableNo] are from "Thursday" table. I found
out
that when I run the code, the "varX" is Null. I believe that the way I
refer
to "TableTemp" in the DLookup line is wrong!
I tried to use "&TableTemp", "TableTemp", etc. yet nothing worked.
Could you please tell me how can I refer to a temp variable on a form in
another table.

Thank you,
Muneer Mikel

 

[Wayne Morgan]

To concatenate in the variable, it needs to be outside the quotes. If it is
in the quotes it is just treated as part of the string and ignored by VBA.

dbs.Execute "UPDATE Thursday SET [NoOfSeats] =" & varX & " WHERE [TableNo]="
& TableTemp & ";"

This syntax assume that both [NoOfSeats] and [TableNo] are Number data type
fields. As you go in and out of the quotes, make sure you leave a space when
needed. A good test to see what the result of your concatenation is would
be:

strSQL = "UPDATE Thursday SET [NoOfSeats] =" & varX & " WHERE [TableNo]=" &
TableTemp & ";"
Debug.Print strSQL
dbs.Execute strSQL, dbFailOnError

The dbFailOnError switch will tell you if something goes wrong when running
the query. The Debug.Print will print the SQL string in the debug window so
that you can see what is actually being acted on.

--
Wayne Morgan
MS Access MVP

Thank you Wayne, it worked like a charm!

One more thing, after doing some calculations and conditions, I have this
line in my code:

dbs.Execute "UPDATE Thursday SET [NoOfSeats] = &varX WHERE [TableNo]=
&TableTemp;"

I used your method to refer to the temp variable. However this time I'm
getting this message: (Syntax error (missing operator) in query expression
'&varX')

What I'm doing wrong here?!

Thank you,
Muneer

You need to concatenate in the variable.

varX = DLookup("[NoOfSeats]", "Thursday", "[TableNo] =" & TableTemp)

--
Wayne Morgan
MS Access MVP

Hi,

The following are a couple of lines from a code in my form:

Dim TableTemp As Integer
Dim varX As Variant

TableTemp = Int((100 - 1 + 1) * Rnd + 1)
varX = DLookup("[NoOfSeats]", "Thursday", "[TableNo] =!TableTemp")

The fields [NoOfSeats] and [TableNo] are from "Thursday" table. I found
out
that when I run the code, the "varX" is Null. I believe that the way I
refer
to "TableTemp" in the DLookup line is wrong!
I tried to use "&TableTemp", "TableTemp", etc. yet nothing worked.
Could you please tell me how can I refer to a temp variable on a form
in
another table.

Thank you,
Muneer Mikel