Write to table then read data

[Guest]

On a form I have a command button with the following code.
When clicked it copies the current forms data to a table and opens a form
which has that copied data.
First time thru it works fine, but when the record is edited and this button
is clicked the data is not updated.

I realise every time the buoon is clicked it writes new data to the table
but how can I make it pull the most current data from the table?

I don't want to over write the data in the table.. Do I need to give more
information??

Suggestions??


stDocName = "frmLetterVals"
lICCNNO = Me!ICNNo

lID = Nz(DMax("ID", "tblLetterVals"), 0) + 1

lCriteria = "INSERT INTO tblLetterVals (ID, ICNNo, ProvNo, ReceiptDate,
SummaryofComplaint, CurrentDate)"

lCriteria = lCriteria & "SELECT " & lID & " AS ID, tblQualityData.ICNNo,
tblQualityData.ProvNo, tblQualityData.CSReceiptDate, "
lCriteria = lCriteria & " tblQualityData.SummaryofComplaint,"
lCriteria = lCriteria & "#" & Format$(Now, "mm\/dd\/yyyy") & "# AS
CurrentDate "

lCriteria = lCriteria & "FROM tblQualityData "
lCriteria = lCriteria & "WHERE (((tblQualityData.ICNNo)=" & """" & lICCNNO &
"""" & "));"
CurrentDb.Execute lCriteria, DAO.dbFailOnError
'DoCmd.RunSQL lCriteria
'Debug.Print lCriteria

stLinkCriteria = "[ICNNo]=" & "'" & Me![ICNNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Jeff Boyce]

You've described a "how" ... i.e., how you are trying to accomplish
something.

Now, "what" and "why"? It sounds like you'd like to be able to see the data
that's just been entered. If so, why "clear" the form? Why not just
preserve the form/data on it and have the user click a button to create a
new record (you'd clear the form then).

You can bind a form to a query (and/or the underlying table), so the data
showing would BE the new data -- no need to "copy to table".

More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP