Text Box value

[PaveFE]

Hi All,
On a form, I have a text box that I want a default value put in and have no one be able to change.
For instance, I currently have Last Name combo box and next to it I want a text box that will show the persons age based on whatever name is chosen from that Last Name combo box. The table source for the combo box has all of that information already, I just want to show their age as well, but in a different box. I also don't want anyone to be able to change it on the form.
Thanks in advance, this place has been extremely helpful.

VD

 

[PHV]

Have a look at the Column property of the ComboBox object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ZmrAbdulla]

Keep it
Locked=True

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[PHV]

Setting the ControlSource property of the TextBox with the formula will prevent anyone to change its value ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PaveFE]

I guess I should clarify...

My first question is how do I set up the text box to have the value automatically put in when a user selects a name from the other combo box?

The second (which seems to have been answered) was how do I lock it. (thanks).

Sorry and thanks,

VD

 

[Golom]

Sub ComboLastName_Click ( ... )
    Person = ComboLastName.Text
    TextAge.Text = ComputeAge ( Person )
End Sub

Where "ComputeAge" is some function that returns the person's age, given their last name.

 

[PaveFE]

Please forgive me....I'm not that up to speed on Access.

I take it this formula would go into the "Build Event" when I click on properties of the text box that I want?
If so, I did that and the "Sub ComboLastName_Click ( ... )
" was in red, so obviously I'm not doing something right.
Could you break down the formula for me so I know what to put where?

Thank you and much appreciation if you can help.

VD

 

[dhookom]

If the text box value always depends on the value selected in the combo box, use and expression in a control source of the text box like:
=cboLastName.Column(1)
Keep in mind that this expression will display the 2nd column defined in the combo box's row source query/sql. Users will not be able to edit this text box other than selecting a new value in the combo box.

Also note this text box is not bound to field in the form's record source. If you want this value from a column stored in the form's record source then set the text box properties:

Name: txtAge
Locked: Yes
Control Source:
Tab Stop: No

Add code in the after update event of the combo box:

Me.txtAge = Me.cboLastName.Column(1)

The use of the "Text" property as suggested by Golom is not appropriate for MS Access. The text property is only available from the control that has the focus.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PaveFE]

Well, it didn't work and I'm sure it is because of something I'm not doing right. So, let me try to be more specific than I have been (and should have been from the beginning).

I have a form (frmACR) for table (tblACR).
I have another table (tblInfoPP_Pilot) that contains fields for Pilot info (Last name, first name, MI, Rank, and Call Sign).
The tblACR has a field for the pilot's last name, which is a lookup to the table tblInfoPP_Pilot so that a user can use a dropdown on the form to select the name.
The tblACR also has a field (displayed as a text box on the frmACR) for the pilot's call sign and I want the value of the call sign field to be automatically filled in when the pilot's name is selected on the frmACR (and locked so no one can change it).
Hopefully this is more explicit and someone can help me again.
Thanks,
VD

 

[dhookom]

When you state "didn't work" it should always be followed by your results or symptoms.

Does tblInfoPP_Pilot have a primary key? If so, this is the value that should be stored in tblACR. What happens in your solution when you have two or more pilots with a last name of "Smith"?

If the Call Sign doesn't change, then you don't need to store it in tblACR.

You shouldn't use a lookup field in a table design

http://www.mvps.org/access/lookupfields.htm.

You should use combo boxes on forms. The row source for your "last name" combo box should contain the Call Sign field so that you can reference it in either code as I have suggested or in the control source of a text box.

What is the row source of your combo box? Do you think you need to store the call sign in tblACR? Is last name unique in your tblINfoPP_Pilot table? Do you have a primary key in tblInfoPP_Pilot?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PaveFE]

When you state "didn't work" it should always be followed by your results or symptoms"
Sorry about that, essentially, the box remained empty.

"Does tblInfoPP_Pilot have a primary key? If so, this is the value that should be stored in tblACR. What happens in your solution when you have two or more pilots with a last name of "Smith"?"
Yes there's a primary key. The table is broken down by Last, First and MI.

"You shouldn't use a lookup field in a table design"
Just doing what I was taught from New Horizons.

I read the link you posted. I guess this means I will need to go back and change everything.
The pilot info (Last Name, First initial, middle initial and Call sign) will be shown on a report for each flight (each row in tblACR is a flight). The schedulers(users) will fill out the frmACR to show a flight schedule (made into a report for printing and exported to a snapshot file) for each day. So, yes, that information or at least the PrimaryID must be stored in that table, but I will need to see the information on the form and in a report. This flight schedule (tblACR) is stored for tracking purposes as well and will be referenced in queries.

 

[dhookom]

I would never use name fields as a primary key. Are you storing all three names fields in the tblACR? If you don't, you lose track of the pilot. I suppose this is why you need to store the Call Sign.

Bottom line, I would create an Autonumber (or other field) as the primary key of the pilot table and store this field value in tblACR. Any where you use this value from tblACR, you can link to the pilot table to display the Call Sign. There should be no reason to store the Call Sign in more than one table.

The New Horizon training seems to not be based on experience with Access. You won't find many seasoned developers who use the "lookup field" mis-feature.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PaveFE]

I guess I didn't explain it correctly. My tblInfoPP_Pilot has an Autonumber primary key field and the fields Last name, First Name, MI, Call Sign.

I will need the tblACR to store the Last name, First name (or first and middle initials), and Call sign. When a pilot leaves, his info in this table will be deleted, however, I will still need it in the tblACR since this table contains all the information about the flight, including his name and call sign. The tblInfoPP_Pilot is just so I can have a dropdown on the form for the user to chose from. Hopefully this is more clear (I'm trying).

Gotcha on the New Horizons, however, my squadron paid for a one-year membership for me, so I have to go. If anything I'll learn some little things that I'm not familiar with.

Thanks,
VD

 

[dhookom]

There generally is no good reason to remove the pilot's information from the table. You should consider adding a [Status] field that you can set to "Inactive".

However, name your combo box cboLastName and set its control source to:
SELECT ID, [Last Name], [First Name], [MI], [Call Sign]
FROM tblInfoPP_Pilot
ORDER BY [Last Name], [First Name];
The add code to the After Update event of this combo box to:

   Me.txtLastName = Me.cboLastName.Column(1)
   Me.txtFirstName = Me.cboLastName.Column(2)
   Me.txtMI = Me.cboLastName.Column(3)
   Me.txtCallSign = Me.cboLastName.Column(4)

This assumes you have named your controls in the frmACR have the names as described in the code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]