How do I build a customer search?

[Guest]

I need to be able look up a customer while I am on the phone with him. I was
thinking of using his phone number as the main search. But if that didn't
find him I would search his last name. I have seen where others put a check
in a checkbox next to the field you would like to search. This was in the
form heading I believe. And when it searched it poped up a list to pick from.
When you selected the right one it fills in all the fields in the form
heading but also the same fields down below in the detail. Then you could
type the info from the call in and then take the next call. You would hit the
clear button and it would clear the fields in the form heading and you would
check the next field to search under.

I am little under experienced for this so any help would greatly be
appreciated.

For starts how do I tell access to search for what I selected by checkmark?
Or should I start elsewhere first?

Thanks Dustin

 

[Guest]

You need a form that will allow you to view and manipulate the customer record.
On this form you will need two Unbound combo boxes. You can use either of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[injanib]

Hi,
I actually need to do the same thing, except that I need to search based on
two fields, 'Start Date', and 'End Date'. My date format is medium date
format example "19-June-07". and also where do you record these codes?

You need a form that will allow you to view and manipulate the customer record.
On this form you will need two Unbound combo boxes. You can use either of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

I need to be able look up a customer while I am on the phone with him. I was
thinking of using his phone number as the main search. But if that didn't

[quoted text clipped - 14 lines]

Thanks Dustin

 

[Guest]

Format does not matter. The method is the same as I described. The only
difference is that if the field in the underlying table is a date data type,
the value being compared has to be enclosed in # instead of quote marks:

It would be something like:
.FindFirst "[BEGIN_DATE] = #" & Me.cboBegDate & "#"
--
Dave Hargis, Microsoft Access MVP

Hi,
I actually need to do the same thing, except that I need to search based on
two fields, 'Start Date', and 'End Date'. My date format is medium date
format example "19-June-07". and also where do you record these codes?

You need a form that will allow you to view and manipulate the customer record.
On this form you will need two Unbound combo boxes. You can use either of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

I need to be able look up a customer while I am on the phone with him. I was
thinking of using his phone number as the main search. But if that didn't

[quoted text clipped - 14 lines]

Thanks Dustin

 

[Douglas J. Steele]

<picky>
Not everyone has their Short Date setting set to mm/dd/yyyy through Regional
Settings, Dave.

Far safer is:

..FindFirst "[BEGIN_DATE] = " & Format(Me.cboBegDate, "\#mm\/dd\/yyyy\#")

or

..FindFirst "[BEGIN_DATE] = " & Format(Me.cboBegDate, "\#yyyy\-mm\-dd\#")
</picky>


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Format does not matter. The method is the same as I described. The only
difference is that if the field in the underlying table is a date data
type,
the value being compared has to be enclosed in # instead of quote marks:

It would be something like:
.FindFirst "[BEGIN_DATE] = #" & Me.cboBegDate & "#"
--
Dave Hargis, Microsoft Access MVP

Hi,
I actually need to do the same thing, except that I need to search based
on
two fields, 'Start Date', and 'End Date'. My date format is medium date
format example "19-June-07". and also where do you record these codes?

You need a form that will allow you to view and manipulate the customer
record.
On this form you will need two Unbound combo boxes. You can use either
of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to
know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

I need to be able look up a customer while I am on the phone with him.
I was
thinking of using his phone number as the main search. But if that
didn't
[quoted text clipped - 14 lines]

Thanks Dustin

 

[Guest]

Thanks, Doug. I keep forgetting and you keep reminding me. I appreciate the
oversight.
--
Dave Hargis, Microsoft Access MVP

<picky>
Not everyone has their Short Date setting set to mm/dd/yyyy through Regional
Settings, Dave.

Far safer is:

..FindFirst "[BEGIN_DATE] = " & Format(Me.cboBegDate, "\#mm\/dd\/yyyy\#")

or

..FindFirst "[BEGIN_DATE] = " & Format(Me.cboBegDate, "\#yyyy\-mm\-dd\#")
</picky>


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Format does not matter. The method is the same as I described. The only
difference is that if the field in the underlying table is a date data
type,
the value being compared has to be enclosed in # instead of quote marks:

It would be something like:
.FindFirst "[BEGIN_DATE] = #" & Me.cboBegDate & "#"
--
Dave Hargis, Microsoft Access MVP

Hi,
I actually need to do the same thing, except that I need to search based
on
two fields, 'Start Date', and 'End Date'. My date format is medium date
format example "19-June-07". and also where do you record these codes?

Klatuu wrote:
You need a form that will allow you to view and manipulate the customer
record.
On this form you will need two Unbound combo boxes. You can use either
of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to
know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

I need to be able look up a customer while I am on the phone with him.
I was
thinking of using his phone number as the main search. But if that
didn't
[quoted text clipped - 14 lines]

Thanks Dustin

 

[Douglas J. Steele]

Just one of my crusades! <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug. I keep forgetting and you keep reminding me. I appreciate
the
oversight.
--
Dave Hargis, Microsoft Access MVP

<picky>
Not everyone has their Short Date setting set to mm/dd/yyyy through
Regional
Settings, Dave.

Far safer is:

..FindFirst "[BEGIN_DATE] = " & Format(Me.cboBegDate, "\#mm\/dd\/yyyy\#")

or

..FindFirst "[BEGIN_DATE] = " & Format(Me.cboBegDate, "\#yyyy\-mm\-dd\#")
</picky>

 

[Guest]

Thanks for responding,

I have four things, Customer ID, First, Last, Business name(some customers
only gave me thier business name).

On either search what will happen, will the customer info be directly
entered in the fields or will multiple names pop up on a seperate screen to
select from. (Ex. More than one customer with the last name Smith.)

Thanks again for the response! Dustin

You need a form that will allow you to view and manipulate the customer record.
On this form you will need two Unbound combo boxes. You can use either of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP

I need to be able look up a customer while I am on the phone with him. I was
thinking of using his phone number as the main search. But if that didn't
find him I would search his last name. I have seen where others put a check
in a checkbox next to the field you would like to search. This was in the
form heading I believe. And when it searched it poped up a list to pick from.
When you selected the right one it fills in all the fields in the form
heading but also the same fields down below in the detail. Then you could
type the info from the call in and then take the next call. You would hit the
clear button and it would clear the fields in the form heading and you would
check the next field to search under.

I am little under experienced for this so any help would greatly be
appreciated.

For starts how do I tell access to search for what I selected by checkmark?
Or should I start elsewhere first?

Thanks Dustin

 

[Guest]

Oh, you do need another crusade, I'm sure
How about working on the beer glass that can't be tipped over
--
Dave Hargis, Microsoft Access MVP

Just one of my crusades! <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug. I keep forgetting and you keep reminding me. I appreciate
the
oversight.
--
Dave Hargis, Microsoft Access MVP

<picky>
Not everyone has their Short Date setting set to mm/dd/yyyy through
Regional
Settings, Dave.

Far safer is:

..FindFirst "[BEGIN_DATE] = " & Format(Me.cboBegDate, "\#mm\/dd\/yyyy\#")

or

..FindFirst "[BEGIN_DATE] = " & Format(Me.cboBegDate, "\#yyyy\-mm\-dd\#")
</picky>

 

[Guest]

No, you will get the entry you selected.
Using a combo box, with the Auto Expand property set to Yes, the entries
will change as you type or you can scroll to the entry you want.
The After Update event doesn't fire until you have selected an entry.
Then your form's controls will be populated with the customer's record.
--
Dave Hargis, Microsoft Access MVP

Thanks for responding,

I have four things, Customer ID, First, Last, Business name(some customers
only gave me thier business name).

On either search what will happen, will the customer info be directly
entered in the fields or will multiple names pop up on a seperate screen to
select from. (Ex. More than one customer with the last name Smith.)

Thanks again for the response! Dustin

You need a form that will allow you to view and manipulate the customer record.
On this form you will need two Unbound combo boxes. You can use either of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP

I need to be able look up a customer while I am on the phone with him. I was
thinking of using his phone number as the main search. But if that didn't
find him I would search his last name. I have seen where others put a check
in a checkbox next to the field you would like to search. This was in the
form heading I believe. And when it searched it poped up a list to pick from.
When you selected the right one it fills in all the fields in the form
heading but also the same fields down below in the detail. Then you could
type the info from the call in and then take the next call. You would hit the
clear button and it would clear the fields in the form heading and you would
check the next field to search under.

I am little under experienced for this so any help would greatly be
appreciated.

For starts how do I tell access to search for what I selected by checkmark?
Or should I start elsewhere first?

Thanks Dustin

 

[Guest]

I see. But how do you connect the required fields to the combo box or am I
getting ahead of myself?

Thanks so much. Dustin

No, you will get the entry you selected.
Using a combo box, with the Auto Expand property set to Yes, the entries
will change as you type or you can scroll to the entry you want.
The After Update event doesn't fire until you have selected an entry.
Then your form's controls will be populated with the customer's record.
--
Dave Hargis, Microsoft Access MVP

Thanks for responding,

I have four things, Customer ID, First, Last, Business name(some customers
only gave me thier business name).

On either search what will happen, will the customer info be directly
entered in the fields or will multiple names pop up on a seperate screen to
select from. (Ex. More than one customer with the last name Smith.)

Thanks again for the response! Dustin

You need a form that will allow you to view and manipulate the customer record.
On this form you will need two Unbound combo boxes. You can use either of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

I need to be able look up a customer while I am on the phone with him. I was
thinking of using his phone number as the main search. But if that didn't
find him I would search his last name. I have seen where others put a check
in a checkbox next to the field you would like to search. This was in the
form heading I believe. And when it searched it poped up a list to pick from.
When you selected the right one it fills in all the fields in the form
heading but also the same fields down below in the detail. Then you could
type the info from the call in and then take the next call. You would hit the
clear button and it would clear the fields in the form heading and you would
check the next field to search under.

I am little under experienced for this so any help would greatly be
appreciated.

For starts how do I tell access to search for what I selected by checkmark?
Or should I start elsewhere first?

Thanks Dustin

 

[Guest]

What do you mean by connect the required fields to the combo box?
--
Dave Hargis, Microsoft Access MVP

I see. But how do you connect the required fields to the combo box or am I
getting ahead of myself?

Thanks so much. Dustin

No, you will get the entry you selected.
Using a combo box, with the Auto Expand property set to Yes, the entries
will change as you type or you can scroll to the entry you want.
The After Update event doesn't fire until you have selected an entry.
Then your form's controls will be populated with the customer's record.
--
Dave Hargis, Microsoft Access MVP

Thanks for responding,

I have four things, Customer ID, First, Last, Business name(some customers
only gave me thier business name).

On either search what will happen, will the customer info be directly
entered in the fields or will multiple names pop up on a seperate screen to
select from. (Ex. More than one customer with the last name Smith.)

Thanks again for the response! Dustin


:

You need a form that will allow you to view and manipulate the customer record.
On this form you will need two Unbound combo boxes. You can use either of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

I need to be able look up a customer while I am on the phone with him. I was
thinking of using his phone number as the main search. But if that didn't
find him I would search his last name. I have seen where others put a check
in a checkbox next to the field you would like to search. This was in the
form heading I believe. And when it searched it poped up a list to pick from.
When you selected the right one it fills in all the fields in the form
heading but also the same fields down below in the detail. Then you could
type the info from the call in and then take the next call. You would hit the
clear button and it would clear the fields in the form heading and you would
check the next field to search under.

I am little under experienced for this so any help would greatly be
appreciated.

For starts how do I tell access to search for what I selected by checkmark?
Or should I start elsewhere first?

Thanks Dustin

 

[Guest]

Ex. When I select phone number in the combo box. I would like it to fill in
the remaining textboxes with background information such as the dealer info,
how big of a machine he has and the options he selected.
This will help our people know what our customer has and also I would like
to be able to query the problems our customers had. Say we a certain size
machine had more problems than the others; well how many more?

Does this explain it?

Thanks Dustin

What do you mean by connect the required fields to the combo box?
--
Dave Hargis, Microsoft Access MVP

I see. But how do you connect the required fields to the combo box or am I
getting ahead of myself?

Thanks so much. Dustin

No, you will get the entry you selected.
Using a combo box, with the Auto Expand property set to Yes, the entries
will change as you type or you can scroll to the entry you want.
The After Update event doesn't fire until you have selected an entry.
Then your form's controls will be populated with the customer's record.
--
Dave Hargis, Microsoft Access MVP


:

Thanks for responding,

I have four things, Customer ID, First, Last, Business name(some customers
only gave me thier business name).

On either search what will happen, will the customer info be directly
entered in the fields or will multiple names pop up on a seperate screen to
select from. (Ex. More than one customer with the last name Smith.)

Thanks again for the response! Dustin


:

You need a form that will allow you to view and manipulate the customer record.
On this form you will need two Unbound combo boxes. You can use either of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

I need to be able look up a customer while I am on the phone with him. I was
thinking of using his phone number as the main search. But if that didn't
find him I would search his last name. I have seen where others put a check
in a checkbox next to the field you would like to search. This was in the
form heading I believe. And when it searched it poped up a list to pick from.
When you selected the right one it fills in all the fields in the form
heading but also the same fields down below in the detail. Then you could
type the info from the call in and then take the next call. You would hit the
clear button and it would clear the fields in the form heading and you would
check the next field to search under.

I am little under experienced for this so any help would greatly be
appreciated.

For starts how do I tell access to search for what I selected by checkmark?
Or should I start elsewhere first?

Thanks Dustin

 

[Guest]

The code I posted will make the record from the form's recordset the current
record. If there are data you want on the form that are not showing up then
either those data are not in the form's recordset or you don't have a control
on the form bound to every field you want to display.

If there are data you want to show that are not in the form's recordset,
then you need to either include the data in the recordset or create a subform
to display the data.
--
Dave Hargis, Microsoft Access MVP

Ex. When I select phone number in the combo box. I would like it to fill in
the remaining textboxes with background information such as the dealer info,
how big of a machine he has and the options he selected.
This will help our people know what our customer has and also I would like
to be able to query the problems our customers had. Say we a certain size
machine had more problems than the others; well how many more?

Does this explain it?

Thanks Dustin

What do you mean by connect the required fields to the combo box?
--
Dave Hargis, Microsoft Access MVP

I see. But how do you connect the required fields to the combo box or am I
getting ahead of myself?

Thanks so much. Dustin

:

No, you will get the entry you selected.
Using a combo box, with the Auto Expand property set to Yes, the entries
will change as you type or you can scroll to the entry you want.
The After Update event doesn't fire until you have selected an entry.
Then your form's controls will be populated with the customer's record.
--
Dave Hargis, Microsoft Access MVP


:

Thanks for responding,

I have four things, Customer ID, First, Last, Business name(some customers
only gave me thier business name).

On either search what will happen, will the customer info be directly
entered in the fields or will multiple names pop up on a seperate screen to
select from. (Ex. More than one customer with the last name Smith.)

Thanks again for the response! Dustin


:

You need a form that will allow you to view and manipulate the customer record.
On this form you will need two Unbound combo boxes. You can use either of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

I need to be able look up a customer while I am on the phone with him. I was
thinking of using his phone number as the main search. But if that didn't
find him I would search his last name. I have seen where others put a check
in a checkbox next to the field you would like to search. This was in the
form heading I believe. And when it searched it poped up a list to pick from.
When you selected the right one it fills in all the fields in the form
heading but also the same fields down below in the detail. Then you could
type the info from the call in and then take the next call. You would hit the
clear button and it would clear the fields in the form heading and you would
check the next field to search under.

I am little under experienced for this so any help would greatly be
appreciated.

For starts how do I tell access to search for what I selected by checkmark?
Or should I start elsewhere first?

Thanks Dustin

 

[Guest]

Ok, I will try it and get back to you.

Thanks, Dustin

The code I posted will make the record from the form's recordset the current
record. If there are data you want on the form that are not showing up then
either those data are not in the form's recordset or you don't have a control
on the form bound to every field you want to display.

If there are data you want to show that are not in the form's recordset,
then you need to either include the data in the recordset or create a subform
to display the data.
--
Dave Hargis, Microsoft Access MVP

Ex. When I select phone number in the combo box. I would like it to fill in
the remaining textboxes with background information such as the dealer info,
how big of a machine he has and the options he selected.
This will help our people know what our customer has and also I would like
to be able to query the problems our customers had. Say we a certain size
machine had more problems than the others; well how many more?

Does this explain it?

Thanks Dustin

What do you mean by connect the required fields to the combo box?
--
Dave Hargis, Microsoft Access MVP


:

I see. But how do you connect the required fields to the combo box or am I
getting ahead of myself?

Thanks so much. Dustin

:

No, you will get the entry you selected.
Using a combo box, with the Auto Expand property set to Yes, the entries
will change as you type or you can scroll to the entry you want.
The After Update event doesn't fire until you have selected an entry.
Then your form's controls will be populated with the customer's record.
--
Dave Hargis, Microsoft Access MVP


:

Thanks for responding,

I have four things, Customer ID, First, Last, Business name(some customers
only gave me thier business name).

On either search what will happen, will the customer info be directly
entered in the fields or will multiple names pop up on a seperate screen to
select from. (Ex. More than one customer with the last name Smith.)

Thanks again for the response! Dustin


:

You need a form that will allow you to view and manipulate the customer record.
On this form you will need two Unbound combo boxes. You can use either of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

I need to be able look up a customer while I am on the phone with him. I was
thinking of using his phone number as the main search. But if that didn't
find him I would search his last name. I have seen where others put a check
in a checkbox next to the field you would like to search. This was in the
form heading I believe. And when it searched it poped up a list to pick from.
When you selected the right one it fills in all the fields in the form
heading but also the same fields down below in the detail. Then you could
type the info from the call in and then take the next call. You would hit the
clear button and it would clear the fields in the form heading and you would
check the next field to search under.

I am little under experienced for this so any help would greatly be
appreciated.

For starts how do I tell access to search for what I selected by checkmark?
Or should I start elsewhere first?

Thanks Dustin

 

[Guest]

Well I got it to work. Thanks for your help so far.

I now that I have found the customer, I need to send his name into the
subform where I make a record of his problem. Is this possible?

Thanks Dustin

Ok, I will try it and get back to you.

Thanks, Dustin

The code I posted will make the record from the form's recordset the current
record. If there are data you want on the form that are not showing up then
either those data are not in the form's recordset or you don't have a control
on the form bound to every field you want to display.

If there are data you want to show that are not in the form's recordset,
then you need to either include the data in the recordset or create a subform
to display the data.
--
Dave Hargis, Microsoft Access MVP

Ex. When I select phone number in the combo box. I would like it to fill in
the remaining textboxes with background information such as the dealer info,
how big of a machine he has and the options he selected.
This will help our people know what our customer has and also I would like
to be able to query the problems our customers had. Say we a certain size
machine had more problems than the others; well how many more?

Does this explain it?

Thanks Dustin



:

What do you mean by connect the required fields to the combo box?
--
Dave Hargis, Microsoft Access MVP


:

I see. But how do you connect the required fields to the combo box or am I
getting ahead of myself?

Thanks so much. Dustin

:

No, you will get the entry you selected.
Using a combo box, with the Auto Expand property set to Yes, the entries
will change as you type or you can scroll to the entry you want.
The After Update event doesn't fire until you have selected an entry.
Then your form's controls will be populated with the customer's record.
--
Dave Hargis, Microsoft Access MVP


:

Thanks for responding,

I have four things, Customer ID, First, Last, Business name(some customers
only gave me thier business name).

On either search what will happen, will the customer info be directly
entered in the fields or will multiple names pop up on a seperate screen to
select from. (Ex. More than one customer with the last name Smith.)

Thanks again for the response! Dustin


:

You need a form that will allow you to view and manipulate the customer record.
On this form you will need two Unbound combo boxes. You can use either of
these to look up the customer. Below is a version to look up by phone
number. Before I show you the method for look up by customer, I need to know
if you have a customer ID or just the customer name?

The row source for the phone number combo should be a query based on the
customer table. Something like:
SELECT [PHONE_NUMBER] FROM tblCustomer;

To do the search, you use the After Update event of the combo box. (All
Names are Made Up - You will need to use real ones)

Private Sub cboPhone_AfterUPdate()

With Me.RecordsetClone
.FindFirst "[PHONE_NUMBER] = '" & Me.cboPhone & "'"
If .NoMatch Then
MsgBob "Customer Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

I need to be able look up a customer while I am on the phone with him. I was
thinking of using his phone number as the main search. But if that didn't
find him I would search his last name. I have seen where others put a check
in a checkbox next to the field you would like to search. This was in the
form heading I believe. And when it searched it poped up a list to pick from.
When you selected the right one it fills in all the fields in the form
heading but also the same fields down below in the detail. Then you could
type the info from the call in and then take the next call. You would hit the
clear button and it would clear the fields in the form heading and you would
check the next field to search under.

I am little under experienced for this so any help would greatly be
appreciated.

For starts how do I tell access to search for what I selected by checkmark?
Or should I start elsewhere first?

Thanks Dustin