Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Thanked 0 Times in 0 Posts

    Adding entries from table to another using a form (Access 97)

    How do I get a drop down box in a form to populate similar columns in table 1 to similar columns in table 2 and save the information in table 2? Just by choosing one filed in table 1.

    I need to create redundant information from Table 1 to Table 2. Building a query that joins Table 1 and Table 2 would be the way I normally would go but the data associated with the master number keeps changing over time due to companies being bought out and merged. The original data is filled by company name it was at that time the data was entered.

    I am trying to use the master number (cage code) associated with the company as the drop down choice because it has the least chance of error.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Adding entries from table to another using a form (Access 97)

    You could use the AfterUpdate event of the dropdown list, but I would recommend using Onclick event of a command button instead. After all, users make mistakes - it's easy to click the wrong item in a dropdown list (I know I do that a lot!)

    The code could look like this - you'll have to substitute the appropriate field names, table names and control name:

    <img src=/w3timages/blueline.gif width=33% height=2>
    Dim strSQL As String

    strSQL = "INSERT INTO [tblTable2] ([Field1], [Field2], [Field3]) " & _
    "SELECT [Field1], [Field2], [Field3] FROM [tblTable1]" & _
    "WHERE [cage code] = " & [comboboxname]

    CurrentDb.Execute strSQL
    <img src=/w3timages/blueline.gif width=33% height=2>

    This assumes that the master number is numeric. If it is a text field, you should enclose the value of the combo box in quotes. To do this in code, you can use Chr(34):

    "WHERE [cage code] = " & Chr(34) & [comboboxname] & Chr(34)

Posting Permissions

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