get a value from a table by query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi! i have a problem when i am running this code. i don't knw what is wrong
with this. i am looping through a table and on each row. when i am on that
row. i want to go to another table with the tempLevel and get the tempSalary.
what happen is i get a invalied argument error. and it point ing to "Set
rstQ = db.OpenRecordset(strSQL, dbOpenSnapshot)". i don't know why this
error. what i want is: while i am looping through main table get a tempLevel
value which is a primary key for table CS22. i want to go there and get the
value. help me with this.Thanks!

Dim strSQL As String
dim tempLevel as String
Dim tempSalary As Currency
Dim rstQ As Recordset
Set db = CurrentDb()
tempLevel = value ' a value will be diffrent in each row when we loop
strSQL = "SELECT [A] FROM [CS22] WHERE [(Level =
tempLevel)];"
Set rstQ = db.OpenRecordset(strSQL, dbOpenSnapshot)
tempSalary = rstQ![A]
MsgBox (strSQL)
 
Hi,

first thing is you have to set a string value in quotes in SQL, second issue
you can't reference directly a vb variable in an SQL string, this should work:

strSQL = "SELECT [A] FROM [CS22] WHERE [Level] = '" & tempLevel &"';"

Assuming A is a table column in CS22, CS22 is a table and Level is a column
in the table CS22, too.
 
Hi! i tried that. and still did not working. i want to get that result and
store it. and i get a "invalied argument" error. and it is pointing to this
statement: "Set strSQL = db.OpenRecordset(strSQL, dbOpenSnapshot)". could you
help me with this. i don't know what i am doing wrong. is there any other way
of using or any other function i could u it. whenever i loop through. this
statement will run. help me!

Dim strSQL As String
dim tempLevel as String
Dim tempSalary As Currency
Dim rstQ As Recordset
Set db = CurrentDb()
tempLevel = value ' a value will be diffrent in each row when we loop
strSQL = "SELECT [A] FROM [CS22] WHERE [Level] =
'"& tempLevel&"';"
Set strSQL = db.OpenRecordset(strSQL, dbOpenSnapshot)
tempSalary = strSQL
MsgBox (strSQL)


Oliver Seiffert said:
Hi,

first thing is you have to set a string value in quotes in SQL, second issue
you can't reference directly a vb variable in an SQL string, this should work:

strSQL = "SELECT [A] FROM [CS22] WHERE [Level] = '" & tempLevel &"';"

Assuming A is a table column in CS22, CS22 is a table and Level is a column
in the table CS22, too.

--
Regards

Oliver Seiffert


Theleepan said:
Hi! i have a problem when i am running this code. i don't knw what is wrong
with this. i am looping through a table and on each row. when i am on that
row. i want to go to another table with the tempLevel and get the tempSalary.
what happen is i get a invalied argument error. and it point ing to "Set
rstQ = db.OpenRecordset(strSQL, dbOpenSnapshot)". i don't know why this
error. what i want is: while i am looping through main table get a tempLevel
value which is a primary key for table CS22. i want to go there and get the
value. help me with this.Thanks!

Dim strSQL As String
dim tempLevel as String
Dim tempSalary As Currency
Dim rstQ As Recordset
Set db = CurrentDb()
tempLevel = value ' a value will be diffrent in each row when we loop
strSQL = "SELECT [A] FROM [CS22] WHERE [(Level =
tempLevel)];"
Set rstQ = db.OpenRecordset(strSQL, dbOpenSnapshot)
tempSalary = rstQ![A]
MsgBox (strSQL)
Click to expand...
Click to expand...
 
Set strSQL = db.OpenRecordset(strSQL, dbOpenSnapshot)
tempSalary = strSQL

should be

Set rstQ = db.OpenRecordset(strSQL, dbOpenSnapshot)
tempSalary = rstQ!A

BTW, you probably should change Dim rstQ As Recordset to Dim rstQ As
DAO.Recordset, just to be safe.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Theleepan said:
Hi! i tried that. and still did not working. i want to get that result and
store it. and i get a "invalied argument" error. and it is pointing to this
statement: "Set strSQL = db.OpenRecordset(strSQL, dbOpenSnapshot)". could you
help me with this. i don't know what i am doing wrong. is there any other way
of using or any other function i could u it. whenever i loop through. this
statement will run. help me!

Dim strSQL As String
dim tempLevel as String
Dim tempSalary As Currency
Dim rstQ As Recordset
Set db = CurrentDb()
tempLevel = value ' a value will be diffrent in each row when we loop
strSQL = "SELECT [A] FROM [CS22] WHERE [Level] =
'"& tempLevel&"';"
Set strSQL = db.OpenRecordset(strSQL, dbOpenSnapshot)
tempSalary = strSQL
MsgBox (strSQL)


Oliver Seiffert said:
Hi,

first thing is you have to set a string value in quotes in SQL, second issue
you can't reference directly a vb variable in an SQL string, this should work:

strSQL = "SELECT [A] FROM [CS22] WHERE [Level] = '" & tempLevel &"';"

Assuming A is a table column in CS22, CS22 is a table and Level is a column
in the table CS22, too.

--
Regards

Oliver Seiffert


Theleepan said:
Hi! i have a problem when i am running this code. i don't knw what is wrong
with this. i am looping through a table and on each row. when i am on that
row. i want to go to another table with the tempLevel and get the tempSalary.
what happen is i get a invalied argument error. and it point ing to "Set
rstQ = db.OpenRecordset(strSQL, dbOpenSnapshot)". i don't know why this
error. what i want is: while i am looping through main table get a tempLevel
value which is a primary key for table CS22. i want to go there and get the
value. help me with this.Thanks!

Dim strSQL As String
dim tempLevel as String
Dim tempSalary As Currency
Dim rstQ As Recordset
Set db = CurrentDb()
tempLevel = value ' a value will be diffrent in each row when we loop
strSQL = "SELECT [A] FROM [CS22] WHERE [(Level =
tempLevel)];"
Set rstQ = db.OpenRecordset(strSQL, dbOpenSnapshot)
tempSalary = rstQ![A]
MsgBox (strSQL)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top