Move current record in a continuous form

[Tinku]

I have a form which has a listbox with 4 values
Active, Inactive, Win, Loss

I have 4 tables with the above values ie Active,
Inactive, Win, Loss. Only the table Active has records.
I have created a form for this table. Depending upon the
selection of the listbox I want to move the current record
from Active to other tables. For eg if the change is from
Active -> Inactive then the current record gets moved to
Inactive table.

I tried some code.. doesnt work

Private Sub State_Change()
Dim db As Database
Dim rs As Recordset
Dim rstemp As Recordset
Dim X As Variant
i = 0

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblActive")

X = Me.State.Value
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF

MsgBox (rs.Fields("State").Value)
If rs.Fields("State").Value = "Inactive" Then
MsgBox (rs.Fields("State").Value)
Set rstemp = db.OpenRecordset("tblInactive")
rstemp.AddNew
Do While i < 19
rstemp.Fields(i) = rs.Fields(i).Value
i = i + 1
Loop
rstemp.Update
rs.Delete
Set rstemp = Nothing
rs.MoveNext
i = 0
End If
Loop
End Sub

 

[DALeffler]

If you don't mind my asking, why are you trying to move records from one table to another identical - except for the table name- table?

At least that's what it seems you're trying to do: If the Active, Inactive, Win, and Loss tables aren't identical except in name, it's very easy to have problems reading and writing records accuratly between the tables using a simple loop counter (i) as a field index.

I would suggest having 1 table with all the records in it and 1 field in each record of the table for Active, Inactive, Win or Loss (you can create a look-up table for the "status" of each record).

hth,

 

[Tinku]

Hi Doug

Thanks for your reply. The diff tables are the requirement of the client.
I had suggested having a listbox field with 4 entries, but he said he needed
for tables. So if I were to do this.. how would I do it ?
Where am I going wrong in my code ?

Regards
Tinku

 

[DALeffler]

Here's the code I wrote to simulate something along the lines of what you were doing:

Function fncFields

Dim dbs as Database
Dim rst1, rst2 as Recordset
Dim I as Integer

Set dbs = CurrentDb

Set rst1 = dbs.OpenRecordset("BatMod", dbOpenSnapShot)
Set rst2 = dbs.OpenRecordset("Temp1", dbOpenDynaset)

rst1.MoveFirst
Do While Not rst1.EOF
     rst2.AddNew
     I = 1 'skip Autonumber Field
     Do While I < 18
          rst2.Fields(I) = rst1.Fields(I)
          I = I + 1
     Loop
rst2.Update
rst1.MoveNext
Loop

End Function

If I left off the recordset type while setting the recordset objects, the code wouldn't run: No error message; just a system beep and no new records in table Temp1. Otherwise, the code ran fine...

Be aware that "State" is a property name for a command bar button control.

hth,