My form won't synch to listbox when opened from switchboard

[tvsmvp]

Why does my record not "synch" when I open the form from the switchboard? It opens fine when I simply double-click on the form icon - but nothing I've tried will get it to synch if opened directly from the switchboard. I have this in the sub-form's code module:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductID] = " & Str(Nz(Me![List4], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I'd simply like to be able to click on a record within a listbox ("List4") and jump to that record - which works fine when I open the form directly - but just sits there if I use the switchboard to open the form.

 

[kjv1611]

As far as your code you have there. What procedure is that stored in? In order for it to work, it must be within some sort of SubProcedure or Function. For Example:

Private Sub Form_Load()
--Your Code--
End Sub

 

[tvsmvp]

It's in "afterUpdate."

The entire sub reads like this:

Private Sub List4_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductID] = " & Str(Nz(Me![List4], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[TheAceMan1]

How are ya tvsmvp . . .

Try this instead:

[blue]   Dim rs As [purple][b]DAO.Recordset[/b][/purple]
   
   Set rs = Me.Recordset.Clone
   
   rs.FindFirst "[ProductID] = [red][b]'[/b][/red]" & Str(Nz(Me![List4], 0)) & "[red][b]'[/b][/red]"
   If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
   
   Set rs = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[tvsmvp]

I plugged that in and got a "data mismatch" - so I swapped out the right side of the equation, making the line: rs.FindFirst "[ProductID] = " & Str(Nz(Me![List4], 0)).

And it worked, sort of. It just sits there is I open from the switchboard, but works fine if I open the file by directly clicking on it.

I don't understand the access disconnect. What's the difference between clicking on it, vs opening from the switchboard?

 

[TheAceMan1]

tvsmvp . . .

For starters the mismatch error shows [blue]"[ProductID] is numeric[/blue]. Therefore you don't need the Str() function. Str() converts its arguement to a string!, so that:

[blue]Str(Nz(Me![List4], 0))
  [purple][b]should be:[/b][/purple]
Nz(Me![List4], 0)[/blue]

See Ya! . . . . . .

 

[tvsmvp]

Thanks for the tip; I made the switch. But...

It still balks when opened from the switchboard. I walked through the code and when it gets to:

If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

It never gets to the "then side" of the equation; it just drops to the next line: "set rs = nothing."

 

[PHV]

And what happens if you replace this:
Set rs = Me.Recordset.Clone
with this ?
Set rs = Me.RecordsetClone

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

 

[tvsmvp]

Same difference. It still avoids the second half of the equation (ignores the "Then Me.Bookmark = rs.Bookmark" part).

 

[TheAceMan1]

or:

[blue]rs.FindFirst "[ProductID] = " & [purple][b]Val(Me![List4])[/b][/purple][/blue]

See Ya! . . . . . .

 

[tvsmvp]

Nope - 'fraid it still sits there staring at me. Aargh!

 

[TheAceMan1]

tvsmvp . . .

It appears where all missing something here. Try the following just as a test. Backup the code and replace with the following:

[blue]   Me.Recordset.FindFirst "[ProductID] = " & Nz(Me![List4], 0)[/blue]

If it fails post back the [blue]RowSource[/blue] of the listbox . . .

See Ya! . . . . . .

 

[tvsmvp]

I replaced the code with that simple line - and... it's the same. Works great by opening it directly, stares at me if I open through the switchboard. How does opening it through the switchboard change it? Somehow that simple line means something to the file when opened one way - and absolutely nothing to the same file when opened via the switchboard.

Anyway, the row source is this: SELECT Products.ProductID, Products.Product FROM Products;

It's just a simple list of products and their id's.

 

[kjv1611]

Do you possibly have any custom code in the switchboard that could be interfering?

 

[tvsmvp]

I created the switchboard using the tool provided via Tools > Database Utilities > Switchboard Mgr. The only thing listed on the sb is the button for the form I've described. (Oh - and I've added no addl code to it.) The code that the tool creates (to create the sb) is loooong - so I backed off tinkering with that, figurig anything it might be doing could be overridden upon opening the form. (Which might be my problem....)

 

[kjv1611]

Well, if you didn't customize the code at all, then the code created from the wizard should be perfectly fine. So, that should not be causing any of your troubles.