Appending problem after splitting field

[forcefield via AccessMonster.com]

I have 2 tables: tblA and tblB. The fields are as follows
tblA:
AID autonumber
AItem text
AModelNo text ( may consists of several numbers )
AExPrice Yes/No (yes if price >$500)

tblB:
BID autonumber
LinkAID number
Bitem text
BModelNo text
BExprice Yes/No

When a form opens tblA fields, after splitting AModelNo, are appended to
tblB where the price >$500.
Eg
In tblA:
AID AItem AModelNo AExPrice
1 Printer P2348 P2349 No
2 TV T2734 T2930 T4530 yes
3 Radio R2946 yes

The result appended in tblB is
BID Bitem BModelNo BExPrice
1 TV T2734 Yes
2 TV T2930 Yes
3 TV T4530 Yes
4 Radio R2946 Yes

To append the split I have the following code when a form opens . This ensure
that tblB is appended automatically.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim IntAID As Integer
Dim strItem As String
Dim lngLoop As Long
Dim strSQL As String
Dim strModelNo As String
Dim varModelNo As Variant
Dim strPrice As AcObjectType

strSQL = "SELECT AID,AItem,AModelNo,AExPrice FROM tblA Where
AExPrice = True "

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

Do While rst.EOF = False
IntAID = rst!AID
strItem = rst!AItem
strPrice = rst!AExPrice
strModelNo = rst!AModelNo
varModelNo = Split(strModelNo, " ")

If IsNull(varModelNo) = False Then
For lngLoop = LBound(varModelNo) To UBound(varModelNo)
'=============================================
' appending to tblB
'=============================================
strSQL = "Insert into tblB(LinkAID,BItem,BModelNo,BExPrice)" &
_
"values('" & IntAID & "','" & Replace(strItem, "'", "''") & "',
'" &
varModelNo(lngLoop) & "','" & strPrice & "')"

dbs.Execute strSQL, dbFailOnError

Next lngLoop
End If

rst.MoveNext
Loop

Meanwhile some records in tblB may be deleted as items are written off and
tblA may be added with new records.
I would like tblB to be appended (with the split in AModelNo) with the new
records only . Those records in tblB that are written off will not be updated
from tblA. Can this be done?

In tblA:
AID AItem AModelNo AExPrice
1 Printer P2348 P2349 No
2 TV T2734 T2930 T4530 Yes
3 Radio R2946 Yes
4 Fridge F7658 F8324 Yes

If record in tblB is deleted say,
BID Bitem BModelNo BExPrice
3 TV T4530 Yes

When a form opens, the result appended to tblB from tblA should be
BID Bitem BModelNo BExPrice
1 TV T2734 Yes
2 TV T2930 Yes
4 Radio R2946 Yes
5 Fridge F7658 Yes
6 Fridge F8324 Yes

Can any expert help?

 

[Tom van Stiphout]

On Wed, 03 Feb 2010 12:26:39 GMT, "forcefield via AccessMonster.com"

I think you will need an extra field in tblA to indicate that the
record has already been split, so you can check this field and not do
it again.

-Tom.
Microsoft Access MVP

 

[forcefield via AccessMonster.com]

Thank you for the reply.
I am a little puzzled how to do it. Can you expound on what you had said or
show me your idea in a table form.

I think you will need an extra field in tblA to indicate that the
record has already been split, so you can check this field and not do
it again.

-Tom.
Microsoft Access MVP

I have 2 tables: tblA and tblB. The fields are as follows
tblA:

[quoted text clipped - 97 lines]

Can any expert help?