Using DoCmd.FindRecord on nested subforms

[bbrazeau]

Below is code I wrote to try and allow the user to search all records for a Part Number and then go to the record that has that number.


I have a combo box on the main form that is unbound but gets its data from the same record source as the sub-subform. The user picks a part from the combobox then presses the Part button.The code works the 1st time and usually more than once. It almost seems as though as long as its searching forward it finds and moves to the correct record, but if you try to go backwards its just finds the customer and the first record for that customer. Does anyone see a issue that sticks out?

Private Sub cmdPartSearch_Click()
   Dim frm As Form
   Set frm = Forms!Main.Form!Molds.Form
   Dim Cust, lngMold, lngPart As Long
   Cust = Me.cboPartSearch.Column(2)
   lngMold = Me.cboPartSearch.Column(3)
   lngPart = Me.cboPartSearch.Column(0)
   MsgBox Cust & " " & lngMold & " " & lngPart
   CustomerID.SetFocus
   DoCmd.FindRecord FindWhat:=Cust, Match:=acEntire, MatchCase:=False, _
      Search:=acSearchAll, SearchAsFormatted:=False, _
      OnlyCurrentField:=acCurrent, FindFirst:=True
   Forms.Main.Molds.SetFocus
   DoCmd.FindRecord FindWhat:=lngMold, Match:=acEntire, MatchCase:=False, _
      Search:=acSearchAll, SearchAsFormatted:=False, _
      OnlyCurrentField:=acCurrent, FindFirst:=True
   frm.Parts.SetFocus
   DoCmd.FindRecord FindWhat:=lngPart, Match:=acEntire, MatchCase:=False, _
      Search:=acSearchAll, SearchAsFormatted:=False, _
      OnlyCurrentField:=acCurrent, FindFirst:=True
   CustomerID.SetFocus
   Me.cboPartSearch = Null
   Set frm = Nothing
   Me.Repaint
End Sub

[bbrazeau]

Found that if MoldID is the active control when choice is made from combobox and Part button is pushed it works everytime. However I can't find a way to make that control active in code. I can't rely on the user allways having that control be the last one they were in.

[RuralGuy]

Have you reviewed this link yet? http://theaccessweb.com/forms/frm0031.htm

[bbrazeau]

Hi RG Thanks. Yes I had referenced to the link you posted. I have it as a document I use, but I was still having trouble. Finally got it to work by as I mentioned getting the focus back to the MoldID field excerpt from code below

Forms.Main.Molds.SetFocus
Molds.Form.Parts.SetFocus
DoCmd.FindRecord FindWhat:=lngPart, Match:=acEntire, MatchCase:=False, _
Search:=acSearchAll, SearchAsFormatted:=False, _
OnlyCurrentField:=acCurrent, FindFirst:=True
Me.cboPartSearch = Null
frm.MoldID.SetFocus

[RuralGuy]

That's great. Are you ready to mark this thread as Solved?