copy a record from 1 table to another via combo box selection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a read only Excel (table1) linked as a data source for my combo box, I
wanted to be able to get the rest of the field data to another table (table2)
by selecting only 1 field value using a form. Example: select one Social
Security number from the combo box and the rest of the personal information
will populate the form. In the event, it kinda downloads the info from table
1 to table2 using the combo box. Is this possible?
 
To populate the rest of the controls in the form, make the Before Update
event of the combo fire a macro with as many SetValue actions as the
controls you want updated, with arguments:
Item: [ControlNameToBeUpdated]
Expression: DLookup("[FieldName]","table1","[SSN]='" & [ComboName] & "'")
Change field, table and control names as required.
Note: the above assumes the SSN field is text; if it's numeric, the
expression should be:
Expression: DLookup("[FieldName]","table1","[SSN]=" & [ComboName])
Needless to say, the same can be accomplished through code like:

Me.ControlName = DLookup("[FieldName]","table1","[SSN]='" & Me.[ComboName]
& "'")
or
Me.ControlName = DLookup("[FieldName]","table1","[SSN]=" & Me.[ComboName])

Now, to get records transferred to another table: Make a query on the linked
table, change it to an Append query on the target table, and in the
criterion line under the SSN field type:
Forms![FormName]![ComboName]
(change to the right names)
When you run the query, it will append record(s) based on the current
selection in the combo. Use the command button wizard to add a button on
your form to open (run) the query, so you choose the SSN in the combo, click
on the button and it's done!

HTH,
Nikos
 
I followed the instruction but skipped on the first part as I cant seem to
make the SetValue actions to work properly... Now the big problem is when the
Append Query runs, it starts to overwrite the previous records that were
transferred. It pops so many error messages like "you are about to append 12
rows" even though im selecting only 1 SSN. Other error message was "..cant
append all the records in the append query, set 0 fields to null due to type
conversion failures, key violations,
lock violations, violation rules violation" I think I violated it so much
that it actually says I violated even a violation :D

The setup is:
Tables :
Table1 (linked xls table)
Table2
Table3

Queries:
Table1toTable2 AppendQuery criteria: (Forms![Main]![ComboSSN])
Table2andTable3 Query

Form:
Main (Data Source: Table2andTable3 Query)

The Objective is to transfer a record from Table 1 to Table 2 using a Social
Security Number combo box. The receiving Table2 is an exact copy of Table1
except that it is blank, when i select SSN from the combo box (which is
getting the data from Table1), I want to be able to transfer the other
records that go with the SSN to Table 2. Right now it is doing it but It has
to go through a lot of error messages and requires closing the form and
re-open to see the result.


Nikos Yannacopoulos said:
To populate the rest of the controls in the form, make the Before Update
event of the combo fire a macro with as many SetValue actions as the
controls you want updated, with arguments:
Item: [ControlNameToBeUpdated]
Expression: DLookup("[FieldName]","table1","[SSN]='" & [ComboName] & "'")
Change field, table and control names as required.
Note: the above assumes the SSN field is text; if it's numeric, the
expression should be:
Expression: DLookup("[FieldName]","table1","[SSN]=" & [ComboName])
Needless to say, the same can be accomplished through code like:

Me.ControlName = DLookup("[FieldName]","table1","[SSN]='" & Me.[ComboName]
& "'")
or
Me.ControlName = DLookup("[FieldName]","table1","[SSN]=" & Me.[ComboName])

Now, to get records transferred to another table: Make a query on the linked
table, change it to an Append query on the target table, and in the
criterion line under the SSN field type:
Forms![FormName]![ComboName]
(change to the right names)
When you run the query, it will append record(s) based on the current
selection in the combo. Use the command button wizard to add a button on
your form to open (run) the query, so you choose the SSN in the combo, click
on the button and it's done!

HTH,
Nikos

sunberries said:
I have a read only Excel (table1) linked as a data source for my combo box, I
wanted to be able to get the rest of the field data to another table (table2)
by selecting only 1 field value using a form. Example: select one Social
Security number from the combo box and the rest of the personal information
will populate the form. In the event, it kinda downloads the info from table
1 to table2 using the combo box. Is this possible?
Click to expand...
Click to expand...
 
Hmmm... definitely something wrong here (most likely several things), but
hard to put my finger on it under the circumstances. If you want, you are
welcome to zip and mail me the database and the Excel file to have a look.
Send to nyannaco at in dot gr.

Nikos

sunberries said:
I followed the instruction but skipped on the first part as I cant seem to
make the SetValue actions to work properly... Now the big problem is when the
Append Query runs, it starts to overwrite the previous records that were
transferred. It pops so many error messages like "you are about to append 12
rows" even though im selecting only 1 SSN. Other error message was "..cant
append all the records in the append query, set 0 fields to null due to type
conversion failures, key violations,
lock violations, violation rules violation" I think I violated it so much
that it actually says I violated even a violation :D

The setup is:
Tables :
Table1 (linked xls table)
Table2
Table3

Queries:
Table1toTable2 AppendQuery criteria: (Forms![Main]![ComboSSN])
Table2andTable3 Query

Form:
Main (Data Source: Table2andTable3 Query)

The Objective is to transfer a record from Table 1 to Table 2 using a Social
Security Number combo box. The receiving Table2 is an exact copy of Table1
except that it is blank, when i select SSN from the combo box (which is
getting the data from Table1), I want to be able to transfer the other
records that go with the SSN to Table 2. Right now it is doing it but It has
to go through a lot of error messages and requires closing the form and
re-open to see the result.


Nikos Yannacopoulos said:
To populate the rest of the controls in the form, make the Before Update
event of the combo fire a macro with as many SetValue actions as the
controls you want updated, with arguments:
Item: [ControlNameToBeUpdated]
Expression: DLookup("[FieldName]","table1","[SSN]='" & [ComboName] & "'")
Change field, table and control names as required.
Note: the above assumes the SSN field is text; if it's numeric, the
expression should be:
Expression: DLookup("[FieldName]","table1","[SSN]=" & [ComboName])
Needless to say, the same can be accomplished through code like:

Me.ControlName = DLookup("[FieldName]","table1","[SSN]='" & Me.[ComboName]
& "'")
or
Me.ControlName = DLookup("[FieldName]","table1","[SSN]=" & Me.[ComboName])

Now, to get records transferred to another table: Make a query on the linked
table, change it to an Append query on the target table, and in the
criterion line under the SSN field type:
Forms![FormName]![ComboName]
(change to the right names)
When you run the query, it will append record(s) based on the current
selection in the combo. Use the command button wizard to add a button on
your form to open (run) the query, so you choose the SSN in the combo, click
on the button and it's done!

HTH,
Nikos

sunberries said:
I have a read only Excel (table1) linked as a data source for my combo box, I
wanted to be able to get the rest of the field data to another table (table2)
by selecting only 1 field value using a form. Example: select one Social
Security number from the combo box and the rest of the personal information
will populate the form. In the event, it kinda downloads the info from table
1 to table2 using the combo box. Is this possible?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top