Table lookup to populate field in subform and add to form field

[naschol]

I am somewhat of a novice and am trying to do a somewhat complicated thing (to me, anyway).

What I would like to do is populate the field Entry Fee in the subform (Category Entries table) from a column (ItemFee) in the table Categories. Then, I would like to add that amount to EntryFees in the Entrant table. Am I right to assume that I have currency in the Catgories table, once I have selected an item in the combobox (column)? How would this statement be coded?

I have hopefully illustrated the part of the definitions involved without confusing anyone. Forms are datasheet.

Form (Registration) Table (Entrant)
Field or textbox (EntryFees)
Subform (Category Entries) Table (Category Entries)
Combobox or Column (Category Name) from table Categories, which
also contains ItemFee
Field or textbox (Entry Fee)

Thank you for your consideration.

[Delerna]

Do you mean after the user has entered a value into ItemFee and selected the currency for that value from the dropdown, then calculate and update Entry Fee.

You would need to place code into the Change event for both ItemFee textbox and the Currency combobox. since either event need to the same thing I would place into both events, a subroutine call to the same subroutine and do the work there.

something like
[codse=vba]
Private sub txtItemFee_Chan ge()
UpdateEntryFee
End Sub
Private sub cboCurrency_Cha nge()
UpdateEntryFee
End Sub
Private Sub UpdateEntryFee( )
--Place the code to do the calculations and updates here
End Sub

[Delerna]

OK, having given you a rough idea on how to do It, a couple of comments.

Saving calculated fields is not necessary. You should only save necessary data in your tables. All calculated fields should only appear in the queries that your forms and reports are based on, and never in the tables.

An example for the reason of this rule is in your question.
You mention that you want to add ItemFee to EntryFee

OK suppose EntryFee is 100
and 10 is entered into ItemFee

EntryFee becomes 110 via your update code.

Now the user realises they have made a mistake
They now enter 15 into ItemFee
EntryFee now becomes 125 via your update code
but it should be 115.

All rules are made to be be broken and I guess there could be occasion to break this one but I have never had occasion to break it yet.

[naschol]

Do you mean after the user has entered a value into ItemFee and selected the currency for that value from the dropdown, then calculate and update Entry Fee.

No, I don't want to have the user enter the amount, at all. What I would like is for the user would select the category from the dropdown and the Entry Fee would automatically be placed in the ItemFee (subform) field and then added to the EntryFees (form).

[naschol]

Saving calculated fields is not necessary. You should only save necessary data in your tables. All calculated fields should only appear in the queries that your forms and reports are based on, and never in the tables.

Great point! So, what I really need to work on now, I guess, is how to move the value of the field(s) from the dropdown to the ItemFee field (both on the subform). It will be one of four fields, depending on data in both the form and subform tables.

Thanks for all your help!

[Delerna]

So, what I really need to work on now, I guess, is how to move the value of the field(s) from the dropdown to the ItemFee field (both on the subform). It will be one of four fields, depending on data in both the form and subform tables.

One way you might be able to do that is with DAO or ADO on the code page for the form.
You create a database object to CurrentDB
Get the values from the four controls on the form
Create a query string with the 4 values in the WHERE clause
execute the query string into a recordset object.
Get the value from the recordset object and update the field on the form to that value.

I hope the explanation makes sense. If you need help.......

[naschol]

One way you might be able to do that is with DAO or ADO on the code page for the form.

Thank you. I will look into that...