Auto Fill a number based on a field

[Christina Thrun-Western Dairyland EOC]

I'm not really sure how to go about setting this up. I have a table for
training registrations. There is form based on that table. I have 4
different trainings that are in a drop down box in the form (BPT1-3, Business
Overview, WNET, and Ebay/Pic/Shipping). For 3 of them the following hours of
training are set:
BPT1-3 9 hours
WNET 2 hours
Ebay... 14.5 hours
What I would like is that when I choose one of these trainings the hours are
automatically filled into the the TrainingHours field. I'm not sure if I
need a separate table, a query, how to pull the numbers in, etc... Right now
we are just typing in the number of hours for the training, but with multiple
people entering data it would be better if the hours could auto fill on our
basic trainings.
Thanks

 

[JP]

1. Set up a 2 column table of training registrations. The first column is
the name of the training (BPT1-3, etc.) and the second is the hours for that
type of training. If there are no set hours for a given type of training
(which appears to be the case for Overview), enter a zero.

2. Make the Row Source Type of the combo box = Table/Query and the Row
Source itself is a query that just pulls in the first column of the table.

3. In the AFTER_UPDATE event for the combobox, put code that does a DLOOKUP
against the 2nd column (the hours column) of the table based on the training
type selected. If the value is not zero, put that value in the hours
textbox on your form. If the value is zero, leave the hours textbox empty
so that the user can enter the hours.

You could do a variation on this in which the combo box is a two column
combobox that has the hours in a second (hidden) column and then retrieve
the hours from the combobox instead of using a DLOOKUP.

I am assuming that there is a reason why you don't have specific hours
established for the fourth training type (Overview).

"Christina Thrun-Western Dairyland EOC" <Christina Thrun-Western Dairyland
[email protected]> wrote in message
news:[email protected]...

 

[Christina Thrun-Western Dairyland EOC]

I'm working on the DLookup formula. I'm not real familiar with this. This
is what I have.
Table: (x)TrainingHours has 2 columns with 1 being the TrainingName and 1
being the TrainingHours
Table: TrainingRegistration-the table the form is based on and would like
the hours auto looked up from previous table to fill in.
The following is the AfterUpdate formula I have for the lookup but am
getting errors.

Me.TrainingHours = DLookup("TrainingHours", "(x)TrainingHours",
"[TrainingName]" = " & VarTrainingHours & ")

Thanks
Christina

 

[JP]

Christina,

The third argument of DLOOKUP is the criteria for selecting the record you
want to look at. You want to look at the record for the selected training
NAME.

If the field in the form that contains the TrainingName selected is called
cmbTrainingName, then your DLOOKUP would read:

Me.TrainingHours = DLOOKUP("TrainingHours", "(x)TrainingHours",
"[TrainingName] = me.cmbTrainingName"

A couple of things about your criteria command:

1. It should all be one big string, or something that evaluates to one big
string. What you have is a string on the left side of an equal sign and
then another string on the right side of an equal sign. That's an
expression, it's not a string.

2. Your criteria is trying to select on the TrainingHours, but what you
want to do is look up the TrainingHours for the TrainingNAME that the user
selected

3. I don't understand the &s in the criteria.



"Christina Thrun-Western Dairyland EOC"

I'm working on the DLookup formula. I'm not real familiar with this. This
is what I have.
Table: (x)TrainingHours has 2 columns with 1 being the TrainingName and 1
being the TrainingHours
Table: TrainingRegistration-the table the form is based on and would like
the hours auto looked up from previous table to fill in.
The following is the AfterUpdate formula I have for the lookup but am
getting errors.

Me.TrainingHours = DLookup("TrainingHours", "(x)TrainingHours",
"[TrainingName]" = " & VarTrainingHours & ")

Thanks
Christina

1. Set up a 2 column table of training registrations. The first column is
the name of the training (BPT1-3, etc.) and the second is the hours for that
type of training. If there are no set hours for a given type of training
(which appears to be the case for Overview), enter a zero.

2. Make the Row Source Type of the combo box = Table/Query and the Row
Source itself is a query that just pulls in the first column of the table.

3. In the AFTER_UPDATE event for the combobox, put code that does a DLOOKUP
against the 2nd column (the hours column) of the table based on the training
type selected. If the value is not zero, put that value in the hours
textbox on your form. If the value is zero, leave the hours textbox empty
so that the user can enter the hours.

You could do a variation on this in which the combo box is a two column
combobox that has the hours in a second (hidden) column and then retrieve
the hours from the combobox instead of using a DLOOKUP.

I am assuming that there is a reason why you don't have specific hours
established for the fourth training type (Overview).

"Christina Thrun-Western Dairyland EOC" <Christina Thrun-Western Dairyland
[email protected]> wrote in message
hours
of hours
are Right
now on
our

 

[Christina Thrun-Western Dairyland EOC]

I've tried what you have written and have this dlookup formula:
Me.TrainingHours = DLookup("TrainingHours", "(x)TrainingHours",
"[TrainingName] = me.TrainingName")
But I get the Error "You canceled previous operation"

Christina,

The third argument of DLOOKUP is the criteria for selecting the record you
want to look at. You want to look at the record for the selected training
NAME.

If the field in the form that contains the TrainingName selected is called
cmbTrainingName, then your DLOOKUP would read:

Me.TrainingHours = DLOOKUP("TrainingHours", "(x)TrainingHours",
"[TrainingName] = me.cmbTrainingName"

A couple of things about your criteria command:

1. It should all be one big string, or something that evaluates to one big
string. What you have is a string on the left side of an equal sign and
then another string on the right side of an equal sign. That's an
expression, it's not a string.

2. Your criteria is trying to select on the TrainingHours, but what you
want to do is look up the TrainingHours for the TrainingNAME that the user
selected

3. I don't understand the &s in the criteria.



"Christina Thrun-Western Dairyland EOC"

I'm working on the DLookup formula. I'm not real familiar with this. This
is what I have.
Table: (x)TrainingHours has 2 columns with 1 being the TrainingName and 1
being the TrainingHours
Table: TrainingRegistration-the table the form is based on and would like
the hours auto looked up from previous table to fill in.
The following is the AfterUpdate formula I have for the lookup but am
getting errors.

Me.TrainingHours = DLookup("TrainingHours", "(x)TrainingHours",
"[TrainingName]" = " & VarTrainingHours & ")

Thanks
Christina

1. Set up a 2 column table of training registrations. The first column is
the name of the training (BPT1-3, etc.) and the second is the hours for that
type of training. If there are no set hours for a given type of training
(which appears to be the case for Overview), enter a zero.

2. Make the Row Source Type of the combo box = Table/Query and the Row
Source itself is a query that just pulls in the first column of the table.

3. In the AFTER_UPDATE event for the combobox, put code that does a DLOOKUP
against the 2nd column (the hours column) of the table based on the training
type selected. If the value is not zero, put that value in the hours
textbox on your form. If the value is zero, leave the hours textbox empty
so that the user can enter the hours.

You could do a variation on this in which the combo box is a two column
combobox that has the hours in a second (hidden) column and then retrieve
the hours from the combobox instead of using a DLOOKUP.

I am assuming that there is a reason why you don't have specific hours
established for the fourth training type (Overview).

"Christina Thrun-Western Dairyland EOC" <Christina Thrun-Western Dairyland
[email protected]> wrote in message
I'm not really sure how to go about setting this up. I have a table for
training registrations. There is form based on that table. I have 4
different trainings that are in a drop down box in the form (BPT1-3,
Business
Overview, WNET, and Ebay/Pic/Shipping). For 3 of them the following hours
of
training are set:
BPT1-3 9 hours
WNET 2 hours
Ebay... 14.5 hours
What I would like is that when I choose one of these trainings the hours
are
automatically filled into the the TrainingHours field. I'm not sure if I
need a separate table, a query, how to pull the numbers in, etc... Right
now
we are just typing in the number of hours for the training, but with
multiple
people entering data it would be better if the hours could auto fill on
our
basic trainings.
Thanks

 

[JP]

Christina,

I'm sorry. I had a REALLY stupid moment when I typed that code.

It should read:
Me.TrainingHours = DLookup("TrainingHours", "(x)TrainingHours",
"[TrainingName] = '" & me.TrainingName & "'")

The above assumes that TrainingName is a text field (as opposed to being a
numeric field.

Assume, for example, that the value of me.TrainingName is Beginner Access,
then this will evaluate to:

me.TrainingHours = DLOOKUP("TrainingHours", "(x)TrainingHours",
"[TrainingName] = 'Beginner Access'")

Sorry about that.



"Christina Thrun-Western Dairyland EOC"

I've tried what you have written and have this dlookup formula:
Me.TrainingHours = DLookup("TrainingHours", "(x)TrainingHours",
"[TrainingName] = me.TrainingName")
But I get the Error "You canceled previous operation"

Christina,

The third argument of DLOOKUP is the criteria for selecting the record you
want to look at. You want to look at the record for the selected training
NAME.

If the field in the form that contains the TrainingName selected is called
cmbTrainingName, then your DLOOKUP would read:

Me.TrainingHours = DLOOKUP("TrainingHours", "(x)TrainingHours",
"[TrainingName] = me.cmbTrainingName"

A couple of things about your criteria command:

1. It should all be one big string, or something that evaluates to one big
string. What you have is a string on the left side of an equal sign and
then another string on the right side of an equal sign. That's an
expression, it's not a string.

2. Your criteria is trying to select on the TrainingHours, but what you
want to do is look up the TrainingHours for the TrainingNAME that the user
selected

3. I don't understand the &s in the criteria.



"Christina Thrun-Western Dairyland EOC"

I'm working on the DLookup formula. I'm not real familiar with this. This
is what I have.
Table: (x)TrainingHours has 2 columns with 1 being the TrainingName and 1
being the TrainingHours
Table: TrainingRegistration-the table the form is based on and would like
the hours auto looked up from previous table to fill in.
The following is the AfterUpdate formula I have for the lookup but am
getting errors.

Me.TrainingHours = DLookup("TrainingHours", "(x)TrainingHours",
"[TrainingName]" = " & VarTrainingHours & ")

Thanks
Christina


:

1. Set up a 2 column table of training registrations. The first

column
is

the name of the training (BPT1-3, etc.) and the second is the hours

for
that

type of training. If there are no set hours for a given type of training
(which appears to be the case for Overview), enter a zero.

2. Make the Row Source Type of the combo box = Table/Query and the Row
Source itself is a query that just pulls in the first column of the table.

3. In the AFTER_UPDATE event for the combobox, put code that does a DLOOKUP
against the 2nd column (the hours column) of the table based on the training
type selected. If the value is not zero, put that value in the hours
textbox on your form. If the value is zero, leave the hours textbox empty
so that the user can enter the hours.

You could do a variation on this in which the combo box is a two column
combobox that has the hours in a second (hidden) column and then retrieve
the hours from the combobox instead of using a DLOOKUP.

I am assuming that there is a reason why you don't have specific hours
established for the fourth training type (Overview).

"Christina Thrun-Western Dairyland EOC" <Christina Thrun-Western Dairyland
[email protected]> wrote in message
I'm not really sure how to go about setting this up. I have a

table
for

training registrations. There is form based on that table. I have 4
different trainings that are in a drop down box in the form (BPT1-3,
Business
Overview, WNET, and Ebay/Pic/Shipping). For 3 of them the

following
hours

of
training are set:
BPT1-3 9 hours
WNET 2 hours
Ebay... 14.5 hours
What I would like is that when I choose one of these trainings the hours
are
automatically filled into the the TrainingHours field. I'm not

sure
if I

need a separate table, a query, how to pull the numbers in, etc... Right
now
we are just typing in the number of hours for the training, but with
multiple
people entering data it would be better if the hours could auto

fill
on

our
basic trainings.
Thanks