Jump to different record

[actionashley]

Hello All,

I have a bill of materials form with a search box up top that will give me the components and usages of top level products in a sub form. Some of these components themselves have bills of materials associated with them also so what I want to do is be able to click on the component part number and have the bom change to show that numbers bom. Hope this makes sense.

Private Sub ComponentMaterial_Click()

'set the componet number=the product number

'requery based on new product number
End Sub

I just don't know the syntex to complete this.
Thanks for any help anyone can provide.

 

[namliam]

Try and lookup RecordsetClone and see how far you get.

 

[actionashley]

Thanks for the reply mailman. After looking up record set clone it just reinforces the fact that the more I know, the more I don't know.

So to do what I want to do I need to create a record set clone then go to the record I have in focus?

I'm sorry I don't really get it. When I opened a new form and went to my current record with all records available my code was this.

Dim rs As Object
Dim StringBookmark As String

'set variable to current record
StringBookmark = Me.Product_Number

'open new form
DoCmd.OpenForm "EditBom3"

'take it to selected record
Set rs = Forms!EditBom3.RecordsetClone

rs.FindFirst "[Product Number] ='" & StringBookmark & "'"
Forms!EditBom3.Bookmark = rs.Bookmark

Set rs = Nothing

Do I need to do something similar?

Dim rs As Object
Dim StringBookmark As String

'set variable to current record
StringBookmark = Needs to be what i clicked?

'open new form, the forms already open?
DoCmd.OpenForm "OrderRequirements"

'take it to selected record
Set rs = Forms!OrderRequirements.RecordsetClone

rs.FindFirst "[Product Number] ='" & StringBookmark & "'"
Forms!OrderRequirements.Bookmark = rs.Bookmark

Set rs = Nothing

Man am I confused

 

[namliam]

Ah, from your original query I didnt get you were opening a new form.

Do you want to see ONLY the data you selected, or see all but jump to the selected one?

If you only want what you selected... use the Openform command and use the Where clause

If you want to jump to, you have to use the OpenArgs of the Openform command to pass your data along.

DoCmd.OpenForm "OrderRequirements",,,,"[Product Number] ='" & StringBookmark & "'"

Or however many comma's one needs to get to the where argument.

Then in your form you can fetch the OpenArgs in your code, which you then put in the "On Open" of the form:
To open the form:

DoCmd.OpenForm "OrderRequirements" ,,,,, StringBookmark

Or however many comma's one needs to get to the openargs argument.

StringBookmark = Me.Openargs
Set rs = Me.RecordsetClone

rs.FindFirst "[Product Number] ='" & StringBookmark & "'"
Me.Bookmark = rs.Bookmark

This will work if Product number is a string, if infact it is
- a number: "[Product Number] = " & StringBookmark & " "
- a date: "[Product Number] =#" & StringBookmark & "#"
In case of a date make sure that StringBookmark is actually in a US format (MM/DD/YYYY) or in a ISO format (YYYY/MM/DD)

 

[actionashley]

Sorry guess I am not explaining correctly.
The form is already open with a subform showing the component materials.
I want to click on the component material and have the same form/ subform combination change to show the components bill of material.

Can I open a form that is already open?

Do I close the form and re-open it as you detailed?

Thank you for taking the time with my problem.

 

[namliam]

In which case we are back to Square #1, Recordsetclone.


This example straight from the access help shows how to search for something using the recordsetclone.

Sub SupplierID_AfterUpdate()
    Dim rst As Recordset
    Dim strSearchName As String

    Set rst = Me.RecordsetClone
    strSearchName = Str(Me!SupplierID)
    rst.FindFirst "SupplierID = " & strSearchName
        If rst.NoMatch Then
            MsgBox "Record not found"
        Else
            Me.Bookmark = rst.Bookmark
        End If
    rst.Close
End Sub