Me.RecordsetClone

[Guest]

I have a button on the form that creates a duplicate record then runs the
code below that should go to the record that was just created. This works
great if I've opened the form and displayed all records. However, if I've
opened the form and filtered the records, the code below isn't working
properly. It navigates to the first record in the filter and doesn't show
the newly created record. I have to close and re-open the form unfiltered in
order to see the added record. Any ideas why this is happening? Thanks.

Public Sub DuplicateRecordRefresh()
With Me.RecordsetClone

' Save this record's key info.
strModel = Forms![CreateDuplicateRecord]![ChooseModel]
strPart = Forms![CreateDuplicateRecord]![ExistingPart]
strNHL = Forms![CreateDuplicateRecord]![NewNHL]


' Requery the form.
Me.Requery

' Position this form to the record
' whose keys we saved.
Me.Recordset.FindFirst _
"[Model#] = '" & strModel & _
"' And [Part#] = '" & strPart & _
"' And [NHL] = '" & strNHL & "'"
End With

End Sub

 

[Dirk Goldgar]

I have a button on the form that creates a duplicate record then runs
the code below that should go to the record that was just created.
This works great if I've opened the form and displayed all records.
However, if I've opened the form and filtered the records, the code
below isn't working properly. It navigates to the first record in
the filter and doesn't show the newly created record. I have to
close and re-open the form unfiltered in order to see the added
record. Any ideas why this is happening? Thanks.

Public Sub DuplicateRecordRefresh()
With Me.RecordsetClone

' Save this record's key info.
strModel = Forms![CreateDuplicateRecord]![ChooseModel]
strPart = Forms![CreateDuplicateRecord]![ExistingPart]
strNHL = Forms![CreateDuplicateRecord]![NewNHL]


' Requery the form.
Me.Requery

' Position this form to the record
' whose keys we saved.
Me.Recordset.FindFirst _
"[Model#] = '" & strModel & _
"' And [Part#] = '" & strPart & _
"' And [NHL] = '" & strNHL & "'"
End With

End Sub

That code looks familiar. You left out the part where does the
RecordsetClone actually get used, right?

Is the record you created a member of the filtered recordset? That is,
does it meet the filter criteria?

 

[Guest]

Thanks Dirk. DUH! No, the new record doesn't meet the criteria. But, while
I've got you . . . After the code below bounces to the first record, the
contents of one of the fields in only the first record is deleted, which does
not happen when a duplicate record is created when all records are shown.
The field that's being deleted is not one of the fields mentioned below. I
can't begin to guess why this is happening. Any suggestions? Thanks again.

I have a button on the form that creates a duplicate record then runs
the code below that should go to the record that was just created.
This works great if I've opened the form and displayed all records.
However, if I've opened the form and filtered the records, the code
below isn't working properly. It navigates to the first record in
the filter and doesn't show the newly created record. I have to
close and re-open the form unfiltered in order to see the added
record. Any ideas why this is happening? Thanks.

Public Sub DuplicateRecordRefresh()
With Me.RecordsetClone

' Save this record's key info.
strModel = Forms![CreateDuplicateRecord]![ChooseModel]
strPart = Forms![CreateDuplicateRecord]![ExistingPart]
strNHL = Forms![CreateDuplicateRecord]![NewNHL]


' Requery the form.
Me.Requery

' Position this form to the record
' whose keys we saved.
Me.Recordset.FindFirst _
"[Model#] = '" & strModel & _
"' And [Part#] = '" & strPart & _
"' And [NHL] = '" & strNHL & "'"
End With

End Sub

That code looks familiar. You left out the part where does the
RecordsetClone actually get used, right?

Is the record you created a member of the filtered recordset? That is,
does it meet the filter criteria?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks Dirk. DUH! No, the new record doesn't meet the criteria.
But, while I've got you . . . After the code below bounces to the
first record, the contents of one of the fields in only the first
record is deleted, which does not happen when a duplicate record is
created when all records are shown. The field that's being deleted is
not one of the fields mentioned below. I can't begin to guess why
this is happening. Any suggestions? Thanks again.

I haven't a clue why that would be. Are you sure the field's value is
actually being changed? Does the form show its "dirty record"
indicator -- the little pencil displayed in the record selector? If the
field is really being changed, then there must be code running in some
form event that is doing it, and you'll have to set breakpoints and step
through the code to see what happens and why.

If the record is *not* dirty, it occurs to me that the field that
appears to be cleared may just possibly be a combo box with a row source
that uses one of the form fields as a criterion. If that's the case,
it's possible that, when the form is filtered, there's no record that
meets that criterion, so the current record's value in that field is not
in the combo's rowsource. That would make the field appear blank, even
though the field value isn't actually changed.

 

[Guest]

You are awesome. I would have never figured that out. I found the code
that's running that is indeed changing that field in the record. Thank you
so much for your time.