Adding a Search Button to a Form

[jonfromdon]

I have recently downloaded the "Service Call Management DB" and successfully
modified it to use our Customer database etc with a view to running our
Service division using this Template etc.
My problem is that I wish to add a Command Button onto the main "Workorders
by Customer" form that allows the user to input a customer name and then
search the Customer Table for any current entries, then if customer is
present then put that customer details into the "workorders by Customer" form.
If no entry is found in the table then open the new customer form.

Can anyone help please, I am relatively new to access 2003 but willing to
learn>

 

[Albert D. Kallal]

Have you tried using the wizard to create a search combo box?

use the wizard to create a combo box, and choose the option to "find a
record based on...."

 

[Klatuu]

Rather than a command button, I would suggest an unbound combo on your Work
Orders By Customer form. This one control will provide you with both
capabilities.
To search for a customer and make that customer's record the current record,
use the After Update event. If the customer doesn't exist in the database,
the Not In List event will fire and you can open your customer form there.
Now, there are a few things that need to be done to make it all work
correctly. First, I am going to assume you have a Long Integer Primary Key
field and a Customer Name text field.
First, you need a row source for the combo. Use a query that pulls those
two fields:

SELECT CustID, CustName FROM tblCustomer ORDER BY CustName;

Now set the following properties for your combo:
Column Count =2
Bound Column =1
Row Source Type = Table/Query
Column Widths 0"; 2" (the 0" makes the ID invisible. Adjust the 2" as
needed)
Limit To List = Yes

Now for the After Update event:

Private Sub cboCustSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[CustID] = " & Me.cboCustSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

And for the Not In List Event;

Private Sub cboCustSearch_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Customer Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then

CurrentDb.Execute ("INSERT INTO tblCustomer (CustName) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Docmd.OpenForm "frmCustomer", , , , , , NewData
Response = acDataErrAdded
Me.cboCustSearch.Requery
Else
Me.cboCustSearch.Undo
Response = acDataErrContinue
End If

End Sub

Now, there is one more thing to do. In the form you open to add a customer,
you will want to use the OpenArg being passed with the customer name to move
to the record just added in the Not In List Event:

Private Sub Form_Load()

With Me.RecordsetClone
.FindFirst "[CustName] = """ & Me.OpenArgs & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Note, this is all air code and not guaranteed to compile or work correctly.
I will most likely take some fine tuning, but it does demonstrate the method.

 

[jonfromdon]

Hi Klatuu
Thanks for the info - I will try that and report back.
--
john

Rather than a command button, I would suggest an unbound combo on your Work
Orders By Customer form. This one control will provide you with both
capabilities.
To search for a customer and make that customer's record the current record,
use the After Update event. If the customer doesn't exist in the database,
the Not In List event will fire and you can open your customer form there.
Now, there are a few things that need to be done to make it all work
correctly. First, I am going to assume you have a Long Integer Primary Key
field and a Customer Name text field.
First, you need a row source for the combo. Use a query that pulls those
two fields:

SELECT CustID, CustName FROM tblCustomer ORDER BY CustName;

Now set the following properties for your combo:
Column Count =2
Bound Column =1
Row Source Type = Table/Query
Column Widths 0"; 2" (the 0" makes the ID invisible. Adjust the 2" as
needed)
Limit To List = Yes

Now for the After Update event:

Private Sub cboCustSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[CustID] = " & Me.cboCustSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

And for the Not In List Event;

Private Sub cboCustSearch_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Customer Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then

CurrentDb.Execute ("INSERT INTO tblCustomer (CustName) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Docmd.OpenForm "frmCustomer", , , , , , NewData
Response = acDataErrAdded
Me.cboCustSearch.Requery
Else
Me.cboCustSearch.Undo
Response = acDataErrContinue
End If

End Sub

Now, there is one more thing to do. In the form you open to add a customer,
you will want to use the OpenArg being passed with the customer name to move
to the record just added in the Not In List Event:

Private Sub Form_Load()

With Me.RecordsetClone
.FindFirst "[CustName] = """ & Me.OpenArgs & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Note, this is all air code and not guaranteed to compile or work correctly.
I will most likely take some fine tuning, but it does demonstrate the method.

--
Dave Hargis, Microsoft Access MVP

I have recently downloaded the "Service Call Management DB" and successfully
modified it to use our Customer database etc with a view to running our
Service division using this Template etc.
My problem is that I wish to add a Command Button onto the main "Workorders
by Customer" form that allows the user to input a customer name and then
search the Customer Table for any current entries, then if customer is
present then put that customer details into the "workorders by Customer" form.
If no entry is found in the table then open the new customer form.

Can anyone help please, I am relatively new to access 2003 but willing to
learn>

 

[jonfromdon]

Hi again Klatuu

Have tried the code you suggested and with a little "tweaking" have had good
success. Slight errors when Customer No Found but not a problem since there
is a "Enter New Customer" button on the Template I am using. Very useful
though since your code produces "predictive text" which certainly speeds up
the selection process.

Thanks again
--
john

Hi Klatuu
Thanks for the info - I will try that and report back.
--
john

Rather than a command button, I would suggest an unbound combo on your Work
Orders By Customer form. This one control will provide you with both
capabilities.
To search for a customer and make that customer's record the current record,
use the After Update event. If the customer doesn't exist in the database,
the Not In List event will fire and you can open your customer form there.
Now, there are a few things that need to be done to make it all work
correctly. First, I am going to assume you have a Long Integer Primary Key
field and a Customer Name text field.
First, you need a row source for the combo. Use a query that pulls those
two fields:

SELECT CustID, CustName FROM tblCustomer ORDER BY CustName;

Now set the following properties for your combo:
Column Count =2
Bound Column =1
Row Source Type = Table/Query
Column Widths 0"; 2" (the 0" makes the ID invisible. Adjust the 2" as
needed)
Limit To List = Yes

Now for the After Update event:

Private Sub cboCustSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[CustID] = " & Me.cboCustSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

And for the Not In List Event;

Private Sub cboCustSearch_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Customer Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then

CurrentDb.Execute ("INSERT INTO tblCustomer (CustName) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Docmd.OpenForm "frmCustomer", , , , , , NewData
Response = acDataErrAdded
Me.cboCustSearch.Requery
Else
Me.cboCustSearch.Undo
Response = acDataErrContinue
End If

End Sub

Now, there is one more thing to do. In the form you open to add a customer,
you will want to use the OpenArg being passed with the customer name to move
to the record just added in the Not In List Event:

Private Sub Form_Load()

With Me.RecordsetClone
.FindFirst "[CustName] = """ & Me.OpenArgs & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Note, this is all air code and not guaranteed to compile or work correctly.
I will most likely take some fine tuning, but it does demonstrate the method.

--
Dave Hargis, Microsoft Access MVP

I have recently downloaded the "Service Call Management DB" and successfully
modified it to use our Customer database etc with a view to running our
Service division using this Template etc.
My problem is that I wish to add a Command Button onto the main "Workorders
by Customer" form that allows the user to input a customer name and then
search the Customer Table for any current entries, then if customer is
present then put that customer details into the "workorders by Customer" form.
If no entry is found in the table then open the new customer form.

Can anyone help please, I am relatively new to access 2003 but willing to
learn>