View Full Version : Access '97

21-04-1999, 08:38 AM
Hi there at F1

Wrote to you earlier this week re a problem with the <Enter>. Thanks for your answer, however it failed to work. I believe I have located the problem but I do not know enough about coding to fix. I think it involves the code below, which includes 'Recordsource' which is affected between the versions 2.0 & '97. Any ideas?

Private Sub Show_Customers_Click()
' Create a WHERE clause using search criteria entered by user and
' set RecordSource property of Committee & Company Subform .
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant
' Initialize argument count.
ArgCount = 0
' Initialize SELECT statement.
MySQL = 'SELECT * FROM Membership WHERE '
MyCriteria = ''
' Use values entered in text boxes in form header to create criteria for WHERE clause.
AddToWhere [Look For Surname], '[Surname]', MyCriteria, ArgCount
AddToWhere [Look for Christian Name], '[Christian Name]', MyCriteria, ArgCount
' If no criterion specifed, return all records.
If MyCriteria = '' Then
MyCriteria = 'True'
End If
' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria
' Set RecordSource property of Committee Company Subform.
Me![Committee Company Subform].Form.RecordSource = MyRecordSource
' If no records match criteria, display message.
' Move focus to Clear button.
If Me![Committee Company Subform].Form.RecordsetClone.RecordCount = 0 Then
MsgBox 'No records match the criteria you entered.', 48, 'No Records Found'
' Enable control in detail section.
Tmp = EnableControls('Detail', True)
' Move insertion point to Production Subform
Me![Production Subform].SetFocus
End If
End Sub

The 'EnableControls' is
Option Compare Database 'Use database order for string comparisons

Function EnableControls(WhichSection As String, State As Integer) As Integer

' Enable or disable controls in specified section of form.
Dim MyForm As Form
Dim MyControl As Control
Dim I As Integer, SelectedSection As Integer
' Use active form. If no form is active, exit
' function without displaying error message.
On Error Resume Next
Set MyForm = Screen.ActiveForm
If Err Then
EnableControls = False
On Error GoTo 0
Exit Function
End If
' Define valid values for WhichSection.
Select Case UCase$(WhichSection)
SelectedSection = 1
SelectedSection = 3
SelectedSection = 0
SelectedSection = 4
SelectedSection = 2
Case Else
MsgBox 'Invalid argument', , 'EnableControls'
EnableControls = False
Exit Function
End Select
' Set state for all controls in specified section.
For I = 0 To MyForm.Count - 1
Set MyControl = MyForm(I)
If MyControl.Section = SelectedSection Then
On Error Resume Next
MyControl.Enabled = State
On Error GoTo 0
End If
Next I
EnableControls = True

End Function

Any help you can give would be greatly appreciated. This code orginates from the Northwind example in V 2.0. Thanks

Ray Pearson