Subform using LookUp field

[Tectu]

I have two tables: tblParties and tblCashbook. The table tblCashbook has a field named Party. I used the Lookup Wizard... as the data type in the design view. In the wizard, I used the "I want the lookup field to get the values from another table or query" option. The values are a column named PartyName from the tblParties table. This way, in the datasheet view the user can use the dropdown menu to select a party by its name.


Everything is working so far.

Now I want to create a form. The form should allow the user to jump to the party entry in the tblParties table when clicking on the party field in the form (of the tblCashbook table). How am I supposed to do that?
What I did in previous Access databases was having a field that simply stored the ID of a row in another table (foreign key) which allowed me to use the subform widget in the form designed to display the data of that second table within that form.

Thanks for your help.


~ Tectu

[June7]

I NEVER build lookups in table. I build comboboxes on form.

Why do you want to open record of tblParties from tblCashbook form?

[Tectu]

Thanks for your quick answer.

Let me begin by telling you that I have never used Access before. So it is very likely that I am doing it wrong so far
I want to be able to open a record of tblParties from the tblCashbook form to get quick access to the information of said party (eg. e-mail addresses and other information).


~ Tectu

[June7]

Maybe better options:

1. multi-column combobox bound to Party field in tblCashbook that lists the records from tblParties and textboxes that reference columns of combobox in order to display party info (these controls would be locked to prevent editing)

2. include tblParties in form RecordSource - join type "Include all records from tblCashbook and only those from tblParties that match", bind textboxes to fields of tblParties and lock them to prevent editing - select party from combobox and info from related record will display in the textboxes

3. DLookup() expressions in textboxes

[Tectu]

Thanks for your recommendations.
When doing this in the form only, I would just have a normal number field in the actual tblCashbook table that is not linked to the tblParties in any way and the actual lookup just happens within the form?

[June7]

That is correct.

[Tectu]

Thank you for your quick replies. I will try implementing this that way in the following days.

[Tectu]

I tried to implement this the way you suggested but I fail to do so. As mentioned before I don't have any previous Access experiences.

What I did so far: Add a field named PartyNumberID to the tblCashbook table. The field is of the type ShortNumber. Is that correct so far? What I want to do next is to create a form which will provide the user with a ComboBox (not editable) that lists all the parties from the tblParties table. When a party is selected the corresponding ID (primary key) of the record in the tblParties table needs to be added to the PartyNumberID field of the tblCashbook table. For this I add a ComboBox to the form and in the Wizard I select the first option: "I want the combo box to get the values from another table or query". And from here I am a bit lost. How do I continue? How do I implement the logic that the user sees the name of the party in the ComboBox but that on selection the ID of the selected party is added to the tblCashbook table?

Thank you for your help.

[June7]

Combobox must be editable in order to select item from dropdown list. It must be bound to PartNumberID field to save the selected value.

Review http://datapigtechnologies.com/flash...combobox3.html