combo box follow up

  • Thread starter Thread starter Chrissy
  • Start date Start date
C

Chrissy

Our employees are assigned locations to work. The
locations are stored in a table and, as I have
discovered, not all locations are used. They do need to
be available for use, however.

My form and the location combo box work fine when a
location in the source table is used by at least one
employee. (Thanks again Rick Brandt.) When a location in
the source is not used by ANY employee (presently), the
combo box returns a blank form. Selecting another
location returns the message "no current record." No
surprise. I have attempted to test for null or empty
string to no avail.

Question is, using the following code, how do I manage to
stay on the open form whereby I can select another
location? Presently I must close and restart the form.

Can anyone shed light? Thanks in advance.
Chrissy

My code:
Private Sub cboLocation_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Location] = '" & Me![cboLocation] & "'"
Me.Bookmark = rs.Bookmark
Me.Filter = "[Location]='" & Me.cboLocation & "'"
Me.FilterOn = True
Me.cboLocation.SetFocus
End Sub
 
You should use the NoMatch Property after the FindFirst
Method to test whether FindFirst is successful or not.
Something like:

****Untested air code****
Private Sub cboLocation_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[Location] = '" & Me![cboLocation] & "'"
If (rs.NoMatch = False) Then
Me.Bookmark = rs.Bookmark
Me.Filter = "[Location]='" & Me.cboLocation & "'"
Me.FilterOn = True
Me.cboLocation.SetFocus
Else 'No Records has the selected Location
MsgBox "Sorry. No go."
End If
End Sub
****

Alternatively, you can change the RowSource of the
ComboBox using an Inner Join so that it only shows
Locations that have at least one Employee assigned to each
of them.

HTH
Van T. Dinh
MVP (Access)
 
Van, thank you so much. I chose to use your (simple)
untested air code. Its now tested and a success!

Thanks again,
C

-----Original Message-----
You should use the NoMatch Property after the FindFirst
Method to test whether FindFirst is successful or not.
Something like:

****Untested air code****
Private Sub cboLocation_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[Location] = '" & Me![cboLocation] & "'"
If (rs.NoMatch = False) Then
Me.Bookmark = rs.Bookmark
Me.Filter = "[Location]='" & Me.cboLocation & "'"
Me.FilterOn = True
Me.cboLocation.SetFocus
Else 'No Records has the selected Location
MsgBox "Sorry. No go."
End If
End Sub
****

Alternatively, you can change the RowSource of the
ComboBox using an Inner Join so that it only shows
Locations that have at least one Employee assigned to each
of them.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Our employees are assigned locations to work. The
locations are stored in a table and, as I have
discovered, not all locations are used. They do need to
be available for use, however.

My form and the location combo box work fine when a
location in the source table is used by at least one
employee. (Thanks again Rick Brandt.) When a location in
the source is not used by ANY employee (presently), the
combo box returns a blank form. Selecting another
location returns the message "no current record." No
surprise. I have attempted to test for null or empty
string to no avail.

Question is, using the following code, how do I manage to
stay on the open form whereby I can select another
location? Presently I must close and restart the form.

Can anyone shed light? Thanks in advance.
Chrissy

My code:
Private Sub cboLocation_AfterUpdate()
'Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Location] = '" & Me![cboLocation] & "'"
Me.Bookmark = rs.Bookmark
Me.Filter = "[Location]='" & Me.cboLocation & "'"
Me.FilterOn = True
Me.cboLocation.SetFocus
End Sub

.
Click to expand...
.
Click to expand...
 
Back
Top