popup form gives "enter parameter value"

[pbuethe]

I have a continuous subform with a button that is supposed to open another continuous form of related records. I have done this in the past with help from the forum and tried to follow the same method. However this time, it does not work. I have the following:

The record source of both forms includes: CaseID, PageSeq, ColumnNo, and arvEvent which are all needed to identify each record. Each case can have multiple pages, each page has four columns (not all of which may have data), each column can have multiple events. Some of the events have one or more modifiers which should be displayed by the second form. I have the following code to open the second form:

Private Sub cmdModifiers_Click()

  On Error GoTo Err_cmdModifiers_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmARVModifiers"
    stLinkCriteria = "[CaseID] = '" & Me.[CaseID] & "' AND [PageSeq] = '" & Me.[PageSeq] & "' AND [ColumnNo] = '" & Me.[ColumnNo] & "' AND [arvEvent] = " & Me.[arvEvent]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdModifiers_Click:
    Exit Sub

Err_cmdModifiers_Click:
    MsgBox Err.Description
    Resume Exit_cmdModifiers_Click
    
End Sub

Also, on the BeforeInsert of frmARVModifiers, I have:

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.CaseID = Forms!frm50973!frmARVModifiers.Form!CaseID
Me.PageSeq = Forms!frm50973!frmARVModifiers.Form!PageSeq
Me.ColumnNo = Forms!frm50973!frmARVModifiers.Form!ColumnNo
Me.arvEvent = Forms!frm50973!frmARVModifiers.Form!arvEvent

End Sub

When I click the button, I get a dialog box saying "Enter Parameter Value" but instead of a variable name it shows the value of the current Event.

I appreciate your help.

 

[pbuethe]

I decided to post some more detail in case this was not clear. My structure, which captures the data on scanned paper forms, is as follows:

tblARVMaster
CaseID - e.g.ADPT0000696 - the case ID is unique in this table only.
FormID - e.g.50973 - this keeps track of the version of the data collection form that was used for each case.

tblARV
CaseID
arvDate
arvEvent - e.g. VT, VL, CD4, Adh, Tx - there can be multiple events in each column of a page
PageSeq - e.g. 00000299-4, 00000269-647 - there may be multiple pages for the same case (I think there are up to 6 pages per case).
ColumnNo - 1, 2, 3, or 4 - there are physically 4 columns on a page. - a column represents all the events on a single date.
<some other fields>

tlkpARVEventsMod
CaseID
arvDate
arvEvent
arvMod - e.g. RE, Und - these are "modifiers" associated with the event. One event can have multiple modifiers. However the only events that may have modifiers are VL and CD4.
PageSeq
ColumnNo

I have forms as follows:

frm50973 - corresponding to the original version of the data collection form, it is based on qryARVMaster which contains CaseID and FormID from tblARVMaster.
This has a subform, frmARVEventsPage, a single form linked by CaseID, based on qryARVEventsPage which contains CaseID and PageSeq from tblARV.
frmARVEventsPage in turn has a subform, frmARVEventsCol, a continuous form linked by CaseID and PageSeq, based on qryARVEvents containing the fields from tblARV.
frmARVEventsCol has a command button which is supposed to open the modifiers form, frmARVModifiers, a continuous form (or datasheet) which is based on the table tlkpARVEventsMod. frmARVModifiers should display the modifiers (arvMod) which correspond to the event (arvEvent) on the current line in frmARVEventsCol. Instead a box pops up titled "Enter parameter value" and if the event on the current line was, for example, "VL", it displays "VL" over an input box.

I hope this explains the setup sufficiently so someone can help me. I am open to any suggestions except that I may not change the structure of the tables.

Thanks.

 

[pbuethe]

OK, I solved this myself. I changed the criteria to:

stLinkCriteria = "[CaseID] = " & QUOTE & Me.[CaseID] & QUOTE & "AND [PageSeq] = " & QUOTE & Me.[PageSeq] & QUOTE & "AND [ColumnNo] = " & Me.[ColumnNo] & "AND [arvEvent] = " & QUOTE & Me.[arvEvent] & QUOTE

(QUOTE is Pat Hartman's global constant)
Then clicking the button produced "Adult QOC Reviews can't find the field 'cboEvent' referred to in your expression"
When I clicked 'OK', frmARVModifiers came up but appeared blank.

The message was coming from the following code in frmARVModifiers:

Private Sub cboMod_Enter()
On Error GoTo Err_ModEnter


Select Case Forms!frm50973!cboEvent
    Case "CD4"
        cboMod.RowSource = "qryModCD4"
    Case "Pdoc"
        cboMod.RowSource = "qryModPdoc"
    Case "VL"
        cboMod.RowSource = "qryModVL"
    Case Else
        cboMod.RowSource = ""
End Select

Exit_ModEnter:
    Exit Sub

Err_ModEnter:
    MsgBox Err.Description
    Resume Exit_ModEnter
End Sub

I then changed this code from:
Select Case Forms!frm50973!cboEvent
to
Select Case Me!arvEvent

I made some other changes in frmARVModifiers, and then everything was OK.