Form field not interpreted correctly

  • Thread starter Thread starter upsman via AccessMonster.com
  • Start date Start date
U

upsman via AccessMonster.com

I have a field on a form, that for the purpose of my test, contains the value
"HSN". I have the following code in the OnOpen event of a completely
different form:

Dim stLinkCriteria As String
Dim stCampus

MsgBox "campus = " & Forms!Switchboard!txtCampus

stLinkCriteria = "SELECT * FROM Classes WHERE Campus = " & Forms!Switchboard!
txtCampus
Me.RecordSource = stLinkCriteria

When the form opens, the msgbox says "campus = HSN". This is correct. But
when it gets to the stLinkCriteria statement, an 'Enter Parameter Value' box
pops up and asks for me to enter a value for the field "HSN". Why does it
think "HSN" is a field and not the value in the field? I want to select all
records where Classes.Campus matches whatever value is in Forms!Switchboard!
txtCampus. How do I do that?

Rod
 
Delimit text value HSN with ' characters:

stLinkCriteria = "SELECT * FROM Classes WHERE Campus = '" &
Forms!Switchboard!txtCampus & "'"
 
upsman via AccessMonster.com said:
I have a field on a form, that for the purpose of my test, contains the
value
"HSN". I have the following code in the OnOpen event of a completely
different form:

Dim stLinkCriteria As String
Dim stCampus

MsgBox "campus = " & Forms!Switchboard!txtCampus

stLinkCriteria = "SELECT * FROM Classes WHERE Campus = " &
Forms!Switchboard!
txtCampus
Me.RecordSource = stLinkCriteria

When the form opens, the msgbox says "campus = HSN". This is correct.
But
when it gets to the stLinkCriteria statement, an 'Enter Parameter Value'
box
pops up and asks for me to enter a value for the field "HSN". Why does
it
think "HSN" is a field and not the value in the field? I want to select
all
records where Classes.Campus matches whatever value is in
Forms!Switchboard!
txtCampus. How do I do that?

Rod
Click to expand...

Try:

stLinkCriteria = "SELECT * FROM Classes WHERE Campus = " & """" &
Forms!Switchboard!
txtCampus & """"

Ed Metcalfe.
 
Thanks guys, that worked. When and where to use "" still confuses me.

Ed said:
I have a field on a form, that for the purpose of my test, contains the
value
Click to expand...
[quoted text clipped - 24 lines]
Rod
Click to expand...

Try:

stLinkCriteria = "SELECT * FROM Classes WHERE Campus = " & """" &
Forms!Switchboard!
txtCampus & """"

Ed Metcalfe.
Click to expand...
 
upsman via AccessMonster.com said:
Thanks guys, that worked. When and where to use "" still confuses me.
Click to expand...

Different data types need to be "delimited" or "qualified" with different
characters. In MS Access these are:

Text/String - A single or double quote.
Numeric - No qualifier needed.
Date/Time - # symbol.

Your SQL statement was giving you problems as the value contained in your
textbox is a string but you were not including the text qualifier. So what
should have been:

SELECT * FROM Classes WHERE Campus = "HSN"

was actually:

SELECT * FROM Classes WHERE Campus = HSN

Ed Metcalfe.
 
Back
Top