Troubles with Combo Boxes and Arrow Keys

[Cowboy]

@vbaInet
Okay so that fixed the type mismatch error, but the code does not change the current displayed record in the combo box.

Thanks,

Ben

 

[vbaInet]

Put an msgbox inside the IF condition block to see if it gets evaluated. That is:

If this = that then
[COLOR=Red][B]    Msgbox "i got here"[/B][/COLOR]
end if

 

[Cowboy]

@vbaInet

Thanks for the debug tip! I'll give it a try later today, hopefully. (All sorts of meetings at work all day ).

Hopefully it will pinpoint where something is missing!

Thanks again for all your help!

 

[vbaInet]

Let us know how you get on.

 

[Cowboy]

@vbaInet

Okay, the message box pops up with the message stating that it evaluated the comparison, however setting the new selected item does not show the new item in the combo box.

In addition, if I try to move to the next record after that, it says I can't go to the next record and highlights (shown in red text below) my DoCmd.GoToRecord , , acNext statement... (or the DoCmd.GoToRecord , , acPrevious statement when going up through the list).

Error message:
Run-time error '2105':

You can't go to the specified record.

So now I'm even more confused.

Here is the code as I have it which produces the error above:

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
            [B][COLOR=Red]DoCmd.GoToRecord , , acNext[/COLOR][/B]         ' If the down arrow key is pressed move to the next record.
            
            For i = 0 To cboSearchColorNum.ListCount - 1
                If CStr(cboSearchColorNum.Column(0, i) & "") = CStr(Nz(Me.txtHiddenColorNum, "")) Then
                    MsgBox "I got here"
                    cboSearchColorNum.Selected(i) = True
                    Exit For
                End If
            Next
        Case 38
            [COLOR=Red][B]DoCmd.GoToRecord , , acPrevious[/B][/COLOR]     ' If the up arrow key is pressed move to the previous record.
           
            For i = 0 To cboSearchColorNum.ListCount - 1
                If CStr(cboSearchColorNum.Column(0, i) & "") = CStr(Nz(Me.txtHiddenColorNum, "")) Then
                    MsgBox "I got here"
                    cboSearchColorNum.Selected(i) = True
                    Exit For
                End If
            Next
    End Select
    
End Sub

Thanks for the continued assistance!

 

[vbaInet]

The error message you're getting has to do with the fact that there's no next or previous record.

Let's see your db

 

[Cowboy]

Okay, so here is the database (attached) in a zip file.

I haven't coded all the forms yet, so some of them don't work the same - mainly because I'm trying to get ONE form to work the way I want, and will then change the rest, however you'll see that the code on one form is not dependent on the others so it should not be the issue.

I purposely choose a record from the combo box that has at least five records before and after it to test the keystroke navigation - and it doesn't let me go back more than two or forward more than two...so I know it can't be because there is no record available.

Anyway, when you open the DB - the form I am talking about is called "EA Gam Search".

Thanks so much!

 

[vbaInet]

The problem has been resolved. I also fixed some other problems you had See attached and go through the code.

Notice I have created two new tables and one query, nothing to do with the problem. Look at those and see what has been done. Unfortunately, your database is not normalised because you have three tables with the same fields and repetition of data. Those three tables can be merged into one and that's what I've done in tblRecords.

You would need to read these to understand what normalisation is:

http://support.microsoft.com/kb/100139
http://r937.com/relational.html

There's more information on this in the Theory and Practice of Database Design section of the forum.

 

[Cowboy]

@vbaInet

Well I have not yet had a chance to take a look at the DB, but I appreciate your time with it.

I have been reading the articles you posted and I will call this thread closed at this time.

Don't be surprised if I show up in the Theory & Practice of DB Design threads trying to fully understand better design practice! (It has been about 6 years since I first learned about relational DBs and apparently lost that information!)

Thank you again!

 

[vbaInet]

You're welcome.

Just let us know how it went when you get time to check it out.

 

[Cowboy]

Thanks for taking a look at the DB a while back. I just got back into it and took a look at your new tables you made. The tblRecords table has a primary key of color number, but in the new table - if I created a table of all records, rather than the current way (table for each manufacturer's records) I will have duplicate color numbers.

I believe I can create a unique primary key by combining the manufacturer ID and color number. Is this correct?

Also, you gave me some material to read about normalization. Would the two tables you created be normalized to the 2nd form?

Thanks so much for your help with this again! (I'm trying to learn...)