Add Record To Existing Table via VB code

[Guest]

I have an application that needs to reuse a temporary table that contains
bits and pieces of three different tables (I'm creating a temporary name
table for labels & envelopes printing.)

Three tables involved: tableA has the following names: name 1) is Licsensee
name (consisting of 4 separate fields - First, Middle, Last, Subtitle), name
2) is Owner name where name field is all one field; name 3) is Hearing
Officer name in one field. Table B has corresponding records to Table A, but
has Driver Name (in one field), Table C has corresponding data (Attorney
Number) which has attorney First, middle, last, subtitle in four fields.

I want to use VB code to write correponding case names to a temporary name
table with record layout of
Table D:
CASE_NUM_YR, CASE_NUM, SEQNO, NAME, FIRM, ADDR1, ADDR2, CITY, STATE, ZIPCODE.

I want to take matching CASE_NUM_YR & CASE_NUM records from Table A and
Table B extract names from these tables and write them to Table D. On some
name fields I need to use VB code to join the 3 or 4 separate fields to one
name field on output file (this part of code I understand), the code that I
can not find is the code to add or APPEND the data to the new table.

I will be placing a function to delete all existing records in this temp
table first, then read the three tables sequentially for specific record
types, extracting names, address, etc and writing them to the new table D.
Then use this completed file for building output records to Envelope & Label
program.

This is partial code, can somone help with the rest... assuming RESULT_CDE
is in Table A and Table A has three names, Licensee, Owner, Hearing Officer
and I want to write this data to Table D.


If RESULT_CDE = 20 Then
TableD.[CASE_NUM_YR] = TableA.[CASE_NUM_YR] 'primary-key part 1
TableD.[CASE_NUM] = TableA.[CASE_NUM] 'primary-key part 2
'TableD.[SEQNO] = (autoincrement field)
'primary-key part 3
TableD.[NAME] = TableA.[FirstName] & " " & TableA.[MiddleName] & " " & _
TableA.[LastName] & " " & TableA.[SubTitle]
TableD.[FIRM] = TableA.[FIRM]
TableD.[ADDR1] = TableA.[ADDR1]
TableD.[ADDR2] = TableA.[ADDR2]
TableD.[CITY] = TableA.[CITY]
TableD.[STATE] = TableA.[STATE]
TableD.[ZIPCODE] = TableA[ZIPCODE]
' how do I append this record to tableD
If RESULT_CDE = 20 Then
TableD.[CASE_NUM_YR] = TableA.[CASE_NUM_YR] 'primary-key part 1
TableD.[CASE_NUM] = TableA.[CASE_NUM] 'primary-key part 2
'TableD.[SEQNO] = (autoincrement field)
'primary-key part 3
TableD.[NAME] = TableA.[OWNERNAME]
TableD.[FIRM] = TableA.[FIRM]
TableD.[ADDR1] = TableA.[ADDR1]
TableD.[ADDR2] = TableA.[ADDR2]
TableD.[CITY] = TableA.[CITY]
TableD.[STATE] = TableA.[STATE]
TableD.[ZIPCODE] = TableA[ZIPCODE]
' how do I append this record to tableD
If RESULT_CDE = 20 Then
TableD.[CASE_NUM_YR] = TableA.[CASE_NUM_YR] 'primary-key part 1
TableD.[CASE_NUM] = TableA.[CASE_NUM] 'primary-key part 2
'TableD.[SEQNO] = (autoincrement field)
'primary-key part 3
TableD.[NAME] = TableA.[HEARINGOFCR]
TableD.[FIRM] = TableA.[FIRM]
TableD.[ADDR1] = TableA.[ADDR1]
TableD.[ADDR2] = TableA.[ADDR2]
TableD.[CITY] = TableA.[CITY]
TableD.[STATE] = TableA.[STATE]
TableD.[ZIPCODE] = TableA[ZIPCODE]
' how do I append this record to tableD

Since the name field on two tables are different, I thought it had to be
done via VB code, but I'm not sure of the code that adds each new record to
Table D.

Any advice would be appreciated. Thanks in advance...

 

[Guest]

Three tables involved: tableA has the following names: name 1) is Licsensee
name (consisting of 4 separate fields - First, Middle, Last, Subtitle), name
2) is Owner name where name field is all one field; name 3) is Hearing
Officer name in one field. Table B has corresponding records to Table A, but
has Driver Name (in one field), Table C has corresponding data (Attorney
Number) which has attorney First, middle, last, subtitle in four fields.

I want to use VB code to write correponding case names to a temporary name
table with record layout of
Table D:
CASE_NUM_YR, CASE_NUM, SEQNO, NAME, FIRM, ADDR1, ADDR2, CITY, STATE, ZIPCODE.

I want to take matching CASE_NUM_YR & CASE_NUM records from Table A and
Table B extract names from these tables and write them to Table D. On some
name fields I need to use VB code to join the 3 or 4 separate fields to one
name field on output file (this part of code I understand), the code that I
can not find is the code to add or APPEND the data to the new table.

I will be placing a function to delete all existing records in this temp
table first, then read the three tables sequentially for specific record
types, extracting names, address, etc and writing them to the new table D.
Then use this completed file for building output records to Envelope & Label
program.

Takes a small line in the code for a button:

CurrentDb.Execute "DELETE TableD.* FROM TableD;"

This is partial code, can somone help with the rest... assuming RESULT_CDE
is in Table A and Table A has three names, Licensee, Owner, Hearing Officer
and I want to write this data to Table D.


If RESULT_CDE = 20 Then
TableD.[CASE_NUM_YR] = TableA.[CASE_NUM_YR] 'primary-key part 1
TableD.[CASE_NUM] = TableA.[CASE_NUM] 'primary-key part 2
'TableD.[SEQNO] = (autoincrement field)
'primary-key part 3

'**** snip
'**** snip

Since the name field on two tables are different, I thought it had to be
done via VB code, but I'm not sure of the code that adds each new record to
Table D.

Any advice would be appreciated. Thanks in advance...

You should change the field "NAME" to something like "txtName" - "Name" is a
reserved word.

If I understand right, you will end up with at least three records added to
TableD if matching case num/yr are in each of tables A,B & C.

This code should work (or get you started):

'*** begin code snippet ***

Dim rsA As Recordset
Dim rsB As Recordset
Dim rsC As Recordset
Dim rsD As Recordset
Dim RESULT_CDE As Integer

' might add a WHERE clause to the Select statement find records
' right now Selects all records
Set rsA = CurrentDb.OpenRecordset("Select * From TableA")
Set rsB = CurrentDb.OpenRecordset("Select * From TableB")
Set rsC = CurrentDb.OpenRecordset("Select * From TableC")
Set rsD = CurrentDb.OpenRecordset("TableD")


'
' other code happens
'

If RESULT_CDE = 20 Then
'TABLE A
If Not (rsA.BOF And rsA.EOF) Then
rsA.MoveFirst
With rsD
While Not rsA.EOF
.AddNew
![CASE_NUM_YR] = rsA![CASE_NUM_YR] 'primary-key
part 1
![CASE_NUM] = rsA![CASE_NUM]
'primary-key part 2
'.[SEQNO] = (autoincrement field)
'primary-key part 3
![txtName] = rsA![FirstName] & " " & rsA![MiddleName] &
" " & rsA![LastName] & " " & rsA![SubTitle]
![FIRM] = rsA![FIRM]
![ADDR1] = rsA![ADDR1]
![ADDR2] = rsA![ADDR2]
![CITY] = rsA![CITY]
![STATE] = rsA![STATE]
![ZIPCODE] = rsA![ZIPCODE]
.Update
rsA.MoveNext
Wend
End With
End If
'End If

' If RESULT_CDE = 20 Then
'TABLE B
If Not (rsB.BOF And rsB.EOF) Then
rsB.MoveFirst
With rsD
While Not rsB.EOF
.AddNew
![CASE_NUM_YR] = rsB![CASE_NUM_YR] 'primary-key part 1
![CASE_NUM] = rsB![CASE_NUM] 'primary-key
part 2
'![SEQNO] = (autoincrement field)
'primary-key part 3
![txtName] = rsB![OWNERNAME]
![FIRM] = rsB![FIRM]
![ADDR1] = rsB![ADDR1]
![ADDR2] = rsB![ADDR2]
![CITY] = rsB![CITY]
![STATE] = rsB![STATE]
![ZIPCODE] = rsB![ZIPCODE]
.Update
rsB.MoveNext
Wend
End With
End If
'End If

'If RESULT_CDE = 20 Then
'TABLE C
If Not (rsC.BOF And rsB.EOF) Then
rsC.MoveFirst
With rsD
While Not rsC.EOF
.AddNew
![CASE_NUM_YR] = rsC![CASE_NUM_YR] 'primary-key part 1
![CASE_NUM] = rsC![CASE_NUM]
'primary-key part 2
'![SEQNO] = (autoincrement field)
'primary-key part 3
![txtName] = rsC![HEARINGOFCR]
![FIRM] = rsC![FIRM]
![ADDR1] = rsC![ADDR1]
![ADDR2] = rsC![ADDR2]
![CITY] = rsC![CITY]
![STATE] = rsC![STATE]
![ZIPCODE] = rsC![ZIPCODE]
.Update
rsC.MoveNext
Wend
End With
End If
End If

rsA.Close
rsB.Close
rsC.Close
rsD.Close

Set rsA = Nothing
Set rsB = Nothing
Set rsC = Nothing
Set rsD = Nothing

' delete next two lines after debuging complete
Me.Requery
MsgBox "DONE"

'*** end code snippet ***

HTH