Help with Inner Join in VBA 1

[BPhilb]

Hi everyone. I'm not very good at this and was hoping someone could give me some guidance. I am trying to join a field WMSDATA.TFITM.IMITM to the PAITM field in the code pasted. I can get this to work in Query form just fine but I can't get the module to recogzie this. This is how it stands now. It currently runs fine but I need to pull TFITM.IMRNUM in with the recordset. Any help would be greatly appreciated.

Private Sub Text0_AfterUpdate()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rstdb As ADODB.Recordset
Dim db As Database
Dim rstp As Recordset, rstc As Recordset
Dim var As Variant

Set cnn = New ADODB.Connection
cnn.Open "Provider=IBMDA400;Password=;User ID=;Data Source=SCHJC400;Transport Product=Client Access;SSL=DEFAULT"

Set db = CurrentDb

Set rst = New ADODB.Recordset
rst.ActiveConnection = cnn

'here's where I need help:
rst.Open "SELECT WMSDATA.TFPCDL03.PAORG, WMSDATA.TFPCDL03.PAPANM, WMSDATA.TFPCDl03.PAITM, WMSDATA.TFPCDL03.PALOT, WMSDATA.TFPCDL03.PAQTY FROM WMSDATA.TFPCDL03 WHERE WMSDATA.TFPCDL03.PAORG='JCD' AND WMSDATA.TFPCDL03.PAPANM=" & Me.Text0 & ""

Set rstp = db.OpenRecordset("tblPcl&quot

Do Until rst.EOF
rstp.AddNew
rstp(0) = rst(1)
rstp(1) = rst(2)
rstp(2) = rst(3)
rstp(3) = rst(4)
rstp(4) = rst(5)
rstp.Update
rst.MoveNext
Loop
Me.tblPcl_subform.Requery

End Sub

 

[cmmrfrds]

A couple of things, (1) you are mixing some DAO objects in with the ADO objects and (2) it is better to build the sql string in a variable and execute the variable that way you can evaluate ahead of time.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim var As Variant

Set cnn = New ADODB.Connection
cnn.Open "Provider=IBMDA400;Password=;User ID=;Data Source=SCHJC400;Transport Product=Client Access;SSL=DEFAULT"

Set rst = New ADODB.Recordset
rst.ActiveConnection = cnn

Dim sqlString as String

sqlString = "SELECT WMSDATA.TFPCDL03.PAORG, " & _
"WMSDATA.TFPCDL03.PAPANM, " & _
"WMSDATA.TFPCDl03.PAITM, " & _
"WMSDATA.TFPCDL03.PALOT, " & _
"WMSDATA.TFPCDL03.PAQTY FROM " & _
"WMSDATA.TFPCDL03 WHERE " & _
"WMSDATA.TFPCDL03.PAORG = 'JCD' AND " & _
"WMSDATA.TFPCDL03.PAPANM = " & "'" & Me.Text0 & "'"

Debug.print sqlString
OR '-- check out the sql
Msgbox sqlString

rst.Open sqlString, cnn
Do Until rst.EOF etc.....

If you use this approach, you can evaluate the sql before trying to execute and spot any errors. I bet you can figure out how to add the join using this approach.