Accessing Temporary Table Created with A SQL SELECT Query

[psimpson]

In MS Access 2007 (VBA) a select query statement is utilized to create a temporary table with the same data. I want to access the temporary table to verify an id field, but it appears that the data is missing and the system does not recognize the variable. I double checked that the name is spelled correctly. However, every time I run the application and select the respective button option, an error message is returned that the 'qualifier is invalid'.

Any suggestions are greatly appreciated?

Thanks.

 

[Beetle]

Can you post your code so we can see what you've done?

 

[psimpson]

Here is my current code:

Dim tmpNumber As String
Dim tmpAddlTableRecords As String
Dim strSQL As String

tmpNumber = Me.NUMBER

If tmpNumber = Me.NUMBER Then
Dim tmpReg_Num As Long <The same error if datatype is 'String'>

tmpAddlTableRecords = "SELECT * FROM Table WHERE Table.NUMBER = 'tmpNumber';"

tmpReg_Num = tmpAddlTableRecords.REG_NUMBER <It is here that the identifier is not recognized--tmpAddlTableRecords.'>
strSQL = "DELETE * FROM Table WHERE Table.NUMBER = 'tmpNumber';"
DoCmd.RunSQL strSQL

 

[Beetle]

First, what is the actual data types of the fields in the underlying table, and do you have a field named NUMBER as well as a field named REG_NUMBER in the table?

Second, it appears that all you want to do is delete records from your table where the value in NUMBER is equal to the value in NUMBER on your form? If that's the case then several of your code lines are not even necessary.

A couple more comments in red below;

Dim tmpNumber As String
Dim tmpAddlTableRecords As String
Dim strSQL As String

tmpNumber = Me.NUMBER [COLOR="Red"]<< You set tmpNumber equal to Me.NUMBER[/COLOR]

If tmpNumber = Me.NUMBER Then [COLOR="red"]<< So this should always be true
                                 unless Me.NUMBER is Null[/COLOR]

Dim tmpReg_Num As Long <The same error if datatype is 'String'>

tmpAddlTableRecords = "SELECT * FROM Table WHERE Table.NUMBER = 'tmpNumber';"
[COLOR="red"]^^You create a Select statement here, but you don't do anything with it
  You would need to open a record set using the statement[/COLOR]

tmpReg_Num = tmpAddlTableRecords.REG_NUMBER <It is here that the identifier is not recognized--tmpAddlTableRecords.'>
[COLOR="red"]^^You're trying to retrieve a value from a record set that doesn't exist
  (because you never opened one)[/COLOR]

strSQL = "DELETE * FROM Table WHERE Table.NUMBER = 'tmpNumber';"
[COLOR="red"]^^ All you're really doing here deleting records where NUMBER is equal to Me.NUMBER[/COLOR]

DoCmd.RunSQL strSQL