OpenArgs to display record in new form

Question

I'm using a cmd button to open a popup (single) form that has (2) txtboxes that I want to reflect the information from a single record in the previous (continuous) form. The cmd will

Code on the cmd button:

Private Sub cmdReassign_Click()
On Error GoTo ErrHandler
Dim strOpenArgs As String

strOpenArgs = Me.txtToolGroupID & "," & Me.txtEmployee_Name
DoCmd.OpenForm "popfrmReassignGroupedTools", OpenArgs:=strOpenArgs '

ExitHere:
    Exit Sub

ErrHandler:
    MsgBox Err.Number & ": " & Err.Description, vbExclamation
    Resume ExitHere

End Sub

Code on Form_Open

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrHandler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strOpenArgs As String

    strOpenArgs = Me.OpenArgs

    Set dbs = CurrentDb
    strSQL = "SELECT * From qryToolReassignment_Grouped Where ToolGroupID=" & Me.txtToolGroupID & ";"
    Set rst = dbs.OpenRecordset(strSQL)

    If rst.EOF Then
        GoTo ExitHere
    End If

    With Me
        .txtToolCategoryQty = rst.Fields("[Quantity]")
        .txtToolLocation = rst.Fields("[Employee Name]")
    End With

ExitHere:
    On Error Resume Next
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Number & ": " & Err.Description, vbExclamation
    Resume ExitHere

End Sub

I'll admit I borrowed the code from a similar setup where the information was called from a cbo not a cmd. When the popup form opens, only the first record in the query is shown not the record associated to the cmd. Any suggestions. TIA.

In your Form_Open methods you never use strOpenArgs to do anything. — Mathieu Pagé, Apr 28 '17 at 16:20
I know. I can't figure out the correct syntax to call the openargs. — JWC14, Apr 28 '17 at 16:44

June7 · Answer 1 · 2017-04-28T18:26:35.653

0

Not clear to me what you want. If popup form is BOUND and you want it to open to existing record then use the WHERE argument of OpenForm:

DoCmd.OpenForm "popfrmReassignGroupedTools", , , "ToolGroupID=" & Me.txtToolGroupID

If txtToolGroupID is on the continuous form then the popup form Open event cannot reference it with Me alias.

If you want to pass multiple values with OpenArgs, will have to use string manipulation functions to parse the data elements.

If Not IsNull(Me.OpenArgs) Then
    intID = Left(Me.OpenArgs, InStr(Me.OpenArgs, ",")-1)
    strEmp = Mid(Me.OpenArgs, InStr(Me.OpenArgs, ",")+1)
End If

Instead of all that code to declare and open recordset object, a DLookup() might serve.

edited Apr 28 '17 at 18:26

answered Apr 28 '17 at 18:21

June7

19,874
8
24
34

Sorry for the confusion. There is (1) cmd located next to each record on the continuous form. Clicking on that will open the popup. On open, the popup will prefill 2 txt (txtToolCategoryQty and txtToolLocation) with the fields Quantity and Employee Name from the record line the cmd is located on from qryToolReassignment_Grouped which is the record source for the original form. Does that make better sense? – JWC14 Apr 28 '17 at 19:12
Answer still applies. Your narrative shows passing id and name data in OpenArgs, not quantity. Adjust suggested code as required. However, if data already exists in record on continuous form, why are you copying it? Are you duplicating data across tables? – June7 Apr 28 '17 at 19:22
Not at all. The popup is used to for the reassignment of an item. It reads "There are currently ____ items located at ______. You are now assigning _____ of those items to _______ location." The first two txt are what will be filled on open and will be locked. The following txt and cbo are for the end user to complete. – JWC14 Apr 28 '17 at 19:50
Okay, did you try suggestion? Parse values from OpenArgs and use wherever. Pull data from table by recordset or DLookup() and use wherever. – June7 Apr 28 '17 at 20:04
I've tried manipulating it a couple of different ways and keep getting "Invaid Use of Null" – JWC14 Apr 28 '17 at 20:46
Error on what line of code? Have to post your attempted code for analysis. The suggested code works for me. My suggestion assumes that if OpenArgs has value it will have both data elements shown in your example. If one of those input fields is null, the string functions could error. Edit your question. – June7 Apr 28 '17 at 20:54
Set dbs = CurrentDb strSQL = "SELECT * From qryToolReassignment_Grouped Where ToolGroupID=" & Me.txtToolGroupID & ";" Set rst = dbs.OpenRecordset(strSQL) strQty = txtToolCategoryQty strLocation = txtToolLocation If Not IsNull(Me.OpenArgs) Then strQty = Left(Me.OpenArgs, InStr(Me.OpenArgs, ",") - 1) strLocation = Mid(Me.OpenArgs, InStr(Me.OpenArgs, ",") + 1) End If – JWC14 Apr 28 '17 at 21:32
Why would you set those variables by reference to something (controls?) and then set them again from OpenArgs? I thought OpenArgs were passing ID and Name, not Quantity and Location??? This discussion is getting too long. I think you have the info needed to accomplish what you want, just apply logic. – June7 Apr 28 '17 at 21:57

OpenArgs to display record in new form

1 Answers1