How to pass variable value from one form to another

[NonProfitDB]

Hi,

I'm having a bit of an issue (confusion) about passing variables from one from to another. I'm worked out the code but something isn't quite right.

The user will select the Issues form, fill out the information requested and click an activity that is associated with the issue. I am grabbing the PK from the issue form (that field is hidden) and passing it to the FK field in the activity form (FK also hidden). That works fine. When the user clicks the appropriate activity button, the code below is run:

Option Compare Database

Private Sub CanvassingCommand_Click()
DoCmd.OpenForm "frmCanvassing Activities", acNormal, , , , acWindowNormal, [ProgramID]
End Sub

When the form loads, the On Load event runs the code below:

Option Compare Database



Private Sub Form_Load()
Dim i As Integer
i = CInt(Me.OpenArgs)
Me.ProgramID.Value = i
End Sub

This works just fine. The problem is when they return to the Issues form, fill out a new issue and then click the activity, let's say the same one for a new issue, the form returns the last issue and not a new data entry. So, how would I reset the variable to get the new PK, etc. (Providing I'm on the right track)

Thanks,

[RuralGuy]

Assuming the control has the same name try using:
DoCmd.OpenForm "frmCanvassing Activities", , , , , , Me.ProgramID

[NonProfitDB]

Hi, thanks. I tried that and I receive a run-time error. Not sure if its because the variable is private or not. I'm stumped.

[RuralGuy]

What runtime error?

[NonProfitDB]

Hey Rural,

Sorry for the delay in responding, but I figured out what I wanted to do was wrong. I was trying to pass multiple vars with that code. I deleted that code and replaced with something that passes multiple vars and its working now. Thanks for responding.

[RuralGuy]

Glad to hear you got it working. Do you want to share the code with others that read this forum? Do you want to use the Thread Tools and mark this thread as Solved?

[NonProfitDB]

Glad to hear you got it working. Do you want to share the code with others that read this forum? Do you want to use the Thread Tools and mark this thread as Solved?

Here's what I found that worked.

I found, after digging deep this code to get multiple vars and created this function globally.

Public Function GetTagFromArg(ByVal OpenArgs As String, _
ByVal Tag As String) As String
Dim strArgument() As String
strArgument = Split(OpenArgs, ":")
Dim i As Integer
For i = 0 To UBound(strArgument)
If InStr(strArgument(i), Tag) And _
InStr(strArgument(i), "=") > 0 Then
GetTagFromArg = Mid$(strArgument(i), _
InStr(strArgument(i), "=") + 1)
Exit Function
End If
Next
GetTagFromArg = ""
End Function

Then, I created this on loading the form:

Private Sub Form_Load()
Dim iid As String
Dim sid As String


sid = (GetTagFromArg(Me.OpenArgs, "StateID"))
iid = (GetTagFromArg(Me.OpenArgs, "IssueID"))

' Passing vars
Me.StateID.Value = CInt(sid)
Me.StateID_tblPartnerToActivity.Value = CInt(sid)
Me.IssueID.Value = CInt(iid)
Me.IssueID_tblPartnerToActivity.Value = CInt(iid)



'When this form loads, check the canvassing checkbox.
Me.Canvassing = True

End Sub

This works just fine with one catch. As you see above, for example, I am passing StateID to another table. To do that, I placed those fields on the canvass form. The catch is there is another field, ActivityID, that is created when the form loads that I need to pass to the PartnerToActivity table but not having the same luck. I assumed I could pass that without declaring it.

[RuralGuy]

Maybe you had not seen this link yet: http://www.baldyweb.com/OpenArgs.htm

[NonProfitDB]

Maybe you had not seen this link yet: http://www.baldyweb.com/OpenArgs.htm

No I hadn't, thanks. It has other info that I know I'll need. But, I don't see how, given the code I put in place, to add the key from the form that I'm sitting on to the other field that's from another table.

[RuralGuy]

I'm sorry but I do not understand your question.

[NonProfitDB]

Sorry about that. Aside from the code that I explained, that is working just fine. Here's what's not:

The form frmIssue is open and the user selects an issue from a lookup list (required) and other fields, then selects an activity associated with that issue. Could be one of 11 issues. We'll select Canvassing.

Information in frmCanvassing_Activities is completed and saved. What has to happen then is the PK's from the activities table are placed in the PartnerToActivity table. (This table forms a link between partners and activities). All PK's are getting into this table except the ActivityID.

I've tried several methods of doing that with no luck. The last attempt was to place the control ActivityID_tblPartnerToActivity on frmCanvassing_Activities and grab it that way. I can grab the key doing it that way but it will not commit to tblPartnerToActivity.

[RuralGuy]

If the field is not part of your RecordSource then you will need to use an Update Query.

[NonProfitDB]

I kept at it and here's what works:

Dim aid As Integer

aid = [ActivityID]

[ActivityID_tblPartnerToActivity] = [ActivityID]

Don't know how I missed that on the first try. I think you telling me about the UpDate Query jogged a good brain cell.

Thanks loads RuralGuy, you really came through for me and I appreciate it.

[RuralGuy]

That's great! Are you ready to use the Thread Tools and mark this thread as Solved yet?

[NonProfitDB]

Yes, there is another issue, but not related to this. Solved.