Select a member and get the hours

[Céline Brien]

Hello
In a subform, in the member field, I have 1 drop down boxes showing
members and hours.
When I click the drop down box and select a member, I would like the
hour field to be automatically filled in with the appropriate data. Once
in a while, I also would like to be able to enter a different hour in
the hour field.
What codes should I use ?
Many thanks,
Céline

 

[Wayne Morgan]

If the hour data you want displayed in the textbox is the same as you have
in the combo box you can use code in the combo box's AfterUpdate event to
fill in the textbox. You will also need to use the same code in the form's
Current event to have the textbox filled automatically with the value from
the combo box as you move from record to record.

Me.txtMyTextbox = Me.cboMyCombo.Column(1)

The column number is the number of the column that the hour data is in. The
number is zero based, so the first column is 0, the second is 1, etc. This
will allow you to manually change the value in the textbox if you desire.
However, there are a couple of problems with this approach that you need to
be aware of.

1) If the subform is in continuous form or datasheet view, the textbox will
only be correct for the selected record.

2) Changing the value in the textbox won't do anything but change it for
that moment. What do you want to do with it if you change it?

Another option would be to use a calculated textbox to display the data. To
do that, you would put the above equation, removing the part to the left of
the =, into the Control Source for the textbox. Set the Locked property of
the textbox to True and probably remove it from the Tab order. Place a
second textbox on the form next to this textbox to make the edit in. You
still need to decide what you want to do with the value you type in.

 

[Céline Brien]

Hi Wayne,
Thank you for your very clear answer.
I choosed the code in the combo box's AfterUpdate event to fill in the
textbox.
The second option (use a calculated textbox) did'nt work. The formula
=Me.Membre.Column(2) turned to =[Me].[Membre].[Colonne](2).
In respond to your comment and question :
Members get hours of service at a various rate. These hours are in the
members form. Sometimes a member want more hours, this is why I want to
be able to change the hours fill automaticaly. If I type in the hours, I
want to keep it until I change it or change the member and get new hours
automaticaly.
This is why I did'nt use the same code in the form's Current event,
because I did'nt want to have the textbox filled automatically with the
value from the combo box as I move from record to record.
Many thanks again,
Céline

 

[Wayne Morgan]

Yes, Access will put the brackets around it. In the Control Source, Access
won't accept the Me, so you also need to drop the "Me." then it should work.

--
Wayne Morgan
MS Access MVP

Hi Wayne,
Thank you for your very clear answer.
I choosed the code in the combo box's AfterUpdate event to fill in the
textbox.
The second option (use a calculated textbox) did'nt work. The formula
=Me.Membre.Column(2) turned to =[Me].[Membre].[Colonne](2).
In respond to your comment and question :
Members get hours of service at a various rate. These hours are in the
members form. Sometimes a member want more hours, this is why I want to
be able to change the hours fill automaticaly. If I type in the hours, I
want to keep it until I change it or change the member and get new hours
automaticaly.
This is why I did'nt use the same code in the form's Current event,
because I did'nt want to have the textbox filled automatically with the
value from the combo box as I move from record to record.
Many thanks again,
Céline

 

[Wayne Morgan]

PS.

Just a reminder, if you put the code in the Control Source, that textbox
won't be updateable. You'll need a 2nd text box. If what you have is
working, that's fine, but I did want to let you know about the "Me." problem
for future reference.

 

[Céline Brien]

Hi Wayne,
Thank you so much for those additionnal precisions.
It works fine now without the Me.
I wont use it for this time, because I want that textbox to be
updateable, but I am happy to know how to use it.
Many thanks again for your kindness !
Céline