Type Down Function

[JK]

I have a main form and a subform. On the main form I have a text box called
txtSearch. I want to be able to using this text box to search for a record in
the subform. As you type in txtSearch the subform should change to only
display records that match whats been typed in txtSearch.

Can anyone find the problem with the following code?
I'm getting an error on sfrmForm


Private Sub TypeDown(strSearch As String)
On Error Resume Next

Dim strSQL As String

If strSearch = "" Then
strSQL = "qryClassItems_Main"
Else

'modify this to desired query


strSQL = "SELECT * FROM qryClassItems_Main" & _
" WHERE [ClassofTrade] Like '*" & strSearch & "*' " & _
" OR [ClassDesc] Like '*" & strSearch & "*' " ' last line
ends like this


End If
Me.sfrmForm.Form.RecordSource = strSQL
Me.sfrmForm.Form.Requery
Me.txtSearch.SetFocus
Me.txtSearch.SelStart = Len(Me.txtSearch)

End Sub


Private Sub Form_Open(Cancel As Integer)

Dim strSearch As String
Dim strSQL As String

strSQL = "qryClassItems_Main" 'change this todesired query


Me.sfrmForm.Form.RecordSource = strSQL
Me.sfrmForm.Form.Requery
Me.txtSearch.SetFocus

End Sub


Private Sub txtSearch_KeyUp(KeyCode As Integer, Shift As Integer)
Me.Requery
TypeDown IIf(IsNull(Me.txtSearch), "", Me.txtSearch)
End Sub

 

[Dirk Goldgar]

I have a main form and a subform. On the main form I have a text box called
txtSearch. I want to be able to using this text box to search for a record
in
the subform. As you type in txtSearch the subform should change to only
display records that match whats been typed in txtSearch.

Can anyone find the problem with the following code?
I'm getting an error on sfrmForm


Private Sub TypeDown(strSearch As String)
On Error Resume Next

Dim strSQL As String

If strSearch = "" Then
strSQL = "qryClassItems_Main"
Else

'modify this to desired query


strSQL = "SELECT * FROM qryClassItems_Main" & _
" WHERE [ClassofTrade] Like '*" & strSearch & "*' " & _
" OR [ClassDesc] Like '*" & strSearch & "*' " ' last line
ends like this


End If
Me.sfrmForm.Form.RecordSource = strSQL
Me.sfrmForm.Form.Requery
Me.txtSearch.SetFocus
Me.txtSearch.SelStart = Len(Me.txtSearch)

End Sub


Private Sub Form_Open(Cancel As Integer)

Dim strSearch As String
Dim strSQL As String

strSQL = "qryClassItems_Main" 'change this todesired query


Me.sfrmForm.Form.RecordSource = strSQL
Me.sfrmForm.Form.Requery
Me.txtSearch.SetFocus

End Sub


Private Sub txtSearch_KeyUp(KeyCode As Integer, Shift As Integer)
Me.Requery
TypeDown IIf(IsNull(Me.txtSearch), "", Me.txtSearch)
End Sub

You don't say what error you're getting, which would be helpful. Questions
for you:

1. Is the TypeDown procedure in the code module on the main form? I would
assume so, from the way you posted it, but it If it's actually in a separate
module, then the reference to Me.sfrmForm won't work.

2. Please check to make sure that "sfrmForm" is actually the name of the
subform *control* on the main form. The name of that control may or may not
be the same as the name of the form object it displays. You must use the
name of the control, not the form object itself, in your references to the
subform from the main form.

As an unrelated observation, please note that you do not have to requery a
form after setting its RecordSource property. Setting the property
automatically requeries the form. So where you have code like this:

Me.sfrmForm.Form.RecordSource = strSQL
Me.sfrmForm.Form.Requery

.... you are actually requerying the form twice.

Also, why are you requerying the main form in the KeyUp event of txtSearch?
I don't see the point of the extra overhead.

 

[JK]

You're the best! The name of my subform was not sfrmForm.
Your other suggestions are also appreciated!

I have a main form and a subform. On the main form I have a text box called
txtSearch. I want to be able to using this text box to search for a record
in
the subform. As you type in txtSearch the subform should change to only
display records that match whats been typed in txtSearch.

Can anyone find the problem with the following code?
I'm getting an error on sfrmForm


Private Sub TypeDown(strSearch As String)
On Error Resume Next

Dim strSQL As String

If strSearch = "" Then
strSQL = "qryClassItems_Main"
Else

'modify this to desired query


strSQL = "SELECT * FROM qryClassItems_Main" & _
" WHERE [ClassofTrade] Like '*" & strSearch & "*' " & _
" OR [ClassDesc] Like '*" & strSearch & "*' " ' last line
ends like this


End If
Me.sfrmForm.Form.RecordSource = strSQL
Me.sfrmForm.Form.Requery
Me.txtSearch.SetFocus
Me.txtSearch.SelStart = Len(Me.txtSearch)

End Sub


Private Sub Form_Open(Cancel As Integer)

Dim strSearch As String
Dim strSQL As String

strSQL = "qryClassItems_Main" 'change this todesired query


Me.sfrmForm.Form.RecordSource = strSQL
Me.sfrmForm.Form.Requery
Me.txtSearch.SetFocus

End Sub


Private Sub txtSearch_KeyUp(KeyCode As Integer, Shift As Integer)
Me.Requery
TypeDown IIf(IsNull(Me.txtSearch), "", Me.txtSearch)
End Sub

You don't say what error you're getting, which would be helpful. Questions
for you:

1. Is the TypeDown procedure in the code module on the main form? I would
assume so, from the way you posted it, but it If it's actually in a separate
module, then the reference to Me.sfrmForm won't work.

2. Please check to make sure that "sfrmForm" is actually the name of the
subform *control* on the main form. The name of that control may or may not
be the same as the name of the form object it displays. You must use the
name of the control, not the form object itself, in your references to the
subform from the main form.

As an unrelated observation, please note that you do not have to requery a
form after setting its RecordSource property. Setting the property
automatically requeries the form. So where you have code like this:

Me.sfrmForm.Form.RecordSource = strSQL
Me.sfrmForm.Form.Requery

... you are actually requerying the form twice.

Also, why are you requerying the main form in the KeyUp event of txtSearch?
I don't see the point of the extra overhead.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)