Renaming a Form Stopped my VBA from Executing (1 Viewer)

[pitt_ph]

Hey all,

Recently, I renamed my forms and my tables to have numbers in front of them (mostly to establish a temporal order in my forms). I had a code which worked when the form names didn't have numbers in front of them... However, upon adding numbers to the form names (and trying to update the VBA respectively), the VBA no longer executes.

The code I was running was:

If Forms![13-frm_Transportation].Transport_DryGoodsCollectedAtSameTime.Value = "0" Then
    CODE HERE
Else
    Forms![13-frm_Transportation].Transport_DryGoodsCollectedAtSameTime.Value = "1"
    CODE HERE
End If

End Sub

From this code, I got the following error:

Run-time error '2450'

Microsoft Office Access can't find the form '13-frm_Transportation' referred to in a macro expression or Visual Basic code.

I've been running into this type of problem with all of my forms -- VBA code which previously worked, but referred to other forms in some manner (include opening said forms), no longer work when numbers are added into the form names!

I've got to ask: does Access not like people to put numbers in their forms? What is the correct syntax for referencing to forms in this manner?

 

[boblarson]

First off, it isn't the numbers most likely. Most likely it is the dash. It is best to not use special characters or spaces when naming things. The one exception is the underscore, but I like CamelCase instead as it helps keep it shorter.

 

[pitt_ph]

Hm, that's odd. I usually write my form names in CamelCase, so that shouldn't have been the problem. I switched all the dashes to underscores, and I still was getting errors along the lines of, "Compile error: Expected: Then or GoTo" -- even when I had Then in the code!

The code I have now is:

If Forms!13_frm_Transportation.Transport_DryGoodsCollectedAtSameTime.Value = "0" Then
    [CODE HERE]
Else
    Forms!13_frm_Transportation.Transport_DryGoodsCollectedAtSameTime.Value = "1"
    [CODE HERE]
    
End If

End Sub

 

[boblarson]

Make sure that you haven't accidentally misspelled the form name (I've seen it before where the name was changed but instead of what was thought it was named to, two letters were reversed, etc.

 

[pitt_ph]

Haha, I thought about that.

I actually copied the form names directly from the form name (aka, I right-clicked on the form name in the navigation panel, clicked "rename" and copied the string from that, without renaming the actual form) when writing the form, so I don't think that's the case...

 

[boblarson]

Okay, I'm out of ideas currently. Can you upload a copy of the database (with fake data)? If you do, remember to run Compact and Repair first and then zip the file (you can right-click on it and select SEND TO > COMPRESSED FOLDER.

Also, be sure to note in your post where the code is, etc. so we don't have to try and figure it out.

 

[pitt_ph]

Hm. If I decide to upload the database, it will mean I can't get to uploading it until tomorrow. Oops, sorry about that!

On a different note, do you think this has anything to do with openargs? The code that I've been copying has also been trying to pass the parameter "Transport_DryGoodsCollectedAtSameTime" in the loop as an openargs. Upon going through and checking each of my parameters, I've found that it's been getting recorded in the recipient form as "Null", so it could be the way that the openargs are being passed. Thoughts on this?

 

[boblarson]

It could be. Hard to tell though. Something isn't right if you are passing OpenArgs and it is null.

 

[pitt_ph]

OpenArgs, you fiend! =P

(Seriously though, half my posts have been about openargs. I'm so sorry about that, retroactively.)

 

[pitt_ph]

It could be. Hard to tell though. Something isn't right if you are passing OpenArgs and it is null.

Alright, I looked at the code of my database later last night. Apparently, I was right about the OpenArgs (that they weren't being passed properly). They were being mentioned in the DoCmd.OpenForm event of each of the buttons, but they were NOT being loaded in the Form_Load() event of the recipient form. (That was an oversight on my part.)

However, I'm still not sure if I did the passing correctly, because even when I loaded the events in the Form_Load() event of the recipient form, the OpenArgs were still coming out as Null. For example, here's the OpenForm bit of my button code (translated from the VBA converted from embedded macros) -- note that I commented out the things that previously pertained to the passing of OpenArgs:

On Error GoTo btn_GoTo_AddData_Click_Err

'    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close , ""
'    DoCmd.OpenForm "data_ENTRY_switchboard", acNormal, "", "", , acWindowNormal, "Transport_DryGoodsCollectedAtSameTime"
    DoCmd.OpenForm "data_ENTRY_switchboard", acNormal, "", "", , acNormal
    Forms!data_Entry_switchboard.btn_GoTo_VehicleTypes.Enabled = True

'    If Not IsNull(Me.OpenArgs) Then
'        Transport_DryGoodsCollectedAtSameTime = Me.OpenArgs
'    End If

In the recipient form, I had used (and subsequently commented out):

'Private Sub Form_Load()

'Dim Transport_DryGoodsCollectedAtSameTime As String

'Transport_DryGoodsCollectedAtSameTime = Me.OpenArgs

'End Sub

After further consideration, I don't think I'm going to use OpenArgs after all -- it's turning out to be too much of a headache to implement, and it's just a way to programmatically shut off controls so there isn't user error when the form is being implemented. It's not necessary, so I don't need it right now. (Learning it for the long run, however, could potentially be helpful.)

 

[boblarson]

Okay, so a few things to help you out.

When not including values in Optional Parameters you don't use "" but you just leave it blank. So this:

DoCmd.OpenForm "data_ENTRY_switchboard", acNormal, "", "", , acNormal

would instead be written like

DoCmd.OpenForm "data_ENTRY_switchboard", acNormal, , , , acNormal

But you can also just use what you need:

DoCmd.OpenForm "data_ENTRY_switchboard", acNormal, WindowMode:=acNormal, OpenArgs:="Transport_DryGoodsCollectedAtSameTime"

The key is using the name of the parameter with the colon and equals sign := like that.

As for the form load, I'm not quite sure why you would have set the string to the same exact name as the string name.

 

[pitt_ph]

Hah, the reason why there are quotation marks is that the code I gave for the DoCmd.OpenForm event was converted in Access 2007 directly from an embedded macro.

As for the Form_Load() event, should I simply make some type of intermediate variable that isn't named the same way as the variable that was being passed in OpenArgs? I feel like that'd be confusing if I keep on passing the variable between a switchboard and multiple forms (i.e., the variable is being passed from form 13 to the switchboard, to form 14, to the switchboard, and is finally being used in form 15).

 

[boblarson]

I feel like that'd be confusing if I keep on passing the variable between a switchboard and multiple forms (i.e., the variable is being passed from form 13 to the switchboard, to form 14, to the switchboard, and is finally being used in form 15).

Why not just create a global variable and set it in form 13 and use it in form 15?

 

[pitt_ph]

Hey boblarson and AccessProgrammers.Co.UK Folks,

Sorry about the delay in my response -- it's nearly been a month since you last heard from me! It turned out that I didn't have the time to strip my database of the non-pertinent information. Luckily, I picked out the majority of the kinks from the database -- so I'm just working on fixing up the global variables right now.

(I originally tried playing around with OpenArgs, but it didn't work because it would've meant daisy-chaining the openargs between multiple forms, as well as initializing the openargs on a switchboard, which was erroring out far more than I'd have liked.)

So I understand how to set a global variable right now, but I have no flippin' idea how to use them, especially in the context of particular fields, and how to reference them between forms.

What I have so far in the general declarations portion of Form 13 is the following:

Option Compare Database

Dim globalSameTrip As String

Option Explicit

For the code that controls the combo boxes --

If Me.Transport_DryGoodsCollectedAtSameTime.Column(1) = 0 Then
    [code here]
ElseIf Me.Transport_DryGoodsCollectedAtSameTime.Column(1) = 1 Then
    [code here]
ElseIf Me.Transport_DryGoodsCollectedAtSameTime.Column(1) = "" Then
    [code here]
End If

'If I played this correctly, then this should be used as expected.
Me.Transport_DryGoodsCollectedAtSameTime = globalSameTrip

After that, however, I don't know how to reference "globalSameTrip" in Form 15! Based on searches I've been doing on the internet. I'm guessing that I'd write in Form 15 something like (making this up off the top of my head):

Public Function Init_Globals()
globalSameTrip = {SOMETHING}
End Function

Private Function Init_ProgramShutOff
Get_Global = globalSameTrip
End Function

I'm not sure if this approach is even right, or what else I should do so I can make the value of the field in "Transport_DryGoodsCollectedAtSameTime" in Form 13 affect the programmatic shut-off (which I know is a whole bunch of ".Enabled") of the fields in Form 15. Any ideas?

 

[boblarson]

Okay, your declaration of the global needs to be:

Public globalSameTrip As String

not DIM.

 

[pitt_ph]

Ooh, good point, forgot about that. Any other tips?