PDA

View Full Version : Solved: Continuous Subform problem



Adaytay
08-11-2004, 05:41 AM
Hi folks :hi:

I have a subform that users enter product codes and quantities into. It's a standard continuous form.

I want to add in a nice touch for the user, whereby when the user enters a product code it will display the description in an unbound textbox. This is for info only and never stored in the db (although the product code is). :roll:

I've got it working by calling the dlookup function as the item code is validated...

...Every time a new record is added to the subform, the value in the textbox changes to reflect the new product code. Ok, right? Wrong. It changes EVERY record in the subform to the new value.

Any ideas? :dunno

Ad

Adaytay
08-11-2004, 06:09 AM
Ah shucks I managed to think up a rather creative way around it.

The recordsource for the subform is a table that's created in a temporary database, and then the information is read off this back into the main DB once the user confirms save.

So, the best way round it? (for me, anyway) Simple. Add a new text field to the table definition as it's being created, and store the information in there! It doesn't get saved anywhere, as I said it's purely for info only...

Cheers for looking though folks. All sorted!

Ad

Pat Hartman
08-11-2004, 06:15 AM
Access keeps only a single instance of control properties therefore it cannot keep separate values for individual records. That is why unbound fields on continuous forms only show the same value.

To solve the problem, ditch the DLookup() (they're inefficient anyway) and change the recordSource of your form to be a query. In the query join the main table to the lookup table and select fields from both sides of the join. Replace the table name or SQL string in your form's RecordSource with the name of the new query. You will now have the "lookup" field available to you in the list of bound fields. So in the controlsource where you now have the DLookup(), get rid of the function and choose the actual field name from the list of bound fields.

If you don't want this field to be accidentally updated from this form, set the field's Locked property to True and its tabStop to No.

When you enter a product in the product field either by typing or choosing from a combo, the description will automatically populate as soon as your cursor leaves the product field.

This is the magic of relational databases. First we separate all the data into properly normalized tables. Then we use queries to bring it all back together again:cool

Adaytay
08-11-2004, 06:35 AM
Pat,

Nice idea, thanks for the tip. Storing the information in a temp table rather than recreating a query on the fly every time the form opens seems a little easier to me though.

Certainly something I'll use for the future, however!

Ad