Changing Report Record Source

N

Nanette

I've been trying to change a record source for a report. Not sure if I'm on
the right track.

I have 3 cascading combo boxes on a form that are used to fill in the
subform. I need the report to use those combo boxes to determine what items
will be chosen to be seen on a report.

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT * FROM LineItem WHERE " & _
"RFQNo = " & Chr(39) & Me![RFQ Selection].cboRFQ & Chr(39) & _
" and Change = " & Chr(39) & Me![RFQ Selection].cboChange & Chr(39) & _
" and LineItem = " & Chr(39) & Me![RFQ Selection].cboLine & Chr(39)

End Sub

My most recent error message was: can't find [RFQ Selection]

Any suggestions?
 
D

Duane Hookom

I'm not sure why you don't use the Where Condition of the DoCmd.OpenReport
method. This would be much easier to code and more conventional.

Your report doesn't know that "Me" refers to some form. You could try
something like:

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT * FROM LineItem WHERE " & _
"RFQNo = " & Chr(39) & Forms![RFQ Selection].cboRFQ & Chr(39) & _
" and Change = " & Chr(39) & Forms![RFQ Selection].cboChange & Chr(39) & _
" and LineItem = " & Chr(39) & Forms![RFQ Selection].cboLine & Chr(39)

End Sub

Again, I would use the Where Condition...
 
N

Nanette

I fixed my code and it worked well in the Report_Open. Thanks much.

I tried putting the code in under the DoCmd.OpenReport, but then all records
show up. Not sure why this happens. I'm a newbie so it might be a simple
answer.

Any ideas?

Duane Hookom said:
I'm not sure why you don't use the Where Condition of the DoCmd.OpenReport
method. This would be much easier to code and more conventional.

Your report doesn't know that "Me" refers to some form. You could try
something like:

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT * FROM LineItem WHERE " & _
"RFQNo = " & Chr(39) & Forms![RFQ Selection].cboRFQ & Chr(39) & _
" and Change = " & Chr(39) & Forms![RFQ Selection].cboChange & Chr(39) & _
" and LineItem = " & Chr(39) & Forms![RFQ Selection].cboLine & Chr(39)

End Sub

Again, I would use the Where Condition...
--
Duane Hookom
Microsoft Access MVP


Nanette said:
I've been trying to change a record source for a report. Not sure if I'm on
the right track.

I have 3 cascading combo boxes on a form that are used to fill in the
subform. I need the report to use those combo boxes to determine what items
will be chosen to be seen on a report.

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT * FROM LineItem WHERE " & _
"RFQNo = " & Chr(39) & Me![RFQ Selection].cboRFQ & Chr(39) & _
" and Change = " & Chr(39) & Me![RFQ Selection].cboChange & Chr(39) & _
" and LineItem = " & Chr(39) & Me![RFQ Selection].cboLine & Chr(39)

End Sub

My most recent error message was: can't find [RFQ Selection]

Any suggestions?
Click to expand...
Click to expand...
 
D

Duane Hookom

Your code to open the report might look like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboRFQ) Then
strWhere = strWhere & " AND RFQNo =""" & Me.cboRFQ & """ "
End If
If Not IsNull(Me.cboChange) Then
strWhere = strWhere & " AND Change =""" & Me.cboChange & """ "
End If
If Not IsNull(Me.cboLine) Then
strWhere = strWhere & " AND LineItem =""" & Me.cboLine & """ "
End If
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

With the above code, the form would not need to be open prior to opening the
report.

--
Duane Hookom
Microsoft Access MVP


Nanette said:
I fixed my code and it worked well in the Report_Open. Thanks much.

I tried putting the code in under the DoCmd.OpenReport, but then all records
show up. Not sure why this happens. I'm a newbie so it might be a simple
answer.

Any ideas?

Duane Hookom said:
I'm not sure why you don't use the Where Condition of the DoCmd.OpenReport
method. This would be much easier to code and more conventional.

Your report doesn't know that "Me" refers to some form. You could try
something like:

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT * FROM LineItem WHERE " & _
"RFQNo = " & Chr(39) & Forms![RFQ Selection].cboRFQ & Chr(39) & _
" and Change = " & Chr(39) & Forms![RFQ Selection].cboChange & Chr(39) & _
" and LineItem = " & Chr(39) & Forms![RFQ Selection].cboLine & Chr(39)

End Sub

Again, I would use the Where Condition...
--
Duane Hookom
Microsoft Access MVP


Nanette said:
I've been trying to change a record source for a report. Not sure if I'm on
the right track.

I have 3 cascading combo boxes on a form that are used to fill in the
subform. I need the report to use those combo boxes to determine what items
will be chosen to be seen on a report.

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT * FROM LineItem WHERE " & _
"RFQNo = " & Chr(39) & Me![RFQ Selection].cboRFQ & Chr(39) & _
" and Change = " & Chr(39) & Me![RFQ Selection].cboChange & Chr(39) & _
" and LineItem = " & Chr(39) & Me![RFQ Selection].cboLine & Chr(39)

End Sub

My most recent error message was: can't find [RFQ Selection]

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
N

Nanette

Thanks for your thoroughness Duane.

I'll give it a try.

Duane Hookom said:
Your code to open the report might look like:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboRFQ) Then
strWhere = strWhere & " AND RFQNo =""" & Me.cboRFQ & """ "
End If
If Not IsNull(Me.cboChange) Then
strWhere = strWhere & " AND Change =""" & Me.cboChange & """ "
End If
If Not IsNull(Me.cboLine) Then
strWhere = strWhere & " AND LineItem =""" & Me.cboLine & """ "
End If
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

With the above code, the form would not need to be open prior to opening the
report.

--
Duane Hookom
Microsoft Access MVP


Nanette said:
I fixed my code and it worked well in the Report_Open. Thanks much.

I tried putting the code in under the DoCmd.OpenReport, but then all records
show up. Not sure why this happens. I'm a newbie so it might be a simple
answer.

Any ideas?

Duane Hookom said:
I'm not sure why you don't use the Where Condition of the DoCmd.OpenReport
method. This would be much easier to code and more conventional.

Your report doesn't know that "Me" refers to some form. You could try
something like:

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT * FROM LineItem WHERE " & _
"RFQNo = " & Chr(39) & Forms![RFQ Selection].cboRFQ & Chr(39) & _
" and Change = " & Chr(39) & Forms![RFQ Selection].cboChange & Chr(39) & _
" and LineItem = " & Chr(39) & Forms![RFQ Selection].cboLine & Chr(39)

End Sub

Again, I would use the Where Condition...
--
Duane Hookom
Microsoft Access MVP


:

I've been trying to change a record source for a report. Not sure if I'm on
the right track.

I have 3 cascading combo boxes on a form that are used to fill in the
subform. I need the report to use those combo boxes to determine what items
will be chosen to be seen on a report.

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "SELECT * FROM LineItem WHERE " & _
"RFQNo = " & Chr(39) & Me![RFQ Selection].cboRFQ & Chr(39) & _
" and Change = " & Chr(39) & Me![RFQ Selection].cboChange & Chr(39) & _
" and LineItem = " & Chr(39) & Me![RFQ Selection].cboLine & Chr(39)

End Sub

My most recent error message was: can't find [RFQ Selection]

Any suggestions?
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

Cascading Combo Boxes 2
Dynamic Naming of Form Button 3
Major Form Design 3
filtering a form based on the values of a subform 0
Cascading Combo Box - find record in query based on 0
Multiple Criteria in Code 13
Run delete query instead of report... 4
Multiple Criteria in Code 0

Top