Combo Box is Unselectable

[Galen Quiring]

I am experienced with VB.Net but am just learning to code in VBA with MS Access 2007. I have an unbound listbox whose items are selectable by both the user & by code. The next unbound listbox I created would not let the user select an item but items could be selected by code (all relevant properties seem to be set the same). Then I built a unbound combo box but can not select any item by either code or by the user.

Combo1.Selected(x) = True



but

MsgBox "Selected = " & Str(Combo1.Selected(x)) returns 0

and

MsgBox "Items Selected = " & Str(combo1.ItemsSelected.Count) returns 0

I seem to be going backwards! What am I doing wrong?

Galen

[June7]

The third control is a combobox not listbox? The combobox is allowing multiple selection?

Do you want to provide project for analysis?

[Galen Quiring]

Yes - While the first two were Listboxes, the last one is a Combobox

I do not need Multi-Select but I have found no way to set this feature. Unlike the Listboxes, there is no Mult-Select Property either in DesignView nor in code.

I am willing to submitt my project for analysis but I am new to the forum and will need to be told how.

Galen

[boblarson]

Yes - While the first two were Listboxes, the last one is a Combobox

I do not need Multi-Select but I have found no way to set this feature. Unlike the Listboxes, there is no Mult-Select Property either in DesignView nor in code.

I am willing to submitt my project for analysis but I am new to the forum and will need to be told how.

Galen

Here are instructions for uploading to the website. This link actually goes to another Access forum but the instructions for uploading are the same because it uses the same VBulletin software that this site uses.
http://www.access-programmers.co.uk/...d.php?t=140587

[Galen Quiring]

Attached is my Project for review.

This is a database to help track the details, status & progress of the many Engineering Jobs that my firm's Engineers are involved with. Only a few jobs have been loaded in this database at this time.

The initial form is a summary form "JobSumaryForm" that by default will display a list of all jobs or by using a command line parameter in the pattern of /cmd "ENGR;engineer_name" (use /cmd "ENGR;QUIRING" or cmd/ "ENGR;COWICK" in this case) you would see only those jobs for that Engineer. My intent was that one could also select any Engineer's Jobs by opening the "EngineerCmb" combobox and selecting the Engineer desired. This is where everything falls apart since I cannot select any engineer in this combo either in code nor by user input.

Any help would be much appreciated - Galen


Also note the listbox "SumaryLst" that displays the sumary info can be selected by code (see the Up & Down buttons below) but not by direct user input.

[June7]

I just did a quick review of project. Have you run Debug>Compile? It will error on these lines:
SaveSiteCmd.SetFocus = True
SaveCOCmd.SetFocus = True

I wonder why you are loading row sources of list and combo boxes with VBA code and not using the RowSource property. Even if want to modify the list with VBA, change the RowSource property SQL statement, no need for looping through recordset and using AddItem method.

You use Command() function to detect if project is opened by user or developer? Interesting, what method do you use to open the project? See http://msdn.microsoft.com/en-us/libr...(v=vs.80).aspx

Using names as keys or saving names instead of a unique ID is risky. I see you handle this by including initial (JMEYER, TMEYER). This does make sorting and filtering by name complicated. Should be consistent with ID. Use initial for one, use for all. Then if want to sort by last name, can code to drop the initial.

[Galen Quiring]

Is there a better way to change which control gets the initial focus when a form is loaded?

As for doing things in code, everytime I tried to use a bond control it either didn't work the way I wanted it to or it limited my future flexibility and I would have to do it in code anyway. This way worked and after 20-30 years of procedural coding I understood it better. I have yet to find an understandable code example to do what you sugested.

I guess my motto is "Anything the MS Office can do - I can do better! (if someone would give me a hardcopy language reference manual)

Galen

[June7]

I use TabIndex and TabStop properties. TabIndex of 0 will be first control to get focus when form is opened. I also use the SetFocus in VBA when want to override the TabIndex ordering.

I just discovered you need to set the form AllowEdits property to Yes to allow combo and list box selection.

Nothing happens after selection in EngineerCmb. Need code in AfterUpdate event?

Here is one example from my project for modifying a combobox RowSource:

    If .lbxTasks = "Foamed Asphalt" Then
        .cbxLabNum.RowSource = "SELECT LABNUM FROM 620 ORDER BY LABNUM DESC;"
    Else
        .cbxLabNum.RowSource = "SELECT LABNUM FROM 610 ORDER BY LABNUM DESC;"
    End If

[Galen Quiring]

Thanks - now the combo box and the list are selectable - I focused on each control and failed to check the form wide setting. Of course now I need to get the code to act on a change to combo box selection.

I still can't seem to set the initial selected entry for the combo box. In other words:

EngineerCmb.Selected(i) = True

seems to have no effect and the form opens with the combo box being blank.

I intend to provide each engineer with a custom short cut to open the application. Mine looks like this.

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "Q:\ENG\Transport\Quiring\Test\JobTracDB.accdb " /cmd "ENGR;QUIRING"

Using names as an index was a simple solution until I realized that we had two engineers with the same last name. I will have to think more about this.

Galen

[June7]

Do this:
Set the BoundColumn property of EngineerCmb to 1 (probably should do same for SumaryLst).

Modify the code to:

                For i = 0 To ect - 1
                    If EngineerCmb.Column(0, i) = Cmd2 Then
                        MsgBox "Match Found at Row " & i
                        EngineerCmb = EngineerCmb.ItemData(i)
                        Exit For
                    End If
                Next i

[Galen Quiring]

Thanks everyone - most everything is working now and I'm cleaning up some small details.

Galen - Happy New Year!