Replacing Column Name in Query with VBA Parameter MS Access-CodePudding

I am trying to open form X through form Y, where docid = docid form X's table has a primary key column named "abcid" (the matching id) I am trying to pass the label name of form Y's textbox's label "abcid" as string "abcid", the idea is to pass the column name dynamically whatever the used form is ==> in VBA OpenForm WHERE condition, but it doesn't execute properly or opens a blank form. code below:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim checklistformName As String
Dim viewdocformName As String

Dim currentformName As String
currentformName = Screen.ActiveForm.Name
'Debug.Print currentformName

Dim docidcolumnName As String
docidcolumnName = Forms(currentformName).Controls(docid).Caption
Debug.Print docidcolumnName

Set rs1 = CurrentDb.OpenRecordset("SELECT DISTINCT checklistformname FROM CyclesDefinitions WHERE cycledefid = " & Forms(currentformName)![cycledefid])

Do Until rs1.EOF = True
    checklistformName = rs1(0)
    rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing

Set rs2 = CurrentDb.OpenRecordset("SELECT DISTINCT viewdocformname FROM CyclesDefinitions WHERE cycledefid = " & Forms(currentformName)![cycledefid])

Do Until rs2.EOF = True
    viewdocformName = rs2(0)
    rs2.MoveNext
Loop
rs2.Close
Set rs2 = Nothing
    
Debug.Print currentformName & " - " & checklistformName & " - " & viewdocformName
DoCmd.OpenForm viewdocformName, , , " & docidcolumnname & " = " & Forms(checklistformName)![docid], acFormReadOnly

CodePudding user response：

alright, I solved the OpenForm with string dilemma with the following solution after testing a statically predefined string:

DoCmd.OpenForm viewdocformName, , , "" & docidcolumnName & " = " & Forms(checklistformName)![docid], acFormReadOnly

it just needed the proper concatenation, and instead of messing with the forms!frmname syntax, because I can't find the proper return value, I set the label name on form to pkcolname and recalled it in VBA like this:

docidcolumnName = Me.pkcolname.Caption:

now everything is working as expected and required.