How to write/update field in 1 table with field from another table ??

[markajem]

I simply have two different tables as:


Table - CheckAct
Fields in table
EntryID
Date
Amount
Notes
Transdescription



Table - Transactiontype
Fields in table
TransID
Transactiondescription


(The table CheckAct has more fields than listed above but irrelavent to this question.)

My challenge is the following:

In designing the form for table CheckAct I have the field Transdescription as a combo box. In this combo box I display the available values from the table Transactiontype field Transactiondescription. This way when data is entered into the form there are preset values for this field as i.e. CHECK, PAYMENT, DEPOSIT, INTEREST, BANK CHARGES etc. So when the user enters information into this form he just selects from the combo box the precreated transactiontypes from the field Transactiondescription in the table Transactiontype. This portion is working well (meaning it properly displays the values of the field Transactiondescription from the table Transactiontype in the drop down box). All the information is written to the table CheckAct correctly with the exception that the field Transdescription is not populated with the value selected in the form field Transdescription combo box.

I assume it is just something simple.

Do I need an expression such as CheckActTransdescription = TransactiontypeTransactiondescription in order to populate the field CheckActTransdescription?

Thanks for the help
Mark
PS Since the field names were similar I used different colored fonts in this post to aid.

 

[vb6novice]

Mark,

Your tanble design is a little non-standard. Typically, you would have the TransID (primary key in the TransactionType table) in the CheckAcct table instead of the description.

Then, your combo box must be based on a query or select statement with 2 fields from the TransactionType table, the TransID (as Column 1) and the Transactiondescription (as Column 2). The combobox properties should be:

Control Source is the field where you want the value of the user's choice to go (TransID)
Row Source Type is Table/Query
Row Source is the name of the query with TransID and Transactiondescription in it or use

SELECT [TransactionType].[TransID], [TransactionType].[Transactiondescription ] FROM [TransactionType];

Column Count is 2 (since the query or select statement returns 2 fields)
Bound Column is 1 (the TransID field which becomes the value of the combobox)
Column Widths are 0"; 1.5" (makes the TransID columnb width zero so it doesn't show, and the user only see the Transactiondescription.

Hope that helps.

 

[markajem]

vb6novice - thanks for your help. I will try that out.

Mark