Reverse engineering an event

[Derrick]

Here's a sample.

This form is used to build the recordsource for another
form.

'Place a global variable in a module
Public gblName as String

'Prompt the user with a form
If Me.txtLookUpName.Value <> "" Then
gblName = Trim(txtLookUpName)
DoCmd.OpenForm "frmFormName", acNormal
DoCmd.Close acForm, Me.Form.Name
Else
MsgBox "You need to provide a name."
End If

'Set the recordsource when the frmFormName loads
Me.Form.RecordSource = "SELECT * FROM TableName WHERE
FieldName= '" & gblName & "';"
Me.Form.Requery

'If you open up the form that prompt for Name you can do
someting like this.

If Me.txtLookUpName.Value <> "" Then
gblName = Trim(txtLookUpName)
Forms![frmFormName].RecordSource = "SELECT * FROM
TableName WHERE FieldName= '" & gblName & "';"

Forms![frmFormName].Requery
DoCmd.Close acForm, Me.Form.Name
Else
MsgBox "You need to provide a name."
End If

Good Luck

-----Original Message-----
I am trying to reverse engineer an On Click event for a

control in an Access form. Here is what I am trying to
do:

There is a field in which the user will enter the name

of a contact (s)he wants to find in the database. Below
it is a control which the user will click to look for
that contact. On Click I want Access to use the criteria
entered into the field to modify the criteria in a query
linked to a form and run that form to display all the
possible matches. I know it can be done because someone
else did it in an older database that we do not use
anymore. I am getting a compile error that says "User-
defined type not defined" Here is the script that was
used:

Option Compare Database
Option Explicit
Sub BuildSQLString(strSQL As String)
Dim strWhere As String
If Not IsNull(txtName) Then
strWhere = strWhere & " AND Name LIKE" & "'*" & txtName & "*'"
End If
If Len(strWhere) > 0 Then strSQL = Mid$(strWhere, 6)

End Sub
Private Sub Run_Click()
If IsNull(txtName) Then MsgBox "You gotta enter a name!"
On Error GoTo Exit_Run_Click
Dim strWhere As String
Dim qdf As QueryDef <------THE

DEBUGGER SELECTS THIS SCRIPT AFTER THE COMPILE ERROR

BuildSQLString strWhere
If ChangeQueryDef("qryContactookup", "select

qryContactLookup1.* from qryContactLookup1 where " &
strWhere) Then

End If
DoCmd.OpenForm "Form ViewContacts", acNormal
DoCmd.Close acForm, "Form ContactLookup", acSaveNo
Exit_Run_Click:
Exit Sub
End Sub

Private Function ChangeQueryDef(strQuery As String, strSQL As String)
If strQuery = "" Or strSQL = "" Then Exit Function
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs(strQuery)
qdf.SQL = strSQL
qdf.Close
RefreshDatabaseWindow
ChangeQueryDef = True
End Function


Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click


DoCmd.Close

Exit_Cancel_Click:
Exit Sub

Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click

End Sub

The truth is that I am a beginning VB user so I may be

biting off more than I can chew, but any help would be
appreciated. How should I define the user-defined type?