Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    NonProfitDB is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    8

    How to pass variable value from one form to another

    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,
    Last edited by NonProfitDB; 04-12-2011 at 01:09 PM.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Assuming the control has the same name try using:
    DoCmd.OpenForm "frmCanvassing Activities", , , , , , Me.ProgramID

  3. #3
    NonProfitDB is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    8
    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.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What runtime error?

  5. #5
    NonProfitDB is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    8
    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.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    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?

  7. #7
    NonProfitDB is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    8
    Quote Originally Posted by RuralGuy View Post
    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.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Maybe you had not seen this link yet: http://www.baldyweb.com/OpenArgs.htm

  9. #9
    NonProfitDB is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    8
    Quote Originally Posted by RuralGuy View Post
    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.

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm sorry but I do not understand your question.

  11. #11
    NonProfitDB is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    8
    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.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If the field is not part of your RecordSource then you will need to use an Update Query.

  13. #13
    NonProfitDB is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    8
    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.

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That's great! Are you ready to use the Thread Tools and mark this thread as Solved yet?

  15. #15
    NonProfitDB is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    8
    Yes, there is another issue, but not related to this. Solved.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Refering to variable form names inside a variable
    By redpetfran in forum Programming
    Replies: 2
    Last Post: 05-21-2010, 01:39 PM
  2. Passing variable values to Stored Procedure
    By rodrigopcnet in forum Access
    Replies: 1
    Last Post: 04-14-2010, 10:35 AM
  3. Passing SQL result into variable
    By jonny in forum Access
    Replies: 3
    Last Post: 10-18-2009, 07:46 AM
  4. Passing a variable to a form
    By cjamps in forum Forms
    Replies: 0
    Last Post: 03-02-2009, 05:32 AM
  5. Passing a value from a variable to an update query
    By MUKUDU99 in forum Programming
    Replies: 0
    Last Post: 08-24-2008, 11:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums