Combo Box Problem 1

[JimUK]

I am trying to get two comboboxes working that are dependant on each other, on eis called cboSelTitle and the second cboSelSys.

Rowsource for cboSelTitle:
SELECT DISTINCT PMI.number, PMI.Title FROM PMI ORDER BY PMI.number

Private SUB cboSelTitle_AfterUpdate()

Me. Title.SetFocus
DoCmd.FIndRecord Me.cboSelTitle

End Sub


Rowsource for cbo SelSys:
SELECT DISTINCT PMI.system FROM PMI WHERE (((PMI.title) LIKE [Forms]![PMI_Signoff_Test1]![cboSelTitle]));

Private Sub cbo SelSys_AfterUpdate()

Dim strSearch As String

strSearch = "Title = " & CHR$(34) & Me![cboTitle] & Chr$(34)
Me.RecordSetClone.FindFirst strSearch
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.System.SetFocus
DoCmd.FindRecord Me.cboSelTitle
End Sub


I am using the following in the On Enter event in both comboboxes:

=ctlRequery()

Which calls the below function:

Public Function ctlRequery()

On Error GoTo Err_ctlRequery

Dim ctl As Control

Set ctl = Screen.ActiveControl
ctl.Requery

Exit Function

Err_ctlRequery:
Exit Function
End Function

WHat is happening is that when I choose the Title combo it comes upi fine and shows records for that Title. Then when I go to second combo "cboSelSys" it shows the selections (in the combobox) for what was chosen in the title, but when selected it doesn't show results? I know I'm missing something silly here. Could someone steer me right?

Jim

 

[scriverb]

Hello JimUK, I can see if I can help here. When you pick from the first combo(cboSelTitle) the user sees the Title being selected but the combo's Bound column is What? 1 or 2? Both the FindRecord command the the query for the second combo are making a direct reference to the first combobox. If the first combobox is bound to the title then that's probably okay. But, if it is bound to the first column which is default when created this may be causing you your problem with the second pick. A direct reference to the combo would be using the PMI.number to compare to the titles.

Post back with a little more information and we can probably figure this out for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JimUK]

Hi Bob,

Actually when the user selects the title combobox they see the PIM number and the Title but believe the combo is bound to the title. This combo is working ok.

As for the 1st combo being bound to the second combo, I thought that's what I was doing in the cboSelSys RowSource (refering back to the Title combo?? I think this is where I'm getting lost and also direct referencing is making the title combo fill fields with the first record with that title (there will be more per title). I'm guessing that I don't have to do the FindRecord for first combo but, am unsure on how to get the fields to populate on form after selection of 2nd combo (cboSelSys)?
I'm on another computer at the moment, let me get back to you with more info.

Jim

 

[scriverb]

If all you want to do is pick from a list of titles in the first combo and have the second combo list out all those titles that are related by this title string, then you are doing things that are not necessary.

Why don't you explain your table sructure and relationship and what you expect the comboboxes to do. don't talk in data language but just describe what is on the main form and what you want the comboboxes to perform like. And, finally after the pick from the second combobox just what is to happen.

Then mr. dense here in Michigan will understand your problem and I can better help you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JimUK]

Ok, Mr. Denser here in the UK will try to explain ;D
All I'm trying to do is have the user select a PMI Title in first box and the system that they will do that PMI on in the second combo box. Example: in first combo he will chose - "Oil the fratistat pulley" then in the second combobox he will only have the choice of systems that have a fratistat, i.e. system1, system2, system3, etc. When the system is chosen it will then fill fields in the detail section of the form with title, system, location, serial num, due date, date last done, etc.

I have chosen to have only 2 combo's instead of 3 as most of the people know the name of the PMI and not neccesarily the number, so I'm displaying the number and title in 1st combo, but the bound column is column 2 with the title.

 

[scriverb]

Is the main form bound to a table? I ask this as after you make the pic from the second combo do we just have to perform a find record and let that process handle the filling of the controls or do we need to do this through VBA code.

My next question is in the first combo how many titles are there. Do you need this to search down alphabetically through the titles as they type them in. We can do this by modifying the query a little for the first combobox. It would still show the Title and the number but but the alpha auto search during entry sure helps. Right now they must scroll down to the title but cannot type in the title as the ID number is the first column and that is the one being searched. What to change that?

Post back and I will try to set you up.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JimUK]

Bob, yes the main form is bound to the PMI table so after picking the second combo I just want to fill out the form with that particular record info.

There are about 40 titles each with a number, some have the same PMI number the only difference being it will have it's own system associated with it. As I have it now, I'm ordering by PMI number so it's starting with lowest number and incrementing up.

That would be great if they could type in the title instead of having to scroll down too.

Jim

 

[scriverb]

Okay then let's change the SQL RowSource for the first combobox(cboSelTitle):

SELECT DISTINCT PMI.Title, PMI.number FROM PMI ORDER BY PMI.Title;

Set the ColumnCount: 2, BoundColumn: 1, ColumnWidths: 3;1 , List Width: 4

This will just change the order of viewing but still show the PMI number and sort by the Title. Now the user can just start typing the Title and the combo will search alphabetically through the list and help to find the correct one. The user after entering a few characters can then use the mouse to click and make the selection.

Now in the first combobox's AfterUpdate event procedure put the following code:

Me.[cbo SelSys].Requery
Me.[cbo SelSys].SetFocus
Me.[cbo SelSys].DropDown

Here is the SQL for the Row Source property for the second combobox(cbo SelSys):

SELECT DISTINCT PMI.system FROM PMI WHERE (((PMI.Number) = Forms![PMI_Signoff_Test1]![cboSelTitle].Column(1)));

You will see that I used the .column property of the first combobox and made a direct criteria expession comaring to the PMI.Number. I am assuming that there is a single PMI number in the table PMI that we can use. It is better to use the PMI number than the title.

Before we go any further with the AfterUpdate of the selection in the second combobox let's make sure that all is well up to this point. Take a look and let me know how this works so far. Don't expect the afterupdate to work now as we have changed a few things.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JimUK]

Okay, 1st combo is working fine and typing in works great thanks!
The second one comes up blank though. Is this because we don't have an AfterUpdate yet (I took mine out).

Jim

 

[scriverb]

Is the second combo coming up with no records in the dropdown? If so then please give me a description of the file PMI. You see the table PMI should have records that can be selected with our SQL. If not then we have a problem with the criteria of the SQL. My last posting modified it to match on the ID field in the table to the ID column in the first combobox.

Post back with what you find.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JimUK]

Okay, got 2nd combo figured out and it works fine now. All possible selections automatically drop down when selecting title in first. Had to change cboSelSys Rowsource to the following:

SELECT DISTINCT PMI.system FROM PMI WHERE (((PMI.title) = [Forms]![PMI_Signoff_Test1]![cboSelTitle]));

SO, how do we work the AfterUpdate on cboSelSys (2nd combo box)?


Jim

 

[scriverb]

Use the following VBA code in the second combo's AfterUpdate event procedure:

Dim rs As DAO.Recordset, strSearch as String
Set rs = Me.RecordsetClone
strSearch = "[system] = '" & Me![cbo SelSys] & "'"
rs.FindFirst strSearch
If rs.NoMatch Then
    MsgBox "Record not found"
Else
    Me.Bookmark = rs.Bookmark
End If
rs.CLOSE

Let me know how this works and we can finish up with the cleanup of the combobox's values after the final selection.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Jim, I just spotted a FAQ from another thread that gives a couple of other code techniques to do the same as above. just for you info:

faq702-4398

But, what I provided should work for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JimUK]

Bob,

It works great the first time but then screws up after that. Let me try to explain:
When Title and System are chosen the second time and there after (until form reopened) it seems to display things right in the comboboxes but FindFirst seems to go to the first "system" that it finds in the table and displays that system on form, but of course its the wrong title. Any ideas?


Jim

 

[JimUK]

Update to my last entry.
After further testing, the only time the combo's get it right is when there is only one possible system for the title. Some of my PMI Titles will have up to 5 possible systems, some may have only one system. The only one that will show up right is one that has only one possible system. All other times when it screws up it goes to right system, but it just happens to be the first instance of that system in the table that it comes to and it will obviously be the wrong title. COmbo's are both showing what they are suppose to, so it sounds like maybe the last AfterUpdate is not doing something right in cboSelSys??

 

[scriverb]

Hello JimUK: I am back now. Let's try using both combobox's values to select the correct record then. Let's change the search string in the code to the following. Now I am not sure which column the first combo is bound to but we want to compare to the Title column. If the Bound Column is 1 then use the following. If the Bound column is 2 then use the .Column(0) reference.

strSearch = "[system] = '" & Me![cbo SelSys] & "' and [Title] = '" & Me![cboSelTitle] & "'"

Let me know how this works out.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JimUK]

Bob,

That's the ticket, works like a charm! Thanks for all your help as I got a little twisted around the axle as they say.


Jim

 

[scriverb]

Great Jim. Glad to be of assistance to you. Good luck.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]