How to auto populate field based on other fields

[WLC]

I have a field on a form that I would like to auto populate with the (concatenated) contents of 3 other fields on the same form. The first 2 fields are drop down boxes and the 3rd is a hand keyed text field.

For example, the first field is Department (4 digit number from drop down box)
Second field is Document Type (3 letter code from drop down box)
Third Field is Document Seq Number (5 digit number hand keyed)

So if I pick 0100 from field1, PCS from field2, and hand key 10000, in my 4th field (called Document ID), I should wind up with 0100PCS10000.

Currently, I have the control source for the Document ID field as
=([Department] & [Document Type] & [Document Seq Number])

In the example above, instead of getting 0100PCS10000, my result is 1110000.

Any help in getting me to the result I am looking for would be greatly appreciated.

 

[Mr. B]

Are you referencing the controls by name or are you trying to refer to the field names?

I created an unbound form with two combo boxes and one text box. The first combo box is the Department and I named that combo box "cboDept". I named the Document Type combo box "cboDocType" and I named the Document Sequence Number text box "txtDocSeqNum".

I then set the Limit to list property of each combo box to "Yes". I also crated an input mask of: "AAAAA" for the text box to require an entry of five characters or digits. I then set the Control Source of the Document ID text box to: =[cboDept] & [cboDocType] & [txtDocSeqNum]

With this setup, when I select "0001" from the first combo box, "PCS" from the second combo box and enter "12b23" in the Doc Seq Num text box, the Document Id text box is populated with: "0001PCS12b23"

Hope something in this description of what I did to make it work will help you fix your problem.

 

[WLC]

Thank you for your response.

I was referring to the field names.

You mention creating an unbound form. This is a bound form.

The first combo box (Department) has a row source = "SELECT [GL Codes Contacts].ID, [GL Codes Contacts].GLCode, [GL Codes Contacts].Department FROM [GL Codes Contacts];

The second combo box (Document Type) has a row source = "SELECT [Document Type].ID, [Document Type].[Document type] FROM [Document Type];

Both of those have the "Limit To List" set to Yes.

Please let me know what other information you may need to help me figure this out.

 

[Mr. B]

If you want the concatenated values to populated the text box control, you need to refer to the name of each of the other controls, not the names of the fields.

I was telling you how I had created the same thing here and that it worked.

 

[WLC]

The field names are the same as the control source names.

I know this is something so simple that I am just missing. I have even gone so far as trying "= [Form]![form name].[control source] & field2 & field3" which results in my field 4 (Document ID text field) = #Name?.

I know you said you got it to work with an unbound form. Could the fact that I have a bound form have anything to do with mine not working?

What about the fact that for my combo fields 1&2, those are the result of a query?

 

[Mr. B]

I am sure that the problem is the way you are providing the control names. Please note in post #2 that i have my controls name with a naming convention. I use a cbo as a prefix for all combo boxes and a txt for all text boxes. Then I use the field name following the prefix. Try naming your controls and then provide their names as I did in Post #2.