Toggle Button Stuck (New Record rather than specified filter.)

[Xonos]

Access 2010 64bit



Hello everyone, long-time reader of the forums - first time poster. I have an issue with the Access "Toggle Filter Button" being ticked whenever I open a form from a listbox (dbl click). Let me give you a bit of information regarding my setup...

[tblOrders] - (Key) [ID] - [Date] - [Consignee] (Text) - [OrderComplete] (Yes/No)
[tblPurchaseOrders] - (Key) [ID] - (Key) [AssignedID] - [ProductName] - [Weight]

This is just an example and it is not exactly what my database looks like however it allows me to explain easier. tblOrders and tblPurchaseOrders are related One-to-Many ( tblOrders.[ID]->tblPurchaseOrders.[AssignedID]).

Anyways, I have a listbox on my primary form that lists tblOrders by ID, Date, Consignee and OrderComplete. When I first built the form and I bound the listbox to column 0 ([ID]) and created the event on double-click which the code looked like this:

Private Sub listOrders_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim intLinkID As String

        intLinkID = Me.tblOrders.Value
        stLinkCriteria = "[ID] = " & intLinkID
        Debug.Print stLinkCriteria
    stDocName = "frmManageOrders"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command23_Click:
    Exit Sub

End Sub

Now, for whatever reason - either something I've changed or a bug somewhere... when I double-click the list item, it opens as a new record rather than the corresponding record. I noticed that the "Toggle Filter" button on the access ribbon was ticked so I unchecked it and it immediately switched to the proper record. Why does this happen and what (if common) changes can make this appear or work this way? I am a novice with Access and I am still learning VBA. I struggle with not knowing parameters by heart, although the syntax seems to be similar to other languages I've worked with.

I can post the database however I'd rather keep it somewhat confidential if possible considering the magnitude of data I've collected is sought after by people in this industry. I'd be willing to pay for advice also if necessary. I don't want anyone to hold my hand and write code for me but aid in giving me hints to what I should look for.

Thank you everyone for making this my favorite forum of reference.


EDIT: Figured it out - I had somehow enabled "Data Entry" which is located on the Property Sheet of a Form under the "Data" tab. Apparently this mode removes your ability to actually filter values properly and is intended for what you'd assume it means - Data Entry.


Duh.... /sigh

[Xonos]

Ok, so I was reading some other threads and read that toggling the filter on and off again will fix the issue I am having. So I added the following code to the targeted form.

Private Sub Form_Load()
    Me.FilterOn = False
    Me.FilterOn = True
End Sub

This seems to have resolved my issue HOWEVER, I don't like it - this cannot be a proper solution to this considering I never had to do it before. I am willing to bet other issues will reveal themselves by this action. Does anyone have insight on this? I would not consider it solved personally even though it works.

[Xonos]

And my first issue has arrived. I made a Wizard-Created Button to create a new record through a form - and because it's unfiltered, it just takes me to the first record. -_- Any ideas?

[irish634]

I had this exact problem... although my resolution was entirely different.

I had imported data into a table then deleted the data because I didn't like it or something.
So I imported again BUT... I did not compact the database to reset the auto number.
This is a key point because I imported 30 records, so my record id/auto number started at 31 and not 1

Every time I double-clicked from the list box, my second form would be filtered on a new record since the list value didn't match the record Id number in the table.

I do not know why this happened this way, but I reset my record id (auto number ) field and everything started working normally again.


EDIT:
What I wrote above didn't fix it either.

It only worked that way if the recordID matched the listvalue
Since the list value starts at 0, they will never match.

So my solution was to use this as my criteria:

sLinkCriteria = "RecordID=" & Me!lstbx_BookList.Column(0, lstbx_BookList.RowSource)

Instead of this (which I used to use in Acc2003):

sLinkCriteria = "RecordID=" & Me!lstbx_BookList.Value