Adding new records based on current record

[Guest]

I am trying to add three new records to a table, based on the record that a
user has just created using a form. When the new record is created, I want
to add three more records with different record types and clear some amount
and date fields so the records are fresh for later calculations. I have
written a macro that accomplishes adding the records using a series of
queries, but the fields are not in the correct order when I do the append.
This seems to be a crude way to accomplish this task. Is there a better way?
I have limited Visual Basic skills.

Thanks,

 

[Lynn Trapp]

Karen,
As far as I can tell the only way to accomplish what you want, if I
understand your description correctly, is to use a series of append queries
to add the new records. I don't quite understand what you mean by the fields
being in the correct order, however. Can you describe what you mean,
possibly by posting some sample data and the SQL for the append queries you
are using?

Thanks,

 

[Guest]

Hi Lynn.

Thanks for trying to help. Here is a more detailed explanation.

I have a table, tblNew with images of the three records that I want to
create, minus the form-specific information (amounts are zero and dates are
blank). The record types are in these three records. I have a macro set up
that runs the steps as follows.

1. This is the SQL for the first query. It just takes my blank records and
copies them to a new table so that the original “clean†records are not
changed.

SELECT [tblNew].* INTO [tblWorkfields] FROM [tblNew];

2. The second query pulls the information from the form and populates the
work records.

UPDATE [tblWorkfields] SET
[tblWorkfields].[FK-PH-ID] = Forms!frmPH!frmHist![FK-PH-ID],
[tblWorkfields].[Policy Number] = Forms!frmPH!frmHist![Policy Number],
[tblWorkfields].[Named Insured] = Forms!frmPH.frmHist![Named Insured],
[tblWorkfields].[Effective Date] = Forms!frmPH.frmHist![Effective Date],
[tblWorkfields].Carrier = Forms!frmPH.frmHist!Carrier,
[tblWorkfields].[MoDocs Option] = Forms!frmPH.frmHist![MoDocs Option],
[tblWorkfields].[Member Policy Number] = Forms!frmPH.frmHist![Member Policy
Number],
[tblWorkfields].[Expiration Date] = Forms!frmPH.frmHist![Expiration Date],
[tblWorkfields].[Limits of Liability] = Forms!frmPH.frmHist![Limits of
Liability],
[tblWorkfields].[Annual Premium] = Forms!frmPH!frmHist![Annual Premium],
[tblWorkfields].[Comm Rate] = Forms!frmPH!frmHist![Comm Rate],
[tblWorkfields].[Last Updated] = Now();

3. I open the tblWorkfields.

4. I Select All Records from the tblWorkfields.

5. I copy all of the selected records.

6. I open the target table, tblHistory.

7. I Paste/Append the copied records from tblWorkfields to tblHistory.

The problem is that data that should end up in one field, ends up in
another…Expiration Date ends up in, for example, Policy Number.

I have a multi-user environment, so I have no guarantee that another user
hasn’t pulled up the table and moved fields around, perhaps in preparation
for a sort. So, the field order in tblNew, which is my new “clean†records
may or may not be the same as the field order in tblHistory, which is what
creates my problem. Is there any way to be field specific in my append?

Thanks for your help.

--
Karen Miller
Kansas City, MO

Karen,
As far as I can tell the only way to accomplish what you want, if I
understand your description correctly, is to use a series of append queries
to add the new records. I don't quite understand what you mean by the fields
being in the correct order, however. Can you describe what you mean,
possibly by posting some sample data and the SQL for the append queries you
are using?

Thanks,

 

[Guest]

Karen:

You might find the following adaptation of a function of mine helpful. I'm
assuming 'record type' refers to a column (field) in the table, and I'll
assume its of text data type and this is the only field to be populated other
than with values from the current record or values which users input
subsequently. I'm also assuming the table has an incrementing numeric
primary key, e.g. an autonumber.

First paste the following function into a standard module:

Public Function CopyRecord(strTable As String, _
strKey As String, _
lngKeyVal As Long, _
ParamArray aColumns() As Variant)

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strColumnList As String
Dim strRecordType as String
Dim lngLastKey As Long
Dim varColumn As Variant
Dim n as Integer

' does record to be copied exist
If IsNull(DLookup(strKey, strTable, strKey & "=" & lngKeyVal)) Then
MsgBox "Record not found.", vbInformation, "Warning"
Else
lngLastKey = DMax(strKey, strTable)

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' build column list
For Each varColumn In aColumns
strColumnList = strColumnList & ",[" & varColumn & "]"
Next varColumn

For n = 1 To 3
Select Case n
Case 1
strRecordType = "Some value"
Case 2
strRecordType = "Some other value"
Case 3
strRecordType = "Yet another value"
End Select

' insert new row into table
strSQL = "INSERT INTO [" & strTable & "](" & _
"[Record Type], [" & strKey & "]" & strColumnList & ")" & _
" SELECT """ & strRecordType & """," & lngLastKey + n &
strColumnList & _
" FROM " & strTable & " WHERE " & strKey & " = " & lngKeyVal

cmd.CommandText = strSQL
cmd.Execute
Next n

Set cmd = Nothing

End If

End Function

You'd call it in the AfterInsert event procedure of the form, passing the
table name, the name of its primary key column and the value of the newly
inserted record's primary key column, plus a list of three name of the fields
you wish to populate with values from the newly inserted record. So if the
table were called MyTable, its primary key MyID and the fields you wish to
populate with values from the current record are Field 1, Field 2, Field 3
and Field 4, you'd call the function like so:

CopyRecord "MyTable", "MyID", Me.MyID, "Field 1", Field 2", "Field 3",
"Field 4"
Ken Sheridan
Stafford, England

 

[Guest]

Ken,

Your assumptions regarding the table are correct. It will take me a while
to figure out what your code is doing, but this looks like it is exactly on
target for what I need.

Thank you very much.
--
Karen Miller
Kansas City, MO

Karen:

You might find the following adaptation of a function of mine helpful. I'm
assuming 'record type' refers to a column (field) in the table, and I'll
assume its of text data type and this is the only field to be populated other
than with values from the current record or values which users input
subsequently. I'm also assuming the table has an incrementing numeric
primary key, e.g. an autonumber.

First paste the following function into a standard module:

Public Function CopyRecord(strTable As String, _
strKey As String, _
lngKeyVal As Long, _
ParamArray aColumns() As Variant)

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strColumnList As String
Dim strRecordType as String
Dim lngLastKey As Long
Dim varColumn As Variant
Dim n as Integer

' does record to be copied exist
If IsNull(DLookup(strKey, strTable, strKey & "=" & lngKeyVal)) Then
MsgBox "Record not found.", vbInformation, "Warning"
Else
lngLastKey = DMax(strKey, strTable)

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' build column list
For Each varColumn In aColumns
strColumnList = strColumnList & ",[" & varColumn & "]"
Next varColumn

For n = 1 To 3
Select Case n
Case 1
strRecordType = "Some value"
Case 2
strRecordType = "Some other value"
Case 3
strRecordType = "Yet another value"
End Select

' insert new row into table
strSQL = "INSERT INTO [" & strTable & "](" & _
"[Record Type], [" & strKey & "]" & strColumnList & ")" & _
" SELECT """ & strRecordType & """," & lngLastKey + n &
strColumnList & _
" FROM " & strTable & " WHERE " & strKey & " = " & lngKeyVal

cmd.CommandText = strSQL
cmd.Execute
Next n

Set cmd = Nothing

End If

End Function

You'd call it in the AfterInsert event procedure of the form, passing the
table name, the name of its primary key column and the value of the newly
inserted record's primary key column, plus a list of three name of the fields
you wish to populate with values from the newly inserted record. So if the
table were called MyTable, its primary key MyID and the fields you wish to
populate with values from the current record are Field 1, Field 2, Field 3
and Field 4, you'd call the function like so:

CopyRecord "MyTable", "MyID", Me.MyID, "Field 1", Field 2", "Field 3",
"Field 4"
Ken Sheridan
Stafford, England