Results 1 to 8 of 8

Thread: Save record from linked table to local table by selecting ID

  1. #1
    Join Date
    Jun 2010
    Location
    Kansas, USA
    Posts
    15

    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

  2. #2
    Join Date
    Jun 2010
    Location
    Kansas, USA
    Posts
    15
    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]))
    Last edited by Thantos Hess; 01-04-2011 at 09:42 AM.

  3. #3
    Join Date
    May 2006
    Posts
    407
    You need to assign the "dbs" variable. Or, you could replace it like I do with "Currentdb()" followed by ".execute "Delete * From LocAssociate;"

  4. #4
    Join Date
    Mar 2006
    Location
    Oklahoma City, OK
    Posts
    184
    Quote Originally Posted by Thantos Hess View Post
    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;"
    Boyd Trimmell aka HiTech Coach
    Microsoft MVP - Access Expert
    [SIGPIC][/SIGPIC]
    Office Programming 25+ years as a Software Developer specializing in:
    Business Process Management
    Accounting/Inventory Control
    Customer Relations Management (CRM)
    Electronic Data Interchange (EDI)

  5. #5
    Join Date
    Jun 2010
    Location
    Kansas, USA
    Posts
    15
    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.

  6. #6
    Join Date
    Mar 2006
    Location
    Oklahoma City, OK
    Posts
    184
    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
    Boyd Trimmell aka HiTech Coach
    Microsoft MVP - Access Expert
    [SIGPIC][/SIGPIC]
    Office Programming 25+ years as a Software Developer specializing in:
    Business Process Management
    Accounting/Inventory Control
    Customer Relations Management (CRM)
    Electronic Data Interchange (EDI)

  7. #7
    Join Date
    Jun 2010
    Location
    Kansas, USA
    Posts
    15
    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

  8. #8
    Join Date
    Mar 2006
    Location
    Oklahoma City, OK
    Posts
    184
    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.
    Boyd Trimmell aka HiTech Coach
    Microsoft MVP - Access Expert
    [SIGPIC][/SIGPIC]
    Office Programming 25+ years as a Software Developer specializing in:
    Business Process Management
    Accounting/Inventory Control
    Customer Relations Management (CRM)
    Electronic Data Interchange (EDI)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •