Problem with DLookup

[Dave]

Hey
I'm having problems with a DLookup function I'm trying to use that I'm
unable to fix. The problem is that when I try and run the function it
returns with a message saying it can't find the form i am referencing.
However, I have checked and rechecked the name, making sure both were written
the same, yet I still have not been able to right the function. Heres the
code :

Dim stmonth As Variant
Dim styear As Variant
Dim formname As String

formname = "Open"
stmonth = DLookup("[Month]", "[Audit Name]", "[Name] = " &
Form!formname!List8)
styear = DLookup("[Year]", "[Audit Name]", "[Name] = " &
Form!formname!List8)


It continues to say it can not find formname. Any ideas.

Thanks

 

[Svetlana]

Why you need a variable for the form's name?

stmonth = DLookup("[Month]", "[Audit Name]", "[Name] = """ &
Forms!Open!List8 & """")
styear = DLookup("[Year]", "[Audit Name]", "[Name] =""" &
Forms!Open!List8 & """")

 

[Jeff L]

You cannot make a reference like that. It would need to be
Forms!frmOpen!List8.

I would also suggest that you not use Month and Year as field names.
They are funtions in Access and are reserved words. You might want to
name your controls on your form something more meaningful than List8.
You will be glad you did.

Hope that helps!

 

[Dave]

thanks for the help....made all the changes that you said, still had no luck
here's what i get when i try and run : Microsoft Office Access can't find
the field 'frmOpen' referred to in your expression.
this was the same problem I was having before.

You cannot make a reference like that. It would need to be
Forms!frmOpen!List8.

I would also suggest that you not use Month and Year as field names.
They are funtions in Access and are reserved words. You might want to
name your controls on your form something more meaningful than List8.
You will be glad you did.

Hope that helps!

Hey
I'm having problems with a DLookup function I'm trying to use that I'm
unable to fix. The problem is that when I try and run the function it
returns with a message saying it can't find the form i am referencing.
However, I have checked and rechecked the name, making sure both were written
the same, yet I still have not been able to right the function. Heres the
code :

Dim stmonth As Variant
Dim styear As Variant
Dim formname As String

formname = "Open"
stmonth = DLookup("[Month]", "[Audit Name]", "[Name] = " &
Form!formname!List8)
styear = DLookup("[Year]", "[Audit Name]", "[Name] = " &
Form!formname!List8)


It continues to say it can not find formname. Any ideas.

Thanks

 

[schasteen]

Is name a text value or number? If it is a text then:

stmonth = DLookup("[Month]", "[Audit Name]", "[Name] = '" &
Form![Open]!List8 & " ' " )
styear = DLookup("[Year]", "[Audit Name]", "[Name] = '" & Form![Open]!List8
& " ' " )

If it is a number than what has been suggested should work unles the form is
not open.

thanks for the help....made all the changes that you said, still had no luck
here's what i get when i try and run : Microsoft Office Access can't find
the field 'frmOpen' referred to in your expression.
this was the same problem I was having before.

You cannot make a reference like that. It would need to be
Forms!frmOpen!List8.

I would also suggest that you not use Month and Year as field names.
They are funtions in Access and are reserved words. You might want to
name your controls on your form something more meaningful than List8.
You will be glad you did.

Hope that helps!

Hey
I'm having problems with a DLookup function I'm trying to use that I'm
unable to fix. The problem is that when I try and run the function it
returns with a message saying it can't find the form i am referencing.
However, I have checked and rechecked the name, making sure both were written
the same, yet I still have not been able to right the function. Heres the
code :

Dim stmonth As Variant
Dim styear As Variant
Dim formname As String

formname = "Open"
stmonth = DLookup("[Month]", "[Audit Name]", "[Name] = " &
Form!formname!List8)
styear = DLookup("[Year]", "[Audit Name]", "[Name] = " &
Form!formname!List8)


It continues to say it can not find formname. Any ideas.

Thanks

 

[fredg]

Hey
I'm having problems with a DLookup function I'm trying to use that I'm
unable to fix. The problem is that when I try and run the function it
returns with a message saying it can't find the form i am referencing.
However, I have checked and rechecked the name, making sure both were written
the same, yet I still have not been able to right the function. Heres the
code :

Dim stmonth As Variant
Dim styear As Variant
Dim formname As String

formname = "Open"
stmonth = DLookup("[Month]", "[Audit Name]", "[Name] = " &
Form!formname!List8)
styear = DLookup("[Year]", "[Audit Name]", "[Name] = " &
Form!formname!List8)

It continues to say it can not find formname. Any ideas.

Thanks

Besides your incorrect use of form!formname!List8 (it should have been
forms!Open!List8, or if this code is on the Open form, Me!List8), you
hit the quadruple whammy!

Open, Month, Year, and Name are all reserved Access/VBA/Jet word and
should not be used as a object name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[Jeff L]

I noticed in your original post that your form name is Open, not
frmOpen. Sorry, I guess I got caught up in my own naming techniques!
LOL. Change FrmOpen to Open.

thanks for the help....made all the changes that you said, still had no luck
here's what i get when i try and run : Microsoft Office Access can't find
the field 'frmOpen' referred to in your expression.
this was the same problem I was having before.

You cannot make a reference like that. It would need to be
Forms!frmOpen!List8.

I would also suggest that you not use Month and Year as field names.
They are funtions in Access and are reserved words. You might want to
name your controls on your form something more meaningful than List8.
You will be glad you did.

Hope that helps!

Hey
I'm having problems with a DLookup function I'm trying to use that I'm
unable to fix. The problem is that when I try and run the function it
returns with a message saying it can't find the form i am referencing.
However, I have checked and rechecked the name, making sure both were written
the same, yet I still have not been able to right the function. Heres the
code :

Dim stmonth As Variant
Dim styear As Variant
Dim formname As String

formname = "Open"
stmonth = DLookup("[Month]", "[Audit Name]", "[Name] = " &
Form!formname!List8)
styear = DLookup("[Year]", "[Audit Name]", "[Name] = " &
Form!formname!List8)


It continues to say it can not find formname. Any ideas.

Thanks

 

[Dave]

Month Year and Name are not object names rather field names in a table

Hey
I'm having problems with a DLookup function I'm trying to use that I'm
unable to fix. The problem is that when I try and run the function it
returns with a message saying it can't find the form i am referencing.
However, I have checked and rechecked the name, making sure both were written
the same, yet I still have not been able to right the function. Heres the
code :

Dim stmonth As Variant
Dim styear As Variant
Dim formname As String

formname = "Open"
stmonth = DLookup("[Month]", "[Audit Name]", "[Name] = " &
Form!formname!List8)
styear = DLookup("[Year]", "[Audit Name]", "[Name] = " &
Form!formname!List8)

It continues to say it can not find formname. Any ideas.

Thanks

Besides your incorrect use of form!formname!List8 (it should have been
forms!Open!List8, or if this code is on the Open form, Me!List8), you
hit the quadruple whammy!

Open, Month, Year, and Name are all reserved Access/VBA/Jet word and
should not be used as a object name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[Dave]

It is a text field, however your changes did not change the outcome. I still
recieve the same message .... thanks though

Is name a text value or number? If it is a text then:

stmonth = DLookup("[Month]", "[Audit Name]", "[Name] = '" &
Form![Open]!List8 & " ' " )
styear = DLookup("[Year]", "[Audit Name]", "[Name] = '" & Form![Open]!List8
& " ' " )

If it is a number than what has been suggested should work unles the form is
not open.

thanks for the help....made all the changes that you said, still had no luck
here's what i get when i try and run : Microsoft Office Access can't find
the field 'frmOpen' referred to in your expression.
this was the same problem I was having before.

You cannot make a reference like that. It would need to be
Forms!frmOpen!List8.

I would also suggest that you not use Month and Year as field names.
They are funtions in Access and are reserved words. You might want to
name your controls on your form something more meaningful than List8.
You will be glad you did.

Hope that helps!

Dave wrote:
Hey
I'm having problems with a DLookup function I'm trying to use that I'm
unable to fix. The problem is that when I try and run the function it
returns with a message saying it can't find the form i am referencing.
However, I have checked and rechecked the name, making sure both were written
the same, yet I still have not been able to right the function. Heres the
code :

Dim stmonth As Variant
Dim styear As Variant
Dim formname As String

formname = "Open"
stmonth = DLookup("[Month]", "[Audit Name]", "[Name] = " &
Form!formname!List8)
styear = DLookup("[Year]", "[Audit Name]", "[Name] = " &
Form!formname!List8)


It continues to say it can not find formname. Any ideas.

Thanks

 

[fredg]

Month Year and Name are not object names rather field names in a table

Hey
I'm having problems with a DLookup function I'm trying to use that I'm
unable to fix. The problem is that when I try and run the function it
returns with a message saying it can't find the form i am referencing.
However, I have checked and rechecked the name, making sure both were written
the same, yet I still have not been able to right the function. Heres the
code :

Dim stmonth As Variant
Dim styear As Variant
Dim formname As String

formname = "Open"
stmonth = DLookup("[Month]", "[Audit Name]", "[Name] = " &
Form!formname!List8)
styear = DLookup("[Year]", "[Audit Name]", "[Name] = " &
Form!formname!List8)

It continues to say it can not find formname. Any ideas.

Thanks

Besides your incorrect use of form!formname!List8 (it should have been
forms!Open!List8, or if this code is on the Open form, Me!List8), you
hit the quadruple whammy!

Open, Month, Year, and Name are all reserved Access/VBA/Jet word and
should not be used as a object name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

From KB Article 209187

"This article lists words and symbols that you should not use in
field, object, and variable names because they are "reserved words."
Reserved words have a specific meaning to Microsoft Access or to the
Microsoft Jet database engine. If you use a reserved word or symbol,
you may receive an error such as the following: "

Note also that the list given is not complete.
From the Access 2000 article:
"MORE INFORMATION
Because it is not practical to provide a list of all reserved words,
such as built-in function names or Microsoft Access user-defined
names, please check your product documentation for additional reserved
words. Note that if you set a reference to a type library, an object
library, or an ActiveX control, that library's reserved words are also
reserved words in your database. For example, if you add an ActiveX
control to a form, a reference is set, and the names of the objects,
methods, and properties of that control become reserved words in your
database.

From Access help:

"When you name a field, control, or object, it's a good idea to make
sure the name doesn't duplicate the name of a property or other
element used by Microsoft Access; otherwise, your database can produce
unexpected behavior in some circumstances. For example, if you refer
to the value of a field called Name in a table NameInfo using the
syntax NameInfo.Name, Microsoft Access displays the value of the
table's Name property rather than the value of the Name field."

Now you're welcome to use any field names you wish, however you would
be well served to change those names as suggested by me earlier and by
Access help and KB articles.