Simple Syntax for Table Reference Problem

[Guest]

Hello,

I have a linked table tblGoals, which has [Messages] of type integer. I am
simply trying to assign

dMsgGoals = [tblGoals].[Messages]/20.

Currently, Messages = 1000. I am expecting dMsgGoals to be assigned 50.

I think my syntax is off because I keep getting "Access can't find the field
"|" referred to in your expression."

Your help on this simple problem is much appreciated.

 

[Marshall Barton]

I have a linked table tblGoals, which has [Messages] of type integer. I am
simply trying to assign

dMsgGoals = [tblGoals].[Messages]/20.

Currently, Messages = 1000. I am expecting dMsgGoals to be assigned 50.

I think my syntax is off because I keep getting "Access can't find the field
"|" referred to in your expression."

VBA code can not just refer to a field in a table, it must
look up the appropriate record and retrieve the value of the
field.

There are many ways to do that, the "best" way depends on
what else you are doing. If the Messages field is in the
form's record source, then you can refer to it as a property
of the form, e.g. Me.Messages

If the field is not related to the form's record source,
then you can use the DLookup function to get the value:
DLookup("Messages", "tblGoals")
but if tblGoals has more than one record, criteria must also
be used to select the appropriate record with the value.

 

[Guest]

I think we are getting somewhere. The new error is "Invalid use of null."
Messages is of type integer with a value of 1000.

I have a linked table tblGoals, which has [Messages] of type integer. I am
simply trying to assign

dMsgGoals = [tblGoals].[Messages]/20.

Currently, Messages = 1000. I am expecting dMsgGoals to be assigned 50.

I think my syntax is off because I keep getting "Access can't find the field
"|" referred to in your expression."

VBA code can not just refer to a field in a table, it must
look up the appropriate record and retrieve the value of the
field.

There are many ways to do that, the "best" way depends on
what else you are doing. If the Messages field is in the
form's record source, then you can refer to it as a property
of the form, e.g. Me.Messages

If the field is not related to the form's record source,
then you can use the DLookup function to get the value:
DLookup("Messages", "tblGoals")
but if tblGoals has more than one record, criteria must also
be used to select the appropriate record with the value.

 

[Marshall Barton]

That might be a useful clue if I could see the code that you
used when you got that error and you identified the line
that Access complained about.

Making some guesses, dMsgGoals is declared as a Double, you
used DLookup and it didn't find anything so it returned
Null, which can not be stored in anything other than a
Variant.
--
Marsh
MVP [MS Access]

I think we are getting somewhere. The new error is "Invalid use of null."
Messages is of type integer with a value of 1000.

I have a linked table tblGoals, which has [Messages] of type integer. I am
simply trying to assign

dMsgGoals = [tblGoals].[Messages]/20.

Currently, Messages = 1000. I am expecting dMsgGoals to be assigned 50.

I think my syntax is off because I keep getting "Access can't find the field
"|" referred to in your expression."

VBA code can not just refer to a field in a table, it must
look up the appropriate record and retrieve the value of the
field.

There are many ways to do that, the "best" way depends on
what else you are doing. If the Messages field is in the
form's record source, then you can refer to it as a property
of the form, e.g. Me.Messages

If the field is not related to the form's record source,
then you can use the DLookup function to get the value:
DLookup("Messages", "tblGoals")
but if tblGoals has more than one record, criteria must also
be used to select the appropriate record with the value.

 

[Guest]

dMsgGoal = DLookup("Messages", "tblGoals").
dMsgGoal as Integer.

That might be a useful clue if I could see the code that you
used when you got that error and you identified the line
that Access complained about.

Making some guesses, dMsgGoals is declared as a Double, you
used DLookup and it didn't find anything so it returned
Null, which can not be stored in anything other than a
Variant.
--
Marsh
MVP [MS Access]

I think we are getting somewhere. The new error is "Invalid use of null."
Messages is of type integer with a value of 1000.

Rod wrote:
I have a linked table tblGoals, which has [Messages] of type integer. I am
simply trying to assign

dMsgGoals = [tblGoals].[Messages]/20.

Currently, Messages = 1000. I am expecting dMsgGoals to be assigned 50.

I think my syntax is off because I keep getting "Access can't find the field
"|" referred to in your expression."


VBA code can not just refer to a field in a table, it must
look up the appropriate record and retrieve the value of the
field.

There are many ways to do that, the "best" way depends on
what else you are doing. If the Messages field is in the
form's record source, then you can refer to it as a property
of the form, e.g. Me.Messages

If the field is not related to the form's record source,
then you can use the DLookup function to get the value:
DLookup("Messages", "tblGoals")
but if tblGoals has more than one record, criteria must also
be used to select the appropriate record with the value.

 

[John W. Vinson]

dMsgGoal = DLookup("Messages", "tblGoals").
dMsgGoal as Integer.

This will look up the value of the field named [Messages] in the table
[tblGoals], returning the value from the first record in the table. If there
is one record, or 100 records, or 100000 records in the table, it makes no
difference - you'll always get the value from the first record in disk storage
order.

I somehow suspect that is not your intent.

John W. Vinson [MVP]

 

[Marshall Barton]

If that's the line with the error, the error message means
that tblGoals has a null value in the Messages field or that
tblGoals has no records.

If that's not the line with the error, then I can't guess
without seeing the rest of the procedure.
--
Marsh
MVP [MS Access]

dMsgGoal = DLookup("Messages", "tblGoals").
dMsgGoal as Integer.

That might be a useful clue if I could see the code that you
used when you got that error and you identified the line
that Access complained about.

Making some guesses, dMsgGoals is declared as a Double, you
used DLookup and it didn't find anything so it returned
Null, which can not be stored in anything other than a
Variant.

I think we are getting somewhere. The new error is "Invalid use of null."
Messages is of type integer with a value of 1000.

:

Rod wrote:
I have a linked table tblGoals, which has [Messages] of type integer. I am
simply trying to assign

dMsgGoals = [tblGoals].[Messages]/20.

Currently, Messages = 1000. I am expecting dMsgGoals to be assigned 50.

I think my syntax is off because I keep getting "Access can't find the field
"|" referred to in your expression."


VBA code can not just refer to a field in a table, it must
look up the appropriate record and retrieve the value of the
field.

There are many ways to do that, the "best" way depends on
what else you are doing. If the Messages field is in the
form's record source, then you can refer to it as a property
of the form, e.g. Me.Messages

If the field is not related to the form's record source,
then you can use the DLookup function to get the value:
DLookup("Messages", "tblGoals")
but if tblGoals has more than one record, criteria must also
be used to select the appropriate record with the value.

 

[Guest]

I have not gotten far on this test form I am building (is i t because it is a
Form_Open?):
Private Sub Form_Open(Cancel As Integer)
'Dim MaxCallTime As Integer 'Maximum minutes in call period
'Dim CallStartTime As Integer 'When does calling start
Dim dMsgGoal As Integer 'daily recruiting message goal

dMsgGoal = DLookup("RecruitMessages", "tblGoals", "")
'MaxCallTime = 210 'Max minutes: 8:30AM to noon
End Sub

If that's the line with the error, the error message means
that tblGoals has a null value in the Messages field or that
tblGoals has no records.

If that's not the line with the error, then I can't guess
without seeing the rest of the procedure.
--
Marsh
MVP [MS Access]

dMsgGoal = DLookup("Messages", "tblGoals").
dMsgGoal as Integer.

That might be a useful clue if I could see the code that you
used when you got that error and you identified the line
that Access complained about.

Making some guesses, dMsgGoals is declared as a Double, you
used DLookup and it didn't find anything so it returned
Null, which can not be stored in anything other than a
Variant.


Rod wrote:
I think we are getting somewhere. The new error is "Invalid use of null."
Messages is of type integer with a value of 1000.

:

Rod wrote:
I have a linked table tblGoals, which has [Messages] of type integer. I am
simply trying to assign

dMsgGoals = [tblGoals].[Messages]/20.

Currently, Messages = 1000. I am expecting dMsgGoals to be assigned 50.

I think my syntax is off because I keep getting "Access can't find the field
"|" referred to in your expression."


VBA code can not just refer to a field in a table, it must
look up the appropriate record and retrieve the value of the
field.

There are many ways to do that, the "best" way depends on
what else you are doing. If the Messages field is in the
form's record source, then you can refer to it as a property
of the form, e.g. Me.Messages

If the field is not related to the form's record source,
then you can use the DLookup function to get the value:
DLookup("Messages", "tblGoals")
but if tblGoals has more than one record, criteria must also
be used to select the appropriate record with the value.

 

[Marshall Barton]

Please explain what is in tblGoals. Without more
information, I have had to assume that there is only one
record (with one field).

Your code below looks up the value in the RecruitMessages
field in the first (only?) record, assigns it to a **local**
variable, and then exits (which discards the local
variable). Because of your use of a local variable, the
procedure has no effect on anything in your application.

The Open event may, or may not, be an appropriate place to
do this. It depends on what your objective for the dMsgGoal
variable happens to be.

Lacking all that important information, you might(?) make
more progress using:

Dim dMsgGoal As Integer 'daily recruiting message goal

Private Sub Form_Open(Cancel As Integer)
dMsgGoal = DLookup("RecruitMessages", "tblGoals") / 20
End Sub
--
Marsh
MVP [MS Access]

I have not gotten far on this test form I am building (is i t because it is a
Form_Open?):
Private Sub Form_Open(Cancel As Integer)
'Dim MaxCallTime As Integer 'Maximum minutes in call period
'Dim CallStartTime As Integer 'When does calling start
Dim dMsgGoal As Integer 'daily recruiting message goal

dMsgGoal = DLookup("RecruitMessages", "tblGoals", "")
'MaxCallTime = 210 'Max minutes: 8:30AM to noon
End Sub

If that's the line with the error, the error message means
that tblGoals has a null value in the Messages field or that
tblGoals has no records.

If that's not the line with the error, then I can't guess
without seeing the rest of the procedure.

dMsgGoal = DLookup("Messages", "tblGoals").
dMsgGoal as Integer.

:

That might be a useful clue if I could see the code that you
used when you got that error and you identified the line
that Access complained about.

Making some guesses, dMsgGoals is declared as a Double, you
used DLookup and it didn't find anything so it returned
Null, which can not be stored in anything other than a
Variant.


Rod wrote:
I think we are getting somewhere. The new error is "Invalid use of null."
Messages is of type integer with a value of 1000.

:

Rod wrote:
I have a linked table tblGoals, which has [Messages] of type integer. I am
simply trying to assign

dMsgGoals = [tblGoals].[Messages]/20.

Currently, Messages = 1000. I am expecting dMsgGoals to be assigned 50.

I think my syntax is off because I keep getting "Access can't find the field
"|" referred to in your expression."


VBA code can not just refer to a field in a table, it must
look up the appropriate record and retrieve the value of the
field.

There are many ways to do that, the "best" way depends on
what else you are doing. If the Messages field is in the
form's record source, then you can refer to it as a property
of the form, e.g. Me.Messages

If the field is not related to the form's record source,
then you can use the DLookup function to get the value:
DLookup("Messages", "tblGoals")
but if tblGoals has more than one record, criteria must also
be used to select the appropriate record with the value.

 

[Guest]

The idea of tblGoals is a place where the powers-that-be can set goals for
specific activities - one is recruiting. There will be one entry per
category, such as RecruitMessages left set to 1000, for example, and
Interviews set to whatever. The thought is when the goal changes a single
entry in tblGoals will feed the change to other forms. There is one record
in tblGoals with fields such as RecreuitMessage=1000, Interviews=20, Hires=10

I hope this helps. Thanks MUCH for sticking with this problem!

Please explain what is in tblGoals. Without more
information, I have had to assume that there is only one
record (with one field).

Your code below looks up the value in the RecruitMessages
field in the first (only?) record, assigns it to a **local**
variable, and then exits (which discards the local
variable). Because of your use of a local variable, the
procedure has no effect on anything in your application.

The Open event may, or may not, be an appropriate place to
do this. It depends on what your objective for the dMsgGoal
variable happens to be.

Lacking all that important information, you might(?) make
more progress using:

Dim dMsgGoal As Integer 'daily recruiting message goal

Private Sub Form_Open(Cancel As Integer)
dMsgGoal = DLookup("RecruitMessages", "tblGoals") / 20
End Sub
--
Marsh
MVP [MS Access]

I have not gotten far on this test form I am building (is i t because it is a
Form_Open?):
Private Sub Form_Open(Cancel As Integer)
'Dim MaxCallTime As Integer 'Maximum minutes in call period
'Dim CallStartTime As Integer 'When does calling start
Dim dMsgGoal As Integer 'daily recruiting message goal

dMsgGoal = DLookup("RecruitMessages", "tblGoals", "")
'MaxCallTime = 210 'Max minutes: 8:30AM to noon
End Sub

If that's the line with the error, the error message means
that tblGoals has a null value in the Messages field or that
tblGoals has no records.

If that's not the line with the error, then I can't guess
without seeing the rest of the procedure.


Rod wrote:
dMsgGoal = DLookup("Messages", "tblGoals").
dMsgGoal as Integer.

:

That might be a useful clue if I could see the code that you
used when you got that error and you identified the line
that Access complained about.

Making some guesses, dMsgGoals is declared as a Double, you
used DLookup and it didn't find anything so it returned
Null, which can not be stored in anything other than a
Variant.


Rod wrote:
I think we are getting somewhere. The new error is "Invalid use of null."
Messages is of type integer with a value of 1000.

:

Rod wrote:
I have a linked table tblGoals, which has [Messages] of type integer. I am
simply trying to assign

dMsgGoals = [tblGoals].[Messages]/20.

Currently, Messages = 1000. I am expecting dMsgGoals to be assigned 50.

I think my syntax is off because I keep getting "Access can't find the field
"|" referred to in your expression."


VBA code can not just refer to a field in a table, it must
look up the appropriate record and retrieve the value of the
field.

There are many ways to do that, the "best" way depends on
what else you are doing. If the Messages field is in the
form's record source, then you can refer to it as a property
of the form, e.g. Me.Messages

If the field is not related to the form's record source,
then you can use the DLookup function to get the value:
DLookup("Messages", "tblGoals")
but if tblGoals has more than one record, criteria must also
be used to select the appropriate record with the value.

 

[Marshall Barton]

OK, it's good to know that my assumption was correct. But,
you still need to explain what you are trying to do with the
value from the table. Just stuffing it into a variable,
public or not, won't accomplish anything.

If you just want to display the goal in a text box on a
form/report, then you don't need any code. Simply use the
DLookup in the text box's control source exoression:
=DLookup("RecruitMessages", "tblGoals") / 20
If you have something else in mind, don't keep it a secret.
--
Marsh
MVP [MS Access]

The idea of tblGoals is a place where the powers-that-be can set goals for
specific activities - one is recruiting. There will be one entry per
category, such as RecruitMessages left set to 1000, for example, and
Interviews set to whatever. The thought is when the goal changes a single
entry in tblGoals will feed the change to other forms. There is one record
in tblGoals with fields such as RecreuitMessage=1000, Interviews=20, Hires=10

Please explain what is in tblGoals. Without more
information, I have had to assume that there is only one
record (with one field).

Your code below looks up the value in the RecruitMessages
field in the first (only?) record, assigns it to a **local**
variable, and then exits (which discards the local
variable). Because of your use of a local variable, the
procedure has no effect on anything in your application.

The Open event may, or may not, be an appropriate place to
do this. It depends on what your objective for the dMsgGoal
variable happens to be.

Lacking all that important information, you might(?) make
more progress using:

Dim dMsgGoal As Integer 'daily recruiting message goal

Private Sub Form_Open(Cancel As Integer)
dMsgGoal = DLookup("RecruitMessages", "tblGoals") / 20
End Sub

I have not gotten far on this test form I am building (is i t because it is a
Form_Open?):
Private Sub Form_Open(Cancel As Integer)
'Dim MaxCallTime As Integer 'Maximum minutes in call period
'Dim CallStartTime As Integer 'When does calling start
Dim dMsgGoal As Integer 'daily recruiting message goal

dMsgGoal = DLookup("RecruitMessages", "tblGoals", "")
'MaxCallTime = 210 'Max minutes: 8:30AM to noon
End Sub


:
If that's the line with the error, the error message means
that tblGoals has a null value in the Messages field or that
tblGoals has no records.

If that's not the line with the error, then I can't guess
without seeing the rest of the procedure.


Rod wrote:
dMsgGoal = DLookup("Messages", "tblGoals").
dMsgGoal as Integer.

:

That might be a useful clue if I could see the code that you
used when you got that error and you identified the line
that Access complained about.

Making some guesses, dMsgGoals is declared as a Double, you
used DLookup and it didn't find anything so it returned
Null, which can not be stored in anything other than a
Variant.


Rod wrote:
I think we are getting somewhere. The new error is "Invalid use of null."
Messages is of type integer with a value of 1000.

Rod wrote:
I have a linked table tblGoals, which has [Messages] of type integer. I am
simply trying to assign

dMsgGoals = [tblGoals].[Messages]/20.

Currently, Messages = 1000. I am expecting dMsgGoals to be assigned 50.

I think my syntax is off because I keep getting "Access can't find the field
"|" referred to in your expression."

 

[Guest]

tblGoals is suppose to store the monthly goals. I'll need to break that down
into weekly and business weekly goals (5days). So, when someone presses the
"Dial" button, for example, the user will see if he is on track for the
number of messages to leave for the day. So, given the start time, the
current time and the end time (noon) I will be able to calculate if the user
is on pace to reach the goal or needs to pick up the pace.

Other fields have other uses, but all of them are of the same nature - a
goal to be used to show if on-pace for the time period
(day/week/month/quarter).

Hopefully this is an OK way get it done.

Thanks MUCH!

OK, it's good to know that my assumption was correct. But,
you still need to explain what you are trying to do with the
value from the table. Just stuffing it into a variable,
public or not, won't accomplish anything.

If you just want to display the goal in a text box on a
form/report, then you don't need any code. Simply use the
DLookup in the text box's control source exoression:
=DLookup("RecruitMessages", "tblGoals") / 20
If you have something else in mind, don't keep it a secret.
--
Marsh
MVP [MS Access]

The idea of tblGoals is a place where the powers-that-be can set goals for
specific activities - one is recruiting. There will be one entry per
category, such as RecruitMessages left set to 1000, for example, and
Interviews set to whatever. The thought is when the goal changes a single
entry in tblGoals will feed the change to other forms. There is one record
in tblGoals with fields such as RecreuitMessage=1000, Interviews=20, Hires=10

Please explain what is in tblGoals. Without more
information, I have had to assume that there is only one
record (with one field).

Your code below looks up the value in the RecruitMessages
field in the first (only?) record, assigns it to a **local**
variable, and then exits (which discards the local
variable). Because of your use of a local variable, the
procedure has no effect on anything in your application.

The Open event may, or may not, be an appropriate place to
do this. It depends on what your objective for the dMsgGoal
variable happens to be.

Lacking all that important information, you might(?) make
more progress using:

Dim dMsgGoal As Integer 'daily recruiting message goal

Private Sub Form_Open(Cancel As Integer)
dMsgGoal = DLookup("RecruitMessages", "tblGoals") / 20
End Sub


Rod wrote:
I have not gotten far on this test form I am building (is i t because it is a
Form_Open?):
Private Sub Form_Open(Cancel As Integer)
'Dim MaxCallTime As Integer 'Maximum minutes in call period
'Dim CallStartTime As Integer 'When does calling start
Dim dMsgGoal As Integer 'daily recruiting message goal

dMsgGoal = DLookup("RecruitMessages", "tblGoals", "")
'MaxCallTime = 210 'Max minutes: 8:30AM to noon
End Sub


:
If that's the line with the error, the error message means
that tblGoals has a null value in the Messages field or that
tblGoals has no records.

If that's not the line with the error, then I can't guess
without seeing the rest of the procedure.


Rod wrote:
dMsgGoal = DLookup("Messages", "tblGoals").
dMsgGoal as Integer.

:

That might be a useful clue if I could see the code that you
used when you got that error and you identified the line
that Access complained about.

Making some guesses, dMsgGoals is declared as a Double, you
used DLookup and it didn't find anything so it returned
Null, which can not be stored in anything other than a
Variant.


Rod wrote:
I think we are getting somewhere. The new error is "Invalid use of null."
Messages is of type integer with a value of 1000.

Rod wrote:
I have a linked table tblGoals, which has [Messages] of type integer. I am
simply trying to assign

dMsgGoals = [tblGoals].[Messages]/20.

Currently, Messages = 1000. I am expecting dMsgGoals to be assigned 50.

I think my syntax is off because I keep getting "Access can't find the field
"|" referred to in your expression."

 

[Marshall Barton]

Since a goal is only useful(?) information (not something
used to control the form's behavior), can I conclude that
all you want to do is display it? If so, try using a text
box with the expression I suggested earlier.

 

[Guest]

NO, I do not only want to display it. The goal will change from tim-to-time.
The "goal setter" will have a simple form to change the goal. The value for
the goal will be used by what I am doing to calculate if on pace or not. I
will need to store the various goals and then operate on them.

Since a goal is only useful(?) information (not something
used to control the form's behavior), can I conclude that
all you want to do is display it? If so, try using a text
box with the expression I suggested earlier.
--
Marsh
MVP [MS Access]

tblGoals is suppose to store the monthly goals. I'll need to break that down
into weekly and business weekly goals (5days). So, when someone presses the
"Dial" button, for example, the user will see if he is on track for the
number of messages to leave for the day. So, given the start time, the
current time and the end time (noon) I will be able to calculate if the user
is on pace to reach the goal or needs to pick up the pace.

Other fields have other uses, but all of them are of the same nature - a
goal to be used to show if on-pace for the time period
(day/week/month/quarter).

 

[Marshall Barton]

Then you need to save the value of the DLookup, probably in
a bound text box.

Use your form's Load event instead of the Open event. The
code we had earlier should work if you add a text box named
dMsgGoal.

 

[Guest]

tblGoals is an external file. How would I do a Dlookup on an external file?

Then you need to save the value of the DLookup, probably in
a bound text box.

Use your form's Load event instead of the Open event. The
code we had earlier should work if you add a text box named
dMsgGoal.
--
Marsh
MVP [MS Access]

NO, I do not only want to display it. The goal will change from tim-to-time.
The "goal setter" will have a simple form to change the goal. The value for
the goal will be used by what I am doing to calculate if on pace or not. I
will need to store the various goals and then operate on them.

 

[Marshall Barton]

Do you always provide critical need to know information in
tiny little trickles ;-)

What is this "external file"?
Can you link to it?

 

[Guest]

I completely deserved that one - my bad. I really thought I gave ALL of the
pertinent information. It is a linked table. Does it make a difference in
this case? The file is Goals.MDB. Do you need the complete path?

Do you always provide critical need to know information in
tiny little trickles ;-)

What is this "external file"?
Can you link to it?
--
Marsh
MVP [MS Access]

tblGoals is an external file. How would I do a Dlookup on an external file?

 

[Marshall Barton]

If it's linked. then Access takes care of the path and other
stuff so the fact that it's in another mdb file is
irrelevant to this problem. Just use the table name in your
front end mdb as if it were actually a local table.

You can test this yourself without getting wrapped up in the
complexities of your form. Use Ctrl+G to open the VBE
Immediate window. Then type:
?DLookup("RecruitMessages", "tblGoals") / 20
and hit enter. You should then see the result or get some
kind of error so you can fix things and try again.