Best practice for hidden fields on form

[TracySHC]

I'm new to designing forms, and need advice on how best to proceed with adding a hidden field to a form. This hidden field is to be populated based on an entry to another field. Also, when the field on the form is populated, it must also populate its corresponding field in a table. (The form is built over a table.) Is this possible? What coding do I need for the hidden field, or the visible field which triggers its entry? What do I need for the underlying table or the form?
Thank you in advance for any and all advice,
TracySHC

 

[dhookom]

It would be nice if you suggested why you need to do this. However, I would start with a visible control so you can see what is happening. Once you are satisfied the control/field is populated correctly, you can hide it. The code in the After Update event of the triggering control might look something like:

   Me.txtHiddenControl = [Your expression here]

Duane
Hook'D on Access
MS Access MVP

 

[SeeThru]

Let us know why you need it. Generally, it is better to recalculate the value every time you need it for a report or similar, if the value is always based on the value in another field.

It is is something that needs to be static, then create it as a field in the table, and use the suggestion above.

Once it works, set it's 'visible' property to No.

SeeThru
Synergy Connections Ltd - Uk Telemarketing and Telesales Services and
Synergy Mobile Solutions - UK Mobile phones, land lines and call packages

 

[TracySHC]

dhookum, SeeThru,
Privacy issues and limited data entry were the reasons for the hidden fields. I am trying to pull in demographic information, such as residence, which data entry does not need (no mailing, and the name is visible) and which will change from time to time. (The demographic information will be used for future reporting.) I have tried the Me.HiddenField = [expression], and cannot seem to make it work. If I use Me.HiddenField.RowSource = [expression], will the underlying table be updated? Should I wait until the table is updated to update the demographics?
Thank you both for your advice,
TracySHC

 

[dhookom]

Is the hidden control a combo box or list box? If not, it wouldn't have a Row Source. You have provided one line of code but not stated what event triggers the code or what your expression might be. "cannot seem to make it work" doesn't tell us anything about what you actually are experiencing. Check out faq705-7148.



Duane
Hook'D on Access
MS Access MVP

 

[TracySHC]

dhookom,
The hidden field is a text box. It should be populated when the customer identification number field is populated. (The customer identification number is key to pulling in the residence information in the customer table.) Right now, I am trying to use a SQL statement to update the hidden text box, as follows:

Me.HiddenField = "Select distinct [Customer].[Residence] where [Customer].[ID] = [Forms]![EntryScreen]![Customer ID];"

This code is listed in the AfterUpdate of the customer id field on the entryscreen form. I am receiving the message that this text information is too long for the field. (Obviously, the SQL statement is being viewed as text rather than as an executable SQL statement.) How should I fix this?
Thank you for your insight and advice,
TracySHC

 

[dhookom]

You can't use an SQL statement like that. Try:
If the ID field is numeric, try:

Me.HiddenField = DLookup("[Residence]","[Customer]","[ID] = " & Me![Customer ID])

If the ID field is text, try:

Me.HiddenField = DLookup("[Residence]","[Customer]","[ID] = """ & Me![Customer ID] & """")

Typically the Customer ID would be a combo box with the Residence field as a 0" width column. Then your code would be:
If the ID field is numeric, try:

'x is the column number with the first column being 0
Me.HiddenField = Me.cboCustomerID.Column(x)

Duane
Hook'D on Access
MS Access MVP

 

[TracySHC]

dhookom,
Thank you for your suggestions! I've had the DLookup function working for testing, and currently have it in limited production. By the way, what would be the best practice for a visible field if a customer id had multiple sites? (For instance, Fedex has multiple stores in certain cities.) The visible field would need to list the sites so that data entry could select the correct site, and then the visible field would need to update the underlying table. (If you would prefer that I open a new thread with this question, please let me know.)
Thank you for all of your help and advice,
TracySHC

 

[dhookom]

I don't understand your table structures and needs.

Duane
Hook'D on Access
MS Access MVP

 

[TracySHC]

dhookom,
How do you need for me to explain?
Thank you,
TracySHC

 

[dhookom]

Your table structures would help. Check out MajP's faq700-6905.

Then provide your specifications pertaining to your question like:
[tt][blue]
I need to take value from fieldA in tableX and store them in fieldB in tableY in the form...
[/blue][/tt]
Typically you don't want to store redundant values so if your specifications require this, please provide a justification.

Duane
Hook'D on Access
MS Access MVP

 

[seaport]

I do not think you need a hidden control for that field. You may simply use:

Me!HiddenField = Me.cboCustomerID.Column(x)

Me!HiddenField refers to a field in the datasource table.

 

[TracySHC]

dhookom, seaport, seethru,
Thank you for your advice, and thank you, dhookom, for the link to document tables and relationships. Simply put, the tables that I am working with are:

Customer Table:
Customer ID, Name, Billing Address Line 1, Billing Address Line 2, Billing City, Billing State, Billing Zip, Billing Country, Contact Name

Customer Delivery Table:
Customer ID, Site Unique ID, Site Address Line 1, Site Address Line 2, Site City, Site State, Site Zip, Site Country

(There can be multiple delivery sites for a customer - for instance, a major customer may have one billing address but multiple delivery sites.)

Customer Payment Status:
Customer ID, Order ID, Payment Amount, Payment Due Date, Payment Status

Customer Order Header:
Order ID, Customer ID, Site Unique ID, Order Total Amount

The order entry form will have the order id already populated. The data entry clerk will populate the customer ID, which will populate the billing address information. What would be the best way to populate the site unique id? It will be visible, and there could be multiple delivery sites.
Thank you for your advice, and please let me know if more information is needed,
TracySHC

 

[dhookom]

To populate the [Site Unique ID], I would expect there would be a combo box on the form that would have a row source like:

SELECT [Site Unique ID], [Site Address Line 1] & " " & [Site City] & ", " & [Site State]
FROM [Customer Delivery Table]
WHERE [Customer ID] = Forms![Order Entry]![Customer ID]
ORDER BY [Site Address Line 1] & " " & [Site City] & ", " & [Site State];

Duane
Hook'D on Access
MS Access MVP

 

[TracySHC]

dhookom,
Would this SELECT statement, when the combo box is populated, also update the field in the underlying table? Why do you choose this method rather than making the field in the underlying table a lookup field?
Thank you for your advice and insights,
TracySHC

 

[dhookom]

The select statement is the row source for the combo box which the user can then select the unique site.

I never use lookup fields in tables. IMO, this is a horrible misfeature that causes lots of Access developers headaches. I have lost count of the number of threads in these and other news groups where people have used lookup fields and are totally lost when creating expressions in reports, queries, and forms.

Duane
Hook'D on Access
MS Access MVP

 

[TracySHC]

dhookom,
Thank you for your insights. How does the combo box selection update the underlying field in the table? Do I write code for the After Update property?
Thanks again,
TracySHC

 

[dhookom]

Set the control source of the combo box to the field from the form's record source.

Duane
Hook'D on Access
MS Access MVP