SubForm Double Click event (1 Viewer)

[kirkm]

Is is possible to call this event and run the code there from a module ?

 

[MajP]

Yes.

 

[theDBguy]

I'd say give it try... Or, did you try it already but ran into a problem?

 

[kirkm]

Great! I've got

call Form_mysubform.Prefix_DblClick

but getting error Argument not optional
Should I expect something else ?

 

[theDBguy]

Great! I've got

call Form_mysubform.Prefix_DblClick

but getting error Argument not optional
Should I expect something else ?

Hi. Anytime you call a routine with a non-optional argument, you will have to provide a value for the argument.

 

[kirkm]

Thanks, found I had to add (vbcancel),
However now I need to select the record in that Form. I know the value of the 'Prefix' field.
Is there any VBA command to do this ? That is I know there must be but I can't think what.

 

[theDBguy]

Thanks, found I had to add (vbcancel),
However now I need to select the record in that Form. I know the value of the 'Prefix' field.
Is there any VBA command to do this ? That is I know there must be but I can't think what.

You may have to modify the routine to get the value from a variable. Otherwise, make the current prefix is the one you want first.

 

[kirkm]

I have the value already. Plus I found a way to do it, but I don't think it's the right or best way.

Sub GotoRecord(ByVal strPrefix As String)

    Dim rst As DAO.Recordset
    Dim strff As String
    
    Set rst =Form_mysubform.Form.RecordsetClone
    strff = "Prefix = '" & strPrefix & "'"
    
    rst.FindFirst strff
    If Not rst.NoMatch Then
             Form_mysubform.Form.Bookmark = rst.Bookmark
    End If
    
    rst.Close
    Set rst = Nothing

End Sub

Seems way too complicated. Any thoughts ? Thanks

 

[theDBguy]

Hi. Glad to hear you got it sorted out. As usual, there are more than one way to get things done in Access. I can't say if this is too complicated without knowing what the original intent was. For instance, what did the original dblclick event code do?

 

[kirkm]

It gets a whole pile of data from that record and outputs it to Notepad. Gives a useful quick overview.
By complicated I meant using a recordset clone and bookmark just to goto a record. I imagined something much simpler
like DoCmd.GotoRecord(Prefix=123) might exist. Or maybe that's wishful thinking!

 

[theDBguy]

Oh, I think I see what you mean. There are several ways to go to a record. I think the bookmark method is a reliable one, even if it may be a bit complicated.

 

[Micron]

There is DoCmd.FindRecord, but I think what you have would be more reliable and isn't all that complicated. You can get unexpected results using a 'Find First' sort of approach which is basically what FindRecord is.

 

[kirkm]

Aren't I using .FindFirst anyway ? Thanks though, I'll stick with what there.. looks liket here's no one-liner (which was probably what I was hoping for). It maybe isn't that complicated but looks like way too much code. It just has to be in that cell to run it's d-click event. (LOL yes it's not a cell but I'm not sure what Access calls it!)

 

[theDBguy]

...looks liket here's no one-liner (which was probably what I was hoping for).

Actually, I think there is (or was it a two -liner?), but I think you're better off with what you got.

 

[MajP]

Gives a useful quick overview.
By complicated I meant using a recordset clone and bookmark just to goto a record. I imagined something much simpler
like DoCmd.GotoRecord(Prefix=123) might exist.

The recordsetclone method is a leftover from before the recordset property of a form was accessible.

Using a recordset
me.recordset.FindFirst "Prefix = 123"

 

[Micron]

Aren't I using .FindFirst anyway

Yes, I mis-spoke. I had in mind finding "First" or finding "Last" which usually trips people up because the names First and Last are misleading.

 

[kirkm]

Roger that Micron. MajP I've successfully changed my Sub GotoRecord to what you suggested and its working nicely. TY.
But I'm not sure what you were telling me about leftover property. And the comments saying what I had was okay or best.
Is there some issue I might run into with me.recordset.FindFirst "Prefix = 123". I like it.

 

[MajP]

But I'm not sure what you were telling me about leftover property. And the comments saying what I had was okay or best.
Is there some issue I might run into with me.recordset.FindFirst "Prefix = 123". I like it.

You mentioned the long code using the recordsetclone and not the shorter recordset method that I posted. I believe the recordset clone and bookmark method that you see on the internet is a leftover from the days when you could not get the actual recordset. You had to do it the long way.
However, if you do not want to physically move the form display then use the clone. If you move in the recordset it moves the record in the form.

Using gotorecord or findfirst are different approaches, but not sure if anyone can say one is better than another. Gotorecord is pure access and findfirst is DAO. Doubt either is going away sometime soon.