Changing Report Record Source

G

Guest

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?
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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
Multiple Criteria in Code 13
Change Record Source 9
Run delete query instead of report... 4
Memo Field Display Problem 2
Multiple Criteria in Listbox 3
Problem passing SQL string to Record Source property of a Report 3
Report - Record Source 5

Top