Problem with "strLinkCriteria"

[jdegeorge]

Hi

I have a control box with this code in the On Change event:

Private Sub ctlGapNumSelect_Change()

On Error GoTo Err_ctlGapNumSelect_Change

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmGapEdit"
    
    stLinkCriteria = "[Gap#]=" & "'" & Me![ctlGapNumSelect] & "'"
    DoCmd.Close acForm, Me.NAME, acSaveYes
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ctlGapNumSelect_Change:
    Exit Sub

Err_ctlGapNumSelect_Change:
    MsgBox Err.Description
    Resume Exit_ctlGapNumSelect_Change

End Sub

The "frmGapEdit" opens but to the FIRST record on that form's record source, not the value of the control box.

This is the other form's record source:

SELECT tblGaps.* FROM tblGaps WHERE tblGaps.DateClosed Is Null;

The field on the other form that I want to set the filter on is called [Gap#].

I'm positive that I'm missing something REALLY simple (I usually do!). Any ideas?

Jim DeGeorge

 

[PHV]

If Gap# is defined as numeric in tblGaps then get rid of the single quotes in stLinkCriteria.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jdegeorge]

PHV...[Gap#] is TEXT.

Jim DeGeorge

 

[PHV]

Have you tried to debug your code, like
MsgBox stLinkCriteria
before closing your form, just to be sure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jdegeorge]

Yep...compiled without errors. Should I expect the 2nd form's filter to be populated?

Jim DeGeorge

 

[jdegeorge]

Instead of the above approach, I'm trying to hide the selection form after a value is selected in the combo box and then opening the 2nd form with a FIND RECORD command that points back to the value in the hidden form

DoCmd.FindRecord Me![ctlGap#] = [Forms]![frmGapEditSelection]![ctlGapNumSelect]

The 2nd form's ON LOAD property has this code as the record source:

Me.RecordSource = "SELECT tblGaps.* FROM tblGaps WHERE tblGaps.DateClosed Is Null;"

Only problem is that it's not working either. ;-)

The FIND RECORD code follows the RECORD SOURCE code.

Is this an easier approach or should I revert to my original approach?

Jim DeGeorge