Combobox List Multiple Options of Contact Name (1 Viewer)

[shlincoln]

Hi all,

I'm only new here and my access skills are not all that crash hot.

I'm using access 2007 and have been upgrading an old Access 95 database - has been many challenges, but it's coming along pretty well. I am also making some modifications to expand the capabilities of the database.

Where I am stuck is that I have a form that we will use to generate letters from Access into a Word template. I have most of this running fine so far.

The form shows a summary of the project details and also lets you select from a list of contacts from the client assigned to the project.

I have a list box that will show a list of the contact names and this works fine - but what I was hoping to do was create a cascading list from this that would show options for how to address the selected person.

For example, say the client for the selected project is "Disney Land"
This client has the following contacts:
Donald Duck
Daisy Duck
Mickey Mouse

These names are stored in a contacts table and are identified by a ContactID - this is what is used to filter down the names on form load. Each name is split into First/Last/Middle and has a salutation assigned to it - Mr/Miss/Mrs etc..

If I select Donald Duck, I would like to have a combobox that will show the following:

Dear Donald
Dear Mr. Duck
Dear Donald Duck
Dear Sirs

The problem that I'm having is that I can't figure out how to generate these options from the one name selection - I can't work out a query to return this info as it would list the options all in one line - i.e. Dear Donald | Dear Mr. Duck |Dear Donald Duck | Dear Sirs would all appear as in the one row of the combobox in a query.

Any thoughts/tips on how to take the one contact from the listbox and generate naming opitons of:

[First]
[First] & " " & [Last]
[Prefix] & " " & [Last]
"Sirs"
Sorry if this has been covered, but I'm even having trouble coming up with a search term to help with this one that is not standard combobox info.

Thanks for any help,

Shlincoln

 

[Isskint]

the only way i can think, is to supply the values directly via VBA, rather than as a table or query. You could put the code at the Form.Current event (possibly the comboboxes ButtonDown.event). It would look something like;

'Empty the current list
While ComboBox.ListCount <> 0
    ComboBox.RemoveItem (0)
Wend
 
Dim strSalutation, strFirstName, strLastName As String
'read the table values into the string variables
 
strSalutation = DLookup("Salutation", "TableContacts", "ContactID=" & Me.ContatctID)
strFirstName = DLookup("FirstName", "TableContacts", "ContactID=" & Me.ContatctID)
strLastName = DLookup("LastName", "TableContacts", "ContactID=" & Me.ContatctID)
 
'add the greetings to the combobox
Me.ComboBox.AddItem "Dear " & strFirstName
Me.ComboBox.AddItem "Dear " & strSalutation & " " & strLastName
Me.ComboBox.AddItem "Dear " & strFirstName & " " & strLastName
Me.ComboBox.AddItem "Dear sirs"

 

[shlincoln]

Thanks heaps for your input. I had been trying to figure out some VBA that would do this. I shall work on your outline and hopefully this will bring the results!

Thanks again for the solution - I'll post back in once I've had a chance to add the code.

 

[shlincoln]

Thanks again for the pointer Isskint.

This way has worked a treat - just made a few changes so that the box draws the data from the listbox showing all client contacts rather than the DLookup as I had a few issues with that. I also added the code to the after update event for the list of contact names - so if you select one name and then change it, the list updates after each change. All working great.

Thanks again.

Shlincoln

 

[Isskint]

good to hear

 

[shlincoln]

If it would not be pushing things on this one, say I had a field in my combo box that showed the preferred salutation/prefix for the contact selected - is it possible to add a conditional default value to the combobox? I started trying to code something up, but I got lost

So what I want to try to do is that on my form, Me.cboContactNameComp.Column(3) has as entry "LastName" - which tells the combobox line 2 is the default

or

If Me.cboContactNameComp.Column(3) = FirstName then combobox default would be Line1

Also, for anyone with a similar scenario and issue, the code that I ended up using to get the box to work is as follows:

Private Sub cboContactNameComp_AfterUpdate()

'Empty the current list
While cboSalutation.ListCount <> 0
cboSalutation.RemoveItem (0)
Wend

Dim strPrefix As String
Dim strLastName As String
Dim strFirstName As String

strPrefix = Me.cboContactNameComp.Column(0)
strFirstName = Me.cboContactNameComp.Column(1)
strLastName = Me.cboContactNameComp.Column(2)

'add the greetings to the combobox
Me.cboSalutation.AddItem "Dear " & strFirstName
Me.cboSalutation.AddItem "Dear " & strPrefix & " " & strLastName
Me.cboSalutation.AddItem "Dear " & strFirstName & " " & strLastName
Me.cboSalutation.AddItem "Dear Sirs"
End Sub

 

[Isskint]

You could use the ListIndex property to indicate this. You would need a select case to identify which row of data.

Select Case DefaultSalutation
Case A
Me.cboSalutation.Listindex = 0
Case B
Me.cboSalutation.Listindex = 1
Case C
Me.cboSalutation.Listindex = 2
Case D
Me.cboSalutation.Listindex = 3
End Select

Oprionally, if you save the default as a number, then you could just set it directly

Me.cboSalutation.Listindex=DefaultSalutation - dont forget listindex starts at 0 so either have the numbers start at 0 or deduct 1.