'Can anyone tell me why the followi

[KrissyB]

'Can anyone tell me why the following code gives me a runtime error "Object Required"? I am very new to this! It gives me the error at the beginning of the If statement.

Thanks

Option Compare Database

'------------------------------------------------------------
' Proc_MacroModuleQueryTest_DONT_USE
'
'------------------------------------------------------------
Function Proc_MacroModuleQueryTest_DONT_USE()
On Error GoTo Proc_MacroModuleQueryTest_DONT_USE_Err

' 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 INVUPC!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 Function

Proc_MacroModuleQueryTest_DONT_USE_Err:
MsgBox Error$
Resume Proc_MacroModuleQueryTest_DONT_USE_Exit

End Function

 

[jebry]

Hi!

You can't reference a query that way. It is probably best to open it as a recordset and look at it that way. The way you are sending the table would send all of the data in the table. It would be better to make a query that limits the data on the supplier field and send the query instead of the table.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[KrissyB]

Hi again

OK so kinda like this?

Dim rs as New Recordset(do I have to put ADODB.Recordset?)

then

Set rs = CurrentDb.OpenRecordSet (Select Supplier, UPC from INVUPC;&quot

Then

If rs!Supplier = ..and so on?

 

[jebry]

Hi!

Yes, that is what I mean! Except you need the quote before the Select also (I'm sure that was just a typo).



Jeff Bridgham
bridgham@purdue.edu

 

[KrissyB]

I am the biggest pain, but the code is now giving me a type mismatch error???

Function Proc_MacroModuleQueryTest_DONT_USE()
On Error GoTo Proc_MacroModuleQueryTest_DONT_USE_Err

Dim RS As New ADODB.Recordset

Set RS = CurrentDb.OpenRecordset("SELECT * 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 Function

Proc_MacroModuleQueryTest_DONT_USE_Err:
MsgBox Error$
Resume Proc_MacroModuleQueryTest_DONT_USE_Exit

End Function

Eternally grateful

 

[jebry]

Hi again!

I am not overly familiar with ADO recordsets. I don't usually use them for anything I am opening from the current database. Here is the code in help to open a ADO recordset:

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

hth


Jeff Bridgham
bridgham@purdue.edu

 

[KrissyB]

Thanks again for your help..still having the hardest time with this but I guess It'll come with time.

THANKS

 

[TommieB]

A type mismatch, in this case, probably means that some of the data you are sending is not consistent with the fields that you are trying to place it in.

You did not specify exactly where in the code the error was occuring.

 

[KrissyB]

The type mismatch error is on the very first line. Could it have something to do with DAO.ADO specifics?

Chris