findfirst sometimes doesn't

S

SandyR

In Access 2002, I have a form with a subform with a combo box (cbotdate). The
subform is linked to the parent form via the tdate field. The data source
of the combo box is the following query:

SELECT tblcrews.tdate, tblcrews.crewid, tblcrews.ccomments, tblcrews.crewtype
FROM tblcrews
WHERE (((tblcrews.tdate)=[forms]![dailylog]![cbotdate]));

In the after_update procedure for the combo box, I make the current record
for the form the one selected in the combo box using code shown below. This
usually works perfectly, however after I create a new record, if I go back to
a previous one, then select the new record from the combo box, I get the “Not
found†error message. I know that the record is there, because the combo box
shows it. I don’t understand why a noMatch condition is being generated.
Does anyone have any suggestions?

Private Sub cboCrewid_AfterUpdate()

On Error GoTo cboCrewid_AfterUpdate_Error
Dim rs As DAO.Recordset

If Not IsNull(Me.cboCrewid) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CrewID] = " & Me.cboCrewid
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
End If

cboCrewid_AfterUpdate_exit:
On Error GoTo 0
Set rs = Nothing
Exit Sub

cboCrewid_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cboCrewid_AfterUpdate of VBA Document Form_team subform"
Resume cboCrewid_AfterUpdate_exit
End Sub
 
O

Ofer Cohen

Try and run requery to refresh the data before using the RecordSource as a
RecordsetClone

Me.Requery
Set rs = Me.RecordsetClone
 
S

SandyR

It is an interesting idea, and it might have worked, but the requery seems to
trigger the Form_current code which resets the value in the combo box:

Private Sub Form_Current()
Me.cboCrewid.Requery
Me.cboCrewid = Me.txtcurrcrew
End Sub

Ofer Cohen said:
Try and run requery to refresh the data before using the RecordSource as a
RecordsetClone

Me.Requery
Set rs = Me.RecordsetClone

--
Good Luck
BS"D


SandyR said:
In Access 2002, I have a form with a subform with a combo box (cbotdate). The
subform is linked to the parent form via the tdate field. The data source
of the combo box is the following query:

SELECT tblcrews.tdate, tblcrews.crewid, tblcrews.ccomments, tblcrews.crewtype
FROM tblcrews
WHERE (((tblcrews.tdate)=[forms]![dailylog]![cbotdate]));

In the after_update procedure for the combo box, I make the current record
for the form the one selected in the combo box using code shown below. This
usually works perfectly, however after I create a new record, if I go back to
a previous one, then select the new record from the combo box, I get the “Not
found†error message. I know that the record is there, because the combo box
shows it. I don’t understand why a noMatch condition is being generated.
Does anyone have any suggestions?

Private Sub cboCrewid_AfterUpdate()

On Error GoTo cboCrewid_AfterUpdate_Error
Dim rs As DAO.Recordset

If Not IsNull(Me.cboCrewid) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CrewID] = " & Me.cboCrewid
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
End If

cboCrewid_AfterUpdate_exit:
On Error GoTo 0
Set rs = Nothing
Exit Sub

cboCrewid_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cboCrewid_AfterUpdate of VBA Document Form_team subform"
Resume cboCrewid_AfterUpdate_exit
End Sub
Click to expand...
Click to expand...
 
O

Ofer Cohen

Try

Me.RecordsetClone.Requery

--
Good Luck
BS"D


SandyR said:
It is an interesting idea, and it might have worked, but the requery seems to
trigger the Form_current code which resets the value in the combo box:

Private Sub Form_Current()
Me.cboCrewid.Requery
Me.cboCrewid = Me.txtcurrcrew
End Sub

Ofer Cohen said:
Try and run requery to refresh the data before using the RecordSource as a
RecordsetClone

Me.Requery
Set rs = Me.RecordsetClone

--
Good Luck
BS"D


SandyR said:
In Access 2002, I have a form with a subform with a combo box (cbotdate). The
subform is linked to the parent form via the tdate field. The data source
of the combo box is the following query:

SELECT tblcrews.tdate, tblcrews.crewid, tblcrews.ccomments, tblcrews.crewtype
FROM tblcrews
WHERE (((tblcrews.tdate)=[forms]![dailylog]![cbotdate]));

In the after_update procedure for the combo box, I make the current record
for the form the one selected in the combo box using code shown below. This
usually works perfectly, however after I create a new record, if I go back to
a previous one, then select the new record from the combo box, I get the “Not
found†error message. I know that the record is there, because the combo box
shows it. I don’t understand why a noMatch condition is being generated.
Does anyone have any suggestions?

Private Sub cboCrewid_AfterUpdate()

On Error GoTo cboCrewid_AfterUpdate_Error
Dim rs As DAO.Recordset

If Not IsNull(Me.cboCrewid) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CrewID] = " & Me.cboCrewid
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
End If

cboCrewid_AfterUpdate_exit:
On Error GoTo 0
Set rs = Nothing
Exit Sub

cboCrewid_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cboCrewid_AfterUpdate of VBA Document Form_team subform"
Resume cboCrewid_AfterUpdate_exit
End Sub
Click to expand...
Click to expand...
Click to expand...
 
S

SandyR

Thanks - that worked! I really appreciate your help.

Ofer Cohen said:
Try

Me.RecordsetClone.Requery

--
Good Luck
BS"D


SandyR said:
It is an interesting idea, and it might have worked, but the requery seems to
trigger the Form_current code which resets the value in the combo box:

Private Sub Form_Current()
Me.cboCrewid.Requery
Me.cboCrewid = Me.txtcurrcrew
End Sub

Ofer Cohen said:
Try and run requery to refresh the data before using the RecordSource as a
RecordsetClone

Me.Requery
Set rs = Me.RecordsetClone

--
Good Luck
BS"D


:

In Access 2002, I have a form with a subform with a combo box (cbotdate). The
subform is linked to the parent form via the tdate field. The data source
of the combo box is the following query:

SELECT tblcrews.tdate, tblcrews.crewid, tblcrews.ccomments, tblcrews.crewtype
FROM tblcrews
WHERE (((tblcrews.tdate)=[forms]![dailylog]![cbotdate]));

In the after_update procedure for the combo box, I make the current record
for the form the one selected in the combo box using code shown below. This
usually works perfectly, however after I create a new record, if I go back to
a previous one, then select the new record from the combo box, I get the “Not
found†error message. I know that the record is there, because the combo box
shows it. I don’t understand why a noMatch condition is being generated.
Does anyone have any suggestions?

Private Sub cboCrewid_AfterUpdate()

On Error GoTo cboCrewid_AfterUpdate_Error
Dim rs As DAO.Recordset

If Not IsNull(Me.cboCrewid) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[CrewID] = " & Me.cboCrewid
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
End If

cboCrewid_AfterUpdate_exit:
On Error GoTo 0
Set rs = Nothing
Exit Sub

cboCrewid_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cboCrewid_AfterUpdate of VBA Document Form_team subform"
Resume cboCrewid_AfterUpdate_exit
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Clearing combo boxes and a text box 2
Dynamic Naming of Form Button 3
you can't assign a value to this object 1
Problem with Combo Box FindNext 10
Error 2147352567 2
Referring to control in the active subform 0
reference in recordset.findfirst 3
VBA for NotInList return to old record or BeforeUpdate value 2

Top