Okay, I thought I included enough to get the idea, here is the routine as I have it now.
gSp(n) is a global variable, so I can pull values needed.
There's some additional cleanup needed in the error checking section as I just changed some of the arguments to variant.
As I said, I copied it from another site, it works, it's just that I'm not sure how to "move" to the new record.
If there is a better approach, I'm all ears (or eyes in this case):
Code:
Public Sub sFrmCopyRecord(aTblNm1 As Variant, aTblNm2 As Variant, aWhereCond As Variant)
' Moves a record to a new one in the same/other table
' gSp(1-5) = 'reserved
' gSp(6) = list of fields to exclude in "]FieldNm]" format
' All fields copied, some can be modified to new values
' gSp(7) = list of fields to modify when included
' gSp(8) = list of replacement values for text that's included, must match fields with gSp(7)
' Error check values entered with
Dim temp1 As Variant, temp2 As Variant
If IsNull(aTblNm1) Or IsNull(aTblNm2) Then
gError = "sFrmCopyRecord.1"
Exit Sub
End If
If aTblNm1 = "" Or aTblNm2 = "" Then
gError = "sFrmCopyRecord.2"
Exit Sub
End If
If IsNull(aWhereCond) Then
gError = "sFrmCopyRecord.3"
Exit Sub
End If
temp1 = Left(gSp(6), 1): temp2 = Right(gSp(6), 1)
If gSp(6) <> "" And (temp1 <> "]" Or temp2 <> "]") Then
gError = "sFrmCopyRecord.4"
Exit Sub
End If
' check 3, 4, 5 for bracket errors
temp1 = fSarrayCount(gSp(7), "]"): temp2 = fSarrayCount(gSp(8), "]")
If temp1 <> temp2 Then
gError = "sFrmCopyRecord.5"
Exit Sub
End If
temp1 = Left(gSp(7), 1): temp2 = Right(gSp(7), 1)
If gSp(7) <> "" And (temp1 <> "]" Or temp2 <> "]") Then
gError = "sFrmCopyRecord.6"
Exit Sub
End If
temp1 = Left(gSp(8), 1): temp2 = Right(gSp(8), 1)
If gSp(8) <> "" And (temp1 <> "]" Or temp2 <> "]") Then
gError = "sFrmCopyRecord.7"
Exit Sub
End If
Dim fimr As String
Dim rs1 As Recordset, rs2 As Recordset, F As Field
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM " & aTblNm1 & " WHERE " _
& aWhereCond)
While Not rs1.EOF
Set rs2 = CurrentDb.OpenRecordset(aTblNm2)
rs2.AddNew
For Each F In rs1.Fields
temp1 = "]" & F.Name & "]"
If InStr(1, gSp(6), temp1) Then
'don 't include the skipped item
Else
fimr = ""
If InStr(1, gSp(7), temp1) Then
' write extraction routines
temp2 = fSarrayPosition(gSp(7), F.Name, "]")
fimr = fSarrayExtract(gSp(8), temp2, "]")
Select Case fimr
Case "*User*"
fimr = SysCtrl(2, 1)
Case "*Now*"
fimr = Now()
Case Else
' use data passed in
End Select
End If
If fimr = "" Then
rs2(F.Name) = rs1(F.Name).Value
Else
rs2(F.Name) = fimr
End If
End If
Next
rs2.Update
rs2.Bookmark = rs2.LastModified
gSp(1) = rs2.Fields(0)
rs1.MoveNext
Wend
Stop
rs2.Close
rs1.Close
Set rs2 = Nothing
Set rs1 = Nothing
ExitCd:
Exit Sub
ErrCd:
' Select Case Err
' Case 998, 999: command
' Case Else
Stop
gError = ErrUnhandled(Err, Error, "sFrmCopyRecord")
' End Select
Resume ExitCd
End Sub