Defining Variables (1 Viewer)

Local time
Today, 17:30
Joined
Dec 13, 2019
Messages
79
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.

Code:
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
 
Last edited by a moderator:

Isaac

Lifelong Learner
Local time
Today, 16:30
Joined
Mar 14, 2017
Messages
8,738
Maybe a line like:


Code:
BldgAbb_ID = Me.ControlName.Value
 
Local time
Today, 17:30
Joined
Dec 13, 2019
Messages
79
Maybe a line like:


Code:
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.
 

GinaWhipp

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
Hmm, try...
Code:
DoCmd.OpenReport "rptElevators", acViewReport, "", "[BldgAbb_ID]='" & Me.BldgAbb_ID & "'", acNormal
 
Local time
Today, 17:30
Joined
Dec 13, 2019
Messages
79
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
Code:
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

1594676143927.png
 
Last edited by a moderator:

Isaac

Lifelong Learner
Local time
Today, 16:30
Joined
Mar 14, 2017
Messages
8,738
Ok ... when I said
Code:
BldgAbb_ID = Me.ControlName.Value
, I meant that you should replace ControlName with the name of your Control.
 
Local time
Today, 17:30
Joined
Dec 13, 2019
Messages
79
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

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
My bad, get rid of the acNormal at the end...
Code:
DoCmd.OpenReport "rptElevators", acViewReport, "", "[BldgAbb_ID]='" & Me.BldgAbb_ID & "'"
 

GinaWhipp

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
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

Lifelong Learner
Local time
Today, 16:30
Joined
Mar 14, 2017
Messages
8,738
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

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
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

Lifelong Learner
Local time
Today, 16:30
Joined
Mar 14, 2017
Messages
8,738
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. 🙃 ))
🤣🤣
 
Local time
Today, 17:30
Joined
Dec 13, 2019
Messages
79
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

Lifelong Learner
Local time
Today, 16:30
Joined
Mar 14, 2017
Messages
8,738
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..
 
Local time
Today, 17:30
Joined
Dec 13, 2019
Messages
79
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.
 
Last edited:

GinaWhipp

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
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?
 
Local time
Today, 17:30
Joined
Dec 13, 2019
Messages
79
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.

Code:
mdlCommon.cmdElevators_Click

Code:
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
 
Last edited:

GinaWhipp

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
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.
 
Local time
Today, 17:30
Joined
Dec 13, 2019
Messages
79
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?
 

Users who are viewing this thread

Top Bottom