Find record in subform

[Song Su]

I'm trying to find a reord in subform with linked with main form.
Following record always gives 'No matchfound' even though I can see it.
What's wrong with my code?

Private Sub cboTag_AfterUpdate()
Dim strCriteria As String

Dim rstTag As DAO.Recordset
' Define search criteria
strCriteria = "LACCDtag = '" & Me.cboTag & "'"

Set rstTag = Me.frmPODetailsSubform.Form.RecordsetClone

' Find the first occurrence
rstTag.FindFirst strCriteria
If rstTag.NoMatch Then
MsgBox "No match found"
Else
'Move to the record on the subform
Me.frmPODetailsSubform.Form.Bookmark = rstTag.Bookmark
End If
End Sub

 

[tina]

look at the *bound column* of cboTag. does it hold the values that will
match up with values found in LACCDtag? if not, you probably need to change
the bound column of cboTag.

also, open the main form in Design view. click once on the subform to select
it. open the Properties box and look at the Name property. is the name
"frmPODetailsSubform"? if not, change the code you posted by replacing
frmPODetailsSubform with the name found in the subform's Name property.

hth

 

[Song Su]

Bound column to 1 which is value of LACCDTag. (column 0 is PODetailID which
is hidden)
Name property of subform IS 'frmPODetailsSubform'

 

[NKTower]

Then you need this...

strCriteria = "LACCDtag = '" & Me.cboTag & "'"


When developing, it helps to do this...


strCriteria = "LACCDtag = '" & Me.cboTag & "'"
debug.print "strCriteria is :" & str_Criteria

At run-time you can then type a Control-G to open up the debug window and
see what got passed in. Then comment out the debug.print or delete the line
once it is working properly.

 

[Song Su]

Debug gave me a hint so I change line to
strCriteria = "LACCDtag = '" & Me.cboTag.Column(1) & "'"
and it works.

However, my combo box list ALL child records related to other parent records
as well. My main form only display 1 parent record and it's related child
records. If I select a child record does NOT belong to that parent record,
it says 'no match found'

In such case, is it possible to locate parent record and display it by
select its child record in my combo box?

Hope I made myself clear.

 

[NKTower]

I think I may have found something...

Question: When the second form opens, can you click on it and MANUALLY bring
it to the foreground, or is it visible behind but not responsive?

If the latter, then it has to do with how you are opening the first form.

I've tested this stripped-down version under both Access 2003 and Access
2007. The results, tabulated below, are the same for both versions of access.

I've created 3 forms - "frm_ZERO" to launch your first form
(frm_First_Form), and from that form I launch frm_Second_Form passing in a
parameter as discussed previously. (In this example, I don't do anyting with
the parameter other than display itm, as UI don't have or need a table behind
my test case.)


Form: frm_ZERO contains just two controls - command buttons. I made this
form to simulate how I think you might be launching your first form.

Control: btn_Launch_Dialog
Caption: Launch Dialog

Control: btn_Launch_Normal
Cation: Launch Normal



Private Sub btn_Launch_Dialog_Click()
DoCmd.OpenForm "frm_First_Form", , , , , acDialog
End Sub

Private Sub btn_Launch_Normal_Click()
DoCmd.OpenForm "frm_First_Form"
End Sub


------------------------------------------
Form: frm_First_Form - this one simulates the form where you select a
record and pass a value from that record into the second form, which uses the
value to position to a specific record. In the simulation, we don't need to
have a record bound to the form, so I just display the value to prove that it
got passed in. There are just two controls on this form - a text box to
handle the 'seed value' that we want to pass to the other form, and the
button to launch and position the second form (or reposition it if it is
already open.)

Command Button: btn_GoTo_Second_Form
Caption: Second Form

Text Box: txt_Seed_Value
Label Caption: Seed Value

Private Sub btn_GoTo_Second_Form_Click()
Dim frm_Second_Form As Form
Dim ctrl_Hidden_Control As Control
If Not CurrentProject.AllForms("frm_Second_Form").IsLoaded Then
DoCmd.OpenForm "frm_Second_Form"
End If
' Give it a chance to open
DoEvents
' create an object so we can reference it easily
Set frm_Second_Form = Forms![frm_Second_Form].Form
' create an object for the hidden control
Set ctrl_Hidden_Control = frm_Second_Form.Controls("txt_Hidden_Control")
' pass a value into it
ctrl_Hidden_Control.Value = Me.txt_Seed_Value.Value
' arm the timer
frm_Second_Form.TimerInterval = 10
' and release the objects
Set ctrl_Hidden_Control = Nothing
Set frm_Second_Form = Nothing
End Sub

------------------------------------------
frm_Second_Form iis the one launched (and positioned). You mentioned that
it has tabs on it, so I have a tab control with two pages Each page has a
single text box on it. The form also has the "hidden control" but it is
visible for now so that you can see that the value was passed in.

Control: txt_Hidden_Control
Visible = False (after you've tested)

Control: tabCtrl_Pages
Page 1:
Control: txt_On_Tab_0
Page 2:
Control: txt_On_Tab_1


Private Sub Form_Timer()
' turn off the timer
Me.TimerInterval = 0

' position the record set here
' using the value in txt_Hidden_Control


' force the 1st tabbed page
Me.tabCtrl_Pages.Value = 0
' put cursor in the first control on the page
Me.txt_On_Tab_0.SetFocus
End Sub

------------------------------------

Test 1: From frm_ZERO, if you launch frm_First_Form with the acDialog
attribute set, the second form is in the background and you can't get to it
without closing frm_First_Form.

Test 2: From frm_ZERO, if you launcn frm_First_Form without the the acDialog
attribute set, the frm_Second_Form responds normally coming to the
foreground. If the form was already open, it still works. If the form had
been openon the second tabbed page, it flips to the first tab.


Test 3: Set the MODAL attribute = TRUE in frm_First_Form -
Launch Normal - frm_Second_Form is available
Launch Dialog - frm_Second_Form is visible behind, but can't do anything
with it


Test 4: Add "Me.Form.SetFocus
to the timer event, immediately before the set focus to txt_On_Tab_0
Same results.


Test 5: Add the acDialog parameter to the open of frm_Second_Form
Didn't expect it to work, and it didn't. Because it is opened as a dialog,
the code following the DoCmd.OpenForm in frm_First_Form that sets the timer
wasn't executed until frm_Second_Form was closed. As a result, the second
form nevef "got the message" of which record to position to. Further, when
frm_Second_Form was closed, the code following the DoCmd.OpenForm
"frm_Second_Form" finally executed, and errored out because the form had been
close.

In summary, I suspect it may have something to do with how you are opening
the form that calls for the second form.

Since it is working here, there must be something else going on. If you
CAN bring the form to the foreground by clicking it, then one more thing to
try - put Me.Refresh or Me.Repaint just ahead of the .SetFocus in the
timer event.


Suggestion: Build a trial database with just the 3 forms defined above
with just those controls and default properties for everything. Convince
yourself that the bare-bones version works the way you want it. Then compare
setting/properties of the working version vs. your real application.