Object Required message

[gnu@this]

This is probably so simple for someone out there, but I have spent a lot of time on it. I am trying to set a variable to the value of the single field in a one line table. I get an Object Required message. I have reviewed many posts, etc., but can't figure out what I am doing wrong. Any help would be greatly appreciated!

Code here:

Option Compare Database
Function Macro1()
'On Error GoTo Macro1_Err

Dim strTOP1 As String
Dim DBS As Database
Set DBS = CurrentDb

DoCmd.OpenQuery "qMT_OAC_PARTS", acViewNormal, acEdit

'Do Until ISNULL strTOP1

DoCmd.OpenQuery "qMT_TOP1", acViewNormal, acEdit '(Generates one value, TOP1)

strTOP1 = TABLES![TOP1].[MK].Value '(Error occurs here)

DoCmd.OpenQuery "qMT_OAC_PARTS2", acViewNormal, acEdit

'DoCmd.TransferSpreadsheet acExport, 8, "OAC_PARTS2", "P:\Development\zLinkedFiles\XXXXXXX\REPORTS\REPORT.XLS", False, "strTOP1!A1:Z65536"

DoCmd.OpenQuery "qDEL_OAC_VALID", acViewNormal, acEdit

'Loop

Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function

Eventually I will have the results of the 2nd query export to Excel, and then loop until the original file is exhausted. Right now, I need to fix this problem.

Thanks for looking at this!

 

[MSAccessRookie]

This is probably so simple for someone out there, but I have spent a lot of time on it. I am trying to set a variable to the value of the single field in a one line table. I get an Object Required message. I have reviewed many posts, etc., but can't figure out what I am doing wrong. Any help would be greatly appreciated!

Code here:

Option Compare Database
Option Explicit

Function Macro1()
'On Error GoTo Macro1_Err
.
.
.

It looks like you might have undefined variables. To get some additional information, try the following:

• Uncomment the Error Handling code (Marked in Green)
• Add the line marked in red above, and compile the code, correcting any errors that are reported.

Note: If pbaldy is right about what you are trying to do, then his advice is also correct. The two steps I suggested are still a good idea.

 

[pbaldy]

You can't refer to a table that way. You can try a DLookup() or open a recordset to get the value.

 

[boblarson]

strTOP1 = TABLES![TOP1].[MK].Value '(Error occurs here)

Well, not surprising - you can't get a value by referencing it that way. You would need to use a DLookup with a query or a recordset.

Also, your 'Do Until ISNULL strTOP1 will not work because strTop1 will never be null. String variables are never null. They can be empty strings ("") but not null.

 

[gnu@this]

Gentlemen, thank you, all your suggestions were spot on. The variable updates, works fine, now I just have to get the transfer command and loop to work!

Thanks for your help.

 

[gnu@this]

Well, I appear to have signed off too soon. Now I get an Error 2498, wrong data type when I attempt to transfer the file to an Excel spreadsheet. The code is:

Option Compare Database
Option Explicit

Function Macro1()
On Error GoTo Macro1_Err

Dim strTOP1 As String
Dim dbs As Database
Dim strFILENAME As String
Dim strFILEPATH As String

Set dbs = CurrentDb
strFILENAME = "OACPARTS2"
strFILEPATH = "P:\Development\zLinkedFiles\REPORTS\XXX_REPORT.XLS"

DoCmd.OpenQuery "qMT_OAC_PARTS", acViewNormal, acEdit

'Do Until strTOP1 = ""

DoCmd.OpenQuery "qMT_TOP1", acViewNormal, acEdit

strTOP1 = DLookup("[MK]", "TOP1", "")

DoCmd.OpenQuery "qMT_OAC_PARTS2", acViewNormal, acEdit

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strFILENAME, strFILEPATH, "", "", "" (Fails here)

DoCmd.OpenQuery "qDEL_OAC_VALID", acViewNormal, acEdit

'Loop

Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function



Ultimately I hope to have it export to a spreadsheet and insert a new tab named from a variable. However, at present I can't get this one to work. Any hints on what I am doing wrong? Thanks for looking!

 

[boblarson]

the only thing that looks wrong on that line is you don't include the other parameters if they are not passed. So, remove the part in red:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strFILENAME, strFILEPATH, "", "", ""

 

[gnu@this]

Thank you! Since the Auto Fill in VBA showed them, I thought I had to use them. I appreciate your time!

 

[boblarson]

If you were using the last one and not the ones in between you would just use commas and nothing in between - so just as an example:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strFILENAME, strFILEPATH, , , True

 

[gnu@this]

Thanks for taking the time, you are making this easier!