Synchronising Forms

[WJReid]

Hi All,

I am trying to synchronise 2 forms and I have taken the subroutine straight from the Northwinds database, where the synchronise criteria is:

SyncCriteria=BuildCriteria("Product",dbText,Me!Product)

When I run the subroutine, I get the message:

Variable not defined and it highlights dbText.

Anyone know the answer to this. I am running XP Professional and Office 2000.

Regards,

Bill

 

[parry]

On 2003-02-08 11:32, WJReid wrote:
Hi All,

I am trying to synchronise 2 forms and I have taken the subroutine straight from the Northwinds database, where the synchronise criteria is:

SyncCriteria=("Product",dbText,Me!Product)

When I run the subroutine, I get the message:

Variable not defined and it highlights dbText.

Anyone know the answer to this. I am running XP Professional and Office 2000.

Regards,

Bill

Hi Bill hard to tell without seeing all the code - can you post your code or indicate which form/module in Northwind this is located?

At a guess I think this line is running another procedure called BuildCriteria and this line is passing three arguments to the procedure. The middle argument is using a variable "dbText" which you cannot have assigned in the procedure sending the values to BuildCriteria.

If you have Option Explicit in your procedure you will need to Dim the variable before initializing it.

Eg:
Dim dbText as string 'this assigns the variable
dbtext = "MyText" 'This initializes (assigns a value) to the variable.

HTH

_________________
cheers
Parry
This message was edited by parry on 2003-02-09 00:22
This message was edited by parry on 2003-02-09 00:23

 

[WJReid]

Hi Parry,

Thanks for the reply.

I have a master form, PROSPECTS, with 2 subforms, Contacts and ProspectNotes. The 2 subforms are linked by the ContactNo field and when I select the current record in the Contacts subform, I need the ProspectNotes to open and synchronise with it. The form_current event for the Contacts subform is:

Private Sub Form_Current()
'If the ContactNo is blank, then exit the Sub.
If IsNull(Me![ContactNo]) Then
Exit Sub
End If

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

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

'Check to see if the ProspectNotes 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 ProspectNotes form.
Set F = Forms(FormName)
Set rs = F.RecordsetClone

'Define the criteria used for the synchronization.
SyncCriteria = BuildCriteria("ContactNo", dbText, _
Me!ContactNo)

'Synchronize the corresponding record in the ProspectNotes form to
'the current record in the subform.
rs.FindFirst SyncCriteria

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

End Sub

Hope this helps.

Regards,

Bill

 

[parry]

Ah, I see. BuildCriteria is a method I wasnt aware of. If you look up BuildCriteria in help (just highlight the words buildcriteria in the code and press F1)it gives a description.

Im not sure exactly what will fix this but you can try the following:-

1)dbText is saying that the criteria you need to look up in BuilCriteria must be a text field where Me!ContactNo may in fact be a number data type. You will need to change from dbtext to another data type

In clicking on the Type hyperlink (under FieldName) in Help to see the available Data Types it doesnt make clear what bloody data types are available. Instead, open the Object Browser (F2 or View-Object Browser) then type dbText in the field. Below you will see the available data types - dbBigInt,
dbbinary etc.

2)I see there is either ADO and DAO for this method. What this means is a different way that Access handles some methods so the available data type names may be different if you use ADO versus DAO (the names beginning with db are DAO).

So you have to reference the appropriate library. Select Tools-References and this brings up a list of installed libraries. You need to put tick next to Microsoft DAO 3.6 Object Library.

Let me know how you got on. Fingers crossed

EDIT
As the error is that it doesnt recognise the varibale then the #2 option is probably the fix. Install this library first before changing the data type.
_________________
cheers
Parry
This message was edited by parry on 2003-02-09 13:45

 

[WJReid]

Hi Parry,

Thank you very much. I changed it to DAOrecordset and changed the dbText to dbDouble and it works perfectly.

Thanks again for all of your help in this.

Regards,

Bill

 

[parry]

Excellent, good stuff. Glad you got it sorted.