Forum Home
Press F1
 
Thread ID: 2000 1999-07-07 05:38:00 Access 97 Guest (0) Press F1
Post ID Timestamp Content User
2360 1999-07-07 05:38:00 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?

Please

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? '

Me!Clear.SetFocus
Else

Tmp = EnableControls( ' Detail ' , True)

Me![Committee Company Subform].SetFocus

End If

End Sub 		Guest (0)
1