Reposting of Use a form for sole purpose to filter another forms

[Guest]

See below: not sure on the response where to tie the code to - tied to a
command button the the original form - on click event to

Docmd.openform........

not sure where the rest goes and how to capture the info from the form
soley for the purpose of filtering.

Thanks,
Barb


I would like to create a form to filter another form that would only include
four fields from the original form. The user doesn't know access at all and
would like to make it a user friendly as possilbe- therefore they wouldn't
now what to do with the filter by form- even if I gave them the button.

the field they want to filter by are clientid, material, and origin. would
like it to be able to handle and/or if possible??? Not sure how to start.
the client id field is a combo box - anyway to let them select from it for
the filter. After they input would like to have original form(filtered) show
up.

Any ideas,
thanks,
Barb


Barry Gilbert 5/8/2006 6:12 AM PST



Babs,
How comfortable are you with VBA code? The way I would approach this is to
have the filter form open modally. The statement looks like this:

DoCmd.OpenForm "frmFilter", windowmode:=acDialog

In the close command button of the filter form, don't close the form, set it
to invisible:
Me.Visible=False

The next line in the main for grabs the values from the combobox/textboxes
of the filter form and builds a filter statement:

DoCmd.OpenForm "frmFilter", windowmode:=acDialog
Me.Filter = "ClientId = " & Forms!frmFilter!cboClientId & " and Material =
'" & Forms!frmFilter!txtMaterial & "' AND Origin = '" &
Forms!frmFilter!txtOrigin & "'"
Me.FilterOn = True
DoCmd.Close acForm, "frmFilter"

This sets the calling form's filter and closes the filter form. You will
probably have to mess with the syntax of the filter statement to get it to
work correctly. Also, you'll need to eliminate the built-in close button on
the filter form to force the use to use your command button.

HTH,
Barry

 

[Albert D.Kallal]

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typically designed for
user interface face stuff like prompts, print buttons etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for information.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above should give you some ideas

So, the solution use now is simply to take the values from the "prompt"
form, and build your own where clause in code. That way, you simply design
the reports (or forms).

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount. The "key" here is that your form (or report) will NOT need any sql
parameters. (so, if that form has any...remove them -- you need to be able
to open the form as normal, without any special query parameters prompts).

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

It stands without saying, if the user enters NOTHING for all 4 controls,
then you display all.....

 

[Guest]

Al,

Thanks for the input your advice and code makes sense but I am getting a
syntax error on the line that says strWhere = strWhere " and " I tried it
with and without the spaces before and after "And" same error.

Please keep helping- any ideas.

See the rest of the code below.- remember dealing with a form. ( Assuming
have unbound form- when click on command button it will then go to open main
form and filter based on where criteria from unbound form)


Dim stDocName As String
Dim strWhere As String


stDocName = "QuoteEntryForm"

'select clientid combo
If IsNull(cboClientId) = False Then
strWhere = "Clientid='" & cboClientId & "'"
End If

'select what job#
If IsNull(cbojob) = False Then
If strWhere <> "" Then
strWhere = strWhere " and "
End If
strWhere = strWhere & "Job = '" & cbojob & "'"
End If

'select what job description
If IsNull(Jobdesc) = False Then
If strWhere <> "" Then
strWhere = strwhere " and "
End If
strWhere = strWhere & "JobDesc='" & Jobdesc & "'"
End If

DoCmd.OpenForm stDocName, , , strWhere

thanks again for your help,
Barb

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typically designed for
user interface face stuff like prompts, print buttons etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for information.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above should give you some ideas

So, the solution use now is simply to take the values from the "prompt"
form, and build your own where clause in code. That way, you simply design
the reports (or forms).

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount. The "key" here is that your form (or report) will NOT need any sql
parameters. (so, if that form has any...remove them -- you need to be able
to open the form as normal, without any special query parameters prompts).

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

It stands without saying, if the user enters NOTHING for all 4 controls,
then you display all.....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Douglas J Steele]

That needs to be

strWhere = strWhere & " and "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Al,

Thanks for the input your advice and code makes sense but I am getting a
syntax error on the line that says strWhere = strWhere " and " I tried it
with and without the spaces before and after "And" same error.

Please keep helping- any ideas.

See the rest of the code below.- remember dealing with a form. ( Assuming
have unbound form- when click on command button it will then go to open main
form and filter based on where criteria from unbound form)


Dim stDocName As String
Dim strWhere As String


stDocName = "QuoteEntryForm"

'select clientid combo
If IsNull(cboClientId) = False Then
strWhere = "Clientid='" & cboClientId & "'"
End If

'select what job#
If IsNull(cbojob) = False Then
If strWhere <> "" Then
strWhere = strWhere " and "
End If
strWhere = strWhere & "Job = '" & cbojob & "'"
End If

'select what job description
If IsNull(Jobdesc) = False Then
If strWhere <> "" Then
strWhere = strwhere " and "
End If
strWhere = strWhere & "JobDesc='" & Jobdesc & "'"
End If

DoCmd.OpenForm stDocName, , , strWhere

thanks again for your help,
Barb

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typically designed for
user interface face stuff like prompts, print buttons etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for information.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above should give you some ideas

So, the solution use now is simply to take the values from the "prompt"
form, and build your own where clause in code. That way, you simply design
the reports (or forms).

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount. The "key" here is that your form (or report) will NOT need any sql
parameters. (so, if that form has any...remove them -- you need to be able
to open the form as normal, without any special query parameters prompts).

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

It stands without saying, if the user enters NOTHING for all 4 controls,
then you display all.....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Albert D.Kallal]

Remember, you are "makeing" sql here.

That menas

Where City = 'Edmonton'
Where AccountNumber = 123
Where InvoiceDate = #12/31/2007#

Note in teh above for "text" fields, you surround the value in quotes
(single, or double)
For number fields, you don't surround the value
For date fields, you surround with #

So, keep the above rules in mind....

'select clientid combo
If IsNull(cboClientId) = False Then
strWhere = "Clientid='" & cboClientId & "'"
End If

Since ClineID is a number, then above does not need the quots...try

strWhere = "Clientid = " & cboClientId

'select what job#
If IsNull(cbojob) = False Then
If strWhere <> "" Then
strWhere = strWhere " and " <-- strWhere & " and "
End If
strWhere = strWhere & "Job = '" & cbojob & "'"
End If

Same for above....is Job a number type field, or a text type field?



'select what job description
If IsNull(Jobdesc) = False Then
If strWhere <> "" Then
strWhere = strwhere " and " <-- strWhere & " and "
End If
strWhere = strWhere & "JobDesc='" & Jobdesc & "'"
End If

DoCmd.OpenForm stDocName, , , strWhere

 

[Guest]

Thanks so much it worked!!! with just adding the (&) like Doug and you
suggested!!

 

[Guest]

THANKS IT WORKED!!!!!!!!!

That needs to be

strWhere = strWhere & " and "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Al,

Thanks for the input your advice and code makes sense but I am getting a
syntax error on the line that says strWhere = strWhere " and " I tried it
with and without the spaces before and after "And" same error.

Please keep helping- any ideas.

See the rest of the code below.- remember dealing with a form. ( Assuming
have unbound form- when click on command button it will then go to open main
form and filter based on where criteria from unbound form)


Dim stDocName As String
Dim strWhere As String


stDocName = "QuoteEntryForm"

'select clientid combo
If IsNull(cboClientId) = False Then
strWhere = "Clientid='" & cboClientId & "'"
End If

'select what job#
If IsNull(cbojob) = False Then
If strWhere <> "" Then
strWhere = strWhere " and "
End If
strWhere = strWhere & "Job = '" & cbojob & "'"
End If

'select what job description
If IsNull(Jobdesc) = False Then
If strWhere <> "" Then
strWhere = strwhere " and "
End If
strWhere = strWhere & "JobDesc='" & Jobdesc & "'"
End If

DoCmd.OpenForm stDocName, , , strWhere

thanks again for your help,
Barb

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typically designed for
user interface face stuff like prompts, print buttons etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for information.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above should give you some ideas

So, the solution use now is simply to take the values from the "prompt"
form, and build your own where clause in code. That way, you simply design
the reports (or forms).

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount. The "key" here is that your form (or report) will NOT need any sql
parameters. (so, if that form has any...remove them -- you need to be able
to open the form as normal, without any special query parameters prompts).

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

It stands without saying, if the user enters NOTHING for all 4 controls,
then you display all.....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal