RE: Add Record To Existing Table via VB code

"RNUSZ@OKDPS" wrote:

> 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...
>
> --
> Robert Nusz
> Sr. Programmer Analyst II

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
---
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
.