Type mismatch Error??? 1

[KrissyB]

If someone could explain what I am doing wrong with the result set in this code to create the type mismatch error on the first line I would be extremely grateful.
Chris

Sub Proc_MacroModuleQueryTest_DONT_USE()
On Error GoTo Proc_MacroModuleQueryTest_DONT_USE_Err

Dim RS As New ADODB.Recordset

Set RS = CurrentDb.OpenRecordset("SELECT Supplier FROM INVUPC;&quot


' This Query Clears the Table POATEST, so that we can obtain data from POA reports specific to DBQ
DoCmd.OpenQuery "QUERY_CLEARPOATEST", acNormal, acEdit
' This Query Appends data specific to DBQ from the table CHP_LOAD_POA_STATUS to POATEST table
DoCmd.OpenQuery "QUERY_APPENDCHP_LOAD_POA_STATUS", acNormal, acEdit
' This Query Appends data specific to DBQ from the table CHP_POA_STATUS to POATEST table
DoCmd.OpenQuery "QUERY_APPENDCHP_POA_STATUS", acNormal, acEdit
' Opens Query INVUPC
DoCmd.OpenQuery "QUERY_INVALTUPC", acNormal, acEdit

' Ok, these are all the queries I run to create the tables, but now I want to specify different email
' address based on the data for each publisher. For example, in hte test data there are 5 UPC's 1 goes
' to Supplier 8303, with email cbotham@indigo.com

If RS!Supplier = "8303" Then
DoCmd.OpenQuery "copyappending", acNormal, acEdit
DoCmd.SendObject acTable, "Publisher Data", "MicrosoftExcel(*.xls)", "cbotham@indigo.ca", "", "", "", "", False, ""
End If

'Will this send only the data from Publisher Data with Supplier 8303, or the whole file?

Proc_MacroModuleQueryTest_DONT_USE_Exit:
Exit Sub

Proc_MacroModuleQueryTest_DONT_USE_Err:
MsgBox Error$
Resume Proc_MacroModuleQueryTest_DONT_USE_Exit

End Sub

 

[jebry]

Hi!

You can't use CurrentDb.OpenRecordset with ADO recordsets only with DAO recordsets. So you need to declare your recordset as DAO or use the syntax for ADO:

rs.Open “Employees”, CurrentProject.Connection, adOpenKeySet, adLockOptimistic

hth


Jeff Bridgham
bridgham@purdue.edu

 

[KrissyB]

Jeff

I am still a bit confused about the ADO DOA business, I know that I believe my version will support both.

Is this line ADO or DOA

DoCmd.OpenQuery "QUERY_APPENDCHP_LOAD_POA_STATUS", acNormal, acEdit

and if I am currently using DOA how would I declare the recordset this way?

Thanks Jeff

 

[jebry]

Hi!

All that line is doing is opening the query in datasheet format, it doesn't allow you access in code to the query at all. To declare the recordset just use:

Dim rs As DAO.Recordset.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[egrant]

jebry,


You helped me get rid of my type mismatch error

THANKS!

IF anyone thinks that there query looks fine but it gives you a type mismatch error, it's because you are trying to use currentdb without declaring your recordset and database objects as DAbject first.