Best Practice on Data Management for Forms

[Tskutnik]

All,
I have a form that references a considerable amount of data, so this is a question on response time and best practice.
One of the fields in the Form requires a join to another table to enhance what is shown. Question is - do I do this join in the Form Data Record Source, or only within the specific Field's Record Source?
The join and related table's data is only needed for this one field.
Thanks

 

[pbaldy]

It sounds like a situation where the form would be bound to a single table (or a query on a single table). The field you describe would be displayed via a combo box whose row source would query the other table.

 

[Tskutnik]

pbaldy - thanks for the response. Slightly different problem I think. There are 5 columns in the Forms' datasheet/table. One of the fields shows a numeric key (from the underlying query), but I'd rather show the text value of that key, which is in a separate table that I would only need to show this one field in a user friendly way.
I can certainly link that additional table to the entire query underlying the Form Data Record Source (Making it available to the entire Form), but since I only need it for this one field I was wondering if it is better practice to just join it within that one fields' control source / data

 

[pbaldy]

Same problem. Normally the field you describe would be represented by a combo box. The row source would pull the ID and text values from your second table. The ID would be the bound column and thus the value saved. The ID column could be hidden if you just wanted users to see the text value, or the text value could be displayed in a textbox next to the combo:

Autofill

 

[Uncle Gizmo]

One of the fields shows a numeric key (from the underlying query), but I'd rather show the text value of that key, which is in a separate table that I would only need to show this one field in a user friendly way.

I think this is what Paul means:-

Display Data from a Combo Box in a Text Box - Nifty Access

Display Data from a Combo Box Combo boxes are one of the most valuable controls you can have on a form, they are so versatile and give you such an extra range of functions for your form that you should really consider understanding how to use them. Also with a combobox as your foundation, you

www.niftyaccess.com

 

[Pat Hartman]

If you need to update the field in question, you would always use a combo. It is only if the field is just for display as it would be in a report, that you would use a join. OR, if you wanted to show several fields from the lookup table, you would use a join. For example, on an order, you might want to show the customer's phone number as well as his name. In that case, you would still use a combo for the customerID so you can pick the customer when you create the order but you would base the form on a query that joins (left join) the order to the customer so you can also pick up the phone number.

FYI,
Best practice is to filter the recordsets for your forms using criteria in your query rather than binding forms to naked queries or forms where thousands of records will be retrieved for no reason.

 

[theDBguy]

Hi @Tskutnik. Just in case it matters, is your form a single or a continuous view form?

 

[Pat Hartman]

theDBGuy, the code behind a form runs on the current record (unless you open a recordset) so it doesn't matter what view the form is in.

 

[theDBguy]

theDBGuy, the code behind a form runs on the current record (unless you open a recordset) so it doesn't matter what view the form is in.

I can't even remember now what I was thinking when I asked that question, but if you're using code to update a field in the current event, then it would matter if the form is single or continuous, because the code will only update one row at a time.

I don't think I was thinking about using code when I asked that question, so let's just forget it. Cheers!

 

[Pat Hartman]

I find myself having more of those senior moments as time goes on