HELP

[Guest]

I have a functioning query, which is linked to a user-friendly dialog box,
which is supposed to show the filtered results in a separate form which I
have linked to another form. The query works by itself, the linked forms are
working great, the dialog box has all the info, but when I click the cmdOK
button, it just brings up the "Facilities" form with all the records,
unfiltered and not matching the search results. Here is the code for the
dialog box:
Option Compare Database

Private Sub Command14cmdOK_Click()

End Sub

Private Sub cmdOK_Click()
DoCmd.Requery
DoCmd.OpenForm "Facilities"
DoCmd.Close acForm, Me.Name



End Sub

And here is query SQL:

SELECT Facilities.[Facility Name], Facilities.[Nearest Major US City],
Facilities.[Nearest Major City], Facilities.[In-Field Recruiting],
Facilities.[Recruiting for Off-Site Sessions],
Facilities.[In-Home/Ethnographies Recruiting], Facilities.[National
Recruiting Database]
FROM Facilities
WHERE (((Facilities.[Facility Name])=Forms!Lookup!Facility) Or
((Facilities.[Nearest Major US City])=Forms!Lookup![Mjr US cty]) Or
((Facilities.[Nearest Major City])=Forms!Lookup![Mjr frgn cty]) Or
((Facilities.[In-Field Recruiting])=Forms!Lookup!infield) Or
((Facilities.[Recruiting for Off-Site Sessions])=Forms!Lookup!offsite) Or
((Facilities.[In-Home/Ethnographies Recruiting])=Forms!Lookup!inhome) Or
((Facilities.[National Recruiting Database])=Forms!Lookup![nat dtb]));

Please help if you know where my problem is, thank you so very much in
advance!

 

[James A. Fortune]

I have a functioning query, which is linked to a user-friendly dialog box,
which is supposed to show the filtered results in a separate form which I
have linked to another form. The query works by itself, the linked forms are
working great, the dialog box has all the info, but when I click the cmdOK
button, it just brings up the "Facilities" form with all the records,
unfiltered and not matching the search results. Here is the code for the
dialog box:
Option Compare Database

Private Sub Command14cmdOK_Click()

End Sub

Private Sub cmdOK_Click()
DoCmd.Requery
DoCmd.OpenForm "Facilities"
DoCmd.Close acForm, Me.Name



End Sub

And here is query SQL:

SELECT Facilities.[Facility Name], Facilities.[Nearest Major US City],
Facilities.[Nearest Major City], Facilities.[In-Field Recruiting],
Facilities.[Recruiting for Off-Site Sessions],
Facilities.[In-Home/Ethnographies Recruiting], Facilities.[National
Recruiting Database]
FROM Facilities
WHERE (((Facilities.[Facility Name])=Forms!Lookup!Facility) Or
((Facilities.[Nearest Major US City])=Forms!Lookup![Mjr US cty]) Or
((Facilities.[Nearest Major City])=Forms!Lookup![Mjr frgn cty]) Or
((Facilities.[In-Field Recruiting])=Forms!Lookup!infield) Or
((Facilities.[Recruiting for Off-Site Sessions])=Forms!Lookup!offsite) Or
((Facilities.[In-Home/Ethnographies Recruiting])=Forms!Lookup!inhome) Or
((Facilities.[National Recruiting Database])=Forms!Lookup![nat dtb]));

Please help if you know where my problem is, thank you so very much in
advance!

It would be better to see if the query returns any records before
opening the Facilities form. The RecordSource for the form can be set
to the query in code.


DoCmd.Requery
'Check to see if the query returns any records here
'...
DoCmd.OpenForm "Facilities"

Forms!Facilities.RecordSource = "SELECT Faci ... Lookup![nat tb]));"

DoCmd.Close acForm, Me.Name

James A. Fortune

 

[Guest]

Am I supposed to enter that exactly as is into my code for my dialog box, it
says there is a bug or something...sorry this is my first database, so I am a
little lost

I have a functioning query, which is linked to a user-friendly dialog box,
which is supposed to show the filtered results in a separate form which I
have linked to another form. The query works by itself, the linked forms are
working great, the dialog box has all the info, but when I click the cmdOK
button, it just brings up the "Facilities" form with all the records,
unfiltered and not matching the search results. Here is the code for the
dialog box:
Option Compare Database

Private Sub Command14cmdOK_Click()

End Sub

Private Sub cmdOK_Click()
DoCmd.Requery
DoCmd.OpenForm "Facilities"
DoCmd.Close acForm, Me.Name



End Sub

And here is query SQL:

SELECT Facilities.[Facility Name], Facilities.[Nearest Major US City],
Facilities.[Nearest Major City], Facilities.[In-Field Recruiting],
Facilities.[Recruiting for Off-Site Sessions],
Facilities.[In-Home/Ethnographies Recruiting], Facilities.[National
Recruiting Database]
FROM Facilities
WHERE (((Facilities.[Facility Name])=Forms!Lookup!Facility) Or
((Facilities.[Nearest Major US City])=Forms!Lookup![Mjr US cty]) Or
((Facilities.[Nearest Major City])=Forms!Lookup![Mjr frgn cty]) Or
((Facilities.[In-Field Recruiting])=Forms!Lookup!infield) Or
((Facilities.[Recruiting for Off-Site Sessions])=Forms!Lookup!offsite) Or
((Facilities.[In-Home/Ethnographies Recruiting])=Forms!Lookup!inhome) Or
((Facilities.[National Recruiting Database])=Forms!Lookup![nat dtb]));

Please help if you know where my problem is, thank you so very much in
advance!

It would be better to see if the query returns any records before
opening the Facilities form. The RecordSource for the form can be set
to the query in code.


DoCmd.Requery
'Check to see if the query returns any records here
'...
DoCmd.OpenForm "Facilities"

Forms!Facilities.RecordSource = "SELECT Faci ... Lookup![nat tb]));"

DoCmd.Close acForm, Me.Name

James A. Fortune

 

[James A. Fortune]

Am I supposed to enter that exactly as is into my code for my dialog box, it
says there is a bug or something...sorry this is my first database, so I am a
little lost

DoCmd.Requery
'Check to see if the query returns any records here
'...
DoCmd.OpenForm "Facilities"

Forms!Facilities.RecordSource = "SELECT Faci ... Lookup![nat tb]));"

DoCmd.Close acForm, Me.Name

James A. Fortune

The Forms!Facilities.RecordSource = "Your entire query"

You can put the query on one very long line or use the _ continuation
character to break it up into several lines.

The '... represents where you can open a recordset to count how many
lines the query returns. If the query returns no records then don't
open the form; instead, MsgBox the user that no records match the criteria.

James A. Fortune

 

[Guest]

The code is too long to be placed on one line, and when I put in the _
continuation character, visual basic automatically places a quoatation mark
at the end of the top line and colors the text of the following line in red,
am I doing something wrong??

Am I supposed to enter that exactly as is into my code for my dialog box, it
says there is a bug or something...sorry this is my first database, so I am a
little lost

DoCmd.Requery
'Check to see if the query returns any records here
'...
DoCmd.OpenForm "Facilities"

Forms!Facilities.RecordSource = "SELECT Faci ... Lookup![nat tb]));"

DoCmd.Close acForm, Me.Name

James A. Fortune

The Forms!Facilities.RecordSource = "Your entire query"

You can put the query on one very long line or use the _ continuation
character to break it up into several lines.

The '... represents where you can open a recordset to count how many
lines the query returns. If the query returns no records then don't
open the form; instead, MsgBox the user that no records match the criteria.

James A. Fortune

 

[John Vinson]

The code is too long to be placed on one line, and when I put in the _
continuation character, visual basic automatically places a quoatation mark
at the end of the top line and colors the text of the following line in red,
am I doing something wrong??

Evidently.

The _ must be preceded by at least one blank. Note that you can type a
line of code as long as you wish (you may not be able to see it all on
screen though).

Please copy and paste the actual code snippet you're having trouble
with if this doesn't help.

John W. Vinson[MVP]

 

[Guest]

The problem is near the very end of the code, VB keeps putting the second
line in red text and saying that there is a Compile Error: Expected end of
statement

Forms!Facilities.RecordSource = "SELECT Facilities.[Facility Name],
Facilities.[Nearest Major US City], Facilities.[Nearest Major City],
Facilities.[In-Field Recruiting], Facilities.[Recruiting for Off-Site
Sessions], Facilities.[In-Home/Ethnographies Recruiting],
Facilities.[National Recruiting Database]FROM Facilities WHERE
(Facilities.[Facility Name]=[Forms]![Facilities]![Lookup] OR
[Forms]![Facilities]![Lookup] IS NULL) AND (Facilities.[Nearest Major US
City]=[Forms]![Lookup}![Mjr US cty] OR [Forms]![Lookup]![Mjr US cty] IS NULL)
AND (Facilities.[Nearest Major City]=[Forms]![Lookup]![Mjr frgn cty] OR
[Forms]![Lookup]![Mjr frgn cty] IS NULL) AND (Facilities.[In-Field
Recruiting]=[Forms]![Lookup]![infield] OR [Forms]![Lookup]![infield] IS NULL)
AND (Facilities.[Recruiting for Off-Site Sessions]=[Forms]![Lookup]![offsite]
OR [Forms]![Lookup]![offsite] IS NULL) AND (Facilities.[In-Home/Ethnographies
Recruiting]=[Forms]![Lookup]![inhome] OR [Forms]![Lookup]![inhome] IS NULL)
And _
(Facilities.[National Recruiting Database} = [Forms]![Lookup]![nat dtb]
Or [Forms]![Lookup]![nat dtb] IS NULL)"

 

[James A. Fortune]

The problem is near the very end of the code, VB keeps putting the second
line in red text and saying that there is a Compile Error: Expected end of
statement

Forms!Facilities.RecordSource = "SELECT Facilities.[Facility Name],
Facilities.[Nearest Major US City], Facilities.[Nearest Major City],
Facilities.[In-Field Recruiting], Facilities.[Recruiting for Off-Site
Sessions], Facilities.[In-Home/Ethnographies Recruiting],
Facilities.[National Recruiting Database]FROM Facilities WHERE
(Facilities.[Facility Name]=[Forms]![Facilities]![Lookup] OR
[Forms]![Facilities]![Lookup] IS NULL) AND (Facilities.[Nearest Major US
City]=[Forms]![Lookup}![Mjr US cty] OR [Forms]![Lookup]![Mjr US cty] IS NULL)
AND (Facilities.[Nearest Major City]=[Forms]![Lookup]![Mjr frgn cty] OR
[Forms]![Lookup]![Mjr frgn cty] IS NULL) AND (Facilities.[In-Field
Recruiting]=[Forms]![Lookup]![infield] OR [Forms]![Lookup]![infield] IS NULL)
AND (Facilities.[Recruiting for Off-Site Sessions]=[Forms]![Lookup]![offsite]
OR [Forms]![Lookup]![offsite] IS NULL) AND (Facilities.[In-Home/Ethnographies
Recruiting]=[Forms]![Lookup]![inhome] OR [Forms]![Lookup]![inhome] IS NULL)
And _
(Facilities.[National Recruiting Database} = [Forms]![Lookup]![nat dtb]
Or [Forms]![Lookup]![nat dtb] IS NULL)"

:

Evidently.

The _ must be preceded by at least one blank. Note that you can type a
line of code as long as you wish (you may not be able to see it all on
screen though).

Please copy and paste the actual code snippet you're having trouble
with if this doesn't help.

John W. Vinson[MVP]

I noticed a couple of small things about the SQL:

Facilities.[National Recruiting Database]FROM Facilities

would be better as:

Facilities.[National Recruiting Database] FROM Facilities

Also, in a couple of spots where I would expect a ], you have }.

[Forms]![Lookup}![Mjr US cty]

[National Recruiting Database}

Other than that I don't see anything obvious.

A really brute force method is to create a string variable and append
each piece:

Dim strSQL As String

strSQL = "SELECT Facilities.[Facility Name], Facilities.[Nearest Major
US City], "
strSQL = strSQL & "Facilities.[In-Field Recruiting],
Facilities.[Recruiting for Off-Site Sessions], "
and so on.

Then

Forms!Facilities.RecordSource = strSQL

Note that you have to make sure to include all necessary spaces.

Or save the query and use

Forms!Facilities.RecordSource = "qrySaved"

I hope this helps,

James A. Fortune

 

[John Vinson]

The problem is near the very end of the code, VB keeps putting the second
line in red text and saying that there is a Compile Error: Expected end of
statement

I've never had good luck breaking a text string with a line
continuation. Try concatenating separate string constants instead:

....
Recruiting]=[Forms]![Lookup]![inhome] OR [Forms]![Lookup]![inhome] IS
NULL) And " _
& "(Facilities.[National Recruiting Database} = [Forms]![Lookup]![nat
dtb] Or [Forms]![Lookup]![nat dtb] IS NULL)"

Just be sure that any syntactically required blanks (such as the one
after the word AND in this case) are included inside the text string.

John W. Vinson[MVP]

 

[Guest]

Thank you both so VERY much! That worked! You guys are amazing!

The problem is near the very end of the code, VB keeps putting the second
line in red text and saying that there is a Compile Error: Expected end of
statement

I've never had good luck breaking a text string with a line
continuation. Try concatenating separate string constants instead:

....
Recruiting]=[Forms]![Lookup]![inhome] OR [Forms]![Lookup]![inhome] IS
NULL) And " _
& "(Facilities.[National Recruiting Database} = [Forms]![Lookup]![nat
dtb] Or [Forms]![Lookup]![nat dtb] IS NULL)"

Just be sure that any syntactically required blanks (such as the one
after the word AND in this case) are included inside the text string.

John W. Vinson[MVP]

 

[Guest]

Okay, actually, one other problem, it linked to my form, but there is more
information on the form that is in the query, and now all the other
information on the form does not appear, it just says #Name?, do I need to
add all of the fields into my SQL?

The problem is near the very end of the code, VB keeps putting the second
line in red text and saying that there is a Compile Error: Expected end of
statement

I've never had good luck breaking a text string with a line
continuation. Try concatenating separate string constants instead:

....
Recruiting]=[Forms]![Lookup]![inhome] OR [Forms]![Lookup]![inhome] IS
NULL) And " _
& "(Facilities.[National Recruiting Database} = [Forms]![Lookup]![nat
dtb] Or [Forms]![Lookup]![nat dtb] IS NULL)"

Just be sure that any syntactically required blanks (such as the one
after the word AND in this case) are included inside the text string.

John W. Vinson[MVP]

 

[John Vinson]

Okay, actually, one other problem, it linked to my form, but there is more
information on the form that is in the query, and now all the other
information on the form does not appear, it just says #Name?, do I need to
add all of the fields into my SQL?

Only the fields included in the Form's Recordsource query (or table)
are available for controls. You don't say what you're doing with this
humungous SQL string so I'm not sure.

But yes, if you want to see a field displayed on a form, you need to
include it in the SQL of that form's Recordsource property.

John W. Vinson[MVP]

 

[Guest]

I want my users to search for facilities based on a limited amount of
pertinant criteria, then I want the search to bring back the facilities form
(filtered) with just the matching facilities, but to include all of the
facility information that is in the database ... does this mean that I have
to enter all fields that are on the form in both parts of the SQL, after
SELECT and WHERE, or just after SELECT, and should they be in the same order
as on the form??

 

[James A. Fortune]

I want my users to search for facilities based on a limited amount of
pertinant criteria, then I want the search to bring back the facilities form
(filtered) with just the matching facilities, but to include all of the
facility information that is in the database ... does this mean that I have
to enter all fields that are on the form in both parts of the SQL, after
SELECT and WHERE, or just after SELECT, and should they be in the same order
as on the form??

:

The fields you need only have to show up after the SELECT. Nothing
changes after the WHERE. The order of the fields in the query are not
important as long as they are there somewhere in the SELECT.

James A. Fortune