duplicate values stoping ALL appends

[Ralph]

I am reformating data from table 1 and sending it to table 2 with a button
command. Sometimes I need to delete one or two rows from table 2 and table 1
to create a new updated record with the same primary key. I want to rerun
the button and replace the updated records in table 2. I do not want to
replace all the records. Problem is when I rerun the button I get an Error
3022 and the append to table 2 fails. I'd like it to append the updated
records and ignore those already appended. I suppose I could delete all the
rows from table 1 after the append. Then when I redo a row it would be the
only one in table 1 and it would work. I was thinking to leave the rows that
were okay in table 1, if I do this in a query the duplicates would not append
but the new rows I've recreated would. Office 2003.

If my best option is to simply delete all the rows from table 1 after they
are appended to table 2, how do I do this?

This is the code I am using:

Private Sub btnPage1_Click()

DoCmd.SetWarnings False
On Error GoTo ProcError

Dim strSQL As String
Dim FNSmo As String

FNSmo = MonthName(Me.grpSampleMonth, True) & txtCalYr


strSQL = "INSERT INTO tbl1FNS" & FNSmo & " ( ReviewNo, CaseNo)" _
& "SELECT tblPage1.ReviewNo, Right(0000000000 & [CaseNo],10) AS CaseN " _
& "FROM tblPage1 " _
& "WHERE CInt(Mid([tblPage1].[ReviewNo],2,2)) = " & _
Me.grpSampleMonth
CurrentDb.Execute strSQL, dbFailOnError

MsgBox "The page 1 data is loaded in the FNS database"
DoCmd.SetWarnings True
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure YourProcedureName..."
Resume ExitProc


End Sub

 

[Ralph]

One of the tables changes depending on the month, there are 12. I'd like to
compare the ReviewNo fields from the one to which ever of the 12 is
appropriate depending on the month. Something like (but it doesn't work)

FNSmo = MonthName(Me.grpSampleMonth, True) & txtCalYr
Del = tbl1FNS & FNSmo
'where Del is the name of the table taking values from two unbound text boxes

'
'
'
& "FROM tblPage1, Del " _
& "WHERE CInt(Mid([tblPage1].[ReviewNo],2,2)) = " & _
Me.grpSampleMonth And tblPage1.ReviewNo <> Del.ReviewNo

I hope it is clear what I am attempting.

thanks,

 

[pietlinden]

Ralph,
You're not going to like my response, so brace yourself. BAD design.
Why do you have repeating tables? Why not just include the year (or
whatever differentiates the tables - month or whatever) IN the table's
definition? Just add a column for YEAR or whatever and put all the
data in one large table. Then your problems should pretty much go
away. You can update subsets of the data in your table by including a
WHERE clause in your UPDATE statements.

The fact that you have to modify the structure of your database over
time should be a dead giveaway that something is wrong. Unless you
are tracking facts about completely new things, the structure of your
database should remain stable. Then you should be able to modify your
data using (parameterized) action queries and be fine. Otherwise, you
will risk breaking your queries etc every time you update your tables
- they're expecting a static structure.

If you cannot change the structure of your database (I would
*strongly* recommend doing it if at all possible!!!), you can query
different tables with the same SQL statement... kind of.

Say you have a combobox of tables you can query on an unbound form and
you have a button. Say for the sake of argument, the query is
"QueryX". You could do something like this:

SubModifyQuerySQL (byval strOldSrc as string, byval strNewSrc as
String)
' strOldSrc = the query that the old SQL statement is based on
' strNewSrc = the query that the NEW SQL statement is based on

dim qdf as dao.querydef
dim strSQL as String

set qdf = CurrentDb.Querydefs(strOldSrc)
strSQL = Replace(qdf.SQL, strOldSrc, strNewSrc)
qdf.SQL = strSQL

set qdf=nothing
End Sub

 

[Ralph]

thank you for helping me to clarify the structure of tables and how I will
handle moving the data around.

I have solved the riddle and do appreciate your assistance.

Ralph