Question Is their a simple way to do this??? (1 Viewer)

[jransom1974]

I think I am getting comfused, and making this harder than what it actually is.

Here is what I am actually trying to do........

I have a database that I collect name number company info and automotive information from customers. What I need to do is... as the customers call back, I want to be able to search by there phone numbers ( i have stored from the previous call) and then populate certain fields. For ex; when they call, I ask them for their phone number. I want to be able to search for their phone number and when I click the record, all I want is to populate ONLY the first name, last name, phone, and company fields. The rest I will fill in manually because that information may change for each call. However the problem I have is.... there may be multiple numbers for some of the names.

Lets say the number (555)-758-9033 may be in the database five times but may have different names. This means that there are five different employees that have called us using that same number. Well, I need to have a way to click the name I need when they call in.

I know there are steps to take to perform this. And I think I am making it harder that what it actually is...

Any ideas on this issue..... or is there a better way to perform this task..


I am really having a time with this....

Thanks for ANY help!

 

[GinaWhipp]

How are you looking the records up now?

Are you using a combo box to search the phone numbers? What is the Primary Key and is it a hidden field in the combo box (if you are using one)?

 

[jonathanchye]

Sounds like you just need to create a query based on Phone No?

 

[GinaWhipp]

@jonathanchye

That won't work because there is more then one name attached to the phone number. You would still need a way to select the correct name.

 

[jonathanchye]

@jonathanchye

That won't work because there is more then one name attached to the phone number. You would still need a way to select the correct name.

Ah, right. Cascading combo boxes then? Combo one = Phone numbers which will filter the query for combo two which are the names?

 

[GinaWhipp]

Ah, right. Cascading combo boxes then? Combo one = Phone numbers which will filter the query for combo two which are the names?

That is why I asking about a Combo Box and PK field. That way no cascading combo needed just one combo box.

 

[jransom1974]

I have created a combobox on my form with the following code;

Private Sub cboNames_AfterUpdate()
If Len(Me![cboNames].Column(1)) > 0 Then Me.txtPhone = Me![cboNames].Column(1) Else Me.txtPhone = " "
If Len(Me![cboNames].Column(3)) > 0 Then Me.txtFirstName = Me![cboNames].Column(3) Else Me.txtFirstName = " "
If Len(Me![cboNames].Column(2)) > 0 Then Me.txtLastName = Me![cboNames].Column(2) Else Me.txtLastName = " "
If Len(Me![cboNames].Column(4)) > 0 Then Me.txtProductFrom = Me![cboNames].Column(4) Else Me.txtProductFrom = " "
End Sub

This works great my allowing me to search by phone number and then it populates the firstname, lastname, phone, companyname fields. I only have these firlds populated because the rest needs to be typed in manually each time I get a call because that information will change from time to time based on the call. However, if there are multipe phone numbers (and there are many times) I cant choose the correct person I need. This is a problem.

That is why i really need to be able to search by the phone number by some means of (filter). So that way whenever I type in "search for": (555)495-5896 it will show me ALL the records for (555)7495-5896.



So..... any ideas of a solution <OR> would you receommend doing this in a completely different way. I thought for a moment that I was making this harder on myself than what it actually was.

thank you for any help

 

[jransom1974]

I am using the ID as the primary key for this table, I need more than one table? I am kinda new to this aspect of Access.

 

[GinaWhipp]

Is the first column in cboNames the ID AND is that ID part of the RecordSource of the form?

 

[jransom1974]

Gina-

I am not sure I quite understand what you mean. The only table I have shows the ID as the PK.

 

[GinaWhipp]

Okay, let's do this what are the columns in the Combo Box?

 

[jransom1974]

I am one table by which I am getting this info for my combobox

[COLOR=darkred]Private Sub cboNames_AfterUpdate()
If Len(Me![cboNames].Column(1)) > 0 Then Me.txtPhone = Me![cboNames].Column(1) Else Me.txtPhone = " "
If Len(Me![cboNames].Column(3)) > 0 Then Me.txtFirstName = Me![cboNames].Column(3) Else Me.txtFirstName = " "
If Len(Me![cboNames].Column(2)) > 0 Then Me.txtLastName = Me![cboNames].Column(2) Else Me.txtLastName = " "
If Len(Me![cboNames].Column(4)) > 0 Then Me.txtProductFrom = Me![cboNames].Column(4) Else Me.txtProductFrom = " "
End Sub[/COLOR]

And like I said, this code is actually working fine for the moment, However, is there anyways to have the phone numbers so they will run in accending order. This will give me a little bit of help.

thanks again!

This is a great forum post!!!!

 

[GinaWhipp]

Sort them in the Row Source of the query... You don't have to show the column you sort on if you don't want the column a part of your combo box, just uncheck the show box.

 

[HGMonaro]

I have an application that does something similar. It has a ComboBox that displays valid suppliers (your phone numbers) powered by a select query on a Suppliers table. Once you select the supplier you want, a listbox is populated with the available materials (your Names). The listbox is powered by a Select query based on the ComboBox selection (Value). I then select a material to do something with by double-clicking on the list row. This doesn't allow for new suppliers (phone numbers) to be added ad-hoc, you'd need to cater for that.

 

[jransom1974]

I have an application that does something similar. It has a ComboBox that displays valid suppliers (your phone numbers) powered by a select query on a Suppliers table. Once you select the supplier you want, a listbox is populated with the available materials (your Names). The listbox is powered by a Select query based on the ComboBox selection (Value). I then select a material to do something with by double-clicking on the list row. This doesn't allow for new suppliers (phone numbers) to be added ad-hoc, you'd need to cater for that.

Thats sounds great! Any way I could see a working example of your work? I would also like to post my databse and see if your code would work for an application like what I am working on....

Hope to hear back from ya!

Very cool!

 

[GinaWhipp]

This is still sitting in my Inbox... Has this been resolved?