Broken VBA Code 2

[sbdeaver]

I have inherited a database with VBA and it is coming up with an error message I can not fix. I'm hoping someone will be able to help me. I'm getting a 'Type Mismatch' error at the 'Set rsSC' line. I'm not sure what information you need to help fix it. Thank you -Sharon

Public Function getSO()
DoCmd.SetWarnings False


Dim strSQL As String, strORG As String
Dim intDEPT As Integer, intCLASS As Integer, intSCLASS As Integer
Dim rsSC As Recordset
Dim rsORG As Recordset

Set rsSC = CurrentDb.OpenRecordset("tblSC")
Set rsORG = CurrentDb.OpenRecordset("tblStoreOrg")


DoCmd.RunSQL ("DELETE * FROM tblMDC_SO;")

With rsORG
.MoveFirst
Do Until .EOF
strORG = ![STR_TXT]
With rsSC
.MoveFirst
Do Until .EOF
lngDEPT = ![MER_DEPT_NBR]
lngCLASS = ![MER_CLASS_NBR]
lngSCLASS = ![MER_SUB_CLASS_NBR]
strSQL = "INSERT INTO tblMDC_SO "
strSQL = strSQL & "( CRT_TS, STR_NBR"
strSQL = strSQL & ", MER_DEPT_NBR, MER_CLASS_NBR"
strSQL = strSQL & ", MER_SUB_CLASS_NBR, ORD_QTY"
strSQL = strSQL & ", ORD_COST_AMT, ORD_RETL_AMT"
strSQL = strSQL & ", CUST_ORD_NBR ) "
strSQL = strSQL & "SELECT PRHDW_SO_MER.CRT_TS"
strSQL = strSQL & ", PRHDW_SO_MER.STR_NBR"
strSQL = strSQL & ", PRHDW_SO_MER.MER_DEPT_NBR"
strSQL = strSQL & ", PRHDW_SO_MER.MER_CLASS_NBR"
strSQL = strSQL & ", PRHDW_SO_MER.MER_SUB_CLASS_NBR"
strSQL = strSQL & ", PRHDW_SO_MER.ORD_QTY"
strSQL = strSQL & ", PRHDW_SO_MER.ORD_COST_AMT"
strSQL = strSQL & ", PRHDW_SO_MER.ORD_RETL_AMT"
strSQL = strSQL & ", PRHDW_SO_MER.CUST_ORD_NBR"
strSQL = strSQL & "FROM PRHDW_SO_MER "
strSQL = strSQL & "WHERE PRHDW_SO_MER.STR_NBR='" & strORG & "' "
strSQL = strSQL & "AND PRHDW_SO_MER.MER_DEPT_NBR=" & lngDEPT & " "
strSQL = strSQL & "AND PRHDW_SO_MER.MER_CLASS_NBR=" & lngCLASS & " "
strSQL = strSQL & "AND PRHDW_SO_MER.MER_SUB_CLASS_NBR=" & lngSCLASS & ";"
Debug.Print ("'" & strORG & "' & " & lngDEPT & " & " & lngCLASS & " & " & lngSCLASS & "")
DoCmd.RunSQL strSQL
.MoveNext
Loop
End With
.MoveNext
Loop
End With

rsORG.Close
rsSC.Close

DoCmd.SetWarnings True
End Function

 

[rjoubert]

Trying dim'ing your rs variables as DAO.Recordset.

Dim rsSC As DAO.Recordset
Dim rsORG As DAO.Recordset

 

[Remou]

Check that you have a reference to the Microsoft DAO x.x Object Library, make sure it is as far up the list of references as it can go, and, finally, explicitly reference the library in your variables:
[tt]Dim rsSC As DAO.Recordset[/tt]

 

[sbdeaver]

Thank you for the help. Am I supposed to delete the two lines with "Dim rsSC As Recordset | Dim rsORG As Recordset" and replace with what you indicated? If so, I get the following error:

Compile error:

User-defined type not defined

Thanks,
Sharon

 

[sbdeaver]

Thanks, but I don't know what you mean to do:

"Check that you have a reference to the Microsoft DAO x.x Object Library, make sure it is as far up the list of references as it can go, and, finally, explicitly reference the library in your variables:
Dim rsSC As DAO.Recordset

 

[Remou]

Go to the code window, then Tolls->References. You will find a list of references. Scroll down until you see Microsoft DAO x.x Object Library (x.x is probably 3.6). Tick the little box.

 

[Remou]

Oops, Tools->References

 

[sbdeaver]

Thank you. I fixed that, but now I'm getting:

Run-time error '3075'

Syntax error (missing operator) in query expression
'PRHDW_SO_MER.CUST_ORD_NBRFROM PRHDW_SO_MER WHERE
PRHDW_SO_MER.STR_NBR = '0105' AND
PRHDW_SO_MER.MER_DEPT_NBR = 28 AND
PRHDW_SO_MER.MER_CLASS_NBR = 8 AND
PRHDW_SO_MER.MER_SUB_CLASS_NBR = 60'.

I do know that means the bottom four lines are working correctly...

 

[randy700]

Change this:
strSQL = strSQL & ", PRHDW_SO_MER.CUST_ORD_NBR"
strSQL = strSQL & "FROM PRHDW_SO_MER "

to this:
strSQL = strSQL & ", PRHDW_SO_MER.CUST_ORD_NBR "
strSQL = strSQL & "FROM PRHDW_SO_MER "

The only change is to add a space at the end of the first line.

Randy

 

[sbdeaver]

Yeah! It works. Thanks so much!!!!