Save record from linked table to local table by selecting ID

OK, so maybe I am making this harder than I should, but I want to save all the information from one record from a linked table by selecting an ID to a local table.
Example would be an Associates linked table with AssociateID, First, Last, Group, JobType
A combo box to select the AssociateID by First and Last name where the form is based on LocAssociate table.
Upon changing the combo box, all the associate data would be entered into the Local table LocAssociate.

The Combo box is fine, and the row source to select the associateID by the Name is fine. But how can I save all the data for the correct AssociateID into the LocAssociate table?

Thanks in Advance
Randy

Not sure why I did not think of this, but I could delete the current record, and use an SQL statement to select the ID in the combo box and add that record to the LocAssociate table.
I can run a make table query and it adds the data fine, but when I try to do a dbs.execute "SQL" statement It does not work.
I get the error "Object Variable or with Block not set" (error 91)
dbs.execute "DELETE FROM LocAssociate"
dbs.execute "Insert INTO LocAssociate SELECT Associates.AssociatesID, Associates.FirstName, Associates.LastName, Associates.Group, Associates.JobType INTO LocAssociate FROM Associates WHERE (((Associates.AssociatesID)=[Forms]![Main Form]![cboName]))

You need to assign the "dbs" variable. Or, you could replace it like I do with "Currentdb()" followed by ".execute "Delete * From LocAssociate;"

Quote:

---

Originally Posted by Thantos Hess

Not sure why I did not think of this, but I could delete the current record, and use an SQL statement to select the ID in the combo box and add that record to the LocAssociate table.
I can run a make table query and it adds the data fine, but when I try to do a dbs.execute "SQL" statement It does not work.
I get the error "Object Variable or with Block not set" (error 91)
dbs.execute "DELETE FROM LocAssociate"
dbs.execute "Insert INTO LocAssociate SELECT Associates.AssociatesID, Associates.FirstName, Associates.LastName, Associates.Group, Associates.JobType INTO LocAssociate FROM Associates WHERE (((Associates.AssociatesID)=[Forms]![Main Form]![cboName]))

---

Try:

Code:

---

CurrentDB.Execute "DELETE LocAssociate.* FROM LocAssociate;"

---

Ok, changed the code to CurrentDB.Execute and it goes through, the new error that comes up is a runtime error 3000 reserved error(-3002)

Any ideas?

Thanks again. Also, if you know of anywhere I can get some good information of the "Execute" commands I would appreciate that as well. I am trying to get better and smarter with my databases.

Basically the .Execute runs action queries (SQL) passed directly to JET/ACE. This is very similar to Docnmd.RunSQL

With the .Execute This mean that you can not include form references in the SQL. This is also true for opening a recordset with ADO or DAO.

Try this if [Forms]![Main Form]![cboName] retuens a alpha data type:

Code:

---

dbs.execute "Insert INTO LocAssociate SELECT Associates.AssociatesID, Associates.FirstName, Associates.LastName, Associates.Group, Associates.JobType INTO LocAssociate FROM Associates WHERE (((Associates.AssociatesID)=""" &  [Forms]![Main Form]![cboName] & """ ))

---

if numeric

Code:

---

dbs.execute "Insert INTO LocAssociate SELECT Associates.AssociatesID, Associates.FirstName, Associates.LastName, Associates.Group, Associates.JobType INTO LocAssociate FROM Associates WHERE (((Associates.AssociatesID)=" &  [Forms]![Main Form]![cboName] & " ))

---

Note: You can use form references with Docmd.RunSQL.

Example:

Code:

---

' empty the table
Docmd.RunSQL "DELETE LocAssociate.* FROM LocAssociate"

' append data
Docmd.RunSQL "Insert INTO LocAssociate SELECT Associates.AssociatesID, Associates.FirstName, Associates.LastName, Associates.Group, Associates.JobType INTO LocAssociate FROM Associates WHERE (((Associates.AssociatesID)=[Forms]![Main Form]![cboName]))

---

*TIP: Use comments in your code

Thanks for the fast response.

I am still receiving the same error. The combo box uses the numeric ID. I tried also using the Docmd.RunSQL to see if it was a problem with getting the information from the combo box, but this was unsuccessful and returned the same error. Is there something missing in the syntax of the SQL statement? I noticed that when you put a query into the SQL form there is a semicolon at the end of the text... I have tried to add that, but without success.
I would prefer to use the Execute function because I do not want the user to receive any notices about the table updating.

Thanks,
Randy

I do think it is an issue with you SQL statement for the append query.

Normally you use something like:

Code:

---

Insert INTO TableName (field1, field2, ....) Select ....

---

I would recommend that you use Access's Query Designer to create the query. Switch to SQL view, then copy and paste the SQL into you VBA code. You can make any required changes.