SQL Select in VBA to Open Recordset

[Canopius]

Product: Access 2003
Goal: To open a recordset via VBA with a specific record selected. The
'WHERE' needs to use the value from recordset#1 to find a code in recordset#2.
Tables: Two tables used; SOI and VCD. SOI has a field named 'S1' that
contains a 5 character string value like "BUILD" or "CONTE", and a field
named 'S1C' that needs to have a code looked up from a second table (VCD in
this case). VCD contains two fields, 'Value' which contains a 5 character
string (that will match the values in 'SOI.S1') and 'VC' which has a single
chararacter string that serves as a code for the string in 'Value'. For
example, "BUILD" would have "B" for a 'VC'.

Need to open the 'VCD' recordset with the VCD.VALUE set to the string in
SOI.S1, and then assign the 'VCD.VC' value to 'SOI.S1C'... update this record
and move on to the next record in SOI and repeat the process.

PROBLEM: I can not construct the SQL statement for VBA so that it selects
the proper record from 'VCD.Value'. Tried numerous syntax but can't get it.
I'm sure it is simple. In effect we are doing an Excel 'VLookup' function in
Access via VBA.

Code is below... all works except 'SQL2' select.

Any assistance would be greatly appreciated. Thanks.


Private Sub Toggle0_Click()

Dim CON1 As Object
Dim rs1 As Object
Dim sql1 As String

Dim CON2 As Object
Dim rs2 As Object
Dim sql2 As String

Set CON1 = Application.CurrentProject.Connection
sql1 = "soi"
Set rs1 = CreateObject("ADODB.Recordset")
rs1.Open sql1, CON1, 1, 3

Set CON2 = Application.CurrentProject.Connection
Set rs2 = CreateObject("ADODB.Recordset")

rs1.MoveFirst

Do Until rs1.EOF
sql2 = "select vc from vcd WHERE [rs2]![Value]=[rs1]![S1]"

rs2.Open sql2, CON2, 1, 3
rs1!S1C = rs2!VC

rs1.Update
rs2.Close
rs1.MoveNext
Loop

End Sub

 

[Douglas J Steele]

The query has no knowledge of what rs2 and rs1 are.

Try:

sql2 = "select vc from vcd WHERE [" & [rs2]![Value] & "]='" & [rs1]![S1] &
"'"

Exagerated for clarity, that's:

sql2 = "select vc from vcd WHERE [ " & [rs2]![Value] & " ]= ' " &
[rs1]![S1] & " ' "

(the reason for the [ and ] is just in case what's in rs2!Value contains
blanks.)

Other alternatives, of course, would be to scrap the second recordset and
use DLookup to get the value, or (even better) write an UPDATE query to
update the first table. (It's almost always more efficient to use SQL than
to loop through a recordset)

 

[Canopius]

Thank you for the quick and effective response. Your suggestions worked
properly... I went with the Update Query.

Very much appreciated.

The query has no knowledge of what rs2 and rs1 are.

Try:

sql2 = "select vc from vcd WHERE [" & [rs2]![Value] & "]='" & [rs1]![S1] &
"'"

Exagerated for clarity, that's:

sql2 = "select vc from vcd WHERE [ " & [rs2]![Value] & " ]= ' " &
[rs1]![S1] & " ' "

(the reason for the [ and ] is just in case what's in rs2!Value contains
blanks.)

Other alternatives, of course, would be to scrap the second recordset and
use DLookup to get the value, or (even better) write an UPDATE query to
update the first table. (It's almost always more efficient to use SQL than
to loop through a recordset)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Product: Access 2003
Goal: To open a recordset via VBA with a specific record selected. The
'WHERE' needs to use the value from recordset#1 to find a code in recordset#2.
Tables: Two tables used; SOI and VCD. SOI has a field named 'S1' that
contains a 5 character string value like "BUILD" or "CONTE", and a field
named 'S1C' that needs to have a code looked up from a second table (VCD in
this case). VCD contains two fields, 'Value' which contains a 5 character
string (that will match the values in 'SOI.S1') and 'VC' which has a single
chararacter string that serves as a code for the string in 'Value'. For
example, "BUILD" would have "B" for a 'VC'.

Need to open the 'VCD' recordset with the VCD.VALUE set to the string in
SOI.S1, and then assign the 'VCD.VC' value to 'SOI.S1C'... update this record
and move on to the next record in SOI and repeat the process.

PROBLEM: I can not construct the SQL statement for VBA so that it selects
the proper record from 'VCD.Value'. Tried numerous syntax but can't get it.
I'm sure it is simple. In effect we are doing an Excel 'VLookup' function in
Access via VBA.

Code is below... all works except 'SQL2' select.

Any assistance would be greatly appreciated. Thanks.


Private Sub Toggle0_Click()

Dim CON1 As Object
Dim rs1 As Object
Dim sql1 As String

Dim CON2 As Object
Dim rs2 As Object
Dim sql2 As String

Set CON1 = Application.CurrentProject.Connection
sql1 = "soi"
Set rs1 = CreateObject("ADODB.Recordset")
rs1.Open sql1, CON1, 1, 3

Set CON2 = Application.CurrentProject.Connection
Set rs2 = CreateObject("ADODB.Recordset")

rs1.MoveFirst

Do Until rs1.EOF
sql2 = "select vc from vcd WHERE [rs2]![Value]=[rs1]![S1]"

rs2.Open sql2, CON2, 1, 3
rs1!S1C = rs2!VC

rs1.Update
rs2.Close
rs1.MoveNext
Loop

End Sub