Filter not triggering on Form Open!

[JeffBarker]

Hi guys,

Well, another week and another new problem from me!!

As anyone who regularly views my posts will know, I'm dealing with a lot of 'inherited' databases that were created by my predecessor who, sometimes I feel, probably had less of an idea of what they were doing than me (although this definitely isn't the case)!

Again, the usual problem is that we have gotten in to a routine of copying, pasting and bastardizing old databases instead of creating new ones from scratch - and we've had a problem recently where we've started off with one master database back in 2011, which has then been the base for practically every single major DB we've created ever since.

So this month we've copied and pasted (again) and started adding new features to what's already there, and the thing has corrupted. I've copied and pasted the same version three times and added all the new bits, and the same thing has happened every time.

So this time I've copied and pasted and tried my best to clean up, get rid of the dead weight and (where necessary) create completely new objects, split into FE and BE versions etc, and I've reduced the overall weight of the DB by about 50%.

Due to time constraints I now need to crack on and get this thing working again and, for the most part, it does - but now I'm having trouble carrying the filter over from the OnClick Event of a form button to the next form it's opening.

We're using the DB to record attendees at an Event we're running later in the year.

This really is a big cry for help, and I'm hoping somebody has the time to help me strip this code back and only use what is necessary - as it's all beginning to get a bit much...I've literally spent a day and a half on this thing trying to sort it out, and I feel I'm almost there but am at my wits end!!!

This is the code that I'm running from the OnClick Event of the button on Form1:

Private Sub btnBkg_Click()
On Error GoTo Err_btnBkg_Click

    Dim stDocName, vFilt As String
    Dim vBkgRef As Long
    Dim vContactID As Long
    
    If Dirty Then Dirty = False 'save record
    
    stDocName = "frmBooking"
    vFilt = "[txtBookingContactID] = " & Me.[Contact_ID] & " AND [txtBkgRef] = " & Me.BkgRef
    vContactID = Me.[Contact_ID]
    
    If btnBkg.Caption = "Add Bkg" Then
        vBkgRef = Me.BkgRef
        DoCmd.OpenForm stDocName, , , , acFormAdd, , vContactID
    Else
'        vBkgRef = BkgRef
        DoCmd.OpenForm stDocName, , , "[txtBookingContactID] = " & Me.[Contact_ID] & " AND [txtBkgRef] = " & Me.BkgRef
    End If

Exit_btnBkg_Click:
    Exit Sub

Err_btnBkg_Click:
    MsgBox Err.Description
    Resume Exit_btnBkg_Click
    
End Sub

You can see I've tried to use vFilt instead of the actual code for frmBooking's Where Condition, but for some reason it does not carry over to the form whichever way I try - when I open the immediate window and type ?vFilt it returns a blank entry. Not sure how to show the 'Where Condition' in the Immediate Window??

There is a HELL of a lot of code that happens in the Current, Before Update, Open and Activate of frmBooking, but I'm hoping that the problem lies with what I've posted up here, as I don't want to get into posting the frmBooking code...

So, fingers crossed one of you guys can help me please!!

NB: It may be worth noting that the button resides in the Header of a continuous form, with conditional formatting that changes the button caption depending on whether there is a value in the BkgRef text box of the record that has the focus.

 

[Ranman256]

In immediate window, you must give the full path of a form object

? forms!frmBooking!txtBox1

 

[JeffBarker]

In immediate window, you must give the full path of a form object

? forms!frmBooking!txtBox1

Thanks for the reply Ranman, can you explain how to apply that logic to this problem please?

To my understanding, this Where Condition is a criteria that we're using to open a form, and doesn't relate to a form object in this instance?

Apologies if I'm misunderstanding your suggestion!

 

[Ranman256]

you are opening the form filtered.
(hopefully your query pulls * ,everything) so it CAN be filtered.

tDocName = "frmBooking"
vFilt = "[txtBookingContactID] = " & Me.[Contact_ID] & " AND [txtBkgRef] = " & Me.BkgRef
vContactID = Me.[Contact_ID]

This is fine, if your form has the data there to filter.

My note was, if you want to see the values from the immediate window, then you must use the full path of the object... forms!frmBooking!control