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
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.