View Full Version : Access 97

07-07-1999, 05:38 PM
Dear F1

Hi. I have a problem. I use the code below to search for a client by Surname or Christian name or both. The problem is that I need to employ referential integrity to allow me to cascade deletes from associated tables.

If I used the ClientID as the unquie identifer and set it as the primary key then when I do a search it only returns the first record in the table. If I remove the primary key feature then the search is OK.

Can you help?


Ray Pearson.

Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> '' Then
' Add 'and' if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & ' and '
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & ' Like ' & Chr(39) & FieldValue & Chr(42) & Chr(39))

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Sub

Private Sub Clear_Click()

' Clear controls in form header and remove records from subform.
Dim MySQL As String
Dim Tmp As Variant

MySQL = 'SELECT * FROM Clients WHERE False'

' Clear search text boxes.
Me![Look for Surname] = Null
Me![Look for Christian Name] = Null

' Reset subform's RecordSource property to remove records.
Me![Committee Company Subform].Form.RecordSource = MySQL

' Move insertion point to Surname text box.
Me![Look for Surname].SetFocus

End Sub

Private Sub Show_Details_Click()

Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant

ArgCount = 0

MySQL = 'Select * from Clients where'
MyCriteria = ''

AddToWhere [Look for Surname], '[Surname]', MyCriteria, ArgCount
AddToWhere [Look for Christian Name], '[ChristianName]', MyCriteria, ArgCount

If MyCriteria = '' Then
MyCriteria = 'True'
End If

MyRecordSource = MySQL & MyCriteria

Me![Committee Company Subform].Form.RecordSource = MyRecordSource

If Me![Committee Company Subform].Form.RecordsetClone.RecordCount = 0 Then
MsgBox 'No Record Found', 48, 'Try Again?'


Tmp = EnableControls('Detail', True)

Me![Committee Company Subform].SetFocus

End If

End Sub