New to access and coding visual basic please explain in depth. I have what may be a simple question. I have 4 tables, Table1, Table2, Table3, and Table4. I will enter all date in Table1 using a form. The table has about 10 fields; field1, field2, field3….. I would like to have several yes/no fields that determine where the record will go next. If box1 is selected, then a copy of the record will be moved to Table2; If box2 is selected I would like a copy of the record to move to Table3. I would like for this to take place when I’ve pushed a button on the form. The record should be appended to the table it is sent to. One important thing is that the button should turn red and not work after it has been done once, so that the record is not duplicated and so that I know the action has been completed. Not all the fields are the same, I would like to take five out of the 10 fields in Table1 and copy them to Table2 and copy four of the fields from Table1 to Table3.

Lastly, I would like to do a similar function within Tables2 and 3, but would like to move the record to Table4 so that it is no longer in Tables2 and 3. Please help. I’ve been trying to get this to work for some time now. I thought it would be easy, it’s not.

Thank you!

Hello firedog,
A little involved, what your asking. I'm not sure how well your VBA is. I'm so so.
My first reaction is to use DAO or ADO. Here's ADO
I'm going to make a lot of assumptions, all the fields on all 4 forms, are named the same, only the table name & form name changes.
On the command button click method...
Dim stTable as String
Dim newRec as ADODB.RecordSet
Set newRec = New ADODB.Recordset

If chkTable1 Then
stTable = "Table1"
ElseIf chkTable2 Then
stTable = "Table2"
ElseIf chkTable3 Then
stTable = "Table3"
ElseIf chkTable4 Then
stTable = "Table4"
End If

NewRec.OpenRecordset stTable,CurrentProject.Connection,adOpenStatic,adL ockOptimistic


NewRec.AddNew
NewRec!Field1 = Me.Field1
NewRec!Field2 = Me!Field2
NewRec!Field3 = Me!Field3
etc...
NewRec.Update

NewRec.close
 set NewRec = Nothing

Rough idea, hope this helps! Good Luck.
Reply if further info needed.