Problem with Combobox and linked tables

[Dave F]

I have an Access mdb file that is linked to a SQL Server database.

I have two problems with combo boxes that link to tables in the SQL Server
database.

1. One combo box has 200 entries. When the form loads, the cbo is not
fully loaded. For eaxmple, the last 100 records are not available in the
cbo until the user scrolls to them. When the user scrolls through the cbo
sorted alphabetically, it appears that all records through "m" are available
but that no records from "o" through "z" exist. If they enter "z" in the
cbo, they are taken to the first "z" record but if they scroll, they must
continue to scroll to the bottom of the list several times to get to "z."

How can I ensure that the cbo is fully loaded when the form loads?

2. The form has "find record" VBA code (see below) to locate a particular
client record based on the user's selection in the cbo box. The "find
record" cbo works fine except for the last two records. If either of those
records are selected, the first record (rather than the selected record) is
loaded into the form.

What could account for such unexpected behavior?

Here is the VBA code behind the cbo "find record" control:

1. Move to selected record:

Private Sub cboClientID_AfterUpdate()

On Error GoTo ErrorHandler

'Find a client

Dim rs As Object
Set rs = Me.Recordset.Clone

rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboClientID], 0))

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number _
& ": Description: " & Err.Description
Resume ErrorHandlerExit:

End Sub



2. Synch cbo with current record

Private Sub Form_Current()

On Error GoTo ErrorHandler

'Make the client in the cbo the current client

If cboClientID.Column(0) <> txtClientID Then
cboClientID.Value = ClientID
End If

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number _
& ": Description: " & Err.Description
Resume ErrorHandlerExit:

End Sub

 

[sparkane]

I have an Access mdb file that is linked to a SQL Server database.

I have two problems with combo boxes that link to tables in the SQL Server
database.

1. One combo box has 200 entries. When the form loads, the cbo is not
fully loaded. For eaxmple, the last 100 records are not available in the
cbo until the user scrolls to them. When the user scrolls through the cbo
sorted alphabetically, it appears that all records through "m" are available
but that no records from "o" through "z" exist. If they enter "z" in the
cbo, they are taken to the first "z" record but if they scroll, they must
continue to scroll to the bottom of the list several times to get to "z."

How can I ensure that the cbo is fully loaded when the form loads?

Hi Dave. Without going through a lot of troubleshooting to see where
the problem actually lies, one approach could be to do programmatically,
before the user uses the cbx, whatever the user would have to do to
restore the missing rest of the list. That sounds like a pain to me,
but if it works for the user, in theory it should work for the code.

Alternately, if you're interested in solving this problem with code, you
could see if reducing the number of records in the cbx will make it
behave more responsibly; that is, when a user presses a key, the cbx
requeries to show only those items starting with that letter. I just
posted a few days ago some code that would require only a little
modification to do something like this; search for the "navigating the
list" thread.

2. The form has "find record" VBA code (see below) to locate a particular
client record based on the user's selection in the cbo box. The "find
record" cbo works fine except for the last two records. If either of those
records are selected, the first record (rather than the selected record) is
loaded into the form.

What could account for such unexpected behavior?

One thing I'd double-check are the variable names in your two routines.
In the second, for example, you refer to ClientID only once, so it isn't
clear what this refers to. The fact that you refer to some form of
"ClientID" several times across the two routines, in different ways,
suggests to me that there may be a naming problem. Are you using Option
Explicit?

Other than this, the best thing I can recommend here is that you
troubleshoot this more thoroughly with code breakpoints, and step
through the code. Do it once for a record that works, and make note of
the progress of the code; then do it for a record that doesn't work, and
see where it goes wrong. You don't mention an error being raised, so it
seems likely that the problem is going to be one where a value isn't
what you expect; e.g., the cbx is returning 0 or null when you expect a
positive value of some kind. Make sure to check the important variables
in your code to verify the values are what you expect.

HTH
spark

Here is the VBA code behind the cbo "find record" control:

1. Move to selected record:

Private Sub cboClientID_AfterUpdate()

On Error GoTo ErrorHandler

'Find a client

Dim rs As Object
Set rs = Me.Recordset.Clone

rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboClientID], 0))

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number _
& ": Description: " & Err.Description
Resume ErrorHandlerExit:

End Sub



2. Synch cbo with current record

Private Sub Form_Current()

On Error GoTo ErrorHandler

'Make the client in the cbo the current client

If cboClientID.Column(0) <> txtClientID Then
cboClientID.Value = ClientID
End If

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number _
& ": Description: " & Err.Description
Resume ErrorHandlerExit:

End Sub