Avatar of ChadLittle
ChadLittle
 asked on

Variables for form names in VBA

Hi,

I have a sub that works for one button, but I am trying to share it for multiple buttons on a form, each of which would write things on a different form than the other buttons.  To accomplish this, I need to put variables in two places to finish, but I can't figure it out.  My problem is in lines 17 and 19 below.  Here is part of the code I am using:
strSubFormName As String
Dim strFormAddress as String

Private Sub cmdCreatePO_Click()

strSubFormName = ("Form_fsubPOLines")

strFormAddress = ("Forms!frmPO.fraPOLines.Form")

Call CreatePO

End Sub


Private Sub CreatePO

Dim frmLines as strSubFormName 'How do I do this as a variable?  Dim frmLines as Form_fsubPOLines works.

Set frmLines = strFormAddress 'How do I do this as a variable?  Set frmLines = Forms!frmPO.fraPOLines.Form works.

Do Until rsSourceLines.EOF
	frmLines.Recordset.AddNew
        frmLines.cboSOLinesID = rsSourceLines!SOLinesID
        frmLines.lngUnitQuantity = rsSourceLines!lngSelectedQuantity
        frmLines.UnitsID = rsSourceLines!UnitsID
        frmLines.cboSOLinesID_AfterUpdate
        frmLines.Dirty = False
        rsSourceLines.MoveNext
Loop

End Sub

Open in new window

Do you know how I can rewrite lines 17 and 19 to work?

Thank you!
Chad
Microsoft Access

Avatar of undefined
Last Comment
ChadLittle

8/22/2022 - Mon
IrogSinta

I'm assuming fraPOLines is a subform in your that has the sales lines.  But what is fsubPOLines?
ChadLittle

ASKER
frmPO is a form.
fraPOLines is a frame control on frmPO whose source is the subform fsubPOLines.  If other words, fraPOLines is the a conrtol on frmPO that holds fsubPOLines.

Does that make sense?

Chad
IrogSinta

You may need to enlighten me some more.  I'm not following the concept of a frame control with a recordsource.  Can you describe this frame control a bit more?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ChadLittle

ASKER
Sorry, I was trying to save your time by not posting extra stuff, but I think I took out too much and wasted your time.  Here is the code again:
strSubFormName As String
Dim strFormAddress as String

Private Sub cmdCreatePO_Click()

strFormName = ("frmPO")
strSubFormName = ("Form_fsubPOLines")
strFormAddress = ("Forms!frmPO.fraPOLines.Form")

Call CreatePO

End Sub


Private Sub CreatePO

Dim rsSourceLines As Recordset

Set rsSourceLines = CurrentDb.OpenRecordset("SELECT tblSOLines.SOLinesID, tblSOLines.UnitsID, tblSOLines.lngSelectedQuantity, tblSOLines.WarehouseID, tblSOHeader.PVID, tblSOHeader.dtmDate " _
                & "FROM tblSOHeader INNER JOIN tblSOLines ON tblSOHeader.SOHeaderID = tblSOLines.SOHeaderID " _
                & "WHERE (((tblSOLines.lngSelectedQuantity) > 0) And ((tblSOHeader.PVID) = " & Me![cboPVID] & ") And ((tblSOHeader.ManufacturerID) = " & Me![cboManufacturerID] & ") And ((tblSOHeader.SOHeaderID) = " & Me![txtSOHeaderID] & ")) " _
                & "ORDER BY tblSOLines.SOLinesID; ")

Dim frmLines as strSubFormName 'How do I do this as a variable?  Dim frmLines as Form_fsubPOLines works.

DoCmd.OpenForm strFormName
DoCmd.GoToRecord acDataForm, strFormName, acLast

Set frmLines = strFormAddress 'How do I do this as a variable?  Set frmLines = Forms!frmPO.fraPOLines.Form works.

Do Until rsSourceLines.EOF
	frmLines.Recordset.AddNew
        frmLines.cboSOLinesID = rsSourceLines!SOLinesID
        frmLines.lngUnitQuantity = rsSourceLines!lngSelectedQuantity
        frmLines.UnitsID = rsSourceLines!UnitsID
        frmLines.cboSOLinesID_AfterUpdate
        frmLines.Dirty = False
        rsSourceLines.MoveNext
Loop

rsSourceLines.Close

Set rsSourceLines = Nothing

End Sub

Open in new window

I added 6, 17-22, 26, 27, 41 and 43.  rsSourceLines is a recordset that I use to enter data into the form. I then open that form, go to the subform, enter the data, and activate the afterupdate events.  
My challenge is I want this same code to be able to open other forms and enter data there instead if another button is pushed.  The problem is now on lines 24 and 29.

Thank you for looking into this!
Chad
IrogSinta

Is all this code in the form, frmPO?  Is it possible for you to upload a stripped down version of your database with any private information removed?
ChadLittle

ASKER
I cannot do that right now, but it is on a  different form called frmSO.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
IrogSinta

Sorry, but I still have a hard time visualizing this.  How about a screen shot at least of frmPO that shows fraPOLines?
ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeffrey Coachman

If your goal is to declare the frame as a "Control" then use something like this:

    Dim ctlFrameLines As Control
    Set ctlFrameLines = Me.frmLines

Then you can retrieve the name like so:
    msgbox ctlFrameLines.name


If your goal is to store just the name of the control, then just do this:
dim strFrameName as string
strFrameName =me.frmLines.Name

Then you can retrieve the name like so:
    msgbox strFrameName


But like IrogSinta, I am still a bit hazy about what you ultimate goal is here...

I have no idea what this means:
"I am trying to share it for multiple buttons on a form, each of which would write things on a different form than the other buttons.  To accomplish this, I need to put variables in two places to finish, "
?
Can you explain what this means in a real-world user scenario?
Perhaps there is a more efficient way of doing this.

I patiently await your sample as well...


JeffCoachman
ChadLittle

ASKER
mbizup, I tried what you said and I get a runtime error 2450: Access cannot find the referenced form 'frmPO'.

Here is my updated code:
Private Sub cmdCreatePO_Click()

CreateOrderLines "frmPO", "fraPOLines"

End Sub


Private Sub CreatePO(strFormName As String, strSubformName As String)
With Forms(strFormName).Controls(strSubformName).Form

Dim rsSourceLines As Recordset

Set rsSourceLines = CurrentDb.OpenRecordset("SELECT tblSOLines.SOLinesID, tblSOLines.UnitsID, tblSOLines.lngSelectedQuantity, tblSOLines.WarehouseID, tblSOHeader.PVID, tblSOHeader.dtmDate " _
                & "FROM tblSOHeader INNER JOIN tblSOLines ON tblSOHeader.SOHeaderID = tblSOLines.SOHeaderID " _
                & "WHERE (((tblSOLines.lngSelectedQuantity) > 0) And ((tblSOHeader.PVID) = " & Me![cboPVID] & ") And ((tblSOHeader.ManufacturerID) = " & Me![cboManufacturerID] & ") And ((tblSOHeader.SOHeaderID) = " & Me![txtSOHeaderID] & ")) " _
                & "ORDER BY tblSOLines.SOLinesID; ")


    DoCmd.OpenForm strFormName
    DoCmd.GoToRecord acDataForm, strFormName, acLast
    
  
    Do Until rsSourceLines.EOF
            .Recordset.AddNew
            .cboSOLinesID = rsSourceLines!SOLinesID
            .lngUnitQuantity = rsSourceLines!lngSelectedQuantity
            .UnitsID = rsSourceLines!UnitsID
            .cboSOLinesID_AfterUpdate
            .Dirty = False
            rsSourceLines.MoveNext
    Loop
    
End With

End Sub

Open in new window

The debug points to line 9.  Can you tell what I am doing wrong?
Thanks.  I feel safe because you are working on this :).
Chad
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mbizup

Or if you don't want the form to be open, there are other ways to do this.

You're just adding records from one table to another, correct?
ChadLittle

ASKER
mbizup,

You nailed it . . . again.  Thanks.

mbizup, Jeff, and Irogsinta,

You have all three answered questions for me before and I appreciate the time you always give me.  I am sorry that my initial question is not always clear.  As you teach me I am learning to both make better code and to ask better questions.  I will endeavor to be more clear next time so it does not take so much of your time to help me - if you would still be as gracious as you have been so far in helping me and if I am still as clumsy as I have been to keep getting stuck and needing help.

If you are interested, what I was doing here is creating purchase orders, service orders, or another type of purchase order from a sales order form.  I have three buttons that the user can press on the sales order form.  Each button creates the different type of purchase / service order the user requires - each of which has its own set of tables and forms.  Variablizing (if that is a word) the name of the form like we did here enables me to use the afterupdate events in the respective forms to fill out the pricing and other information for the service orders and purchase orders.  I want the pricing handled by afterupdate events on the form because sometimes the user may make changes on those forms and I want to use the same code for pricing for both creating new purchase orders and for updating existing ones.

Regarding frames and subforms, I am (trying) to use the Leszynski naming convention (http://en.wikipedia.org/wiki/Leszynski_naming_convention) and it uses fra to name a frame and fsub to name a subform.  I may be going about things in an archaic fashion, but when creating a subform I create it separately from the main form and name it, say fsubPOLines. Then I insert a frame for that subform in the main form, name that frame, then set the subform as the source.  In this case I created fraPOLines in the main form frmPO and used fsubPOLines as the source for fraPOLines.  That is why I say I have the subform fsubPOLines sitting in the control fraPOLines of the mainform frmPO.

I did not want to let your questions go unanswered and I hope that answers your questions about what I am doing.  

Thanks again for your help!
Chad
mbizup

Sorry - We've got the Open Form statement in the wrong place - it needs to go before the With statement.

This should get you a little further:

Private Sub CreatePO(strFormName As String, strSubformName As String)


Dim rsSourceLines As Recordset

Set rsSourceLines = CurrentDb.OpenRecordset("SELECT tblSOLines.SOLinesID, tblSOLines.UnitsID, tblSOLines.lngSelectedQuantity, tblSOLines.WarehouseID, tblSOHeader.PVID, tblSOHeader.dtmDate " _
                & "FROM tblSOHeader INNER JOIN tblSOLines ON tblSOHeader.SOHeaderID = tblSOLines.SOHeaderID " _
                & "WHERE (((tblSOLines.lngSelectedQuantity) > 0) And ((tblSOHeader.PVID) = " & Me![cboPVID] & ") And ((tblSOHeader.ManufacturerID) = " & Me![cboManufacturerID] & ") And ((tblSOHeader.SOHeaderID) = " & Me![txtSOHeaderID] & ")) " _
                & "ORDER BY tblSOLines.SOLinesID; ")


DoCmd.OpenForm strFormName

With Forms(strFormName).Controls(strSubformName).Form

        DoCmd.GoToRecord acDataForm, strFormName, acLast
    
  
    Do Until rsSourceLines.EOF
            .Recordset.AddNew
            .cboSOLinesID = rsSourceLines!SOLinesID
            .lngUnitQuantity = rsSourceLines!lngSelectedQuantity
            .UnitsID = rsSourceLines!UnitsID
            .cboSOLinesID_AfterUpdate
            .Dirty = False
            rsSourceLines.MoveNext
    Loop
    
End With

End Sub 

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mbizup

Those naming conventions are a great thing to follow - I use the Reddick VBA standards which are pretty similar.

Frgading the fra prefix - I might have confused you in an earlier thread.  I often refer to subform controls as "Frames that house subforms" to make clear the distinction between the subform control (which looks like a frame) and the source object.  However, a subform control is not quite the same as a "frame object" which gets the fra prefix.  The fra prefix is generally applied to Option Group frames.

(Our organization standard for subform and subform control prefixes is "subfrm" )

Clear as mud?
mbizup

<<
That is why I say I have the subform fsubPOLines sitting in the control fraPOLines of
>>

So I guess with the standards you are using, Id recommend calling it subPOLines instead of fraPOLines.
IrogSinta

Well that clears things up a bit.  I was stuck for awhile trying to figure out how a frame Option Group could have a recordset.  That's why I wanted to at least see a screenshot of what you had.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ChadLittle

ASKER
I've been meaning to write back ever since you wrote.  Thanks for your replies even after the questions was closed.

mbizup, I am glad you pointed out to open the form before the with statement.  To make it work before I had moved the open commands out of the public function which is not as nice, but more importantly you taught me that the With command you wrote does not have to be the first thing written.  I thought it had to be which is why I had removed the open command from the public function - that was a big lesson!

I have been taking all of you advice beyond the specific cases where you taught me something except two - which I intend to do in a later release of the database after people can start using the first release.  The first one is passing variables between functions instead of using public variables.  I have deeply used public variables and do not want to rework them out right now as I know that will generate more questions (how to pass a variable from one function, through another, and to another?).  

The other one is changing fra... to sub... as you suggested. I will adopt what you say as I think it is better, but I want to make what I have work first because of pressure to finish.  Later I will make the change so I will not have to remember this difference when I support this database vs. supporting others that may create later . . . or when people after me support it.  Don't fret though, you did not get me using fra, I did that all by myself by trying to decide what to call that control that holds the subforms while looking at the naming conventions with armature eyes.

By the way, one of the things I do when I am frustrated and need a break is play a guitar badly.  

Chad