Defining Variables

[HelpNewtoAccess]

How would I define the variables for this code? The BldgAbb_ID is text so I would set it to string but where would I go form here? I am opening a report based on the building abbreviation of a form. Thanks in advance for your help.

Option Compare Database
Option Explicit
Dim BldgAbb_ID As String

Private Sub cmdElev_Click()
On Error GoTo cmdElev_Click_Err

    DoCmd.OpenReport "rptElevators", acViewReport, "", "[BldgAbb_ID]=" & "'" & BldgAbb_ID & "'", acNormal

cmdElev_Click_Exit:
    Exit Sub

cmdElev_Click_Err:
    MsgBox Error$
    Resume cmdElev_Click_Exit

End Sub

 

[Isaac]

Maybe a line like:

BldgAbb_ID = Me.ControlName.Value

 

[HelpNewtoAccess]

Maybe a line like:

BldgAbb_ID = Me.ControlName.Value

Doesn't seem to work get errors. Trying to pass the value from a form to open a report to the same building.

 

[Isaac]

Doesn't seem to work get errors.

Can you provide more details?

 

[GinaWhipp]

Hmm, try...

DoCmd.OpenReport "rptElevators", acViewReport, "", "[BldgAbb_ID]='" & Me.BldgAbb_ID & "'", acNormal

 

[HelpNewtoAccess]

Put code like your suggestions and below is the error that I got.

Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please read this for further information:-
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/
Please feel free to Remove this Comment

Option Compare Database
Option Explicit
Dim BldgAbb_ID As String
BldgAbb_ID = Me.Control.Value


Private Sub cmdElev_Click()
On Error GoTo cmdElev_Click_Err

 
    DoCmd.OpenReport "rptElevators", acViewReport, "", "[BldgAbb_ID]='" & Me.BldgAbb_ID & "'", acNormal
 

cmdElev_Click_Exit:
    Exit Sub

cmdElev_Click_Err:
    MsgBox Error$
    Resume cmdElev_Click_Exit

End Sub

 

[Isaac]

Ok ... when I said

BldgAbb_ID = Me.ControlName.Value

, I meant that you should replace ControlName with the name of your Control.

 

[HelpNewtoAccess]

So the control name would be the same as the text box on the report which would be BldgAbb_ID correct? I tried this first but doesn't seem to work. I thought maybe they couldn't have the same name so I changed it.

 

[GinaWhipp]

My bad, get rid of the acNormal at the end...

DoCmd.OpenReport "rptElevators", acViewReport, "", "[BldgAbb_ID]='" & Me.BldgAbb_ID & "'"

 

[GinaWhipp]

There is no need to declare a control which is already on your Form, unless it is NOT a control on your Form and a Public Variable.

 

[Isaac]

Well, in theory I agree Gina, but the OP seemed to specifically prefer to separate it out by declaring a string variable and assigning it the value of a form control. Nothing wrong with that, particularly, and was just trying to assist them in that regard.

 

[GinaWhipp]

Well, in theory I agree Gina, but the OP seemed to specifically prefer to separate it out by declaring a string variable and assigning it the value of a form control. Nothing wrong with that, particularly, and was just trying to assist them in that regard.

I was not disagreeing with you, sorry if it came off like that. Was just pointed out that not really necessary unless that control is not on the Form. (Always more than one way to skin a cat. (My apologies to the cat lovers. ))

 

[Isaac]

I was not disagreeing with you, sorry if it came off like that. Was just pointed out that not really necessary unless that control is not on the Form.

Oh ok, yeah I see what you mean. Didn't mean to be overly reactive. I've been a little "off" today for some reason generally speaking.

(Always more than one way to skin a cat. (My apologies to the cat lovers. ))

 

[HelpNewtoAccess]

Thanks for all the help but I would like to put in a module and make it public as I have multiple forms that would use this code. In the future if the code need to be altered it would be nice just to do it in one spot instead of on multiple forms. The original code for opening the form seemed to work until it wanted me to declare the variable for BldgAbb_ID. Thanks

 

[Isaac]

Do all the forms have that same control name?
Or are you saying that you want to make a procedure that takes the control name itself as a parameter, and when called with that passed in, it opens a report using that as the openargs? Do you want the report name to also be a variable? Do all the reports being opened have the same controlsource field in them?

More specific information on what you want, please..

 

[HelpNewtoAccess]

Ok I try to explain this the best I can as I am very green with all this.

1. I have a main menu form that you can select a number of buildings (eg.1-100).

2. I then click and open a form called bldgform for that building that a have a textbox with name and control source of BldgAbb_ID for bldg1.

3. I then click on a button cmdElev on bldgform that opens a report to show all the elevators that match (BldgAbb_ID) bldg1 and have information on it.

4. On the same bldgform I have another button cmdAlarms that opens another report that same way with control source and name BldgAbb_ID.

These are just 2 buttons but I have about 5 more for stuff related to the buildings.
I want to set up a standard module to call the public subroutines but I need to define the variables.

 

[GinaWhipp]

Hmm, not really suited for a Module if each report has different parameters and names. I mean you could do it but seems like more work than it is worth. Each report will already opens filtered. You just need to copy the code and change Report Name and Where statement.

Or am I misunderstanding?

 

[HelpNewtoAccess]

So basically what I want is the on the click events for 7 buttons on each form to refer to the subroutine in the module like this.
If I ever need to change the code I can just change it in the module instead of going to over a 100 forms. It work for just opening a form without filtering but when I try to filter it wants me to define my variable for BldgAbb_ID. If I put the code just on the form everything works good I can filter the report based on the BldgAbb_ID.

mdlCommon.cmdElevators_Click

Option Compare Database
Option Explicit

Public Sub cmdGraphicNotes_Click()
On Error GoTo cmdGraphicNotes_Click_Err

    DoCmd.OpenReport "rptGraphicNotes", acViewReport, "", "[BldgAbbID]=" & "'" & BldgAbbID & "'", acNormal
    DoCmd.Maximize


cmdGraphicNotes_Click_Exit:
    Exit Sub

cmdGraphicNotes_Click_Err:
    MsgBox Error$
    Resume cmdGraphicNotes_Click_Exit

End Sub

Public Sub cmdBldgNotes_Click()
On Error GoTo cmdBldgNotes_Click_Err

    DoCmd.OpenReport "rptBldgNotes", acViewReport, "", "[BldgAbb_ID]=" & "'" & BldgAbb_ID & "'", acNormal
    DoCmd.Maximize


cmdBldgNotes_Click_Exit:
    Exit Sub

cmdBldgNotes_Click_Err:
    MsgBox Error$
    Resume cmdBldgNotes_Click_Exit

End Sub

Public Sub cmdElevators_Click()
On Error GoTo cmdElevators_Click_Err

 
    DoCmd.OpenReport "rptElevators", acViewReport, "", "[BldgAbb_ID]=" & "'" & BldgAbb_ID & "'", acNormal

cmdElevators_Click_Exit:
    Exit Sub

cmdElevators_Click_Err:
    MsgBox Error$
    Resume cmdElevators_Click_Exit

End Sub

 

[GinaWhipp]

You can just put the DoCmd.OpenReport.... lines under one Command Button., no need to separate them since you want them to all open at once.

 

[HelpNewtoAccess]

Thanks but I only want an elevator report to show if I select that command button not all the reports. Is there not an easy way to declare the variable for BldgAbb_ID to make this work like it would on a form?