Please Help with SQL Statement

S

S Jackson

I have a subform called fsubSurveyors inserted into a from called frmMaster.

The fsubSurveyors form is based on tblCaseSurveyors. It contains a Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter event of the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE clause and I
do not know how to fix it. Any help is very much appreciated. Thanks!
S. Jackson
 
C

Cheryl Fischer

Instead of:

& "WHERE tblSurveyors.Region = "frmMaster.Region" _

try the following, presuming frmMaster.Region is a Text field,

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region & chr(34) _

Also, I think I would run all of this code from the On Current event of
frmMaster, changing the last line from:

Me.Combo2.RowSource = strSQL

to

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource=strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

hth,
 
S

S Jackson

Hi Cheryl!

Thanks for your response. The Region field is a Number field. How does
this change things?

I guess I need to change the Dim statement to: (????)

Dim intSQL = Integer

Also does it affect the WHERE statement?????
&"WHERE tblSurveyors.Region=

What does the chr(34) stand for?

Thanks in advance!
Shelly


Cheryl Fischer said:
Instead of:

& "WHERE tblSurveyors.Region = "frmMaster.Region" _

try the following, presuming frmMaster.Region is a Text field,

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region & chr(34) _

Also, I think I would run all of this code from the On Current event of
frmMaster, changing the last line from:

Me.Combo2.RowSource = strSQL

to

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource=strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

S Jackson said:
I have a subform called fsubSurveyors inserted into a from called frmMaster.

The fsubSurveyors form is based on tblCaseSurveyors. It contains a Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter event of the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE clause
Click to expand...
and
I
do not know how to fix it. Any help is very much appreciated. Thanks!
S. Jackson
Click to expand...
Click to expand...
 
C

Cheryl Fischer

If Region is a Number data type, change this:

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region & chr(34) _

to this:

& "WHERE tblSurveyors.Region = " & frmMaster.Region _

What you had originally: & "WHERE tblSurveyors.Region = "frmMaster.Region"
_ would have looked for a tblSurveyors.Region value equal to
"frmMaster.Region" - not the number but the exact text value:
"frmMaster.Region". Since you put quotes around "frmMaster.Region" you made
it a literal or string value.

One good way to test out what a SQL string is going to look like is to build
it and then put a MsgBox strSQL statement in your code. That way you can
see exactly what your query is going to look like. For example, your
original strSQL would have been evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region =
"frmMaster.Region" ORDER by tblSurveyors.SvyName;"

If the value of frmMaster.Region is 1, then you would want your SQL string
to be evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region = 1 ORDER
by tblSurveyors.SvyName;"

To get that you need, the complete build of the string should look like:

Dim strSQL As String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName; "


Chr(34) is one way to put double quotes around a string that you are
building in VBA; for example, if Region were a text type and not a number.

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

S Jackson said:
Hi Cheryl!

Thanks for your response. The Region field is a Number field. How does
this change things?

I guess I need to change the Dim statement to: (????)

Dim intSQL = Integer

Also does it affect the WHERE statement?????
&"WHERE tblSurveyors.Region=

What does the chr(34) stand for?

Thanks in advance!
Shelly


Cheryl Fischer said:
Instead of:

& "WHERE tblSurveyors.Region = "frmMaster.Region" _

try the following, presuming frmMaster.Region is a Text field,

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region & chr(34) _

Also, I think I would run all of this code from the On Current event of
frmMaster, changing the last line from:

Me.Combo2.RowSource = strSQL

to

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource=strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

S Jackson said:
I have a subform called fsubSurveyors inserted into a from called frmMaster.

The fsubSurveyors form is based on tblCaseSurveyors. It contains a Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter event of the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE clause
Click to expand...
and
I
do not know how to fix it. Any help is very much appreciated. Thanks!
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
S

S Jackson

Cheryl:

Thanks so much for your patience and your help. But, here is what I have:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle" _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip" _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

End Sub

Here is the error I get:

Run-time error '424'
Object required.

Any ideas?

Thanks, Shelly

Cheryl Fischer said:
If Region is a Number data type, change this:

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region & chr(34) _

to this:

& "WHERE tblSurveyors.Region = " & frmMaster.Region _

What you had originally: & "WHERE tblSurveyors.Region = "frmMaster.Region"
_ would have looked for a tblSurveyors.Region value equal to
"frmMaster.Region" - not the number but the exact text value:
"frmMaster.Region". Since you put quotes around "frmMaster.Region" you made
it a literal or string value.

One good way to test out what a SQL string is going to look like is to build
it and then put a MsgBox strSQL statement in your code. That way you can
see exactly what your query is going to look like. For example, your
original strSQL would have been evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region =
"frmMaster.Region" ORDER by tblSurveyors.SvyName;"

If the value of frmMaster.Region is 1, then you would want your SQL string
to be evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region = 1 ORDER
by tblSurveyors.SvyName;"

To get that you need, the complete build of the string should look like:

Dim strSQL As String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName; "


Chr(34) is one way to put double quotes around a string that you are
building in VBA; for example, if Region were a text type and not a number.

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

S Jackson said:
Hi Cheryl!

Thanks for your response. The Region field is a Number field. How does
this change things?

I guess I need to change the Dim statement to: (????)

Dim intSQL = Integer

Also does it affect the WHERE statement?????
&"WHERE tblSurveyors.Region=

What does the chr(34) stand for?

Thanks in advance!
Shelly


Cheryl Fischer said:
Instead of:

& "WHERE tblSurveyors.Region = "frmMaster.Region" _

try the following, presuming frmMaster.Region is a Text field,

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region & chr(34) _

Also, I think I would run all of this code from the On Current event of
frmMaster, changing the last line from:

Me.Combo2.RowSource = strSQL

to

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource=strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have a subform called fsubSurveyors inserted into a from called
frmMaster.

The fsubSurveyors form is based on tblCaseSurveyors. It contains a Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter event
Click to expand...
of
the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE
Click to expand...
clause
and
I
do not know how to fix it. Any help is very much appreciated. Thanks!
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
R

Randy Harris

You need spaces and commas. Keep in mind that you are concatenating all of
those string segments. Access has no way of knowing that you show them on
different lines. It looks like one big long string to the program. Try
this:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip," _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region & " " _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

End Sub


As Cheryl has suggested, use a MsgBox to see exactly what the SQL string is.
That will help to find the problems.

MsgBox SQL


S Jackson said:
Cheryl:

Thanks so much for your patience and your help. But, here is what I have:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle" _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip" _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

End Sub

Here is the error I get:

Run-time error '424'
Object required.

Any ideas?

Thanks, Shelly

Cheryl Fischer said:
If Region is a Number data type, change this:

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region & chr(34) _

to this:

& "WHERE tblSurveyors.Region = " & frmMaster.Region _

What you had originally: & "WHERE tblSurveyors.Region = "frmMaster.Region"
_ would have looked for a tblSurveyors.Region value equal to
"frmMaster.Region" - not the number but the exact text value:
"frmMaster.Region". Since you put quotes around "frmMaster.Region" you made
it a literal or string value.

One good way to test out what a SQL string is going to look like is to build
it and then put a MsgBox strSQL statement in your code. That way you can
see exactly what your query is going to look like. For example, your
original strSQL would have been evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region =
"frmMaster.Region" ORDER by tblSurveyors.SvyName;"

If the value of frmMaster.Region is 1, then you would want your SQL string
to be evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region = 1 ORDER
by tblSurveyors.SvyName;"

To get that you need, the complete build of the string should look like:

Dim strSQL As String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName; "


Chr(34) is one way to put double quotes around a string that you are
building in VBA; for example, if Region were a text type and not a number.

hth,
Click to expand...
chr(34)
_
Also, I think I would run all of this code from the On Current event of
frmMaster, changing the last line from:

Me.Combo2.RowSource = strSQL

to

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource=strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have a subform called fsubSurveyors inserted into a from called
frmMaster.

The fsubSurveyors form is based on tblCaseSurveyors. It contains a
Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter event of
the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE clause
and
I
do not know how to fix it. Any help is very much appreciated. Thanks!
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
S

S Jackson

Here is another twist (I thought this would be easy, but now I am having
second thoughts).

If frmMaster.Region = 2, 9 or 10, THEN

strSQL="Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle"_
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, tblSurveyors.SvyCity,
tblSurveyors.SvyZip" _
& "tblSurveyors.SvyPhone,tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors" _
& "WHERE tblSurveyors.Region =

That's as far as I get. I want the fsubSurveyors to display information for
Regions 2, 9 AND 10 when frmMaster.Region equals EITHER 2, 9 or 10 (the
surveyors from these regions cover these regions combined, whereas other
Regions have their own surveyors exclusively). I know this is getting
fancy, but if I do not limit fsubSurveyors by Region, it makes the user
scroll through gobs of people.

What do you think? Too hard to do it?

Shelly


S Jackson said:
Cheryl:

Thanks so much for your patience and your help. But, here is what I have:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle" _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip" _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

End Sub

Here is the error I get:

Run-time error '424'
Object required.

Any ideas?

Thanks, Shelly

Cheryl Fischer said:
If Region is a Number data type, change this:

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region & chr(34) _

to this:

& "WHERE tblSurveyors.Region = " & frmMaster.Region _

What you had originally: & "WHERE tblSurveyors.Region = "frmMaster.Region"
_ would have looked for a tblSurveyors.Region value equal to
"frmMaster.Region" - not the number but the exact text value:
"frmMaster.Region". Since you put quotes around "frmMaster.Region" you made
it a literal or string value.

One good way to test out what a SQL string is going to look like is to build
it and then put a MsgBox strSQL statement in your code. That way you can
see exactly what your query is going to look like. For example, your
original strSQL would have been evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region =
"frmMaster.Region" ORDER by tblSurveyors.SvyName;"

If the value of frmMaster.Region is 1, then you would want your SQL string
to be evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region = 1 ORDER
by tblSurveyors.SvyName;"

To get that you need, the complete build of the string should look like:

Dim strSQL As String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName; "


Chr(34) is one way to put double quotes around a string that you are
building in VBA; for example, if Region were a text type and not a number.

hth,
Click to expand...
chr(34)
_
Also, I think I would run all of this code from the On Current event of
frmMaster, changing the last line from:

Me.Combo2.RowSource = strSQL

to

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource=strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have a subform called fsubSurveyors inserted into a from called
frmMaster.

The fsubSurveyors form is based on tblCaseSurveyors. It contains a
Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter event of
the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE clause
and
I
do not know how to fix it. Any help is very much appreciated. Thanks!
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
C

Cheryl Fischer

Shelly,

The obvious thing I see is that you need spaces and commas that are left
out. Try the following:

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle, " _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, " _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName;"

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

S Jackson said:
Cheryl:

Thanks so much for your patience and your help. But, here is what I have:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle" _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip" _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

End Sub

Here is the error I get:

Run-time error '424'
Object required.

Any ideas?

Thanks, Shelly

Cheryl Fischer said:
If Region is a Number data type, change this:

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region & chr(34) _

to this:

& "WHERE tblSurveyors.Region = " & frmMaster.Region _

What you had originally: & "WHERE tblSurveyors.Region = "frmMaster.Region"
_ would have looked for a tblSurveyors.Region value equal to
"frmMaster.Region" - not the number but the exact text value:
"frmMaster.Region". Since you put quotes around "frmMaster.Region" you made
it a literal or string value.

One good way to test out what a SQL string is going to look like is to build
it and then put a MsgBox strSQL statement in your code. That way you can
see exactly what your query is going to look like. For example, your
original strSQL would have been evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region =
"frmMaster.Region" ORDER by tblSurveyors.SvyName;"

If the value of frmMaster.Region is 1, then you would want your SQL string
to be evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region = 1 ORDER
by tblSurveyors.SvyName;"

To get that you need, the complete build of the string should look like:

Dim strSQL As String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName; "


Chr(34) is one way to put double quotes around a string that you are
building in VBA; for example, if Region were a text type and not a number.

hth,
Click to expand...
chr(34)
_
Also, I think I would run all of this code from the On Current event of
frmMaster, changing the last line from:

Me.Combo2.RowSource = strSQL

to

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource=strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have a subform called fsubSurveyors inserted into a from called
frmMaster.

The fsubSurveyors form is based on tblCaseSurveyors. It contains a
Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter event of
the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE clause
and
I
do not know how to fix it. Any help is very much appreciated. Thanks!
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
S

S Jackson

I do not know how to use the MsgBox SQL.

What is it?
Where does it go?
What do I type exactly?

Sorry, ignorance prevails here. I'm doing the best I can on my own without
training or manuals.

Thanks for your help.
S. Jackson


Randy Harris said:
You need spaces and commas. Keep in mind that you are concatenating all of
those string segments. Access has no way of knowing that you show them on
different lines. It looks like one big long string to the program. Try
this:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip," _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region & " " _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

End Sub


As Cheryl has suggested, use a MsgBox to see exactly what the SQL string is.
That will help to find the problems.

MsgBox SQL


S Jackson said:
Cheryl:

Thanks so much for your patience and your help. But, here is what I have:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle" _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip" _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

End Sub

Here is the error I get:

Run-time error '424'
Object required.

Any ideas?

Thanks, Shelly

you
made chr(34) event
of
Click to expand...
contains
a
Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter
Click to expand...
event
of
the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE clause
and
I
do not know how to fix it. Any help is very much appreciated.
Thanks!
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
S

S Jackson

Same error. Your reply and Mr. Harris' reply differed somewhat in the WHERE
clause, but each way I typed it I get the same error: Object required.

Here's what I've got now with all the proper spaces and commas:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorId, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip," _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors" _
& "WHERE tblSurveyors.Region=" & frmMaster1.Region _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster1.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster1.fsubSurveyors.Form!Combo2.Requery

End Sub

Any thoughts? Or should I just give this up? The users can simply cycle
through entire list instead.

S. Jackson

Cheryl Fischer said:
Shelly,

The obvious thing I see is that you need spaces and commas that are left
out. Try the following:

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle, " _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, " _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName;"

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

S Jackson said:
Cheryl:

Thanks so much for your patience and your help. But, here is what I have:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle" _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip" _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

End Sub

Here is the error I get:

Run-time error '424'
Object required.

Any ideas?

Thanks, Shelly

you
made chr(34) event
of
Click to expand...
contains
a
Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter
Click to expand...
event
of
the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE clause
and
I
do not know how to fix it. Any help is very much appreciated.
Thanks!
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
R

Randy Harris

Shelly, you're still missing spaces.

Take, for instance, these two lines:
& "FROM tblSurveyors" _
& "WHERE tblSurveyors.Region=" & frmMaster1.Region _
Click to expand...

Access will put them together like this:

& "FROM tblSurveyorsWHERE tblSurveyors.Region=" & frmMaster1.Region _

See the problem? No space before the word WHERE!

You mentioned that you typed in the solution provided by Cheryl. Any reason
you can't simply do a copy/paste to get it?

Don't give up. You're very close, you'll get it.

Randy

S Jackson said:
Same error. Your reply and Mr. Harris' reply differed somewhat in the WHERE
clause, but each way I typed it I get the same error: Object required.

Here's what I've got now with all the proper spaces and commas:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorId, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip," _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors" _
& "WHERE tblSurveyors.Region=" & frmMaster1.Region _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster1.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster1.fsubSurveyors.Form!Combo2.Requery

End Sub

Any thoughts? Or should I just give this up? The users can simply cycle
through entire list instead.

S. Jackson

Cheryl Fischer said:
Shelly,

The obvious thing I see is that you need spaces and commas that are left
out. Try the following:

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle, " _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, " _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName;"
Click to expand...
chr(34)
_
to this:

& "WHERE tblSurveyors.Region = " & frmMaster.Region _

What you had originally: & "WHERE tblSurveyors.Region =
"frmMaster.Region"
_ would have looked for a tblSurveyors.Region value equal to
"frmMaster.Region" - not the number but the exact text value:
"frmMaster.Region". Since you put quotes around "frmMaster.Region" you
made
it a literal or string value.

One good way to test out what a SQL string is going to look like is to
build
it and then put a MsgBox strSQL statement in your code. That way
Click to expand...
you
can
see exactly what your query is going to look like. For example, your
original strSQL would have been evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region =
"frmMaster.Region" ORDER by tblSurveyors.SvyName;"

If the value of frmMaster.Region is 1, then you would want your SQL string
to be evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region = 1
ORDER
by tblSurveyors.SvyName;"

To get that you need, the complete build of the string should look like:

Dim strSQL As String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName; "


Chr(34) is one way to put double quotes around a string that you are
building in VBA; for example, if Region were a text type and not a number.

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl!

Thanks for your response. The Region field is a Number field.
Click to expand...
How
does
this change things?

I guess I need to change the Dim statement to: (????)

Dim intSQL = Integer

Also does it affect the WHERE statement?????
&"WHERE tblSurveyors.Region=

What does the chr(34) stand for?

Thanks in advance!
Shelly


Instead of:

& "WHERE tblSurveyors.Region = "frmMaster.Region" _

try the following, presuming frmMaster.Region is a Text field,

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region & chr(34)
_

Also, I think I would run all of this code from the On Current event
of
frmMaster, changing the last line from:

Me.Combo2.RowSource = strSQL

to

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource=strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have a subform called fsubSurveyors inserted into a from called
frmMaster.

The fsubSurveyors form is based on tblCaseSurveyors. It
Click to expand...
contains
a
Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter event
of
the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the
Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE
clause
and
I
do not know how to fix it. Any help is very much appreciated.
Thanks!
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
S

S Jackson

I put in a space between the " and the word WHERE and it did not work. Same
error - Object required.

I tried cutting and pasting the code directly from Cheryl's response and it
pasted in wierd with returns in the middle of the lines and still gave me
the same error: Object required.

Is it my newsreader (outlook express) that is creating this misunderstanding
here? Because I DO NOT see where you have spaces in your replies between
the " and the word WHERE.

Here is the code and I put in the [space] whereever I have a space:

strSQL [space]=[space] "Select[space]
tblSurveyors.SurveyorId,[space]tblSurveyor.SvyName,[space]tblSurveyor.SvyTit
le,[space] "[space] _
&[space] "tblSurveyor.SvyProgram,[space] tblSurveyor.SvyAddress,[space]
tblSurveyor.SvyCity,[space] tblSurveyor.SvyState,[space] "[space] _
&[space] "tblSurveyor.SvyZip,[space] tblSurveyor.SvyPhone,[space]
tblSurveyor.SvyEmploy,[space] "[space] _
&[space] "FROM [space]tblSurveyors"[space] _
&[space] "WHERE[space] tblSurveyors="[space] &[space]
frmMaster1.Region[space] _
&[space] "ORDER[space] by[space] tblSurveyor.SvyName;"


Help me - I'm getting spacey!! (lol!)

Sorry, this is so difficult. Maybe one day I'll understand this enough to
be even more embarrassed than I am today over my ignorance.

Shelly



Randy Harris said:
Shelly, you're still missing spaces.

Take, for instance, these two lines:
& "FROM tblSurveyors" _
& "WHERE tblSurveyors.Region=" & frmMaster1.Region _
Click to expand...

Access will put them together like this:

& "FROM tblSurveyorsWHERE tblSurveyors.Region=" & frmMaster1.Region _

See the problem? No space before the word WHERE!

You mentioned that you typed in the solution provided by Cheryl. Any reason
you can't simply do a copy/paste to get it?

Don't give up. You're very close, you'll get it.

Randy

S Jackson said:
Same error. Your reply and Mr. Harris' reply differed somewhat in the WHERE
clause, but each way I typed it I get the same error: Object required.

Here's what I've got now with all the proper spaces and commas:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorId, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip," _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors" _
& "WHERE tblSurveyors.Region=" & frmMaster1.Region _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster1.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster1.fsubSurveyors.Form!Combo2.Requery

End Sub

Any thoughts? Or should I just give this up? The users can simply cycle
through entire list instead.

S. Jackson

chr(34) "frmMaster.Region"
you
Click to expand...
is
to
Click to expand...
=
1
ORDER
by tblSurveyors.SvyName;"

To get that you need, the complete build of the string should look like:

Dim strSQL As String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName; "


Chr(34) is one way to put double quotes around a string that you are
building in VBA; for example, if Region were a text type and not a
number.

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl!

Thanks for your response. The Region field is a Number field. How
does
this change things?

I guess I need to change the Dim statement to: (????)

Dim intSQL = Integer

Also does it affect the WHERE statement?????
&"WHERE tblSurveyors.Region=

What does the chr(34) stand for?

Thanks in advance!
Shelly


Instead of:

& "WHERE tblSurveyors.Region = "frmMaster.Region" _

try the following, presuming frmMaster.Region is a Text field,

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region &
chr(34)
_

Also, I think I would run all of this code from the On Current event
of
frmMaster, changing the last line from:

Me.Combo2.RowSource = strSQL

to

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource=strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have a subform called fsubSurveyors inserted into a from called
frmMaster.

The fsubSurveyors form is based on tblCaseSurveyors. It contains
a
Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter event
of
the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the
Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip,
tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE
clause
and
I
do not know how to fix it. Any help is very much appreciated.
Thanks!
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
R

Randy Harris

Hi Shelly

Spacey! Very cute. <G>

I think I might now understand what the problem is. Access will ignore
(throw away) all of the spaces that are NOT inside the quotes. Take those
same two lines:
&[space] "FROM [space]tblSurveyors"[space] _
&[space] "WHERE[space] tblSurveyors="[space] &[space]
Click to expand...

You do not have a space between the last letter of tblSurveyors and the
first letter of WHERE that is INSIDE the quotes! When Access processes that
line, absolutely everything that is not inside the quotes will be discarded.
Go back to Cheryl's example and make sure that everything INSIDE the quotes
is exactly as she posted it. Spaces outside of the quotes make no
difference.

Does that help?

Randy

S Jackson said:
I put in a space between the " and the word WHERE and it did not work. Same
error - Object required.

I tried cutting and pasting the code directly from Cheryl's response and it
pasted in wierd with returns in the middle of the lines and still gave me
the same error: Object required.

Is it my newsreader (outlook express) that is creating this misunderstanding
here? Because I DO NOT see where you have spaces in your replies between
the " and the word WHERE.

Here is the code and I put in the [space] whereever I have a space:

strSQL [space]=[space] "Select[space]
tblSurveyors.SurveyorId,[space]tblSurveyor.SvyName,[space]tblSurveyor.SvyTit
le,[space] "[space] _
&[space] "tblSurveyor.SvyProgram,[space] tblSurveyor.SvyAddress,[space]
tblSurveyor.SvyCity,[space] tblSurveyor.SvyState,[space] "[space] _
&[space] "tblSurveyor.SvyZip,[space] tblSurveyor.SvyPhone,[space]
tblSurveyor.SvyEmploy,[space] "[space] _
&[space] "FROM [space]tblSurveyors"[space] _
&[space] "WHERE[space] tblSurveyors="[space] &[space]
frmMaster1.Region[space] _
&[space] "ORDER[space] by[space] tblSurveyor.SvyName;"


Help me - I'm getting spacey!! (lol!)

Sorry, this is so difficult. Maybe one day I'll understand this enough to
be even more embarrassed than I am today over my ignorance.

Shelly



Randy Harris said:
Shelly, you're still missing spaces.

Take, for instance, these two lines:


Access will put them together like this:

& "FROM tblSurveyorsWHERE tblSurveyors.Region=" & frmMaster1.Region _

See the problem? No space before the word WHERE!

You mentioned that you typed in the solution provided by Cheryl. Any reason
you can't simply do a copy/paste to get it?

Don't give up. You're very close, you'll get it.

Randy

is way
you
Click to expand...
tblSurveyors.Region
=
Click to expand...
tblSurveyors.Region
=
1
ORDER
by tblSurveyors.SvyName;"

To get that you need, the complete build of the string should look
like:

Dim strSQL As String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName; "


Chr(34) is one way to put double quotes around a string that you are
building in VBA; for example, if Region were a text type and not a
number.

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl!

Thanks for your response. The Region field is a Number field. How
does
this change things?

I guess I need to change the Dim statement to: (????)

Dim intSQL = Integer

Also does it affect the WHERE statement?????
&"WHERE tblSurveyors.Region=

What does the chr(34) stand for?

Thanks in advance!
Shelly


Instead of:

& "WHERE tblSurveyors.Region = "frmMaster.Region" _

try the following, presuming frmMaster.Region is a Text field,

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region &
chr(34)
_

Also, I think I would run all of this code from the On Current
event
of
frmMaster, changing the last line from:

Me.Combo2.RowSource = strSQL

to

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource=strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have a subform called fsubSurveyors inserted into a from
called
frmMaster.

The fsubSurveyors form is based on tblCaseSurveyors. It
contains
a
Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter
event
of
the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event
Click to expand...
of
the
Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip,
tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE
clause
and
I
do not know how to fix it. Any help is very much appreciated.
Thanks!
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
C

Cheryl Fischer

Randy,

Thanks for picking up on this and helping Shelly - I had to be away from the
computer this afternoon.
 
C

Cheryl Fischer

Hi Shelly,

We are hitting you with new techniques, here; and I know how difficult it is
to learn "as you go", while producing a usable product for your office - so
you should NOT feel ignorant!

Here's how I use MsgBox when I need to check out a problematic SQL
statement. The following code snippet shows a very simple SQL string where
we are looking for all orders in tblOrders, where the CustID (numeric field)
equals the CustID on the current form, frmCustomers.

Dim strSQL as string

strSQL = "SELECT * from tblOrders " _
& "WHERE tblOrders.CustID = " & Forms!frmCustomers!CustID

MsgBox strSQL

(Notice in the first line above, there is a space between tblOrders and the
ending ". The statement also could have been correctly written as follows:

strSQL = "SELECT * from tblOrders" _
& " WHERE tblOrders.CustID = " & Forms!frmCustomers!CustID

Notice that I put the required space between the words tblOrders and WHERE
before the where. It is not important whether you put the spaces at the
beginning or the end of the "broken" line. Just get into the habit of doing
it one way or another.)

------
Right after you have built your SQL string, simply put in the MsgBox
statement followed by strSQL. Presume that the CustID on frmCustomers is
1455. The MsgBox statement will show, in a popup window, how your SQL
string will look *as evaluated*:

SELECT * from tblOrders WHERE tblOrders.CustID = 1455

hth and hang in there!
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

S Jackson said:
I do not know how to use the MsgBox SQL.

What is it?
Where does it go?
What do I type exactly?

Sorry, ignorance prevails here. I'm doing the best I can on my own without
training or manuals.

Thanks for your help.
S. Jackson


Randy Harris said:
You need spaces and commas. Keep in mind that you are concatenating all of
those string segments. Access has no way of knowing that you show them on
different lines. It looks like one big long string to the program. Try
this:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip," _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region & " " _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

End Sub


As Cheryl has suggested, use a MsgBox to see exactly what the SQL string is.
That will help to find the problems.

MsgBox SQL
Click to expand...
chr(34)
_
to this:

& "WHERE tblSurveyors.Region = " & frmMaster.Region _

What you had originally: & "WHERE tblSurveyors.Region =
"frmMaster.Region"
_ would have looked for a tblSurveyors.Region value equal to
"frmMaster.Region" - not the number but the exact text value:
"frmMaster.Region". Since you put quotes around "frmMaster.Region" you
made
it a literal or string value.

One good way to test out what a SQL string is going to look like is to
build
it and then put a MsgBox strSQL statement in your code. That way
Click to expand...
you
can
see exactly what your query is going to look like. For example, your
original strSQL would have been evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region =
"frmMaster.Region" ORDER by tblSurveyors.SvyName;"

If the value of frmMaster.Region is 1, then you would want your SQL string
to be evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region = 1
ORDER
by tblSurveyors.SvyName;"

To get that you need, the complete build of the string should look like:

Dim strSQL As String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName; "


Chr(34) is one way to put double quotes around a string that you are
building in VBA; for example, if Region were a text type and not a number.

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl!

Thanks for your response. The Region field is a Number field.
Click to expand...
How
does
this change things?

I guess I need to change the Dim statement to: (????)

Dim intSQL = Integer

Also does it affect the WHERE statement?????
&"WHERE tblSurveyors.Region=

What does the chr(34) stand for?

Thanks in advance!
Shelly


Instead of:

& "WHERE tblSurveyors.Region = "frmMaster.Region" _

try the following, presuming frmMaster.Region is a Text field,

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region & chr(34)
_

Also, I think I would run all of this code from the On Current event
of
frmMaster, changing the last line from:

Me.Combo2.RowSource = strSQL

to

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource=strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have a subform called fsubSurveyors inserted into a from called
frmMaster.

The fsubSurveyors form is based on tblCaseSurveyors. It
Click to expand...
contains
a
Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter event
of
the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the
Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE
clause
and
I
do not know how to fix it. Any help is very much appreciated.
Thanks!
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
S

Shelly Jackson

I just want to tell you ALL a BIG thank you! I am not at my office
computer right now and I will try out the fix tomorrow.

I'll post new and let you know if it works! Thanks again.

:D

Shelly
 
S

SteveS

Shelly,

Once you get the SQL statement working, you can modify it
to do what you want using IF..ELSEIF...END IF or SELECT
CASE...CASE...END CASE. I prefer the Select Case syntax.

This should give you the idea:

(NOTE: watch for line wrap)

strSQL = "Select tblSurveyors.SurveyorId,
tblSurveyors.SvyName,tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip," _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& " FROM tblSurveyors"


' Now to build the WHERE statement
' the 2 lines after each CASE should be on one line
SELECT CASE Forms!frmMaster1.Region

' if Region = 2 or 9 or 10
CASE 2, 9, 10
strSQL = strSQL & " WHERE tblSurveyors.Region = 2 OR
tblSurveyors.Region = 9 OR tblSurveyors.Region = 10"

' Region is NOT 2 or 9 or 10
CASE Else
strSQL = strSQL & " WHERE tblSurveyors.Region = " &
Forms!frmMaster.Region
END SELECT

' add the ORDER statement
strSQL = strSQL & " ORDER by tblSurveyors.SvyName;"

' comment out or delete the next line after debugging
MsgBox strSQL

Forms!frmMaster1.fsubSurveyors.Form!Combo2.RowSource =
strSQL
Forms!frmMaster1.fsubSurveyors.Form!Combo2.Requery


HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
-----Original Message-----
Here is another twist (I thought this would be easy, but now I am having
second thoughts).

If frmMaster.Region = 2, 9 or 10, THEN

strSQL="Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle"_
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, tblSurveyors.SvyCity,
tblSurveyors.SvyZip" _
& "tblSurveyors.SvyPhone,tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors" _
& "WHERE tblSurveyors.Region =

That's as far as I get. I want the fsubSurveyors to display information for
Regions 2, 9 AND 10 when frmMaster.Region equals EITHER 2, 9 or 10 (the
surveyors from these regions cover these regions combined, whereas other
Regions have their own surveyors exclusively). I know this is getting
fancy, but if I do not limit fsubSurveyors by Region, it makes the user
scroll through gobs of people.

What do you think? Too hard to do it?

Shelly


S Jackson said:
Cheryl:

Thanks so much for your patience and your help. But, here is what I have:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle" _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip" _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

End Sub

Here is the error I get:

Run-time error '424'
Object required.

Any ideas?

Thanks, Shelly

tblSurveyors.Region =
"frmMaster.Region" around "frmMaster.Region" you
made to look like is to
build
Click to expand...
code. That way you
can want your SQL
string
tblSurveyors.Region = 1
ORDER
Click to expand...
type and not a
number. Number field. How
does frmMaster.Region &
chr(34)
_ the On Current event
of
Click to expand...
tblCaseSurveyors. It contains
a
Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In
Click to expand...
the On Enter event
of
the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the
Click to expand...
OnEnter event of the
Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is
Click to expand...
because of my WHERE
clause
and
I
do not know how to fix it. Any help is very much appreciated.
Thanks!
S. Jackson
Click to expand...
Click to expand...


.
Click to expand...
 
R

Randy Harris

IMO, Steve's approach is a good one, provided the surveyors / regions won't
change. If there is a likelihood that they might change somewhere down the
road, the data should probably be kept in tables and added to the query.
That way there would be no need to change the code to accommodate a change
in surveyor assignments.


SteveS said:
Shelly,

Once you get the SQL statement working, you can modify it
to do what you want using IF..ELSEIF...END IF or SELECT
CASE...CASE...END CASE. I prefer the Select Case syntax.

This should give you the idea:

(NOTE: watch for line wrap)

strSQL = "Select tblSurveyors.SurveyorId,
tblSurveyors.SvyName,tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip," _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& " FROM tblSurveyors"


' Now to build the WHERE statement
' the 2 lines after each CASE should be on one line
SELECT CASE Forms!frmMaster1.Region

' if Region = 2 or 9 or 10
CASE 2, 9, 10
strSQL = strSQL & " WHERE tblSurveyors.Region = 2 OR
tblSurveyors.Region = 9 OR tblSurveyors.Region = 10"

' Region is NOT 2 or 9 or 10
CASE Else
strSQL = strSQL & " WHERE tblSurveyors.Region = " &
Forms!frmMaster.Region
END SELECT

' add the ORDER statement
strSQL = strSQL & " ORDER by tblSurveyors.SvyName;"

' comment out or delete the next line after debugging
MsgBox strSQL

Forms!frmMaster1.fsubSurveyors.Form!Combo2.RowSource =
strSQL
Forms!frmMaster1.fsubSurveyors.Form!Combo2.Requery


HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
-----Original Message-----
Here is another twist (I thought this would be easy, but now I am having
second thoughts).

If frmMaster.Region = 2, 9 or 10, THEN

strSQL="Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle"_
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, tblSurveyors.SvyCity,
tblSurveyors.SvyZip" _
& "tblSurveyors.SvyPhone,tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors" _
& "WHERE tblSurveyors.Region =

That's as far as I get. I want the fsubSurveyors to display information for
Regions 2, 9 AND 10 when frmMaster.Region equals EITHER 2, 9 or 10 (the
surveyors from these regions cover these regions combined, whereas other
Regions have their own surveyors exclusively). I know this is getting
fancy, but if I do not limit fsubSurveyors by Region, it makes the user
scroll through gobs of people.

What do you think? Too hard to do it?

Shelly


S Jackson said:
Cheryl:

Thanks so much for your patience and your help. But, here is what I have:

Private Sub Form_Current()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle" _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip" _
& "tblSurveyors.SvyPhone, tblSurveyors.SvyEmploy" _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region= " & frmMaster.Region _
& "ORDER by tblSurveyors.SvyName;"

Forms!frmMaster.fsubSurveyors.Form!Combo2.RowSource = strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

End Sub

Here is the error I get:

Run-time error '424'
Object required.

Any ideas?

Thanks, Shelly

If Region is a Number data type, change this:

& "WHERE tblSurveyors.Region = & chr(34) & frmMaster.Region & chr(34) _

to this:

& "WHERE tblSurveyors.Region = " & frmMaster.Region _

What you had originally: & "WHERE tblSurveyors.Region =
"frmMaster.Region"
_ would have looked for a tblSurveyors.Region value equal to
"frmMaster.Region" - not the number but the exact text value:
"frmMaster.Region". Since you put quotes around "frmMaster.Region" you
made
it a literal or string value.

One good way to test out what a SQL string is going to look like is to
build
it and then put a MsgBox strSQL statement in your
Click to expand...
code. That way you
can
see exactly what your query is going to look like. For example, your
original strSQL would have been evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region =
"frmMaster.Region" ORDER by tblSurveyors.SvyName;"

If the value of frmMaster.Region is 1, then you would
Click to expand...
want your SQL
string
to be evaluated as:

Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," tblSurveyors.SvyProgram, tbllSurveyors.SvyAddress,
tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy FROM tblSurveyors WHERE tblSurveyors.Region = 1
ORDER
by tblSurveyors.SvyName;"

To get that you need, the complete build of the string should look like:

Dim strSQL As String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & frmMaster.Region _
& " ORDER by tblSurveyors.SvyName; "


Chr(34) is one way to put double quotes around a string that you are
building in VBA; for example, if Region were a text
Click to expand...
type and not a
number.
hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl!

Thanks for your response. The Region field is a
Click to expand...
Number field. How
does
this change things?

I guess I need to change the Dim statement to: (????)

Dim intSQL = Integer

Also does it affect the WHERE statement?????
&"WHERE tblSurveyors.Region=

What does the chr(34) stand for?

Thanks in advance!
Shelly


Instead of:

& "WHERE tblSurveyors.Region = "frmMaster.Region" _

try the following, presuming frmMaster.Region is a Text field,

& "WHERE tblSurveyors.Region = & chr(34) &
Click to expand...
frmMaster.Region &
chr(34)
_

Also, I think I would run all of this code from the On Current event
of
frmMaster, changing the last line from:

Me.Combo2.RowSource = strSQL

to

Forms!frmMaster.fsubSurveyors.Form! Combo2.RowSource=strSQL
Forms!frmMaster.fsubSurveyors.Form!Combo2.Requery

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have a subform called fsubSurveyors inserted into a from called
frmMaster.

The fsubSurveyors form is based on
Click to expand...
tblCaseSurveyors. It contains
a
Combo2
that obtains its information from tblSurveyors.

I want the combo box to display its contents where the field
[tblSurveyors].[Region] = [frmMaster].[Region]

First, where do I put this Event procedure? In the On Enter event
of
the
Combo2 control on the subform, fsubSurveyors?

So far, I've tried putting the code in the OnEnter event of the
Combo2
control on fsumSurveyors:

Private Sub Combo2_Enter()
Dim strSQL As String

strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyName,
tblSurveyors.SvyTitle," _
& "tblSurveyors.SvyProgram, tblSurveyors.SvyAddress, " _
& "tblSurveyors.SvyCity, tblSurveyors.SvyZip, tblSurveyors.SvyPhone,
tblSurveyors.SvyEmploy " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = "frmMaster.Region" _
& "ORDER by tblSurveyors.SvyName; "
Me.Combo2.RowSource = strSQL

End Sub

I am getting a syntax error. I know it is because of my WHERE
clause
and
I
do not know how to fix it. Any help is very much appreciated.
Thanks!
S. Jackson
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help With "Like" in strSQL 4
Need Help With Link Table 1
Cascading Combo Box Hell! 2
Help With Cascading Combo Boxes 1
2 combo box, 2 txt boxes - Populate problem 5

Top