Cool Search Tool Recordsets

[natep26]

I'm using the following code with a listbox and the Cool search tool. The search is working fine. The problem occurs when I try to click or double click a filtered record to open another form and I get the errer "Runtime error 7591. You entered an expression that has an invalid reference to the recordset clonse property.

Any ideas?

Private Sub tanklist_AfterUpdate()
    
DoCmd.Requery
Me.RecordsetClone.FindFirst "[tankno] = '" & Me![TankList] & "'"
If Not Me.RecordsetClone.NoMatch Then
   Me.Bookmark = Me.RecordsetClone.Bookmark
Else
   MsgBox "Could not locate [" & Me![TankList] & "]"
End If

End Sub


Private Sub TankList_DblClick(Cancel As Integer)

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "ftankinfo"
    
    stLinkCriteria = "[TankNo]=" & "'" & Me![TankList] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

 

[Moniker]

First, you can lose all the extra variable declarations. Second, I'm not positive this will work, but try referencing the RecordsetClone directly, like this:

Private Sub TankList_DblClick(Cancel As Integer)

    DoCmd.OpenForm "ftankinfo", , , "[TankNo]='" & Me.RecordsetClone.Fields("TankList") & "'"

End Sub

If that's not it, does it give you the same error or a new one?

 

[natep26]

Same Error

in this line from
Private Sub tanklist_AfterUpdate()

Me.RecordsetClone.FindFirst "[tankno] = '" & Me![TankList] & "'"

 

[Moniker]

I'm not sure what the issue is here. Set a breakpoint on the line that is bombing, go to the immediate window, and see if you can hit the recordsetclone from there. In steps:

1) Put a breakpoint on the line starting with the DoCmd in the DblClick event. Do this by clicking in the left margin of the code window. The line will highlight to a marroon color.

2) Run the form and double-click in the TankList object. The code window will open and code execution is now paused at the line in question.

3) Open the Immediate Window (press Ctrl-G) and see if you can address the RecordsetClone from here. In other words, type something like this in:

?Me.RecordsetClone.Fields("TankNo")

If it returns the value for TankNo, then you can hit the RecordsetClone just fine. This leads down two different paths:

1) You're either searching for a value that doesn't exist (TankNo = Me.TankList, and the value TankNo cannot be found), or

2) You cannot use the RecordsetClone as the source to open a new form.

 

[natep26]

Returns the same error message when ran in the immediate window.

I don't know if it matters, but I started out the database with the listbox and double clicking to open the selected record. Then I incorporated the search tool to filter the listbox.

Thanks for your help thus far.

 

[Moniker]

Can you take most of the data out, zip it, and post it? Don't leave any sensitive data in there (if there is any), but leave a few records for testing purposes. We'll get an answer a lot faster that way as I'm having to guess at existing structures. If I can see it, I'll probably be able to figure it out.

 

[natep26]

Thanks Moniker

 

[Moniker]

A number of things here:

1) You can change this:

Me.username = "Current User: " & fSystemUserName

To this:

Me.username = "Current User: " & Environ("UserName")

This also allows you to completely get rid of the Module called UserName.

2) You keep referring to Me.RecordsetClone, but the variable Me refers to the form, and the form has no Recordet defined. Ta da, there's the problem! For a RecordsetClone to exist, a Recordset must exist, and currently, it doesn't.

Give me a little time here and I'll straighten that out.

 

[Moniker]

Here you go. There's a lot of stuff in there I'd change, but it's your code, so I didn't do much beyond getting it working. I assume the form fTankInfo is supposed to not yet be functional because there are a lot of broken references in there.

Anyway, all I did was take out the references to Me.RecordsetClone.<property> and I replaced them with TankList.Recordset.<property>. This works fine. Also, having the clock on the form is redundant as the clock is in the corner anyway. Having it requery itself 60 times a minute is also overkill. Again though, it's your form.

You'll see everything I did in the attached, fixed DB. (Note that I did a compact/repair as well, which is how it went from 167KB zipped to 42KB zipped.)

HTH

 

[natep26]

I appreciate you taking the time work on this. I don't really understand alot of VBA yet so I'm open to any shortcuts on reducing the amount of code.

Thanks again

 

[Moniker]

For now, just delete the module named "username" as I replaced it with the Environ() function.

As for other things, they're more stylistic. It looks like you've copy/pasted a lot of code from various examples, and that's fine for beginners. The trick is to understand what the code is doing, at which point you'll begin to develop your own style of doing things. I could go in and make it match my style, but that may mean nothing to you, so I instead did minimal changes and left what you already know intact.