Default values from table on unbound form

[Pawtang]

Yo,

The purpose of this form is to allow the user to edit the customer info that will populate a generated report before it is created. The form is opened from a specific record, so it opens to a specific customer. Sometimes the user needs to edit some of the information for the specific case of the generated report, so this allows them to modify on the fly without changing the customer's actual stored data.




I set the record source of the form to tblCustomers, and the Customer Number box is bound, but all other boxes are unbound. I set their default value to equal (for example) =[company] or =[city], which should refer to the record source's field. As you can see I'm getting these referencing errors. Any ideas why?

[Gicu]

You cannot reeference a table like that you would need to use dLookup("[City]","[tblCustomer]","[CustomerNumber]='" & [Customerumber] & "'") or a reference to a control on another open form (Forms!frmMainForm!City).

Cheers,

[davegri]

Also, textboxes with an expression as the datasource cannot be edited. All the DLookups as described above would have to be in the form's Current event.
Another option could be a query filtered by Customer Number, containing all the required fields. The query would be the recordsource for the form.
Then open a dao.recordset based on the query and individually set the textboxes to the appropriate values in the recordset, again in the form's current event.

In either case, you still have the chore of coding the update of each individual field back to tblCustomers.
Lotsa trouble. Why not have a bound form?

[Pawtang]

I want to avoid a bound form because I don't want to change the existing data in the Customer table. For example, sometimes we are just entering a different contact at the same company for the report to be addressed to, but they are not the main contact to be saved for the company - just a one-off contact for the report. Perhaps there's a way to delay updating such that it never actually changes the records?

[davegri]

I want to avoid a bound form because I don't want to change the existing data in the Customer table. For example, sometimes we are just entering a different contact at the same company for the report to be addressed to, but they are not the main contact to be saved for the company - just a one-off contact for the report. Perhaps there's a way to delay updating such that it never actually changes the records?

I assume your report is based on a table or query, not values transferred directly from the form to a report (or just printing the form).
Create a temporary table that contains all the fields required for the report. After the form is updated, empty the table and then have an append query insert all the fields from the form into the temporary table. There's your report recordsource.

[Gicu]

I think the dLookups in the default value will work as long as you set the Data Entry=Yes to force a new record (or open the form with Docmd.OpenForm with DataMode acFormAdd.

[Pawtang]

I assume your report is based on a table or query, not values transferred directly from the form to a report (or just printing the form).
Create a temporary table that contains all the fields required for the report. After the form is updated, empty the table and then have an append query insert all the fields from the form into the temporary table. There's your report recordsource.

It originally was just based on customers table, and matching the customer number of the form that launched it. I just started building this intermediary form today, my plan was to spit the form's values out into the report.

I'm not sure about the temporary table approach; if I'm binding to that spoof table, I won't have the existing customer records to populate the form anyway, unless I use DLookups I guess?

[davegri]

I'm not sure about the temporary table approach; if I'm binding to that spoof table, I won't have the existing customer records to populate the form anyway, unless I use DLookups I guess?

Either method from post #3 will fill the table with the current customer fields.

[Micron]

I vote for the temp, or as I call it, a staging table because as soon as you say temp table people start warning you to not do make table queries all the time. That table would have all fields required for the report and get the records from an append query so I don't get that comment about missing fields. This solution eliminates any DLookups and any fields can be altered without affecting the original data. When you're done, you simply dump the staging data.

You could also just pass a recipient value to the report via report OpenArgs and use it from there.