Access Forms/Query problem

[dstogsdill]

I have set up a database for our brick/stone houses. It has Brick/Form house
database form and a Form 1 that pops up when you open access, then you can
select a command button and type in information you are looking for each
individual command button, manufacturer, description, address, and city. I
also have querries set up for each of these. What I am wanting to do is be
able to say search for a certain brick and city at one time? Or brick and
address at one time to filter these out and print the customer a report with
all the information on it though, manufacuturer, description, address, city,
year, size, & color on the report? Any help would be greatly appreciated.

 

[Klatuu]

Use a combo box for each of the fields you want to filter on. Then use a
command button to print the report. The code below will not include every
field, but it will give you an idea of how it works.

Private Sub cmdReport_Click()
Dim strWhere As String

If Not IsNull(Me.cboBrick) Then
strWhere = "[Brick] = '" & Me.cboBrick & "'"
End If

If Not IsNull(Me.cboCity) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[City] = '" & Me.cboCity & "'"
End If

If Not IsNull(Me.cboMfgr) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Mfgr] = '" & Me.cboMfgr & "'"
End If

'Add other fields as needed

Docmd.OpeReport "MyReportName", , , strWhere

End Sub

Private Function AddAnd(strWhereString As String) As String
If Len(strWhereString) > 0 Then
strWhereString = strWhereString & " And "
End If
AddAnd = strWhereString
End Function

 

[dstogsdill]

Hello, Thank you for your assistance. But I am not sure where to start with
this? Am I going into forms, report, queries, etc? Once I am in one of
these I should hit design view? I think this is correct, but after thatnot
sure. Sorry for the confusion, I am just not that familiar with the combo
boxes. Thank you again.

Use a combo box for each of the fields you want to filter on. Then use a
command button to print the report. The code below will not include every
field, but it will give you an idea of how it works.

Private Sub cmdReport_Click()
Dim strWhere As String

If Not IsNull(Me.cboBrick) Then
strWhere = "[Brick] = '" & Me.cboBrick & "'"
End If

If Not IsNull(Me.cboCity) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[City] = '" & Me.cboCity & "'"
End If

If Not IsNull(Me.cboMfgr) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Mfgr] = '" & Me.cboMfgr & "'"
End If

'Add other fields as needed

Docmd.OpeReport "MyReportName", , , strWhere

End Sub

Private Function AddAnd(strWhereString As String) As String
If Len(strWhereString) > 0 Then
strWhereString = strWhereString & " And "
End If
AddAnd = strWhereString
End Function

I have set up a database for our brick/stone houses. It has Brick/Form house
database form and a Form 1 that pops up when you open access, then you can
select a command button and type in information you are looking for each
individual command button, manufacturer, description, address, and city. I
also have querries set up for each of these. What I am wanting to do is be
able to say search for a certain brick and city at one time? Or brick and
address at one time to filter these out and print the customer a report with
all the information on it though, manufacuturer, description, address, city,
year, size, & color on the report? Any help would be greatly appreciated.

 

[Klatuu]

This code should go in the Click event of a command button that will print
the report. What it does is look at the value in each of your search combo
boxes and if it has a value other than Null, it will add it to the filtering
of the report. Once is has built the string to pass to the openreport's
where argument, it prints the report.

Hello, Thank you for your assistance. But I am not sure where to start with
this? Am I going into forms, report, queries, etc? Once I am in one of
these I should hit design view? I think this is correct, but after thatnot
sure. Sorry for the confusion, I am just not that familiar with the combo
boxes. Thank you again.

Use a combo box for each of the fields you want to filter on. Then use a
command button to print the report. The code below will not include every
field, but it will give you an idea of how it works.

Private Sub cmdReport_Click()
Dim strWhere As String

If Not IsNull(Me.cboBrick) Then
strWhere = "[Brick] = '" & Me.cboBrick & "'"
End If

If Not IsNull(Me.cboCity) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[City] = '" & Me.cboCity & "'"
End If

If Not IsNull(Me.cboMfgr) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Mfgr] = '" & Me.cboMfgr & "'"
End If

'Add other fields as needed

Docmd.OpeReport "MyReportName", , , strWhere

End Sub

Private Function AddAnd(strWhereString As String) As String
If Len(strWhereString) > 0 Then
strWhereString = strWhereString & " And "
End If
AddAnd = strWhereString
End Function

I have set up a database for our brick/stone houses. It has Brick/Form house
database form and a Form 1 that pops up when you open access, then you can
select a command button and type in information you are looking for each
individual command button, manufacturer, description, address, and city. I
also have querries set up for each of these. What I am wanting to do is be
able to say search for a certain brick and city at one time? Or brick and
address at one time to filter these out and print the customer a report with
all the information on it though, manufacuturer, description, address, city,
year, size, & color on the report? Any help would be greatly appreciated.