make a duplicate of a record (1 Viewer)

[nata4]

Hi
I want the user to be able to make a copy of a record automatically by clicking a button, but I only want that button to copy certain fields
Is there a way to code it so that it copies specific fields from that record to a new record?
Thanks in advance

 

[ghudson]

You need to search the forum and post your question if you can not find the answer to your situation.

This link will show you how I copy specific fields from the current record and create a new record pre filled with only the fields I want.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=34060

HTH

 

[nata4]

thanks a lot, it worked,
but if the field is empty, then it gives me the END DEBUG box
so I had to go and take out some fields from the code to be able to copy that record

But now, what if the next record I want to copy will have one of the specified fields empty, then the user will get the same box and they won't be able to to anything

So basically, how can I tell it that if the field is empty, to just ignore it or to copy the empty space?

 

[nata4]

well, anyone?

to rephrase my question, how do I tell it to either ignore or copy the empty string if the field is empty?

 

[ghudson]

My "Duplicate Current Record" attached sample will allow a Null field to NOT be copied into the new record. I personally do not allow Null fields but my example show how easy it is to duplicate a record even if it contains a Null field. Just follow my code to test the "X_Code" field and what to not do with the X_Code field when it is Null.

HTH

 

[lala]

This is NATA4, but this is my real login, it's just that I couldn't remember the password

Having said that, thanks a lot again for your example, and I have another question

This is part of your code for the field that might be empty

If Not IsNull(X_Code) Or X_Code = "" Then
Null_X_Code = False
Else
Null_X_Code = True
End If


I want to do this for all my fields, because any of them can be empty

Should I do separate IF statements for each, or is there a way to put them all in the same IF statement
And if so, can you show me how

Thanks again
Lala

 

[lala]

I personally do not allow Null fields

I can't do that, I didn't hire her, she hired me)))))))))))))
So she's the one telling me what she needs, my job is to make it as reliable and easy to use as possible

Speaking of which, there's another issue that I'm working on, and I'm not sure of the best possible way to do it
I've tried different things, and they always managed to go around it and break it in a new way

So if you like solving puzzles, I'll post my problem and all the BUTs

If not, thanks for so much help with this one

 

[ghudson]

I suggest you use a separate IF statement for each field. It will make the coding a bit long but you need to evaluate each field individually.

By allowing Null values, how do you know the user did not accidentally leave a field empty by mistake before moving to the next record or before closing the form?

I would have to question the integrity and accuracy of the data but that is my personal opinion.

As for your other problem, post a new topic to the correct forum.

HTH

 

[lala]

they could've accidentally left it blank, but for the most part, they don't have complete information

the database (the one I'm working on now, and really, all of them) has about 50 fields, and 3 subforms

they don't always have all the info at the beginning (like all the 6 phone numbers, e-mail and the website, and some records won't ever have website), and they fill it in as they go on


but the integrity of the data is very important, I'm a perfectionist, and I agree with you here
and I've done my part to make sure it'll be as accurate as possible by making combo boxes in all possible fields, and by putting input masks and so on, but a user is still a user


and for this one, will it look like this?

If Not IsNull(X_Code) Or X_Code = "" Then
Null_X_Code = False
Else
Null_X_Code = True
End If

If Not IsNull(FirstName) Or FirstName = "" Then
Null_FirstName = False
Else
Null_FirstName = True
End If

If Not IsNull(LastName) Or LastName = "" Then
Null_LastName = False
Else
Null_LastName = True
End If

and so on for all fields?


and I'll post my other question a little later and will give a link here

 

[lala]

Here's a link to my other question that I was talking about

 

[lala]

I suggest you use a separate IF statement for each field

By the way, thank you very much
It works perfect

It's amazing, every time I come to this forum, I'm always sure that my problem is so weird and unique, and noone probably knows how to fix it

And there wasn't even one time that I didn't get a perfect solution

Thank you people!!!

 

[Clueless Newbie]

Sorry to drag this old thread up again, but I tried to use ghudsons's code too -- and it doesn't work for me, for some reason. (WinNT 4.0, Access 2002) The Button does just nothing. No beep, no duplication, no error message, just plain nothing.

I have 77 data fields, one of which ("ID", primary key) being an auto-increment type. I assume that that's the one causing the problem. (Among other things perhaps...)

Thinking that that field would get filled automatically once a new record was created, I first omitted it altogether. Didn't work, of course. Then I tried including the field in my function, but of course I can't very well use "If Null_ID = False Then sID = ID" or "If Null_ID = False Then ID = sID" for an auto-increment field, can I?

Can anyone tell me what I have to do and/or what could be my mistake. please?

Thanks a bunch!

Ute

 

[Mile-O]

I just wrote this - I haven't looked at ghudson's.

This will duplicate every field except the primary key - you need to send the primary key field, the table in which to duplicate a record, and the primary key value of the record to duplicate.

It's dodgy in that the tables it will work with must not have a composite key.

Public Function Duplicate(ByVal strKey As String, ByVal strTable As String, _
    ByVal lngID As Long) As Boolean

    On Error GoTo Err_Duplicate
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim varValue As Variant
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strTable)
    
    With rs
        .AddNew
        For Each fld In rs.Fields
            varValue = DLookup("[" & fld.Name & "]", strTable, "[" & strKey & "] = " & lngID)
            If Not IsNull(varValue) And fld.Name <> strKey Then
                fld = varValue
            End If
        Next
        .Update
        .Close
    End With
    
    Duplicate = True

Exit_Duplicate:
    Set fld = Nothing
    Set rs = Nothing
    Set db = Nothing
    Exit Function
    
Err_Duplicate:
    Duplicate = False
    Resume Exit_Duplicate
    
End Function

 

[Clueless Newbie]

Well, it sure looks a bit shorter than the previous code. <g> I'll try it out straight away, thank you!

Ute

 

[Clueless Newbie]

Thanks again -- the code works fine! My colleague helped me with a few modifications though:

Public Function Duplicate(ByVal strKey As String, ByVal strTable As String, _
    ByVal lngID As Long) As Long

    On Error GoTo Err_Duplicate
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim varValue As Variant
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strTable)
    
    With rs
        .AddNew
        For Each fld In rs.Fields
            If fld.Name <> strKey Then
                varValue = DLookup("[" & fld.Name & "]", strTable, "[" & strKey & "] = " & lngID)
                If Not IsNull(varValue) Then
                    fld = varValue
                End If
            Else
                Duplicate = DMax("[" & strKey & "]", strTable) + 1
                fld = Duplicate
            End If
        Next
        .Update
        .Close
    End With
    
    'Duplicate = True

Exit_Duplicate:
    Set fld = Nothing
    Set rs = Nothing
    Set db = Nothing
    Exit Function
    
Err_Duplicate:
    Duplicate = False
    Resume Exit_Duplicate
    
End Function

------------

Private Sub bDuplicateCurrentRecord_Click()
Dim newId As Long
Beep

newId = Duplicate("ID", "Daten", Me!ID)
Call Auswahl(newId)
MsgBox ("Der Datensatz wurde dupliziert und mit einer neuen ID gespeichert. Sie arbeiten nunmehr mit der Kopie und können diese ändern.")

End Sub

Ute

 

[Mile-O]

newId = Duplicate("ID", "Daten", Me!ID)

newId = Duplicate("ID", "Daten", Me.ID)

Always Me. and not Me!

Also, it's always better to prefix your controls i.e. txtID

Bitte Schön.

 

[Clueless Newbie]

Changed it. Danke schön!

Ute

 

[paul young]

Create duplicate records in a form with subform


I wanted to duplicate a record without having to do the paste command. I looked at this thread and tried to re-create the SQL statement to get the results I wanted. But I could't. I am a novice on SQL. I wonder if anyone out there can help. This database I am creating is for a hospital function booking facility and often client would want to book weekly buffets. Without having to type the same booking again, I would like to duplicate the record with all the fields, including the food items selected in the subform, with the exception of the primary key (BookingID). The form in question is 'FunctionBooking' with a subform 'MenuSelection'.
Any help would be appreciated.

Paul