One report Many Forms...Anyone Any Ideas??? 1

[WB786]

Here is my goal: To have one report that will point to the customer ID on various forms. Currently I have one report per form with its query pointing to the custid of the form. To reduce the size of my database I would like the report to open up and change the record source to point to the form that is active.

I can change the record source on report open function but am not able to figure out how to say:

if formA active then record source ="queryA"
else
if formB active then recordsource = "queryB"
end if
end if

Any other ideas are most welcome. Thanks in advance,

WB

 

[randysmid]

Hi WB,
I think I have part (or all) of the solution. In Access, you can create a public (or global) variable that can be used by all parts of an Access application.
1) Place code similar to this in the General Declarations section of a module:
Public pstrRecordSource As String
(if you have no modules, simply create a new one)
2) In each form that is used to run the report, set the public variable to the name of the recordsource desired.
3) In the ReportOpen event, set this code:
Me.Recordsource = pstrRecordSource

HTH,
Randy Smith
California Teachers Association

 

[WB786]

I kind of understand what you are tyring to do here but need more help with the coding:

1. I have a module now called RecSource with the code:
Option Compare Database

Public vRecordSource As String
Function frmLMFP()
vRecordSource = "qryLMFP"
End Function
Function frmLBC()
vRecordSource = "qryLBC"
End Function
Function frmAll()
vRecordSource = "qryAll"
End Function

2. I have put this in frmLBC:
Public frmLBC As AccessObject

I know AccessObject is not right so need to know what to choose here.

3. I have this in my report now:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = vRecordSource
End Sub

Please provide further insight and help and correct my code where needed.

Thank you.
WB

 

[randysmid]

Hi,
You don't need all those functions. Instead, you can simply set the value of pstrRecordSource inside the form(s) (e.g., frmLBC) that are used to enter parameters for the report. (I am not sure why you are using "v" as a prefix.) Plus, the Public variable definition is supposed to be in the General Declarations section of the module, and that is all the module needs.

HTH,
Randy Smith
California Teachers Association

 

[WB786]

I was looking at some other post and got the prefix "v" from there.

So my module should say then:
Public pstrRecordSource As String

And my frmLBC:
Public Function pstrRecordSource() As String
pstrRecordSource = "qryLBC"
End Function

And my report:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = pstrRecordSource
End Sub


I am getting close right? Can you just tell me (with the proper code) where I am being wrong I have to get this up and running by tomorrow if all possible.

Thanks,
WB

 

[randysmid]

Hi,
This section is incorrect:
And my frmLBC:
Public Function pstrRecordSource() As String
pstrRecordSource = "qryLBC"
End Function

WHENEVER you wish to set the value of pstrRecordSource, just make the assignment like this (with NO FUNCTION CALLS):
pstrRecordSource = "qryLBC"


HTH,
Randy Smith
California Teachers Association

 

[WB786]

Ok that didn't work. I am getting invalid outside procedure. MS Help says: The statement must occur within a Sub or Function, or a property procedure (Property Get, Property Let, Property Set).

But you are saying don't use any function calls. I guess I need more clarification then.

Thanks,
WB

 

[randysmid]

WB,
Once you declare a Public variable in the module (it MUST be in the General Declarations section), then that variable will be available to the entire database, including all the forms, queries, reports, etc. The error you mentioned means that your Public declaration is NOT in the General Declarations section.

HTH,
Randy Smith
California Teachers Association

 

[WB786]

When I edit my module all I see from the two drop downs at the top is (General) and (Declarations) then below that in the white space I have the code as follows:

Public pstrRecordSource As String

That's it.
WB

 

[randysmid]

Hi,
The line immediately above it should say:
"Option Compare Database"
Next, your Public statement will appear.
After that, you should see a horizontal line indicating that your Public declaration statement is inside the General Declarations section. Once this is done, and you save the module (it will ask for module name), then you will be able to use the variable anywhere in your database application.

HTH,
Randy Smith
California Teachers Association

 

[WB786]

I didn't get the horizontal line as you indicated. I did add the line of Option Compare Database in the module. But when the form loads it is still giving me the same error and highlighting the "qryLBC" part.

So what is next?

Thank you so far in putting up with me.

WB

 

[randysmid]

Hi,
You mentioned that you saw (General) in the top left drop down box, and (Declarations) in the top right drop down box, then you are ok. The line will appear if you have other procedures or functions. Now that your global variable has been declared in a module, you are now free to use it anywhere.

HTH,
Randy Smith
California Teachers Association

 

[WB786]

Ok that's what I thought. But I am still getting that invalid outside procedure. Should I just add the "Public pstrRecordSource As String" into an already exsisting module then?

Thanks,
WB

 

[randysmid]

Hi,
Can you post the code where you are getting the error? Do you know how to use the debugger? If not, let me know and I will post some info on how to use it. You can use the debugger to find the line of code that is causing the error.

HTH,
Randy Smith
California Teachers Association

 

[WB786]

When I try to open the form called frmLBC is when I am getting the error. This is the code I have in the form as you suggested:

Option Compare Database
pstrRecordSource = "qryLBC"

WB

 

[randysmid]

Hi,
You have placed this code in the wrong place. Because it includes the "Option Compare....", that means it is in the General Declarations section. You should probably place it in the Forms Open event, or maybe the clicked event of a command button that runs the report.

HTH,
Randy Smith
California Teachers Association

 

[WB786]

Well, I tried as you said. I put the code in both places one at a time and the report comes up with no data. It is not pointing to the qryLBC like it should.

Is there anyway that during report open I can just simply say if formx is active then use a certain query to run the data from.

Thank you for trying to help me out with this.

WB