Use Recordset name in SQL statment within VBA

[Poduska]

I have an Input form with an AutoNumber. The user enters a new record and a popup form appears with a multi select list box. The user then selects the approporiate "SubProjects" and clicks a button to run the code below.

[bold]The idea is to create ONE line for EACH SubProject. There is no join required. [/bold]

Selected in the list box. I have something similar to this but it goes against a static predetermined table but I now need to have the users perform the List box selection.

I call a procedure which creates a Disconnected Recordset which is new to me but seems to work. I actually debug.Print the recordset values just before I run my SQL and they appear to still exist.

[bold]The problems is when I run the SQL I receive an error that JET cannot find the recordset (rst)!

'##############################
'RunTime Error '3078'
'The microsoft jet Database engine cannot find the input table 'rst'
'Make sure it exists and that the name is spelled correct.
'#######################

How do I use an existing recordset in a SQL query?
[/bold]

Private Sub cmdAssignSubProjects_Click()

Dim VarItem As Variant
Dim intNewID As Integer
Dim SQL As String

[COLOR=green]'READ UNIQUE IDENTIFIER FROM FORM [/color]
    intNewID = Forms!tblSpecNoticeMaster.intID

[COLOR=green]'CREATE RECORDSET (rst)[/color]
    Call CreateRSTfromListBox

[COLOR=GREEN]'DEBUG.PRINT CONTENTS OF RECORDSET TO MAKE SURE IT STILL EXISTS WHEN RETURNED TO THIS PROCEDURE.[/COLOR]
    rst.MoveFirst
    
    Do Until rst.EOF
        Debug.Print rst.Fields(0)
        rst.MoveNext
    Loop
[COLOR=GREEN]'2 lines are printed out so the recordset exists with two lines of data[/color]

SQL = "INSERT INTO tblVSRSubProject( intID,txtSubProject ) " _
        & "SELECT " & intNewID & ", rst.fields(0) " _
        & "FROM rst ;"
Debug.Print SQL

[COLOR=green]

When I paste the SQL from the Debug window into a blank query the RST appears like a table but without any fields.

'ERROR OCCURS HERE when I attempt to run the SQL.
'##############################
'RunTime Error '3078'
'The microsoft jet Database engine cannot find the input table 'rst'
'Make sure it exists and that the name is spelled correct.
'#######################[/color]
DoCmd.RunSQL SQL

End Sub

---------------------------------------------------
Sub CreateRSTfromListBox()

Dim varItemSel As Variant

Set rst = New ADODB.Recordset

rst.Fields.Append "txtSubProject", advarChar, 10

rst.Open

For Each varItemSel In Me.lstboxSubProject.ItemsSelected
    rst.AddNew
    rst!txtSubProject.Value = Me.lstboxSubProject.ItemData(varItemSel)
    rst.Update
Next varItemSel

rst.MoveFirst

Do Until rst.EOF
    
    Debug.Print rst.Fields(0)
    
    rst.MoveNext
Loop

End Sub

 

[randy700]

When/how do you declare the rst variable?
Is it a PUBLIC variable? If not, there will be nothing in it once the subroutine ends.


Randy

 

[Poduska]

I declare the recordset (rst) above all the code to make it global to the procedure.

I create the recordset in the bottom procedure Sub CreateRSTfromListBox() and I added the little Debug.print code before the SQL statement, which is causing the error, just to prove to myself that the recordset still existed just before I executed the SQL.

I cannot think of any way to use the multiple selections of the list box in a query to insert records into my table.

Perhaps there is a different way of looking at the problem but the recordset idea "Appeared" to be straight forward, perhaps it cannot work.

Thanks

 

[RoyVidar]

You're concatenating the name of the recordset into the string you pass to the database engine for evaluation and execution. That does not exist as an object in the database, try concatenating in the value;

[tt]SQL = "INSERT INTO tblVSRSubProject( intID,txtSubProject ) " _
& "Values (" & intNewID & ", '" & rst.fields(0) & "')"[/tt]

the recordset field seems to be declared as text, in which case the text delimiters (single quotes) are necessary, if the field in tblVSRSubProject is numeric, then remove those (the single quotes).

BTW - the result of the debug.print, you should be able to copy/paste into the SQL view of a query, and it should run. Posting it here, would also help us help you.

Roy-Vidar

 

[Poduska]

Thanks Roy-Vidar,

So if I understand you correctly I CANNOT use a recordset like a table in a SQL statement?

I guess I just assumed I could.

When I changed the SQL a bit

SQL = "INSERT INTO tblVSRSubProject( intID,txtSubProject ) " _
        & "SELECT " & intNewID & " , " & rst.Fields(0) _
        & " FROM rst ;"

I receive this Debug.Print of the SQL. It always appears to just place the first value of the recordset and not the record set field name like it would with a table.

INSERT INTO tblVSRSubProject( intID,txtSubProject ) SELECT 178 , BASE FROM rst ;

Then I receive the same message when I run the SQL

'RunTime Error '3078'
'The microsoft jet Database engine cannot find the input table 'rst'
'Make sure it exists and that the name is spelled correct.


I thought if I changed the designation of the recordset field it might act like a table field

SQL = "INSERT INTO tblVSRSubProject( intID,txtSubProject ) " _
        & "SELECT " & intNewID & " , " & rst![txtSubProject] _
        & " FROM rst ;"

But I receive the same SQL statement (shows just the first value from the recordset) and again an error. Interestingly, however when I use the actual recordset field name the SQL recognized that the field was a string and inserted the single quotes.

INSERT INTO tblVSRSubProject( intID,txtSubProject ) SELECT 178, 'BASE' FROM rst ;

I finally gave up stubbornly trying to prove RoyVidar was wrong (never happend yet) and took the advise and now this works for the small task I require.

I just loop thorugh the recordset and run the "Insert" SQL for each line of the recordset.

Private Sub cmdAssignSubProjects_Click()

Dim VarItem As Variant
Dim intNewID As Integer
Dim SQL As String

'READ UNIQUE IDENTIFIER FROM FORM
    intNewID = Forms!tblSpecNoticeMaster.intID

'CREATE RECORDSET (rst)
    Call CreateRSTfromListBox

rst.MoveFirst

Do Until rst.EOF
    SQL = "INSERT INTO tblVSRSubProject( intID,txtSubProject ) " _
            & "Values (" & intNewID & ", '" & rst.Fields(0) & "')"
    Debug.Print SQL
    DoCmd.RunSQL SQL
    rst.MoveNext
Loop

End Sub

And I receive these two SQL statements as I loop through the recordset

INSERT INTO tblVSRSubProject( intID,txtSubProject ) Values (178, 'BASE')
INSERT INTO tblVSRSubProject( intID,txtSubProject ) Values (178, 'GCC')

This populates my table with two records of two fields with the data shown in the SQL statements.

Thanks RoyVidar for the good tip using the "Values" method.

A star of course!