Create a report from a subform

Let me just say I am very new to access.I am working with Access 2007.My
situation is this,i have a main form with 2 unbound text boxes to search a
query based on my main table.The results of this search are then displayed in
a subform in datasheet view.I am trying to create a report button on the main
form based on search results.If i make the report on the subform i am getting
labels only no data results.I would appreciate any assistance provided.

Thanks

How are you filtering the subform? Have you tried creating a report based on
the same record source as your subform? Can you get that much to work?
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

Let me just say I am very new to access.I am working with Access 2007.My
situation is this,i have a main form with 2 unbound text boxes to search a
query based on my main table.The results of this search are then displayed in
a subform in datasheet view.I am trying to create a report button on the main
form based on search results.If i make the report on the subform i am getting
labels only no data results.I would appreciate any assistance provided.

Thanks

My first post was was incorrect. The subform is based on a recordsource clone.
This is the code behind the find button i created.
Private Sub Command36_Click()
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
' Initialize SELECT statement.
MySQL = "SELECT * FROM [Maintable] WHERE "

' Use values entered in text boxes in form header to create criteria
for WHERE clause.
'text box name on form 'Field name in Table 'blank info '
number of times run Addt
AddToWhere [Find1], "[ItemNumber]", MyCriteria, ArgCount
AddToWhere [Find2], "[Sub]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Optional Order By clause
If Me![Find1] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
ElseIf Me![Find2] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [Sub]"
Else
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
End If


' set record source to Subform
Me![MaintableSubform].Form.RecordSource = MyRecordSource

Exit_VIEW_Click:
Exit Sub

Err_VIEW_Click:
MsgBox Error$
Resume Exit_VIEW_Click

End Sub

Thank you for your response. Im just unsure of what the recordsource should
actually be.

"Duane Hookom" wrote:

How are you filtering the subform? Have you tried creating a report based on
the same record source as your subform? Can you get that much to work?
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

Let me just say I am very new to access.I am working with Access 2007.My
situation is this,i have a main form with 2 unbound text boxes to search a
query based on my main table.The results of this search are then displayed in
a subform in datasheet view.I am trying to create a report button on the main
form based on search results.If i make the report on the subform i am getting
labels only no data results.I would appreciate any assistance provided.

Thanks

I would create a report based on Maintable. Then create your button to open
the report using the command button wizard. Then add code to the command
button click event like:

Dim strWhere as String
strWhere = "True "
If Not IsNull(Me.Find1) Then
'assuming ItemNumber is numeric
strWhere = strWhere & " And ItemNumber = " & Me.Find1
End If
If Not IsNull(Me.Find2) Then
'assuming [Sub] is string
strWhere = strWhere & " And [Sub] = """ & Me.Find2 & """"
End If
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

My first post was was incorrect. The subform is based on a recordsource clone.
This is the code behind the find button i created.
Private Sub Command36_Click()
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
' Initialize SELECT statement.
MySQL = "SELECT * FROM [Maintable] WHERE "

' Use values entered in text boxes in form header to create criteria
for WHERE clause.
'text box name on form 'Field name in Table 'blank info '
number of times run Addt
AddToWhere [Find1], "[ItemNumber]", MyCriteria, ArgCount
AddToWhere [Find2], "[Sub]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Optional Order By clause
If Me![Find1] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
ElseIf Me![Find2] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [Sub]"
Else
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
End If


' set record source to Subform
Me![MaintableSubform].Form.RecordSource = MyRecordSource

Exit_VIEW_Click:
Exit Sub

Err_VIEW_Click:
MsgBox Error$
Resume Exit_VIEW_Click

End Sub

Thank you for your response. Im just unsure of what the recordsource should
actually be.

"Duane Hookom" wrote:

How are you filtering the subform? Have you tried creating a report based on
the same record source as your subform? Can you get that much to work?
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

Let me just say I am very new to access.I am working with Access 2007.My
situation is this,i have a main form with 2 unbound text boxes to search a
query based on my main table.The results of this search are then displayed in
a subform in datasheet view.I am trying to create a report button on the main
form based on search results.If i make the report on the subform i am getting
labels only no data results.I would appreciate any assistance provided.

Thanks

OK I,ll try that. But before i do I've got this far, If i take the where
statement off the recordsource of the subform which is "SELECT * FROM
[Maintable] WHERE [ItemNumber] Like 'p*' ORDER BY [ItemNumber]" the report
returns everything in the query that the subform is based on. I just want the
report to display what is currently displayed in the subform after the Find
Button is pressed.

"Duane Hookom" wrote:

I would create a report based on Maintable. Then create your button to open
the report using the command button wizard. Then add code to the command
button click event like:

Dim strWhere as String
strWhere = "True "
If Not IsNull(Me.Find1) Then
'assuming ItemNumber is numeric
strWhere = strWhere & " And ItemNumber = " & Me.Find1
End If
If Not IsNull(Me.Find2) Then
'assuming [Sub] is string
strWhere = strWhere & " And [Sub] = """ & Me.Find2 & """"
End If
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

My first post was was incorrect. The subform is based on a recordsource clone.
This is the code behind the find button i created.
Private Sub Command36_Click()
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
' Initialize SELECT statement.
MySQL = "SELECT * FROM [Maintable] WHERE "

' Use values entered in text boxes in form header to create criteria
for WHERE clause.
'text box name on form 'Field name in Table 'blank info '
number of times run Addt
AddToWhere [Find1], "[ItemNumber]", MyCriteria, ArgCount
AddToWhere [Find2], "[Sub]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Optional Order By clause
If Me![Find1] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
ElseIf Me![Find2] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [Sub]"
Else
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
End If


' set record source to Subform
Me![MaintableSubform].Form.RecordSource = MyRecordSource

Exit_VIEW_Click:
Exit Sub

Err_VIEW_Click:
MsgBox Error$
Resume Exit_VIEW_Click

End Sub

Thank you for your response. Im just unsure of what the recordsource should
actually be.

"Duane Hookom" wrote:

How are you filtering the subform? Have you tried creating a report based on
the same record source as your subform? Can you get that much to work?
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

Let me just say I am very new to access.I am working with Access 2007.My
situation is this,i have a main form with 2 unbound text boxes to search a
query based on my main table.The results of this search are then displayed in
a subform in datasheet view.I am trying to create a report button on the main
form based on search results.If i make the report on the subform i am getting
labels only no data results.I would appreciate any assistance provided.

Thanks

I used my own coding to build the where clause. You could use the same type
of code you used to filter your subreport to build the where condition of the
DoCmd.OpenReport method.
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

OK I,ll try that. But before i do I've got this far, If i take the where
statement off the recordsource of the subform which is "SELECT * FROM
[Maintable] WHERE [ItemNumber] Like 'p*' ORDER BY [ItemNumber]" the report
returns everything in the query that the subform is based on. I just want the
report to display what is currently displayed in the subform after the Find
Button is pressed.

"Duane Hookom" wrote:

I would create a report based on Maintable. Then create your button to open
the report using the command button wizard. Then add code to the command
button click event like:

Dim strWhere as String
strWhere = "True "
If Not IsNull(Me.Find1) Then
'assuming ItemNumber is numeric
strWhere = strWhere & " And ItemNumber = " & Me.Find1
End If
If Not IsNull(Me.Find2) Then
'assuming [Sub] is string
strWhere = strWhere & " And [Sub] = """ & Me.Find2 & """"
End If
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

My first post was was incorrect. The subform is based on a recordsource clone.
This is the code behind the find button i created.
Private Sub Command36_Click()
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
' Initialize SELECT statement.
MySQL = "SELECT * FROM [Maintable] WHERE "

' Use values entered in text boxes in form header to create criteria
for WHERE clause.
'text box name on form 'Field name in Table 'blank info '
number of times run Addt
AddToWhere [Find1], "[ItemNumber]", MyCriteria, ArgCount
AddToWhere [Find2], "[Sub]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Optional Order By clause
If Me![Find1] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
ElseIf Me![Find2] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [Sub]"
Else
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
End If


' set record source to Subform
Me![MaintableSubform].Form.RecordSource = MyRecordSource

Exit_VIEW_Click:
Exit Sub

Err_VIEW_Click:
MsgBox Error$
Resume Exit_VIEW_Click

End Sub

Thank you for your response. Im just unsure of what the recordsource should
actually be.

"Duane Hookom" wrote:

How are you filtering the subform? Have you tried creating a report based on
the same record source as your subform? Can you get that much to work?
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

Let me just say I am very new to access.I am working with Access 2007.My
situation is this,i have a main form with 2 unbound text boxes to search a
query based on my main table.The results of this search are then displayed in
a subform in datasheet view.I am trying to create a report button on the main
form based on search results.If i make the report on the subform i am getting
labels only no data results.I would appreciate any assistance provided.

Thanks

I'm sorry but im lost now. I dont understand how to finish the docmd.
"Duane Hookom" wrote:

I used my own coding to build the where clause. You could use the same type
of code you used to filter your subreport to build the where condition of the
DoCmd.OpenReport method.
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

OK I,ll try that. But before i do I've got this far, If i take the where
statement off the recordsource of the subform which is "SELECT * FROM
[Maintable] WHERE [ItemNumber] Like 'p*' ORDER BY [ItemNumber]" the report
returns everything in the query that the subform is based on. I just want the
report to display what is currently displayed in the subform after the Find
Button is pressed.

"Duane Hookom" wrote:

I would create a report based on Maintable. Then create your button to open
the report using the command button wizard. Then add code to the command
button click event like:

Dim strWhere as String
strWhere = "True "
If Not IsNull(Me.Find1) Then
'assuming ItemNumber is numeric
strWhere = strWhere & " And ItemNumber = " & Me.Find1
End If
If Not IsNull(Me.Find2) Then
'assuming [Sub] is string
strWhere = strWhere & " And [Sub] = """ & Me.Find2 & """"
End If
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

My first post was was incorrect. The subform is based on a recordsource clone.
This is the code behind the find button i created.
Private Sub Command36_Click()
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
' Initialize SELECT statement.
MySQL = "SELECT * FROM [Maintable] WHERE "

' Use values entered in text boxes in form header to create criteria
for WHERE clause.
'text box name on form 'Field name in Table 'blank info '
number of times run Addt
AddToWhere [Find1], "[ItemNumber]", MyCriteria, ArgCount
AddToWhere [Find2], "[Sub]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Optional Order By clause
If Me![Find1] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
ElseIf Me![Find2] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [Sub]"
Else
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
End If


' set record source to Subform
Me![MaintableSubform].Form.RecordSource = MyRecordSource

Exit_VIEW_Click:
Exit Sub

Err_VIEW_Click:
MsgBox Error$
Resume Exit_VIEW_Click

End Sub

Thank you for your response. Im just unsure of what the recordsource should
actually be.

"Duane Hookom" wrote:

How are you filtering the subform? Have you tried creating a report based on
the same record source as your subform? Can you get that much to work?
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

Let me just say I am very new to access.I am working with Access 2007.My
situation is this,i have a main form with 2 unbound text boxes to search a
query based on my main table.The results of this search are then displayed in
a subform in datasheet view.I am trying to create a report button on the main
form based on search results.If i make the report on the subform i am getting
labels only no data results.I would appreciate any assistance provided.

Thanks

How far did you get? Do you have a command button to run the report? Can you
change the code to look something like:

Dim MyCriteria As String
Dim ArgCount As Integer
AddToWhere [Find1], "[ItemNumber]", MyCriteria, ArgCount
AddToWhere [Find2], "[Sub]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If
DoCmd.OpenReport "rptYourReport", acPreview, , MyCriteria

--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

I'm sorry but im lost now. I dont understand how to finish the docmd.
"Duane Hookom" wrote:

I used my own coding to build the where clause. You could use the same type
of code you used to filter your subreport to build the where condition of the
DoCmd.OpenReport method.
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

OK I,ll try that. But before i do I've got this far, If i take the where
statement off the recordsource of the subform which is "SELECT * FROM
[Maintable] WHERE [ItemNumber] Like 'p*' ORDER BY [ItemNumber]" the report
returns everything in the query that the subform is based on. I just want the
report to display what is currently displayed in the subform after the Find
Button is pressed.

"Duane Hookom" wrote:

I would create a report based on Maintable. Then create your button to open
the report using the command button wizard. Then add code to the command
button click event like:

Dim strWhere as String
strWhere = "True "
If Not IsNull(Me.Find1) Then
'assuming ItemNumber is numeric
strWhere = strWhere & " And ItemNumber = " & Me.Find1
End If
If Not IsNull(Me.Find2) Then
'assuming [Sub] is string
strWhere = strWhere & " And [Sub] = """ & Me.Find2 & """"
End If
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

My first post was was incorrect. The subform is based on a recordsource clone.
This is the code behind the find button i created.
Private Sub Command36_Click()
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
' Initialize SELECT statement.
MySQL = "SELECT * FROM [Maintable] WHERE "

' Use values entered in text boxes in form header to create criteria
for WHERE clause.
'text box name on form 'Field name in Table 'blank info '
number of times run Addt
AddToWhere [Find1], "[ItemNumber]", MyCriteria, ArgCount
AddToWhere [Find2], "[Sub]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Optional Order By clause
If Me![Find1] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
ElseIf Me![Find2] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [Sub]"
Else
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
End If


' set record source to Subform
Me![MaintableSubform].Form.RecordSource = MyRecordSource

Exit_VIEW_Click:
Exit Sub

Err_VIEW_Click:
MsgBox Error$
Resume Exit_VIEW_Click

End Sub

Thank you for your response. Im just unsure of what the recordsource should
actually be.

"Duane Hookom" wrote:

How are you filtering the subform? Have you tried creating a report based on
the same record source as your subform? Can you get that much to work?
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

Let me just say I am very new to access.I am working with Access 2007.My
situation is this,i have a main form with 2 unbound text boxes to search a
query based on my main table.The results of this search are then displayed in
a subform in datasheet view.I am trying to create a report button on the main
form based on search results.If i make the report on the subform i am getting
labels only no data results.I would appreciate any assistance provided.

Thanks

I am to that point. The report is based on same query as the subform. I now
need help with the where condition and anything after that. By the way thanks
for all your help so far. Just to verify, i am deleteing the old event
procedure and opening the code manager to insert the code in the buttons on
click event?

"Duane Hookom" wrote:

How far did you get? Do you have a command button to run the report? Can you
change the code to look something like:

Dim MyCriteria As String
Dim ArgCount As Integer
AddToWhere [Find1], "[ItemNumber]", MyCriteria, ArgCount
AddToWhere [Find2], "[Sub]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If
DoCmd.OpenReport "rptYourReport", acPreview, , MyCriteria

--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

I'm sorry but im lost now. I dont understand how to finish the docmd.
"Duane Hookom" wrote:

I used my own coding to build the where clause. You could use the same type
of code you used to filter your subreport to build the where condition of the
DoCmd.OpenReport method.
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

OK I,ll try that. But before i do I've got this far, If i take the where
statement off the recordsource of the subform which is "SELECT * FROM
[Maintable] WHERE [ItemNumber] Like 'p*' ORDER BY [ItemNumber]" the report
returns everything in the query that the subform is based on. I just want the
report to display what is currently displayed in the subform after the Find
Button is pressed.

"Duane Hookom" wrote:

I would create a report based on Maintable. Then create your button to open
the report using the command button wizard. Then add code to the command
button click event like:

Dim strWhere as String
strWhere = "True "
If Not IsNull(Me.Find1) Then
'assuming ItemNumber is numeric
strWhere = strWhere & " And ItemNumber = " & Me.Find1
End If
If Not IsNull(Me.Find2) Then
'assuming [Sub] is string
strWhere = strWhere & " And [Sub] = """ & Me.Find2 & """"
End If
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

My first post was was incorrect. The subform is based on a recordsource clone.
This is the code behind the find button i created.
Private Sub Command36_Click()
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
' Initialize SELECT statement.
MySQL = "SELECT * FROM [Maintable] WHERE "

' Use values entered in text boxes in form header to create criteria
for WHERE clause.
'text box name on form 'Field name in Table 'blank info '
number of times run Addt
AddToWhere [Find1], "[ItemNumber]", MyCriteria, ArgCount
AddToWhere [Find2], "[Sub]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Optional Order By clause
If Me![Find1] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
ElseIf Me![Find2] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [Sub]"
Else
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
End If


' set record source to Subform
Me![MaintableSubform].Form.RecordSource = MyRecordSource

Exit_VIEW_Click:
Exit Sub

Err_VIEW_Click:
MsgBox Error$
Resume Exit_VIEW_Click

End Sub

Thank you for your response. Im just unsure of what the recordsource should
actually be.

"Duane Hookom" wrote:

How are you filtering the subform? Have you tried creating a report based on
the same record source as your subform? Can you get that much to work?
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

Let me just say I am very new to access.I am working with Access 2007.My
situation is this,i have a main form with 2 unbound text boxes to search a
query based on my main table.The results of this search are then displayed in
a subform in datasheet view.I am trying to create a report button on the main
form based on search results.If i make the report on the subform i am getting
labels only no data results.I would appreciate any assistance provided.

Thanks

Disregard my last post. It works great. Thank you for all your help. You rule!

"Asib" wrote:

I am to that point. The report is based on same query as the subform. I now
need help with the where condition and anything after that. By the way thanks
for all your help so far. Just to verify, i am deleteing the old event
procedure and opening the code manager to insert the code in the buttons on
click event?

"Duane Hookom" wrote:

How far did you get? Do you have a command button to run the report? Can you
change the code to look something like:

Dim MyCriteria As String
Dim ArgCount As Integer
AddToWhere [Find1], "[ItemNumber]", MyCriteria, ArgCount
AddToWhere [Find2], "[Sub]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If
DoCmd.OpenReport "rptYourReport", acPreview, , MyCriteria

--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

I'm sorry but im lost now. I dont understand how to finish the docmd.
"Duane Hookom" wrote:

I used my own coding to build the where clause. You could use the same type
of code you used to filter your subreport to build the where condition of the
DoCmd.OpenReport method.
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

OK I,ll try that. But before i do I've got this far, If i take the where
statement off the recordsource of the subform which is "SELECT * FROM
[Maintable] WHERE [ItemNumber] Like 'p*' ORDER BY [ItemNumber]" the report
returns everything in the query that the subform is based on. I just want the
report to display what is currently displayed in the subform after the Find
Button is pressed.

"Duane Hookom" wrote:

I would create a report based on Maintable. Then create your button to open
the report using the command button wizard. Then add code to the command
button click event like:

Dim strWhere as String
strWhere = "True "
If Not IsNull(Me.Find1) Then
'assuming ItemNumber is numeric
strWhere = strWhere & " And ItemNumber = " & Me.Find1
End If
If Not IsNull(Me.Find2) Then
'assuming [Sub] is string
strWhere = strWhere & " And [Sub] = """ & Me.Find2 & """"
End If
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

My first post was was incorrect. The subform is based on a recordsource clone.
This is the code behind the find button i created.
Private Sub Command36_Click()
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
' Initialize SELECT statement.
MySQL = "SELECT * FROM [Maintable] WHERE "

' Use values entered in text boxes in form header to create criteria
for WHERE clause.
'text box name on form 'Field name in Table 'blank info '
number of times run Addt
AddToWhere [Find1], "[ItemNumber]", MyCriteria, ArgCount
AddToWhere [Find2], "[Sub]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Optional Order By clause
If Me![Find1] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
ElseIf Me![Find2] "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [Sub]"
Else
MyRecordSource = MySQL & MyCriteria & " ORDER BY [ItemNumber]"
End If


' set record source to Subform
Me![MaintableSubform].Form.RecordSource = MyRecordSource

Exit_VIEW_Click:
Exit Sub

Err_VIEW_Click:
MsgBox Error$
Resume Exit_VIEW_Click

End Sub

Thank you for your response. Im just unsure of what the recordsource should
actually be.

"Duane Hookom" wrote:

How are you filtering the subform? Have you tried creating a report based on
the same record source as your subform? Can you get that much to work?
--
Duane Hookom
Microsoft Access MVP


"Asib" wrote:

Let me just say I am very new to access.I am working with Access 2007.My
situation is this,i have a main form with 2 unbound text boxes to search a
query based on my main table.The results of this search are then displayed in
a subform in datasheet view.I am trying to create a report button on the main
form based on search results.If i make the report on the subform i am getting
labels only no data results.I would appreciate any assistance provided.

Thanks