Not a Problem...

[satty]

hey again people!!

i have yet another problem!!

i have a table called tblMembership here all the members are stored. i also have a table called tblMembershipArchive. i have a append query which sends all the 1 year old members to the member archive. this is all working fine!!

the problem

i have a field called Activate in the tblMembershipArchive. this is a YES/NO field. When i select Yes i want a query to move this record back to the tblmembership table.

im just having problems doing this? can you guys help!?


Thanx



Satty
Merry Christmas People!!!!

 

[davidpcurtis]

Using event processing, set the ONCLICK property to run a query to move all records with a YES in this field back to the membership table then refresh.

 

[satty]

ok i have been trying to do this but it is just not working. should i be putting a YES in the query criteria or a 1 or 2 to show it is selected. im finding it confusing.

the query comes up with ZERO matches or something similar like this and is always empty.

thanx

 

[Russell Hauf]

You can't do it from the field in the table. You'll need to do it via a Form or some other way. You don't necessarily need an "activate" field in either table.

So what I would do is to make a form based on the Archive table, and put in the fields that you'd want to look at (name, membership number, etc.) and then put an unbound CheckBox on the form. Then you could run the query (actually, it would probably be queries, one append, aand one delete...since you'd want to delete them from the archive table after putting them back into the active table, correct?) in the _Click Event of the checkbox. It might look something like this:

Private Sub chkActive_Click()
    DoCmd.SetWarnings False
    
    ' Append first
    DoCmd.RunSQL "INSERT INTO tblMembership " _
        & "SELECT * FROM tblMembershipArchive " _
        & "WHERE tblMembershipArchive.MemberID='" & Me.MemberID & "';"

    ' Delete after append
    DoCmd.RunSQL "DELETE tblMembershipArchive.* FROM " _
        & tblMembershipArchive " _
        & "WHERE tblMembershipArchive.MemberID='" & Me.MemberID & "';"
    DoCmd.SetWarnings True
    Me.Requery
    Me.chkActive = False
End Sub

Hope this helps,

Russell

p.s. One alternative to this (depending on table size and how long it takes your queries to run, etc.) is to simply have one Membership table, and add a field called "Active". Then instead of running a query to put all the 1-year people into another table, just set their Active field to No/False. Then you can query the "active" people by saying Active = Yes, and the inactives by saying Active = No. Again, if your tables are huge and you do a lot of querying off of them, then this might not be the best way. But if they are relatively small and/or you don't query off them too much, then this way would be easier in a lot of ways.