auto filling a number

[Guest]

I posted last week (2/16) on this topic and have been working on it. I've
been trying to have a TrainingHours automatically fill in 1 field when a
training name is selected. I made a table with the training names and their
hours. After some discussion this is the formula I have:

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

But now I get the message "The field 'TRAINING REGISTRATION.TrainingHours'
cannot contain a Null value because the Required property for this field is
set to True. Enter a value in this field."
This is a required field, but when I'm selecting a Training Name it is
filling the field.



(I copied and pasted all previous posts on this below).


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
(e-mail address removed)> 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 posted corrected code for you in that thread from last week (I had an
error in the code I first posted for you).

Basically, the code should be:

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

Note that the code you posted below is missing a few ". As a result, it's
not finding anything with that training name, so TrainingHours is null,
which your database doesn't allow.

Try the above code.


"Christina Thrun-Western Dairyland EOC"

I posted last week (2/16) on this topic and have been working on it. I've
been trying to have a TrainingHours automatically fill in 1 field when a
training name is selected. I made a table with the training names and their
hours. After some discussion this is the formula I have:

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

But now I get the message "The field 'TRAINING REGISTRATION.TrainingHours'
cannot contain a Null value because the Required property for this field is
set to True. Enter a value in this field."
This is a required field, but when I'm selecting a Training Name it is
filling the field.



(I copied and pasted all previous posts on this below).


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"
message 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

the

Row

 

[Guest]

It worked! Thanks a ton.

Christina,

I posted corrected code for you in that thread from last week (I had an
error in the code I first posted for you).

Basically, the code should be:

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

Note that the code you posted below is missing a few ". As a result, it's
not finding anything with that training name, so TrainingHours is null,
which your database doesn't allow.

Try the above code.


"Christina Thrun-Western Dairyland EOC"

I posted last week (2/16) on this topic and have been working on it. I've
been trying to have a TrainingHours automatically fill in 1 field when a
training name is selected. I made a table with the training names and their
hours. After some discussion this is the formula I have:

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

But now I get the message "The field 'TRAINING REGISTRATION.TrainingHours'
cannot contain a Null value because the Required property for this field is
set to True. Enter a value in this field."
This is a required field, but when I'm selecting a Training Name it is
filling the field.



(I copied and pasted all previous posts on this below).


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"
message 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
(e-mail address removed)> 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