RecordSource not changing correctly

[dr00bie]

I have a report that runs off a Stored Procedure. The SP looks like this,

CREATE PROCEDURE spCreditCardLogAllUsers
@ImportDate Varchar(20)
AS
SELECT UID, C.CardholderID, CardholderName, Department, CardNumber, CardType, PONum, PODate, Vendor, Description, Amount, CCCObjectCode, ImportDate, Complete, Carryover, Credit, PriorCO,
Case When Carryover = 1 Then Amount Else 0 End as CarryoverTot, Case When Complete = 1 Then Amount Else 0 End as CompleteTot, Case When Credit = 1 Then Amount Else 0 End as CreditTot, Case When PriorCO = 1 Then Amount Else 0 End as PriorCOTot
FROM CreditCardLog C INNER JOIN Cardholder CH ON C.CardholderID = CH.CardholderID INNER JOIN CardType CT ON CH.CardTypeID = CT.CardTypeID
WHERE ImportDate = @ImportDate
ORDER BY C.PONum;
GO

I have made a form, which has a dropdown box and button on it and the dropdown list displys a list of all the valid Import Dates. The user selects the date, then clicks the button and a report should load. I have done this before and it works like a dream, but now I am running into some issues...

This is the code on the Open event of the report.

'Open Import Date form to get user input
DoCmd.OpenForm "frmImportDateCCC", acNormal, , , , acDialog

'Change Recordsource to use the input given by user.
Dim strRecordSource As String
strRecordSource = "Exec [spCreditCardLogAllUsers] " & "'" & Forms!frmImportDateCCC.ddlImportDate & "'"
Me.RecordSource = strRecordSource

DoCmd.Close acForm, "frmImportDateCCC"

When I try to run this report, it shows me the frmImportDateCCC form, and I can choose a value, but when I click Get Report, it opens the report window with no data and the frmImportDateCC stays open. If I close the frmImportDateCCC then it gives me the following error,

Credit Card Log Reports can't find the form "frmImportDateCCC" referred to in a macro expression or Visual Basic code.

The form is there and the spelling is correct... can anyone spot where I am screwing up?

Thanks,
Drew

 

[cmmrfrds]

It looks like the variable is defined local to the Form. I assume the recordsource is in the Report you want to Open.

Make strRecordSource a Public variable so that it is available for the record source in the report. I usually put public variable in the standard module so they are available through out the App.