Duplicate selected recored

[ez2012]

I have a form that is built upon query, when the user insert the request field access will provide the form with the matching names found this DB has duplicate name and the form will provide all the duplicate. now some of the duplicate has an updatable data like address I want the user to be able to duplicate the selected record. right now im running the following

Private Sub Toggle97_Click()
On Error GoTo Err_Command97_Click
    
    DoCmd.GoToRecord , , acNewRec
    With Me.RecordsetClone
    Me.[Last name] = ![Last name]
    Me.[first name] = ![first name]
    Me.[middle name] = ![middle name]
Me.[date of birth] = ![date of birth]
    Me.Address = !Address
    Me.City = !City
    Me.Zip = !Zip
    Me.[phone number] = ![phone number]
    Me.COUNTY = !COUNTY
Me.GCITY = !GCITY
    Me.GState = !GState
    Me.GZIP = !GZIP
    Me.GPHONE = !GPHONE
    
End With

Exit_Command97_Click:
    Exit Sub
Err_Command97_Click:
    MsgBox Err.Description
    Resume Exit_Command97_Click

this will duplicate but it always duplicate the first record found in the search and not the one the user selected.

Any ideas will be welcome.

Thanks

[June7]

You need some other identifier than name, such as record ID or EmployeeID or SSN.

However, I don't really understand the 'search' and how user selects record. Nor why you are duplicating data.

[ez2012]

I have ID field that is unique. the reason for dup is because it needed for some kind of report we running. here is the query that been run for search
SELECT *
FROM dbo.Ob
WHERE ([Last Name] LIKE @Enter_Last_Name) OR
([First Name] LIKE @Enter_First_Name) OR
([Date of Birth] LIKE @Enter_DOB) OR

[June7]

Never seen @ character used.

Still don't understand the reason for duplicating the address data. It's already in the database in one table. Why does it have to be copied to another table?

Still lost. That query is an SQL statement in form RecordSource?

The code makes no sense to me. If you move to new record then there is no data and until data entry into any field the record is not initiated. But the code references the fields to set values of controls. I am guessing it is just pulling values from the first record of the form's RecordSource because that record really has focus. This code will not work. Suggest will need to open a recordset object in VBA filtered to the desired record and use data from recordset to populate new record row.

[ez2012]

I really do not have any issue with validate the user input, they pretty good in doing so. Im not copy the data to another table im duplicating on the same table. the reason for the duplicate is each row represent a client the client can have multiple cases each case is assign to a different user and been done on different dates and years, I can not update the record since then I will lose the last time the client has been checked by the user, I need to keep all the client checks by all the users that the client work with. I hope now it is more clear, all I need is to be able is to duplicate a the selected record I want too. to make it even more simple I have a database and I want to be able to select (highlight) record with the mouse click on a button and it duplicate the record for me.

and yes for the SQL question. Thanks

[June7]

Okay, instead of duplicating all the address info, there isn't just an AddressID that could be saved? Oh well, I just don't know enough about your data structure and business process.

So, did you understand suggestion in my previous post?

[ez2012]

Yes I have try to add filter and change the focus to the address field but none help.

Thank you for your suggestion and time.

[June7]

Applying filter and changing focus won't accomplish anything. Your code is attempting to populate new record with expressions that have controls directly referencing the fields they are bound to. That won't work. It is a circular reference.

Really need code to open a recordset filtered to desired record and populate the new record by referencing fields of the recordset.

[ez2012]

It should like there is no solution unless I filter to one record and then duplicate it, which already work, if only one record exist.
If the same record come on search 2 or 3 times it will duplicate only the first record, and not the one the user has focus on. Ok