Move current record in a continuous form (1 Viewer)

[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,

 

[Pat Hartman]

DALeffler,

Dim rst1, rst2 as Recordset

Only rst2 is defined as a RecordSet.
The following is the correct code:

Dim rst1 as Recordset
Dim rst2 as Recordset

1. Also, to avoid conversion/upgrade issues, all DAO objects should be disambiguated.
2. The code loop using index values to copy the data from one table to another is an accident waiting to happen should someone move or insert a column in one of the tables as well as being obtuse. Take a little extra time and spell out the column names.

Tinku,

Thanks for your reply. The diff tables are the requirement of the client.

If the client is paying you to develop the application, why is he designing the tables? A single table is the "correct" solution in a properly normalized relational database. Show the client how a query can select each different set of records. You should also point out the cost difference between a single table and duplicate tables. You are certainly having to do more work to accomodate this constraint and the client should be aware that he is paying for it.