Change one field's value during record duplication

[Clueless Newbie]

I'm currently using the function below (thank you, Mile-O-Phile! ) in order to duplicate a current record with the click of one button. What I would also like to do is to not duplicate but change the value of just one single field ("xyz") during that duplication process.

I assume that theoretically I should open a some sort of input box, ask the user to type in the new value for "xyz", save that to a temporary variable and stick the latter into my record duplicate -- while the original record is left untouched. The only problem is: I'm not sure whether I'm even aiming in the right direction with this kind of solution. In case I am, how does one implement this? Everything I've tried so far has only resulted in messing up the function.

Here's the "pure duplication" code I have so far; any help with the above-mentioned add-on would be most appreciated. Thank you!

Ute

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

 

[Mile-O]

Do you want an InputBox (horrible) or a textbox (good) that you type into on the form and then reference that in the code?

 

[Clueless Newbie]

Wellllllll... If you tell me that a textbox is better, then I'm inclined to opt for the latter of course. *g*

 

[Mile-O]

Assuming their is a textbox (called txtInfo) on your form, and the field its value goes into is called Info:

This?

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 .Fields
            If fld.Name <> strKey Then
                varValue = Nz(DLookup("[" & fld.Name & "]", strTable, "[" & strKey & "] = " & lngID))
                If fld.Name = "Info" Then
                    fld = Nz(Me.txtInfo, varValue)
                Else
                    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

 

[Mile-O]

Why did you change the function to return a Long value?

 

[Clueless Newbie]

It messed up my IDs with "Boolean", I'm afraid. The button would only work once; it set the new ID to -1 and no more duplications were possible.

 

[Mile-O]

For functions where I run a process I prefer to return their value as a Boolean so that I know they were successful.

That was why I had it as a Boolean

i.e.

If Not Duplicate() = Then
    MsgBox "Unable to duplicate record.", vbExclamation
End If

 

[Mile-O]

I never did understand why it was changed in such a way:

i.e.

fld = Duplicate

When Duplicate is the 'Return' value and not a value sent.

 

[Clueless Newbie]

I'm currently trying something that looks promising, haven't quite finished it yet, though. The textbox already works, it also already saves the user's new temporary value. I guess by the end of the day I'll know how to write the stuff into my table as well. (It turned out I had to use a different table for the changed value of xyz.) But thank you anyway for helping me.

BTW: Interesting Homepage. Are you a linguist?

Greetings from "The Old Europe"

Ute

 

[Mile-O]

Interesting Homepage. Are you a linguist?

Thank; I'm a budding amateur linguist. Just started working on the site so hopefully it will grow as I learn more.

Greetings from "The Old Europe"

I would have thought Scotland was old enough to be Old Europe.

 

[Clueless Newbie]

Not according to Donald Rumsfeld...

 

[Clueless Newbie]

Oh dear... This promises to turn into a never ending story...

OK, what I'm trying to do is this:

As before, the current record is duplicated into the same table ("Daten ") that also contains the original data. The new ID is "Highest ID + 1", the remaining 76 values are added one after another, I update & close my table. Works fine, like I already said.

Then I open a little dialogue, asking the user to type in a new value for the field named "VertragsNr". That value occurs in "Daten" as well as another table named "Vertragsnummern". It is, however, supposed to be updated in "Vertragsnummern " only. The value in "Daten" is simply duplicated and will probably disappear altogether some time soon.

"Vertragsnummern" may contain duplicate "ID"s but no duplicate "VertragsNr"s (I've attached a screenshot of the relation between the two tables.)

So what I need to do is to
a) check whether the user's input is compatible with the data type, as VertragsNr is a purely numeric field

b) check whether the value a user types into my input box is already present in Vertragsnummern and

If a) = true => check b)

If a) false = remind User that only numbers are allowed

If b) = true => Write a new record into Vertragsnummern where ID = ID from my above-mentioned duplicate and VertragsNr = User's input from my textbox.

If b) = false => ask User to type in a different number

What I have so far (apart from the actual duplication code) is the following, still incomplete since even the first steps don't really seem to work. (I have inserted it between ".close" and "End With" in the original code above):

[SIZE=1] (...)
    Dim SAPzwischen As String
    Dim IDZwischen
(...)[/size]

'Dim test
'test = 1
'Do While test <> 0
    SAPzwischen = InputBox("Eingabeaufforderung blah...", "Neue SAP-Vertragsnummer")

' 1. mögliche Variante?
Dim NeuerSatz As String
NeuerSatz = "select * from Vertragsnummern where id = " & fld
If rs.EOF Then

.AddNew
    !ID = IDZwischen
    !VertragsNr = SAPzwischen
.Update

Else
 rs.FindNext "VertragsNR = " & SAPzwischen
 If rs.NoMatch Then
.AddNew
    !ID = IDZwischen
    !VertragsNr = SAPzwischen
.Update
Else
MsgBox "Blah... schon vorhanden.", vbCritical, "Achtung" 'Please use another value

End If

End If


Set rs = db.OpenRecordset(NeuerSatz)
If Not rs Then
    rs.Edit
        rs!VertragsNr = SAPzwischen
    rs.Update
End If
    
    
'Loop

Where am I going wrong?

Exasperated greetings,

Ute

 

[Clueless Newbie]

OK, got it! This one here works fine, it seems:

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
    Dim SAPzwischen As String
    Dim IDZwischen
    
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strTable)
    


DoCmd.Hourglass True
    
    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
                IDZwischen = fld
            End If
        Next
        .Update
        .Close
    End With
Dim test
test = 2
Do While test <> 0
    test = 2
    SAPzwischen = InputBox("Bitte geben Sie eine neue SAP-Vertragsnummer ein! Bitte nur Zahlen eingeben!", "Neue SAP-Vertragsnummer")
    If SAPzwischen = "" Then
        test = 0
    Else
        If IsNumeric(SAPzwischen) Then
            test = test - 1
        Else
            MsgBox ""
        End If
        If IsNull(DLookup("Vertragsnr", "vertragsnummern", "vertragsnr=" & SAPzwischen)) Then
            test = test - 1
        Else
            MsgBox ""
        End If
    End If
Loop
'Variante 2
Dim NeuerSatz As String
NeuerSatz = "select * from Vertragsnummern"
Set rs = db.OpenRecordset(NeuerSatz)
With rs
    .AddNew
        !ID = IDZwischen
        !VertragsNr = SAPzwischen
    .Update
    .Close
End With

Exit_Duplicate:
    Set fld = Nothing
    Set rs = Nothing
    Set db = Nothing
    DoCmd.Hourglass False
    Exit Function
    

    
Err_Duplicate:
    Duplicate = False
    Stop
    Resume Exit_Duplicate

    
End Function

Ute (not quite as clueless anymore)

 

[Mile-O]

Just a little info for you.

Have a look at my post on this thread regarding early and late binding (. and ! notation).

And, if Access provides a constant then always use that over an actual value.

i.e.

Instead of using "" in parts of your code like: MsgBox ""
you should use the constant: vbNullString

It's more to type but it compiles faster.

 

[Clueless Newbie]

Oh, OK -- thanks for the info!

Ute