findfirst sometimes doesn't

G

Guest

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
 
G

Guest

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

Me.Requery
Set rs = Me.RecordsetClone
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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

you can't assign a value to this object 2
combo box not updating accoring to record selector 2
reference in recordset.findfirst 3
updating a recordset that is not the record source of a form 2
Problem with Combo Box FindNext 7
Combo box based on a query to navigate to a specific record 6
Error 2147352567 2
VBA for NotInList return to old record or BeforeUpdate value 2

Top