Help with OpenForm code

[gojets1721]

I have listbox in a form which lists changes made to a record. I have code that lets the user click an entry in the listbox and it opens up a single view form with a more detailed view of the change. I want the code to open to that particular entry but also apply a filter as well so that the user can move to the other changes within the single view form.

Here's my code:

If Me.list.ListIndex > -1 Then
   DoCmd.OpenForm "frmChangeLogExpanded", , "ComplaintNumber = " & Me.txtComplaintNumber, OpenArgs:=Me.list.Column(0)
End If

Its not working though. It opens up to the right record but does not apply the filter so every change across every record can be accessed. Any suggestions? I can supply an example DB if this is confusing.

 

[jdraw]

Do you have a copy of the database with only a few records that illustrate the issue that you could post?

 

[Pat Hartman]

To do this, you would need to open the form unfiltered and then in the form's load event use a value passed using the OpenArgs property to move to the passed ID. The form would then be bound to the full table and you can scroll however you want.

This is not a technique I would ever use because most of my apps get upsized to SQL Server and this technique brings too much data over the wire and prevents the server from doing the heavy lifting. So, if you think the app might be upsized, I would not do this.

 

[gojets1721]

Do you have a copy of the database with only a few records that illustrate the issue that you could post?

Of course. See attached.

So when you open it up, hit the green change log. And then hit one of the entries on the list box. It opens up to the right entry, but also includes complaint number 6's entries. I only want complaint number 5 entries to show because that is what record the user is specifically on.

Appreciate any help! TY

 

[Pat Hartman]

The form is working correctly. HOWEVER, your relationship is incorrect and needs to be fixed.
1. Naming all PKs "ID" just causes confusion. Give each a unique name that ties to its table. ChangeLogID and ComplaintID would be appropriate.
2. Relationships are ALWAYS FK to PK. They are NEVER data field to data field and that is what you have.
3. Remove the ID field from tblComplaints and make ComplaintNumber the PK. Then add the two tables to the relationship diagram and draw the connecting line AND enforce RI.

 

[jdraw]

I moved to record 2, hit change, then clicked Open Change Details and got the following.