VBA filter for form - newest record in table

[Mark1]

My form's current record comes from my master table.

I want a command button on my form that will duplicate the current record in
my master table and then make the form be filtered to that new record.

Right now, I have a query that duplicates the current record in the table.
And I have VBA code that is going to do it all, but I just don't know what to
put in my filter statement. Here's my code.... I'll leave the filter
statement (in quotes) blank. Thank you!!!!!!!!!!!!!!!!!

Private Sub Command6_Click()

DoCmd.OpenQuery "Duplicate_MRSA_query"

Dim filter_one As String

filter_one = " "

Me.Filter = filter_one
Me.FilterOn = True

End Sub

 

[Allen Browne]

Instead of executing an append query to duplicate the record, it would be
easier and more efficient to AddNew to the RecordsetClone of the form, and
then set the form's Bookmark to show the newly created record.

Example in:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html
Just ignore the bit about duplicating the subform records as well.

 

[Mark1]

Thanks for the help Allen. The only thing I worry about is that I've got to
copy the record to my table, which your code does, but not all fields in the
table are on the form. So, I need the table record to be duplicated. The
reason I worry is because of this bit of code:

With Me.RecordsetClone
.AddNew
!Name = Me.Name
!DOB = Me.DOB
'!OrderDate = Date
'etc for other fields.
.Update

 

[Allen Browne]

If the form is bound to the table, or to a query that gets all fields from
the table, then the fields are available in the RecordsetClone of the form.
It doesn't matter whether the fields are displayed on the form or not (i.e.
whether there are controls for all the fields or not.)

Just use one line for each field whose value you wish to set.

 

[Mark1]

Thanks Allen, but I must be missing something. This is what I'm missing

With Me.RecordsetClone
.AddNew
!Name = Me.Name
!DOB = Me.DOB
!COMMENT= ?????????????????
.Update

I've tried !COMMENT = !COMMENT and its blank when I open the table. I've
tried !COMMENT = COMMENT.Value - same thing. So, I need some handholding
here. Thanks!
I've tried

 

[Douglas J. Steele]

If COMMENT is the name of a control on your form, and you want its value put
in the Comment field of the current recordset, try

!COMMENT = Me!COMMENT

 

[Mark1]

COMMENT is a field in the form's underlying table. But it is not displayed
on the field itself. When I duplicate the current record displaying on the
form, I want that record's data duplicated in the underlying table.

So, I've tried setting !COMMENT = [Tables]![Table1]![COMMENT], but it says
that an object is required.
When I tried putting it in quotes, it actually wrote the string
"[Tables]![Table1]![COMMENT]" in the new record's COMMENT field.

 

[Douglas J. Steele]

[Tables]![Table1] is not a valid way to refer to a table.

If there's only a single row in the table, you can try using

DLookup("Comment", "Table1")

If there's more than one row, you'll need a Where clause there as well:

DLookup("Comment", "Table1", "Id = " & Me.Id)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

COMMENT is a field in the form's underlying table. But it is not
displayed
on the field itself. When I duplicate the current record displaying on
the
form, I want that record's data duplicated in the underlying table.

So, I've tried setting !COMMENT = [Tables]![Table1]![COMMENT], but it says
that an object is required.
When I tried putting it in quotes, it actually wrote the string
"[Tables]![Table1]![COMMENT]" in the new record's COMMENT field.

If COMMENT is the name of a control on your form, and you want its value
put
in the Comment field of the current recordset, try

!COMMENT = Me!COMMENT