move more than one record at a time

[jmarcrum]

I have a table with about 100 records. The user has the ability to click a button on the main form that will open up a "move to the next year" form. A query displays all 100 records on the continuous form. The user can then checkbox which record he/she wants to move to the next year.

Here's my question....

How do i check which checkboxes were checked and ONLY move those that were checked? Also, the table that it pulls from is a linked table, linked to the server...when the user hits the cancel button on the form, i try to run an update query to reset all of the checkboxes to false, but the query hangs there b/c it won't let me update. Is there a better solution for this as well?

Here's my code so far on the form that doesn't work. As of now, the only record that will be moved to the next year when the "Move" button is clicked is the current record selected. How do I check which checkboxes are checked?

Private Sub cmdMoveto1_Click()
MoveFeedersOnceRightInBudgetPlan Right(cmdMoveto1.Caption, 4)
End Sub

-----------------------------------------------------------------------------------------------------------

Public Function MoveFeedersOnceRightInBudgetPlan(yearToMoveTo As Integer)
 
'Desc: When feeders are moved from year to year in the budget '
'       plan, depending on certain factors, records may have  '
'       to be kept on why the feeder(s) was/were moved        '
 
If Forms!frmMoveMultipleFeeders!chkMoveFeeders.value = True Then
    'Save all records that might have been changed
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Dim CurrentYear As Integer
    'Set CurrentYear to the value of the current year
    CurrentYear = Forms!frmPlan!txtYear.value
 
 
        If Year(Forms!frmPlan!frmPlanList.Form.Controls("PlanDate").value) = yearToMoveTo Or _
            Year(Forms!frmPlan!frmPlanList.Form.Controls("PlanDate").value) <> CurrentYear Then
 
            MsgBox "You cannot change the date from here." & vbCrLf & "To reschedule this line, press the Requery Plan button and goto the year that matches the plan date."
            Exit Function
        End If
 
 
        'The user is trying to move something into or out of current year, a record needs to be kept on why the
        'feeder is being moved. This is done thru frmPlanMove
        If isPlanLocked(CurrentYear, Forms!frmMain!frameDivision.value) Or isPlanLocked(yearToMoveTo, Forms!frmMain!frameDivision.value) Then
 
            DoCmd.OpenForm "frmPlanMove"
            'Copy values from frmPlanList to frmPlanMove
            Forms!frmPlanMove!FdrID.value = Forms!frmPlan!frmPlanList.Form.Controls("FdrID").value
            Forms!frmPlanMove!lblFeeder.Caption = Forms!frmPlan!frmPlanList.Form.Controls("FeederName").value & " - " & Forms!frmPlan!frmPlanList.Form.Controls("FeederNumber").value
            Forms!frmPlanMove!NewPlanDate.value = DateAdd("yyyy", yearToMoveTo - CurrentYear, Forms!frmPlan!frmPlanList.Form.Controls("PlanDate").value)
            Forms!frmPlanMove!UTMiles.value = Forms!frmPlan!frmPlanList.Form.Controls("UTMiles").value
            Forms!frmPlanMove!UTCost.value = Forms!frmPlan!frmPlanList.Form.Controls("UTCost").value
            Forms!frmPlanMove!RTMiles.value = Forms!frmPlan!frmPlanList.Form.Controls("RTMiles").value
            Forms!frmPlanMove!RTCost.value = Forms!frmPlan!frmPlanList.Form.Controls("RTCost").value
            Forms!frmPlanMove!MTMiles.value = Forms!frmPlan!frmPlanList.Form.Controls("MTMiles").value
            Forms!frmPlanMove!MTCost.value = Forms!frmPlan!frmPlanList.Form.Controls("MTCost").value
            Forms!frmPlanMove!RLMiles.value = Forms!frmPlan!frmPlanList.Form.Controls("RLMiles").value
            Forms!frmPlanMove!RLCost.value = Forms!frmPlan!frmPlanList.Form.Controls("RLCost").value
 
            Forms!frmPlanMove!LowDate.value = "1/1/" & yearToMoveTo
 
            'There are two sets of reasons.
 
            'Moving Feeder out of current year
            If CurrentYear = getPlanStartingYear Then Forms!frmPlanMove!Reason.RowSource = "SELECT tblPlanMoveReason.ReasonID, tblPlanMoveReason.Reason FROM tblPlanMoveReason WHERE (((tblPlanMoveReason.MoveIn)=False));"
                'Moving Feeder in to current year
                If CurrentYear <> getPlanStartingYear Then Forms!frmPlanMove!Reason.RowSource = "SELECT tblPlanMoveReason.ReasonID, tblPlanMoveReason.Reason FROM tblPlanMoveReason WHERE (((tblPlanMoveReason.MoveIn)=True));"
                    Forms!frmPlan!frmPlanList.Form.Requery
                Else
                    Forms!frmPlan!frmPlanList.Form!PlanDate.value = DateAdd("yyyy", yearToMoveTo - CurrentYear, Forms!frmPlan!frmPlanList.Form!PlanDate.value)
        End If
        Exit Function
 
    End If
 
errorHandle:
If Err.Number = 2105 Then
    Exit Function
Else
    MsgBox (Err.Number & " " & Err.Description)
End If
 
End Function

Thanks in advance for your help!!

[Rabbit]

Whether the tables are linked or embedded, residing on a server or not, as long as you have read/write permissions you should be able to update those tables. The query shouldn't hang if the query is set up properly.

As for moving the records, just run an append query that selects all records that have the checkbox checked. And then run another query to uncheck the records.

[jmarcrum]

As for moving the records, just run an append query that selects all records that have the checkbox checked. And then run another query to uncheck the records.

But I don't want to move the data from the table the query is pulling from to another table. I just want to change the data in the year column that's listed within the same table, while also allowing the user to insert comments on a pop-up form as to why they are wanting to move the record. My code shows something to that effect, but right now, only applies to the current record SELECTED, not checked.

[Rabbit]

Did I misunderstand when you said "How do i check which checkboxes were checked and ONLY move those that were checked?"

[jmarcrum]

Did I misunderstand when you said "How do i check which checkboxes were checked and ONLY move those that were checked?"

Well, when I said move, I mean change, like, leave the other records alone that weren't changed (as in checkbox check change). When the checkbox is checked, there is a column within the table the becomes -1 while the others remain 0.

Example:

Record 1 is in the 2007 Budget Plan Query
Record 2 is in the 2007 Budget Plan Query
Record 3 is in the 2007 Budget Plan Query
Record 4 is in the 2008 Budget Plan Query
Record 5 is in the 2008 Budget Plan Query
Record 6 is in the 2010 Budget Plan Query

The user has specified by clicking the 2007 radio button that he/she wants to view the 2007 Budget Plan. The user sees Records 1, 2, and 3 and doesn't want them to show up in his/her 2007 Plan. So they have to use the record selector on EACH record and go through the process ONE AT A TIME of clicking the "move to 2008 Budget Plan" command button and entering their reason why they want EACH ONE to show up there instead. NOW, when they click the 2008 radio button, the record shows up in THERE instead.

I need a way of selecting all 3 at the same time!

[jmarcrum]

there should be code for this, which i am unsure of still, like....

chkMoveFeeders. SetFocus
Move = chkMoveFeeders. Value

If Forms!frmMoveMu ltipleFeeders!c hkMoveFeeders.V alue = True Then


something like that...?

[Rabbit]

Can't you just have a BudgetYear field that they change? Changing that field should change the results of the queries.

[jmarcrum]

Whether the tables are linked or embedded, residing on a server or not, as long as you have read/write permissions you should be able to update those tables. The query shouldn't hang if the query is set up properly.

As for moving the records, just run an append query that selects all records that have the checkbox checked. And then run another query to uncheck the records.

I see what you were saying now with the append query...

here's a question from someone else on google...

I have a database with training records in it and they each have several categories within. Example: Jones, Smith, Gordon, Jordan are all required to have a suicide awareness class annually. How can I update the date of the class for Jones and Jordan all at once? In reality, there are 200 records and the class is given 25 at a time and I don't want to have to update 25 at a time individually. Can I select 25 records and update the dates all at one time if the 25 users are from different departments, etc?
Thanks for your time.

Here's the answer...

Bruce,

Many thanks for the question.

To do what you ask, add a Yes/No field to the client record.

I have presumed there is a relationship between the client and training tables, if not create one.

Then create a form that list both the client and the training tables. You should be able to do this by following the form wizard. Choose a continuous form as the type of form. Add the fields that you require to make a decision about which records you wish to update, but be sure to include the new Yes/No field.

You can use this form to sort, filter etc by using the toolbar . Then just tick the check boxes of those clients you want to update.

Then make an append query by:

IMPORTANT: Make a copy of the table of which you wish to change the dates, in case something gets messed up. An update query cannot be undone once it has been run. (Right click on the table > copy then right click in some white space > paste)


In the query section click on New > Design View

Add the client table and the classes table. Close the add table window.

At the top of the query, click on Query > Update Query

This will add a new row in your query with Update to:

Add the following fields (by double clicking on them):

'date of class' , then in the update row type in the new date.

'Yes/No' field. Leave the update to row blank and in the criteria row type -1 (which is a Yes or a tick). Only those records you have ticked will be updated.

You can automate this by adding a command button to your continuous form, follow the Wizard and choose to open/run this append query you have just created.

You will need to change the date you have entered in the update to row: to [Enter New Date] this will prompt you to enter a new date each time the query is run.

Hope this is of some help

Regards

Julie

Thank you for your help again Rabbit...i'm a novice at this stuff and it kills me when I don't know what I'm doing or what I'm talking about, which is the case MOST of the time.

[Rabbit]

Not a problem, good luck.