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

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

  • 2 Contributors
  • 2 Replies
  • 134 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by pmark019

Dani AI

Generated

Quick summary and extra tips: the "object required" came from treating table names like objects and from ambiguous column names when selecting from two tables. Good catch by on the duplicate-column problem; the thread-solution that worked for is correct, and these additional points make that approach more robust.

When using an ADO Recordset to add a row, set the values on the recordset fields (for example with rs.Fields("ISBN").Value or the bang notation), then call rs.Update before closing the recordset. Do not try to assign using literal table identifiers (they are not VB object variables). Also ensure the recordset object was created with Set and that the connection and recordset opened without error.

For larger or set-based copies prefer a single SQL operation executed on the connection rather than row-by-row recordset work. Wrap such copies in a transaction (BeginTrans / CommitTrans) so an error can roll back the whole batch. Avoid SELECT * across multiple tables — specify the columns you need or alias them so names are not duplicated.

Quick checklist:

  • Verify ADODB reference and that your Connection is open.
  • Confirm the source SQL returns the expected rows before copying.
  • Use rs.Fields("Col").Value and call rs.Update after rs.AddNew.
  • For bulk copies, use a connection-level INSERT...SELECT and a transaction.
  • Protect against duplicates and SQL injection (use parameterized commands or check existence first).

See an example of set-based copy syntax at W3Schools: INSERT INTO SELECT and read guidance on preventing SQL injection at OWASP.

Recommended Answers

All 2 Replies

Member Avatar for BitBlt

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
etc.
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 because: n/a
Member Avatar for pmark019

Thanks...it worked..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.