Passing and Processing Multiple Values through OpenArgs

[jdb1229]

I am completely unfamiliar with access coding until the middle of last year. I am still not very adept but I have been working on a database for the owner of my company that has gotten much more in depth than anything I have done in access before. I have a main data form called frmDatabaseInfo rmation and other forms that are connect various ways. My example for this question will be my frmAdditionalCo ntacts. These two forms are connect by a 1 to many relationship from the CompanyID fields in each. 1 record in frmDatabaseInfo rmation and possible Many fields in frmAdditionalCo ntacts. I had successfully found code that allowed me to pass the companyID from frmDatabase Information when I clicked a button through the openargs to frmAdditionalCo ntacts. This was imperative so that when a user created a new contact the company id the connected them was copied over. The problem is that I also need to bring over the data from a field called No Work. This is a check box field that when checked in frmDatabaseInfo rmation has code to keep the user from editing that specific record. I need to copy that over to the frmAdditionalCo ntacts for the same reason. I have tried a few ways to transfer the information via openargs but nothing has been successful. I did create a No Work field in the frmAdditionalCo ntacts that is a check box in case that is necessary for me to use the data in the new form.

The following is the code that was working to pass the CompanyID. First the code in the on click function on frmDatabaseInfo rmation:

Private Sub CmdOpenContacts_Click()
If Me.CompanyID > 0 Then
    DoCmd.OpenForm "FrmAdditionalContacts", acNormal, , "CompanyID =" & CompanyID, acFormEdit, acWindowNormal, Me.CompanyID
    DoCmd.OpenForm "FrmDatabaseInformation", acNormal, , , acFormAdd
End If

End Sub

Next the code in the onclick function in frmAdditionalCo ntacts (If I left it in the onopen event it changed current contacts and I didn't want that)

Private Sub CmdNewRecord_Click()
Dim strCompanyID As String

strCompanyID = Nz(Form_FrmAdditionalContacts.OpenArgs)

Me.AllowAdditions = True



    If Len(strCompanyID) > 0 Then
    DoCmd.GoToRecord , , acNewRec
    Me.CompanyID.Value = strCompanyID
    End If
    

End Sub

Thank you so much for any help you may be able to give me!

[Seth Schrock]

I have done this a few times. Normally what I do is to create a list of values separated by a character that can't ever be found in the data that I want. This is simple when you are passing only numbers. I use the semicolon (;) a lot. So you would build your OpenArgs string like this (you will need to change the name of your No_work control to match yours):

Me.CompanyID & ";" & Me.No_Work

Based on my experiences of trouble using OpenArgs, my recommendation is to create module level variables (declared above all event procedures) and then use the On_Open event to get the values from the OpenArgs to those variables. You can then use your Click event to use the values in the variables whenever you want. So in the On_Open event of your FrmAdditionalCo ntacts form you will use the Split function to get the individual values from your OpenArgs.

'Module level variables
Dim lngCompanyID As Long
Dim blnNoWork As Boolean

'Inside On_Open event
Dim strParts() As String

strParts=Split(Me.OpenArgs, ";")
lngCompanyID = strParts(0)
blnNoWork = strParts(1)

[jdb1229]

So far I think I am following you. I have a module created (I am not sure what you meant by "module level variables" but I created it while the code for my Contacts form was opened. I copied exactly what you have written.

I also copied what you noted in my on open event and so far I have not had any issues with that. (I had an issue when I tried to split my openargs with a pipe "|" symbol before). My only real question is how do I use them now? For example, when I hope the documents I need it to see if the No Work box was checked, if it was I need some code to run to keep the user from editing the information. I put this on the On Current Event and it said the strParts(1) was out of range. Thank you so much for your help so far!!

 Dim strParts() As String

If strParts(1) = True Then
    Me.CmdNewRecord.Visible = False
End If

[Seth Schrock]

Read this link for more information about module level variables. Basically, it just means a variable defined before any procedures.

If you have just exactly what you have posted in your On_Current event, then you never populated the strParts() array, thus your out of range error.

Above the declaration of the On_current event, you should have at least the following text Option Compare Database. Hopefully you also have Option Explicit. When you are viewing your On_Current event, you are in a module (a class module to be exact, but the difference doesn't matter for this discussion). Here is an example of some code in one of my forms:

Option Compare Database
Option Explicit

Private Sub Form_Current()
If Me!DisplayDetails = True Then
    Me.Parent.sfrmInvoiceItemsDetailDisplay.Visible = True
    Me.Parent.sfrmInvoiceItemsDetailDisplay.Requery
Else
    Me.Parent.sfrmInvoiceItemsDetailDisplay.Visible = False
End If

End Sub

To put module level variables, it would look like the following:

Option Compare Database
Option Explicit

Dim lngCompanyID As Long
Dim blnNoWork As Boolean

Private Sub Form_Current()
If Me!DisplayDetails = True Then
    Me.Parent.sfrmInvoiceItemsDetailDisplay.Visible = True
    Me.Parent.sfrmInvoiceItemsDetailDisplay.Requery
Else
    Me.Parent.sfrmInvoiceItemsDetailDisplay.Visible = False
End If

End Sub

Notice lines 4 and 5. Those are module level variables whose values are available within any procedure in the module. Here is an example of what your module could look like:

Option Compare Database
Option Explicit

Dim lngCompanyID As Long
Dim blnNoWork As Boolean

Private Sub Form_Current()

If blnNoWork = True Then
    Me.CmdNewRecord.Visible = False
End If

End Sub

Private Sub Form_Open(Cancel As Integer)
Dim strParts() As String
 
strParts = Split(Me.OpenArgs, ";")
lngCompanyID = strParts(0)
blnNoWork = strParts(1)

End Sub

The On_Open event runs before the On_Current event and splits out the open args and places the values in the module level variables. Then the On_Current event uses the values in the module level variables to perform its functions.

[jdb1229]

Seth, sir, you are a life saver! I apologize you had to do all of that work for me but I really appreciate your help. The information you provided has been extremely helpful! I do have one other question but I have not used Bytes.com before so I don't know if I can ask it here or if I should start a new post. It is completely unrelated to this topic so I would guess new post.

Thank you again!!

[Seth Schrock]

Glad that I could be a help. I would recommend reading the site FAQ as well as How to ask good questions.

And you are correct about posting your other question in a new post.

Good luck on your project.