Syncronize 2 forms problem 1

[crisis2007]

I have the following code on a main form command button that is intended to pop-up a second form. This second form is supposed to synchronize using the RosterID as a common field. This second form, based on a different table allows me to enter data. But the code does not seem to populate the textbox and synchronize the second form (F_Stat2) as I intend. I am using ms access 2003. Any assistance is greatly appreciated. I am still learning vba and am not proficient in it.

Private Sub Command113_Click()
'If the employee number is blank, then exit the Sub.
If IsNull(Me.[EmployeeNumber]) Then
Exit Sub
End If

'Dimension variables.
Dim FormName As String, SyncCriteria As String
Dim frm As Form, rs As DAO.Recordset

'Set the formname to "F_Stat2," the form that will be
'synchronized.
FormName = "F_Stat2"

'Check to see if the F_Stat2 form is open. If it
'is not open, open it.
If Not SysCmd(acSysCmdGetObjectState, acForm, FormName) Then
DoCmd.OpenForm FormName
End If

'Define the form object and Recordset object for
'the F_Stat2 form.
Set frm = Forms(FormName)
Set rs = frm.RecordsetClone

'Define the criteria used for the synchronization.
SyncCriteria = BuildCriteria("RosterID", dbInteger, Me.txtRosterID)

'Synchronize the corresponding record in the F_Stat2 form to
'the current record in the F_NorthEdit
rs.FindFirst SyncCriteria

'If a record exists in the F_NorthEdit form, find the
'matching record.
If rs.NoMatch Then
MsgBox "No match exists!", 64, FormName
Else
frm.Bookmark = rs.Bookmark
End If

End Sub

 

[PHV]

Why not use a linked subform ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya crisis2007 . . .

Although I agree with [blue]PHV[/blue] screen realestate may not make a subform possible ... at least not in good taste. An alternative would be to have a [blue]WHERE[/blue] clause in the [blue]recordsource[/blue] of F_Stat2, that compares against [blue]RosterID[/blue] of the calling form. This way, code in the calling form only has to [blue]Requery[/blue] F_Stat2 to make things happen.

In any event we need to see:
[ol][li]The [blue]recordsource[/blue] of F_Stat2. If its a query ... post the SQL.[/li]
[li]The code for your function [blue] BuildCriteria().[/blue][/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[crisis2007]

Thanks for responding. I had to sit this aside as my clothes washer broke down and I had to fix it first.

TheAceMan1 was right - screen real estate was my problem. But the form is based on a table. I have no code for the function BuildCriteria(). It appears that it is a built-in MS Access function from what I read on the internet. I used this code in an old database I built in MS Access 97. I believe Microsoft posted it in one of their articles if I remember correctly. My old database seems to work fine in Access 2003 still.

 

[hmlhml]

another possibility is to open the second form using the OpenArgs parameter to specify the data you need for synchronising. Then use the OnOpen event of the 2nd form to catch OpenArgs and change filters etc. If you need to set controls programatically, save a copy of OpenArgs and do it in the 2nd form's OnLoad event.

 

[crisis2007]

Ok - I finally got it to work. I realized that the 2nd form (F_Stat2) needed to be a subform within another form that was linked to the F_NorthEdit form via the properties - parent/child fields using RosterID. I don't know if that makes any sense but it seems to work fine now. Thanks for your help. Just the things that were pointed out helped direct me into looking in the correct places eventually. Thanks again!!

 

[TheAceMan1]

crisis2007 . . .

Wow ... I've had 2003 since it 1st came out and never saw or heard of the [blue]BuildCriteria[/blue] method (thats because I'm too busy making my own criteria). After checking it out your main problem comes to the forefront. You have ...

[blue]SyncCriteria = BuildCriteria("RosterID", dbInteger, Me.[purple][b]txtRosterID[/b][/purple])[/blue]

... you can't compare a numeric value against a string! ... and apparently [blue]BuildCriteria[/blue] doesn't coerce [purple]txtRosterID[/purple] to numeric for you. So you have to do it yourself. The following should cause your code to filter properly:

[blue]SyncCriteria = BuildCriteria("RosterID", dbInteger, [purple][b]Val([/b][/purple]Me.txtRosterID[purple][b])[/b][/purple])[/blue]

That done, for what your code does you could shorten it to the following:

[blue]   Dim frm As Form, frmName As String, Cri As String
   
   If IsNull(Me.[EmployeeNumber]) Then Exit Sub
   frmName = "F_Stat2"
   Cri = BuildCriteria("[RosterID]", dbInteger, [purple][b]Val([/b][/purple]Me.txtRosterID[purple][b])[/b][/purple])
   
   If Not SysCmd(acSysCmdGetObjectState, acForm, frmName) Then
      DoCmd.OPenForm frmName, , , Cri
   Else
      Set frm = Forms(frmName)
      frm.FilterOn = False
      frm.Recordset.FindFirst Cri
      Set frm = Nothing
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[crisis2007]

Thanks AceMan. I will play around and try your code. What you said does make sense to me. I always appreciate your help!