Automatic fill in a field

[Sanjo]

Greetings. I am wondering if Access 2010 has the capability of automatically filling in a field from info keyed into previous fields. For example, I want a confirmation number issued which is made up of all the characters keyed in the previous three fields. For example, assume the previous three fields are:
Block = 01
Building = 125
Room = A
Confirmation Number =

Therefore, the confirmation number would automatically be filled in as 01125A.

If so, how do I go about setting it up?

Thanks in advance for your help.

 

[John Big Booty]

You could use the following as the Control Source of your filed Confirmation Number;

= Me.[Confirmation Number] = Me.Block & Me.Building & Me.Room

 

[Sanjo]

Big John, thanks for your reply. By way of further info, I am referring to an input form and would like for the "Confirmation Number" field to be created or filled in as each of the other three fields are keyed in. I need the "confirmation number" to always begin with a "1". So I used the following code:
=Me.[confirmation number] = 1 & Me.[block] & Me.[building] & Me.[room]

I still can't make that happen. Can you advise me again?

Thanks in advance.

 

[John Big Booty]

Try;

=Me.[confirmation number] = [B][COLOR="Red"]"[/COLOR][/B]1[COLOR="Red"][B]"[/B][/COLOR] & Me.[block] & Me.[building] & Me.[room]

 

[Sanjo]

Thanks again John. the code using the real field names after I keyed is:

=[Me].[Certificate #] = " 1 " & [Me].[Niche] & [Me].[Space "A"] & [Me].[Space "B"] & [Me].[Transfer]

that is the certificate # is 1+niche+space "a" + space "b"+ transfer

so, if someone keys 120 in the niche field, check space "a" and space "b" and the transfer field, then the certificate # should be 1120abt

After I key the code in, the words "#Name?" continuously shows in the Certificate's input field, but 1120abt never shows.

Sorry to be so thick headed.

 

[catalin.petrut]

If you don't need to store the confirmation number (and i see no reasor for you to do it) you can simple put into control source of the confirmation field on the form this: =[field1] & [field2]. Field1 and Field2 and so one are the fields you want to concatenate.

 

[Sanjo]

Additional info. the last suggestion is beginning to work. The Niche field is one where a number is keyed into. The next three fields (Space A, Space B and Transfer) are Yes/No check fields. So
(1) if 125 is keyed into the Niche field and a check is placed in Space "A", then the Confirmation Number would be 1125A.
(2) if 125 is keyed into the Niche field and a check is placed in Space "A" and Transfer, then the Confirmation Number would be 1125AT.

And the Confirmation Number needs to be stored onto the table.

Am I hoping for too much?

Thanks again.

 

[John Big Booty]

I think you need to post a copy of of your DB.

Secondly this type of Constructed (calculated) result should, in most cases (including this one), should not be stored. They should simply be reconstituted at the time of display at either Form or Report level.

 

[catalin.petrut]

Like John Big Booty says, i see no reason for you to store it. Also, till the 2010 version, Access doesn't allow calculated fields into the tables. Just use the formula in any report or form you will create and need to show that confirmation value.
Even if one of your filds is a yes/no field, you still can use the same principle.

 

[catalin.petrut]

For your other demand, i sugest you to change yes/no fields with lookups filed based on the letter (A or T or any other) and a blank space. This is the simplest solution. If you insist on yes/no field, you can use IIF function under the control source.

 

[missinglinq]

If I follow this correctly (and it is confusing) in the Control Source of Certificate #

= "1" & [Niche] & (IIF([Space "A"] = -1,"A","") & (IIF([Space "B"] = -1,"B","") & (IIF([Transfer] = -1,"T","")

If [Space "A"] and [Space "B"] are not the exact actual names of these fields, you'll need to replace it with actual names.


BYW, Lookup Fields, at the Table level, are almost always ill-advised:

The Evils of Lookup Fields in Tables

Linq ;0)>

 

[catalin.petrut]

BYW, Lookup Fields, at the Table level, are almost always ill-advised:The Evils of Lookup Fields in Tables

Agree! But the suggestion was for the form in with is obtained the confirmation#.

 

[Sanjo]

John, thanks for your time, patience and advise. I don't think I can use the automatic fill approach to create the confirmation number so we will simply have to key it in.

 

[catalin.petrut]

Too bad. Tat will make room for mistakes.

 

[Sanjo]

OK, after my "give up" statement, I saw Missinglinq's idea and used it. It works perfectly. That is, it creates a confirmation number from the Niche, Space A, Space B and Transfer fields, even with the last three being yes/no fields. !! Great. It would be really good if the confirmation number could be stored on the table so that queries and reports could show it. Thanks so much guys/gals. Now I am trying to use the same principle when I call up reports since the number is not stored . Thanks again.

 

[John Big Booty]

... It would be really good if the confirmation number could be stored on the table so that queries and reports could show it. Thanks so much guys/gals. Now I am trying to use the same principle when I call up reports since the number is not stored . Thanks again.

Once again the confirmation number does not need to be stored. If you can construct it on the form you can construct it in a query or on a report as required, using exactly the same principal as shown by missinglinq.

 

[Sanjo]

thanks John, I have tried a couple of times using the exact same code on the Control Source for the Confirmation Number on the Reports, but to no avail. I will keep trying. Sorry, but I am an infrequent user of Access and lapses of time between the uses dim previous knowledge.

 

[missinglinq]

You simply do the exact same thing on your Report as you did, successfully, on the Form! You create an Unbound Control, on your Report, and use the same Control Source for it:

= "1" & [Niche] & (IIF([Space "A"] = -1,"A","") & (IIF([Space "B"] = -1,"B","") & (IIF([Transfer] = -1,"T","")

Linq ;0)>

 

[Sanjo]

Thanks, Ling. Will keep you posted.

Sanjo

 

[Sanjo]

Ling & John
Things work just as you said. Thanks again for your time and guidance.