Assistance with Subform Synching

[Rey]

Howdy all.

My search form has a list box displaying the results of a wild card
search for first or last name or pet name.

When listbox is clicked, the following code is executed which
basically opens the form hidden and sets the cboGroomerID combo to the
first hidden field of the listbox.

Next, a subform control(named Clients on the main form) is synched to
the hidden clientID from the listbox.

Problem is that if I step through the code the Clients subform is
synched to the clientID passed in. However, if I run the form, the
Clients subform is not synched to the passed in value as there is no
match in the recordset clone. Its almost as if there is a delay in
filling the recordset and currently client records number 10 not
hundreds...

I might also like to link the PetData subform with the selected PetID
also hidden but need to resolve this first.

Thank you in advance for your suggestions/comments,

Rey

lstCustomers_Click()
Dim lngRow As Long
Dim lngGroomerID As Long
Dim strFormName As String
Dim lngCustID As Long

Dim ctrlGroomer As Control
Dim ctrlFName As Control
Dim ctrlLName As Control

Dim strCriteria As String
Dim frm As Form

strFormName = "Groomers_SidebySide"

' get lngGroomerID from 1st column in listbox (bound column)
lngGroomerID = lstCustomers.Value

' pulling custID from list
lngCustID = lstCustomers.Column(1)

DoCmd.OpenForm strFormName, acNormal, , , , acHidden

' access groomer combobox to set its value to groomer
Set ctrlGroomer = Forms!Groomers_SidebySide.Form.Controls.Item
("cboGroomerID")
Set ctrlFName = Forms!Groomers_SidebySide.Form.Controls.Item
("txtFirstName")
Set ctrlLName = Forms!Groomers_SidebySide.Form.Controls.Item
("txtLastName")

ctrlGroomer.Value = lngGroomerID
ctrlGroomer.Requery

ctrlFName = ctrlGroomer.Column(2)
ctrlLName = ctrlGroomer.Column(1)

' now to set the client subform to clientID value
' Define search criteria
strCriteria = "ClientID = " & lngCustID

Set frm = Forms!Groomers_SidebySide!Clients.Form

'sSleep (4000)
With frm.RecordsetClone
.FindFirst strCriteria
If .NoMatch Then
MsgBox "not found"
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing

' this redisplays form
DoCmd.SelectObject acForm, strFormName, False

' exit search form
DoCmd.Close acForm, "frmSearch", acSaveNo
Set ctrlGroomer = Nothing


Tables:
Groomers (PKey - GroomerID)
Linked 1-M with Clients on GroomerID
Clients (PKey - ClientID)
Linked 1-M with PetData on ClientID
PetData (PKey - PetID)
Linked 1-M with GroomingVisits on PetID
GroomingVisits (PKey - VisitID)

 

[Jeanette Cunningham]

Don't open the subform and try to sync it.
The idea with a main form - subform setup, is to open the main form to the
ClientID you want.
The main form will automatically sync the subform to show all records with a
ClientID that matches the ClientID in the main form.

Open the main form at the ClientID from the listbox. The Link Child and Link
Master fields will sync the subform to the correct ClientID.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Rey]

Howdy Jeanette.

Thanks for replying.

Totally forgot to mention that the cboGroomerID combo and first and
last name textboxes are unbound on the Groomers_SidebySide form. Form
works when used by itself.

A SQL query loads the combobox with groomerID (hidden) and groomer
first name. On selecting groomer, the textboxes are filled w/first and
last. Then the clients associated/linked to that groomerID are
displayed in the Clients subform as well as the respective pets for
client when selected.

I'm trying to programmatically set the cboGroomerID combo with the
groomerID obtained from the listbox on the search form. And then have
the Clients subform have its record selector positioned on the row w/
the correct clientID. Right now that works only if I step through the
code.

Rey

 

[Rey]

Resolved for now but don't understand why...

Searched in group for synching subforms and came across post that
suggested using doEvents and it worked:
Dim i As Integer
With frm.RecordsetClone
For i = 1 To 300: DoEvents: Next

From: "Graham Mandeno"
subject:Updating the value of a bound control on the main form from
the subform

Thanks,

Rey

 

[Rey]

May have spoken to soon as now it does not work.
But it did earlier...

Problem surfaced after I added a do until loop to see if that could
replace doEvents.

Removed the do while loop, compacted and repaired the MDB and now the
problem has surfaced again.
If I step through it works but not if I just run it.

What the hay?

Ideas anyone?

Thanks,

Rey

 

[Jeanette Cunningham]

From the search form, open the Groomers form, not hidden, but visible.
I suggest you use the where clause to open the groomers form at the groomer
id from the search form.
Dim strWhere as String
strWhere = "[GroomerID] = " & lngGroomerID
DoCmd.OpenForm strFormName, , , strWhere

After the groomers form opens, user selects the groomer and the form shows
all the customers for that groomer.
You can use a subform to show the customers for a groomer.
Then user selects a customer and opens the customer form at the selected
customer.


The code below runs in the subform and opens the customer details form at
the selected customer.
Dim strWhere as String
strWhere = "[CustID] = " & lngCustID
DoCmd.OpenForm strFormName, , , strWhere



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Rey]

Howdy Jeanette.
Thanks for the suggestion. Currently, the Groomers_SidebySide form
works as per your suggestion.

The issue is in calling the form from the search form which uses the
hidden customerID and groomerID associated w/the selected customer.

The Groomers_SidebySide form is hidden in order to set the groomer id
of the groomer combo box to the correct groomer and then synchronize
the client subform to have the correct clientID selected vice just the
first record of clients related to the groomerID.

My previous post suggesting I have have spoken to early has been
resolved by running the /decompile switch (msaccess.exe fullpath to
MDB /decompile). Now the client subform is synch with the passed in
clientID.

Open is still the issue of why code works w/o DoEvents code line when
stepping through code and fails/errs when form is just run. will try
searching group on timing...

Thanks again,

Rey

 

[Jeanette Cunningham]

You wrote Now the client subform is synch with the passed in
clientID.

Open is still the issue of why code works w/o DoEvents code line when
stepping through code and fails/errs when form is just run. will try
searching group on timing...

If the client subform is now syncing with the passed in clientid, then why
do you have an issue with DoEvents?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Rey]

Howdy Jeanette.

No issue w/using DoEvents...Just like to understand what causes the
timing problem.

Was reading posts that seemed to indicate - to me - the issue may be
related to using recordsetclone. But recordsetclone is used for
synching subforms and checking for empty subform etc.

If I encounter a similar issue, will again use DoEvents.

Thanks for your assistance,

Rey

 

[Jeanette Cunningham]

DoEvents is typically used when access has a lot of intensive processing
without any breaks, such as in loop code.
Using DoEvents, as I understand it, temporarily halts access processing and
allows other background processes to run, then access goes back to its
intensive processing.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia