Private Sub cmdAddCart_Click()
Set globRSRecordset2 = New ADODB.Recordset
    globRSRecordset2.Open "SELECT * FROM tblBooks, tblTemporary WHERE tblBooks.Title = '" & Trim(Me.txtTitle) & "'", strVar, adOpenKeyset, adLockOptimistic
    With globRSRecordset2
             tblBooks.ISBN = tblTemporary.ISBN
             tblTemporary.UserID = ID
             !DateReserve = Date
             !Notes = "Fair"
             MsgBox "Added to Cart", vbInformation, "Add to Cart"
    End With

I get ang error of "object required" on tblBooks.ISBN = tblTemporary.ISBN

7 Years
Discussion Span
Last Post by pmark019

There are two things going on here that you may want to look at, but only one has to do with your original question. I'll answer that one first.

When you specify "Select *" you get as column names ONLY the column name. So if I have table1 and table2, each with column names Col1, Col2, Col3, and do a "select * from ..." you get:
Col1 Col2 Col3 Col1 Col2 Col3
In order to differentiate them you have to explicitly select the columns. like so:

select table1.col1 as Col1, table1.col2 as Col2, table1.col3 as Col3, table2.col1 as Col1_A, table2.col2 as Col2_A, table2.col3 as Col3_A from table1, table2 where...

That should handle your column name issue.
Now, what you didn't ask for...there are no join criteria between your two tables. I'm fairly certain you didn't intend this. You should alter your SQL statement to join the tables on some key so you can copy the data.

However, if you are really just copying data and it doesn't exist in tblTemporary, you should just use

insert into tblTemporary (Col1, Col2, Col3) select col1, col2, col3 from tblBooks

and then just execute the statement.
Or, if you insist on doing it with recordsets, you should create two recordsets (one for tblBooks and one for tblTemporary) and copy the data that way.

Hope this helps. Good luck!

Edited by BitBlt: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.