[gotagoJOE]

Private Sub cmdSubmit_Click()
Dim db As DAO.Database
Dim rstCurrent As DAO.Recordset
Dim rstHistory As DAO.Recordset
Dim response As Integer

	On Error GoTo ERR_HANDLER
	' open the tables
	Set db = CurrentDb
	Set rstCurrent = db.OpenRecordset("tblTable", dbOpenTable)
	Set rstHistory = db.OpenRecordset("tblHistory", dbOpenTable)
	
	' add a record to history
	rstHistory.AddNew
	rstHistory("EmployeeID") = rstCurrent("EmployeeId")
	rstHistory("EmployeeName") = rstCurrent("EmployeeName")
	rstHistory("reg1") = rstCurrent("reg1")
	rstHistory("reg2") = rstCurrent("reg2")
	rstHistory("reg3") = rstCurrent("reg3")
	rstHistory("reg4") = rstCurrent("reg4")
	rstHistory("reg5") = rstCurrent("reg5")
	rstHistory("reg6") = rstCurrent("reg6")
	rstHistory("reg7") = rstCurrent("reg7")
	rstHistory("reg8") = rstCurrent("reg8")
	rstHistory("RegTotal") = rstCurrent("regtotal")
	rstHistory("ot1") = rstCurrent("ot1")
	rstHistory("ot2") = rstCurrent("ot2")
	rstHistory("ot3") = rstCurrent("ot3")
	rstHistory("ot4") = rstCurrent("ot4")
	rstHistory("ot5") = rstCurrent("ot5")
	rstHistory("ot6") = rstCurrent("ot6")
	rstHistory("ot7") = rstCurrent("ot7")
	rstHistory("ot8") = rstCurrent("ot8")
	rstHistory("sick1") = rstCurrent("sick1")
	rstHistory("sick2") = rstCurrent("sick2")
	rstHistory("sick3") = rstCurrent("sick3")
	rstHistory("sick4") = rstCurrent("sick4")
	rstHistory("sick5") = rstCurrent("sick5")
	rstHistory("sick6") = rstCurrent("sick6")
	rstHistory("sick7") = rstCurrent("sick7")
	rstHistory("sick8") = rstCurrent("sick8")
	rstHistory("mut1") = rstCurrent("mut1")
	rstHistory("mut2") = rstCurrent("mut2")
	rstHistory("mut3") = rstCurrent("mut3")
	rstHistory("mut4") = rstCurrent("mut4")
	rstHistory("mut5") = rstCurrent("mut5")
	rstHistory("mut6") = rstCurrent("mut6")
	rstHistory("mut7") = rstCurrent("mut7")
	rstHistory("mut8") = rstCurrent("mut8")
	rstHistory("birthday1") = rstCurrent("birthday1")
	rstHistory("birthday2") = rstCurrent("birthday2")
	rstHistory("birthday3") = rstCurrent("birthday3")
	rstHistory("birthday4") = rstCurrent("birthday4")
	rstHistory("birthday5") = rstCurrent("birthday5")
	rstHistory("birthday6") = rstCurrent("birthday6")
	rstHistory("birthday7") = rstCurrent("birthday7")
	rstHistory("birthday8") = rstCurrent("birthday8")
	rstHistory("copaid1") = rstCurrent("copaid1")
	rstHistory("copaid2") = rstCurrent("copaid2")
	rstHistory("copaid3") = rstCurrent("copaid3")
	rstHistory("copaid4") = rstCurrent("copaid4")
	rstHistory("copaid5") = rstCurrent("copaid5")
	rstHistory("copaid6") = rstCurrent("copaid6")
	rstHistory("copaid7") = rstCurrent("copaid7")
	rstHistory("copaid8") = rstCurrent("copaid8")
	rstHistory("personal1") = rstCurrent("personal1")
	rstHistory("personal2") = rstCurrent("personal2")
	rstHistory("personal3") = rstCurrent("personal3")
	rstHistory("personal4") = rstCurrent("personal4")
	rstHistory("personal5") = rstCurrent("personal5")
	rstHistory("personal6") = rstCurrent("personal6")
	rstHistory("personal7") = rstCurrent("personal7")
	rstHistory("personal8") = rstCurrent("personal8")
	rstHistory("vac1") = rstCurrent("vac1")
	rstHistory("vac2") = rstCurrent("vac2")
	rstHistory("vac3") = rstCurrent("vac3")
	rstHistory("vac4") = rstCurrent("vac4")
	rstHistory("vac5") = rstCurrent("vac5")
	rstHistory("vac6") = rstCurrent("vac6")
	rstHistory("vac7") = rstCurrent("vac7")
	rstHistory("vac8") = rstCurrent("vac8")
	rstHistory("GrandTotal") = rstCurrent("GrandTotal")
	rstHistory("otcode1") = rstCurrent("otcode1")
	rstHistory("otcode2") = rstCurrent("otcode2")
	rstHistory("otcode3") = rstCurrent("otcode3")
	rstHistory("otcode4") = rstCurrent("otcode4")
	rstHistory("otcode5") = rstCurrent("otcode5")
	rstHistory("otcode6") = rstCurrent("otcode6")
	rstHistory("otcode7") = rstCurrent("otcode7")
	rstHistory("otcode8") = rstCurrent("otcode8")
	rstHistory("EndDate") = rstCurrent("EndDate")
	rstHistory.Update
	
	' Delete the record in the "current" table
	CurrentDb().Execute "DELETE * FROM tblTable"
EXITHERE:
	'clear the objects
	Set rstCurrent = Nothing
	Set rstHistory = Nothing
	Set db = Nothing
	
	'close the form
	DoCmd.Close acForm, "frmStaticTimeSheet"
	Exit Sub
	
	
ERR_HANDLER:
	response = MsgBox("An error has occurred: " & Err.Description, vbOKOnly, "Error")
	Resume EXITHERE
End Sub

I am having trouble with zero's displaying and do not want them. I originally used a statement in update event - if me.textbox - 0 then me.textbox = null.
but now I had to create submit button lets user add data to a history table and open their table blank again-
Is there still a way to have zero's not appear.
thank you

[ForcedSterilizationsForAll]

I'm a little confused, where are you getting the data on the form? Is it something that is loaded from another table or just manually typed in? If it's loaded from another table, what is the value in that table?

[gotagoJOE]

yes typed into form that is connected to tblTable. When I hit submit button data transfers into tblHistory ( which is a ghost table) and the form when reopened is empty and so is tblTable.

[ForcedSterilizationsForAll]

so when you first open the form and everything is blank, the number columns have zeroes? What is the default value for those columns in the table?

[gotagoJOE]

ForcedSterilizationsForAll, on 1 Feb, 2010 - 12:39 PM, said:

so when you first open the form and everything is blank, the number columns have zeroes? What is the default value for those columns in the table?

in design view of table default value blank - data type is number- wait a minute
you know I think you found the problem- I see zero's now
thank you-

[PsychoCoder]

This is really more of a VB6 question rather than a database one. Moved to VB6

[ForcedSterilizationsForAll]

Actually, it's more of an MSAccess Forms question. The code is VBA. But it's a moot point.

[June7]

gotagoJoe,

Why are you going to the trouble of moving data and deleting record? Instead of saving the same data to another table, add a field to the original table that holds a value that indicates the status of the record, such as 'active' or 'inactive'.

If you really must do this then more efficient structure is the SQL INSERT with SELECT statement. Example:

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO TestsFieldAlias (DataTable, DataField, Alias, Specification, Transfer) " & _
"SELECT " & Me.tbxTestNum & " AS DataTable, DataField, Alias, Specification, Transfer " & _
"FROM BuildTable WHERE Specification = -1 Or Transfer = -1;"
DoCmd.RunSQL "DELETE FROM BuildTable WHERE DataField <> 'LabNum' AND DataField <> 'method'"
DoCmd.SetWarnings True

[gotagoJOE]

thank you DIC - I will have to study this to fathom- I moved data - because I want submit button to open table to add data for the week and when Friday comes- users hit submit button and are returned a blank form for next week
thank you