Troubles with Combo Boxes and Arrow Keys

[Cowboy]

Hi.

So a brief explanation of the project:

A Gel database which holds an inventory of our company's current gel stock by manufacturer, including counts of different size gels and a description of the gel with primary keys being "Color Number" for each manufacturer's table.

I have built a custom switchboard to open forms to search the inventory based on company, and then also forms to update the inventory, again based on company.

Both the Search form and the Update form are designed the same, with the exception of added "+/-" buttons on the Update form. Both forms have a combo box with the manufacturer's "Color Number" for that manufacturer's table populating the combo box.

The forms currently work to search for a "Color Number" in the combo box by clicking the box and picking a number from the list or if the user types in a valid "Color Number" into the combo box. When the user selects the number the fields are all populated with the correct current counts for each gel size and and the description comes up.

Here is the problem:

I have coded the form to move to the next record based on the user's keystroke for the UP ARROW (previous record) and DOWN ARROW (next record).

The form does move to the next record, all the fields change to the next record's values, EXCEPT the combo box.

I have the DB zipped (about 600KB) if anyone could help me out, or if more explanation is need, I can go into more detail or post the code.

Thanks!

 

[YNWA]

Sounds like you need a refresh or something on the combo box to make it reset when you move to next record.

 

[Cowboy]

@YNWA:
That was my thought, though I cannot seem to find a way to refresh the value of the combo box. I believe it is a bound combo box (as when I look at the properties it states "Bound Column: 1".) I do not know how to refresh the value to reflect the next record when the form moves to the next record.

This is my first real project in Access and this silly interface issue is driving me nuts!
Any suggestions on how to refresh the combo box?

Thanks for the reply!

 

[vbaInet]

A bound combo box means in the CONTROL SOURCE property it refers to a field. If there's nothing in that property for the combo box then it's not bound.

The Bound Column property is simply the column in the combo box that will be used as the combo box's value, nothing to do with it being bound. Have a look in the help files for a more precise explanation.

 

[Cowboy]

@vbaInet
Ah! Well in that case, it is not bound as the Control Source is empty.
There is a row source that populates the combo box with the available Color Numbers from the manufacturer's table.

Any thoughts on how to update the combo box's displayed value when I move to the next record using the UP/DOWN ARROW keys?

Thanks for the reply!

 

[vbaInet]

You can use the SELECTED property of the combo box to set the value. You would need to do this in the Form's On Current event. You would also need to iterate through the values in the combo box until you find the matching ID. Something like this:

Dim i as integer

for i = 0 to combobox1.listcount - 1
    If combobox(0, i) = Me.ID_Field Then
         combobox.selected(i) = True
         exit for
    End if
next

 

[Cowboy]

@vbaInet

Okay, I think I understand your suggestion:

If I programmatically set the SELECTED value to the value of the newly "current" record (the record I have just moved to) then that new displayed value should show up.

I have some questions about the code you posted though:

The Me.ID_Field (highlighted in red below): Is that a field I need to replace with my own field name, and if so, what is that looking for, as the "Color Number" which is found in the combo box is the primary key of the table from which the data is being queried?

Also, the combo box in your code for the listcount has a numeric identifier and then I believe the two combo box references later are missing this identifier (see blue text in code below). Should these be the same identifiers?

Dim i as integer

for i = 0 [COLOR=Blue][COLOR=Black]to[/COLOR] combobox1[/COLOR].listcount - 1
    If [COLOR=Blue]combobox[/COLOR](0, i) = [COLOR=Red]Me.ID_Field[/COLOR] Then
         [COLOR=Blue]combobox[/COLOR].selected(i) = True
         exit for
    End if
next

Thank you for your time assisting me and the reply!

 

[vbaInet]

It's simply a comparison, checking the first column (which is most likely the Colour ID) of the combo box against the ID_Field present in the form's record source.

If green in combo box = green in colour field in form Then

If each colour is a unique identifier then simply use the field that hold's the Colour value.

Nothing missing in the code. That's what i is there for.

 

[Cowboy]

@vbaInet

Okay, so I guess my issue is that the only place the Color Number value shows up on the form is the combo box. So if I were to try to compare the Color Number of the current record to something, would I need to create a new field (maybe hidden) which would hold this value for the form?

Attached to this reply is an image illustrating the form and the issue.

Also, just because my head is having trouble wrapping around the combobox1 vs combobox bit of the code:

combobox1 and combobox both refer to the same combo box?

Thanks again, and my apologies for the apparent leaky sponge in my noggin!

 

[vbaInet]

Yes, you can do that.

That was a slight typo, it's the same combo box.

 

[Cowboy]

@vbaInet

One more question: does the code need to be placed inside the Form's On_Current event or can I put the code in where I make my On Key Down event for the combo box?

This is what I was going to try and then got a type mismatch...

Private Sub cboSearchColorNum_KeyDown(KeyCode As Integer, Shift As Integer)
   ' *******************************************************************
   ' *  Cannot get to change the value shown in the combo box when     *
   ' *  moving through records with keystrokes!                        *
   ' *******************************************************************
   
   
   ' Move through records utilizing keyboard strokes of the DOWN & UP ARROW keys.
   
   
   
    Me.KeyPreview = True                        ' Turn KeyPreview on so the following actions occur:
    
    Dim i As Integer
    
    Select Case KeyCode                         ' Do one of two actions depending on the key:
        Case 40
            DoCmd.GoToRecord , , acNext         ' If the down arrow key is pressed move to the next record.
            
            For i = 0 To cboSearchColorNum.ListCount - 1
                If cboSearchColorNum(0, i) = Me.txtHiddenColorNum Then
                    cboSearchColorNum.Selected(i) = True
                    Exit For
                End If
            Next
        Case 38
            DoCmd.GoToRecord , , acPrevious     ' If the up arrow key is pressed move to the previous record.
           
            For i = 0 To cboSearchColorNum.ListCount - 1
                If cboSearchColorNum(0, i) = Me.txtHiddenColorNum Then
                    cboSearchColorNum.Selected(i) = True
                    Exit For
                End If
            Next
    End Select
    
End Sub

Thanks!

 

[vbaInet]

Type mismatch in the comparison, try this:

                If cboSearchColorNum(0, i) & "" = Nz(Me.txtHiddenColorNum, "") Then

You can put it anywhere but I would be inclined to put it in the On Current event of the form.

 

[Cowboy]

@vbaInet

Still getting the type mismatch which is highlighting the new line you gave me to replace my If statement.

The type mismatch is stating:

If cboSearchColorNum (0,i) = <type mismatch>

Thanks for your continued help!

 

[vbaInet]

What I wrote is slightly different from what you wrote. Let me see that full line as you have it now.

 

[Cowboy]

The line I pasted over my code was copied from your post directly and is:

If cboSearchColorNum(0, i) & "" = Nz(Me.txtHiddenColorNum, "") Then

The message I typed about the <type mismatch> comes up in the debugger when the line above is highlighted yellow and I hover over the line with my mouse.

Hmm.

Thanks!

 

[vbaInet]

It was missing the & "" which was I thought you hadn't copied it.

Let's explicitly cast then:

If CStr(cboSearchColorNum(0, i) & "") = CStr(Nz(Me.txtHiddenColorNum, "")) Then

 

[Cowboy]

Still receiving the same type mismatch error (see attached screenshot).

This is confusing and I appreciate your help with this!

Thanks again.

 

[vbaInet]

When you were describing the problem it was missing & "" so I thought you typed it in.

Let's explicitly cast then:

If CStr(cboSearchColorNum(0, i) & "") = CStr(Nz(Me.txtHiddenColorNum, "")) Then

 

[Cowboy]

I'm a bit confused by the quoted post below, did you look at the screen shot I provided? You will see that the line is in the code exactly as you typed it to me in the previous post about explicitly casting. I hovered over the yellow line with the mouse and that is the message that pops up when I hover over "cboSearchColorNum(0,i) & "")" in the code.

When you were describing the problem it was missing & "" so I thought you typed it in.

Let's explicitly cast then:

If CStr(cboSearchColorNum(0, i) & "") = CStr(Nz(Me.txtHiddenColorNum, "")) Then

Thanks.

 

[vbaInet]

That was a duplicate post It was showing me your old message so I rewrote it.

I see the problem. This should do it:

If cboSearchColorNum[COLOR=Blue][B].Column[/B][/COLOR](0, i) & "" = Nz(Me.txtHiddenColorNum, "") Then