Re: moving a main form and subform to a specific subform record

DawnTreader wrote:
i have a main form with a subform that contains data that i want to search
and goto a specific record. i created a combo find box through the wizard on
a form based on the table that creates my subform and then tried copying and
pasting to the form i really want it on, but the combo box does nothing.

i have a site table that has a one to many relationship with the products
table.

SiteTable
SITEID
SITEADDRESS

ProductsTable
SITEID
PRODUCTID
SERIALNUMBER

the site ID is on the main form and sub form. the mainform is where i need
to place the combo box to find the serial number of the product and have the
mainform and subform move to that product record. how do i do this?

i tried this code:

Private Sub Combo46_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.subfrmtblProductList.Form.Recordset.Clone
rs.FindFirst "[ProductID] = " & Str(Nz(Me![Combo46], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

the subfrmtblProductList is the name of the subform, but the idea i had
doesnt work. do i need to put references to the subform anywhere else? or is
there another approach i need to take when creating this combo box?


"Doesn't work" is not much of a clue about what's happening.

I think you are confusing form and subform bookmarks and
mixing DAO and ADO methods. If you're using DAO, try this:

Private Sub Combo46_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset

Set rs = Me.subfrmtblProductList.Form.RecordsetClone
rs.FindFirst "[ProductID] = " & Str(Nz(Me![Combo46], 0))
If Not rs.NoMatch Then
Me.subfrmtblProductList.Form.Bookmark = rs.Bookmark
End If
End Sub

Not 100% sure, but I think the ADO equivalent might be:

Private Sub Combo46_AfterUpdate()
' Find the record that matches the control.
Dim rs As ADODB.Recordset

Set rs = Me.subfrmtblProductList.Form.Recordset.Clone
rs.Find "[ProductID] = " & Str(Nz(Me![Combo46], 0))
If Not rs.EOF Then
Me.subfrmtblProductList.Form.Bookmark = rs.Bookmark
End If
End Sub

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: Combobox Display Problem
    ... Private Sub Item_AfterUpdate ... ' Find the record that matches the control. ... Header section of the form (subform), ... The Control Source for cboItem on fsubGroup is chrItem, ...
    (microsoft.public.access.forms)
  • Re: Is this a Sound Database Design? -- procedurenames must be unique in same module
    ... Since these are PRIVATE functions, it is ok to have another Private function with this name in ANOTHER module sheet. ... Created a form with a subform in it, named it TabSubform and then created a command button, named it SwitchTabs. ... Dummy is an unbound control -- really tiny because I use labels instead of command buttons and a label does not get the focus ... Dim mCurrentTab As Integer, i As Integer, mBoo As Boolean ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Is this a Sound Database Design? -- code: SwitchTabs
    ... The "command buttons" (put in quotes because I actually used label ... Dummy is an unbound control -- really tiny because I use labels instead ... Sometimes, the subforms do not have linking fields, like a lookup subform ... Dim mCurrentTab As Integer, i As Integer, mBoo As Boolean ...
    (microsoft.public.access.tablesdbdesign)
  • RE: append query and update field problem
    ... You are using the main form's NewRecord property in the subform. ... one record to a new record is to use the controls Tag properties to save the ... Private Sub TagValues ... Dim varCtlNames As Variant ...
    (microsoft.public.access.formscoding)
  • Subform RecordsetClone No Current Record error
    ... of rows all displayed in an Access 2000 subform. ... The "after update" event of the form control ... Dim bCategoryValOpposite As Boolean ...
    (microsoft.public.access.formscoding)
Loading