Combos can a be serious hassle!

[JohnGunn]

Man, combo boxes can be really tricky. What I want to do is not quite "cascading"--I've checked out previous posts in that area. Here's the challenge, which I'm sure many of you have already faced:

Using a form based, for example, on Northwind's Customers table, you want to place two combo boxes on the form. One is connected with Customers.CompanyName the other with Customers.ContactName.

When you open the either combo you see ALL the 91 records in the customers table. We aren't looking to use one combo to display a subset of records based on a selection in the other. Instead, one can use either combo to select the record you want to edit.

I real life you might want one combo to show Customers by state, city and name. The other might show them just by name. If you use combo "A" to select a record, combo "B" needs to be refreshed so it doesn't confuse the user by showing field(s) from some previous record. I.e. each needs to refresh/requery the other.

When I try to get this to work, there's some missing piece of the puzzle that prevents each combo from being able to select a record to work on, some binding issue with controlsources, boundcolumns and the form's recordsource that I'm missing.

I figured that each combo could be unbound and in the OnChange method I could put some code to Seek the selected record and then refresh the form. So far I haven't been able to get that to work.

I have tried creating a query for the form and using it as the recordsource for each combo. Still nogo.

If it were my native language (MS Vis FoxPro) I could have done this in my sleep, but in Access I have not yet hit the right method. I hope that in a minute or two one of you folks could save me hours!

 

[John.Woody]

Perhaps use the after update property of combo1 and go for

Me.combo2.requery

This assumes that the criteria for combo2 is affected by combo1

HTH
John

 

[JohnGunn]

My impression is that if combo2's contents were linked to or based on combo1's, this suggestion would work, but that's the cascading combo scenario.

What I believe I'm asking for is two or three independent combo's one can use for selecting a record, and a technique for (a) moving the form's recordsource to the chosen record and (b) synchronizing the visible text portion of the other 2 combos also to reflect the current choice.

This should be easy, seems to me, but I have a lot of hours (as a newbie) trying to sort it out. Even searched 5 or 6 Access books in BooksAMillion, and none really goes into combo boxes in depth...

 

[David R]

Did you try it?

While I haven't tried it, my impression is that JohnWoody's suggestion would 'resync' the other combos so that they displayed information for the current record as well.

However - I confess that I do not understand what it is you're doing. You have three (or however many) combos that can be used to find a record. Presumably you've got the Finding of that record down pat - you just want the other combos to give equivalent results. But if they all point to the same record, why do you have three combos? Or is it like a LastName, a City, and a JobPostion combo, and you just jump to the first record that matches?

And would the simplest answer be to blank all three unbound combos after the search is completed? Or blank the other two, at the least....since they're search boxes, showing a blank shouldn't be a problem. These are unbound fields, right??

 

[JohnGunn]

Got it working. Thanks for your hints. Here's how I ended up getting three different combo boxes working as independent record selectors at the top of a data entry form:

1) put 3 unbound combo boxes on the form. One lists Team numbers, the next lists teams alphabetically by name, the third lists teams by state, city and name. If you know anything about the team you can find it with one of these 3 combos.

1a) the recordsource of each combo looks roughly like this:

SELECT [TEname], [TEteamId] FROM TE WHERE TEdtapprove is not null ORDER BY [TEname];

...Bound Column is set to 0 and the column width of the second column is set to 0. We use it in step to below.

2) supply Click event code for each combo, with code like this:

Private Sub cboName_Change()
Dim intTemp As Integer, intTemp2 As Integer, strSql As String
' since Bound column is 0 this returns the index of the selection
intTemp = Me.cboName
' retrieve the value of TeamID from 2nd col of combo
intTemp2 = Me.cboName.Column(1, intTemp)
' redo the form's record source based on the selection.
' selects a single record
strSql = "SELECT [team].[teamId], [team].[password], [team].[active], [TE].[TEname]" + _
"FROM team LEFT JOIN TE ON [team].[teamId]=[TE].[TEteamId] " + _
"WHERE [team].[active] And [team].[teamId] = " & CStr(intTemp2) & " ; "
Me.RecordSource = strSql

' now blank out the other two combos since they are unbound.
' if this step is skipped, they may show info from a previous
' selection, which is VERY distracting
Dim cboID As ComboBox
Dim cboLocation As ComboBox
Me.cboID = Null
Me.cboLocation = " "
End Sub

Thanks for your feedback on this problem. I hope this solution helps anyone else who cares to try this way of presenting non-cascaded multiple record selection options to the user.

BTW if someone sees that I have done this the hard way (changing the for's record source on the fly each time) I'd like to here a better way. Maybe a query whose TeamID is taken from a global variable? Unsure what I'm doing since this is my first 3 weeks with Access(!)

 

[David R]

Looks good to me..

I've used a similar effect before.

However I think it is unnecessary to Dim(ension) the Comboboxes there at the end. Simply setting Me.ComboName = Null should suffice, though I've been wrong before...