Copy field between tables during data entry

[Steve Lowe]

Hi,

I have 2 tables one called accounts and one called transactions
The accounts table has a primary key called AccountID which is used as
a one to many relationship with the transactions table

I have a form called Add_Transaction which uses the Transactions table
as it's focus and simply adds records into the transaction table, the
1st field on the form is a lookup to the accounts table so the
transaction belong to a certain account.

In the accounts table there is a field called ValidationNumber which
is a text field length 10

This number can change from time to time and what I need to accomplish
is have a field in the transactions table populate itself with what
the validation number was on the account when the transaction was
added.

I'm having problems getting this to work

I have a button on the form called update which uses some SQL commands
to update a balance field and date of last transaction on the accounts
table but I can't seem to get the validationnumber field in the
transactions table updated at the same time.

Any suggestions

Regards

Steve.


- Steve Lowe
- E-Mail : (e-mail address removed)
- Before Replying Remove .NO.SPAM
- UK Resident although my e-mail address is usa.net

 

[tina]

the

1st field on the form is a lookup to the accounts table so the
transaction belong to a certain account.

so you're using a combo box *control* on the form, with the accounts table
as its' RowSource, correct? (btw, if you have a Lookup *field* in the
transactions *table*, i strongly recommend that you get rid of the Lookup in
the table. for more information, see
http://home.att.net/~california.db/tips.html#aTip8.) open the form in Design
view, and click on the combo box control to select it. in the Properties
box, click on the RowSource property "line", and then click the Build (...)
button at the right. in the grid, add the ValidationNumber field. close, and
save at the prompt. increase the ColumnCount property of the combo box
control by 1, to include the field you just added to the RowSource. also
change the ColumnWidths property to include the additional field, setting
the width of the new column to zero, as

....; 0"

suggest you read up on combo box controls in Access Help, so you understand
how the various properties work and how to use them effectively.

now, when you open the combo box control's "droplist" in Form view, you do
not see the ValidationNumber column, *but the data in it is available to you
programmatically*.

i'll assume that you've added a validation field to the transactions table.
i'll call it TransValNum. in the Add_Transaction form, add the following
code to the combo box control's AfterUpdate event procedure, as

If Not IsNull(Me!ComboBoxName) Then
Me!TransValNum = Me!ComboBoxName.Column(n)
Else
Me!TransValNum = Null
End If

replace "n" with the index number of the column that you added to the combo
box control (as described above). note that the index is zero-based, so the
first column in the combo box control is 0, the second column is 1, the
third column is 2, etc. if you don't know how to create an event procedure,
go to http://home.att.net/~california.db/instructions.html and click the
CreateEventProcedure link.

hth

 

[Steve Lowe]

Hi Tina,

Thanks very much, got it working OK - followed your suggestion and
then also tried putting your example code in with my own code in my
update button and got it working.

I can now see the principles of how the fields from the accounts table
are available through the combo box, it does make sense really but
just needed a bit of help in looking in the right direction.

Thanks again

Regards

Steve.

so you're using a combo box *control* on the form, with the accounts table
as its' RowSource, correct? (btw, if you have a Lookup *field* in the
transactions *table*, i strongly recommend that you get rid of the Lookup in
the table. for more information, see
http://home.att.net/~california.db/tips.html#aTip8.) open the form in Design
view, and click on the combo box control to select it. in the Properties
box, click on the RowSource property "line", and then click the Build (...)
button at the right. in the grid, add the ValidationNumber field. close, and
save at the prompt. increase the ColumnCount property of the combo box
control by 1, to include the field you just added to the RowSource. also
change the ColumnWidths property to include the additional field, setting
the width of the new column to zero, as

...; 0"

suggest you read up on combo box controls in Access Help, so you understand
how the various properties work and how to use them effectively.

now, when you open the combo box control's "droplist" in Form view, you do
not see the ValidationNumber column, *but the data in it is available to you
programmatically*.

i'll assume that you've added a validation field to the transactions table.
i'll call it TransValNum. in the Add_Transaction form, add the following
code to the combo box control's AfterUpdate event procedure, as

If Not IsNull(Me!ComboBoxName) Then
Me!TransValNum = Me!ComboBoxName.Column(n)
Else
Me!TransValNum = Null
End If

replace "n" with the index number of the column that you added to the combo
box control (as described above). note that the index is zero-based, so the
first column in the combo box control is 0, the second column is 1, the
third column is 2, etc. if you don't know how to create an event procedure,
go to http://home.att.net/~california.db/instructions.html and click the
CreateEventProcedure link.

hth

- Steve Lowe
- E-Mail : (e-mail address removed)
- Before Replying Remove .NO.SPAM
- UK Resident although my e-mail address is usa.net

 

[tina]

combo box controls are great tools, but take a bit of working with to get
the hang of them. you're very welcome