Jumping to a record in datasheet view 3

[dlsd619]

I have a Form that is single form, and the subform is datasheet.

I would like to have a combo box on the main form that when you enter a text value in it, it will jump to that same record in the subform. I don't want to filter just that record, I would only like the subform to jump to that record so the users can see that record and the surrounding records.

The field is a TEXT value. Can somebody get me pointed in the right direction? Thank you.

 

[lameid]

On the afterupdate event on the text box you could use (VBA) a recordsetclone and bookmark to navigate the record in the subform.

If you have advanced wizards installed, this is the same kind of thing you get when you make a combobox that moves to a selected record. The key difference is you want to do it on a subform instead of a mainform.

Does that help?

 

[TheAceMan1]

How are ya dlsd619 . . .

There's a litle more to this than meets the eye.

If the [blue]subForm is linked to the mainform[/blue] via [blue]Master/Child Link[/blue] properties then when going to the record in question in the subform, you may first hav to goto the parent record in the mainform! . . .

[blue]Hoping this makes sense! . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[lameid]

dlsd619,

Just in case you didn't know, based on your post you do not want to use Master an Child fields...

I don't want to filter just that record, I would only like the subform to jump to that record so the users can see that record and the surrounding records.

 

[dlsd619]

Ok, I am finally having a chance to tinker with this a bit.

AceMan, the subform is NOT LINKED. The main form is a single form of Items from a table that can be edited. On that form I have a combo box to jump to the record on the main form as follows:

Private Sub Combo15_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ActivityCode] = '" & Me![cbo_ActivityCode] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The standard Wizard helped me with that. Now, the subform is a datasheet view of every record from the same table that is not editable, just for viewing.

What I want is when the user uses that combo box on the main form to GoTo the selected record, I would like the subform to jump to the same record. There is a reason they will need to view the records around the selected record.

I hope this makes sense to you. Thanks for your help.

 

[lameid]

You need to update it to use your subform instead of the main form because it is supposed to move the subform record...

Private Sub Combo15_AfterUpdate()
    Dim rs As Object
    Set rs = Forms!SubFormCOntrol.Form.Recordset.Clone
    rs.FindFirst "[ActivityCode] = '" & Me![cbo_ActivityCode] & "'"
    If Not rs.EOF Then Forms!SubFormCOntrol.Form.Bookmark = rs.Bookmark
End Sub

 

[dlsd619]

SubFormControl" being the name of the Subform?

 

[dlsd619]

Here's what I tried:

Private Sub cbo_ActivityCodeSearch_AfterUpdate()
Dim rs As Object
Set rs = Forms![AddEditActivityCodesSub].Form.Recordset.Clone
rs.FindFirst "[ActivityCode] = '" & Me![cbo_ActivityCodeSearch] & "'"
If Not rs.EOF Then Forms![AddEditActivityCodesSub].Form.Bookmark = rs.Bookmark
End Sub

I am getting the error "Microsoft Office can't find the form 'AddEditActivityCodesSub' referred to in a Macro expression etc...

 

[dlsd619]

Figured it out...it's just:

[AddEditActivityCodesSub].Form.Recordset.Clone

No need for Forms! to start

Thanks for all your help

 

[lameid]

Sorry I must be tired or something. The form has to reference the subform on it. Hopefully that will work.

Private Sub cbo_ActivityCodeSearch_AfterUpdate()
    Dim rs As Object
    Set rs = ME![AddEditActivityCodesSub].Form.Recordset.Clone
    rs.FindFirst "[ActivityCode] = '" & Me![cbo_ActivityCodeSearch] & "'"
    If Not rs.EOF Then ME![AddEditActivityCodesSub].Form.Bookmark = rs.Bookmark
End Sub

 

[lameid]

Or you could figure it out during the cross post and let the ME! be implied.

 

[marjerie888]

Hi everyone

I have same problem but Im using lisbox and 2 forms
that when they select a record in form2 listed in list box when they click the cmdFind the form1 appear to show the record.

form1 = frmQuoteSheetMaster
form2 = frmFindQuteSheet

I'm having an error "runtime 424"

Please anyone can help i'm new in access...Thank u in advance
----------------------------------------

Private Sub cmdFind_Click()

Dim rst As Object

Set rst = frmQuoteSheetMaster.Form.RecordsetClone
rst.FindFirst "[Placing_ID] = " & "'" & lstFind & "'"
frmQuoteSheetMaster.Form.Bookmark = rst.Bookmark
DoCmd.Close acForm, "frmFindQuoteSheet"

End Sub

 

[lameid]

Most of us don't have error numbers memorized... what is the text of the error? Also what line is it failing on?

I did notice you reference frmQuoteSheetMaster you are not using the forms collection...

instead of

frmQuoteSheetMaster

try

Forms!frmQuoteSheetMaster

Other than that it looks to me on the surface it should work.

 

[TheAceMan1]

marjerie888 . . .

Error 424 is [blue]Object Required.[/blue]

Try this:

[blue]   Dim rst As DAO.Recordset
   [green]'the rest of the code[/green][/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884: