By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

move more than one record at a time

100+
P: 105
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdMoveto1_Click()
  2. MoveFeedersOnceRightInBudgetPlan Right(cmdMoveto1.Caption, 4)
  3. End Sub
-----------------------------------------------------------------------------------------------------------

Expand|Select|Wrap|Line Numbers
  1. Public Function MoveFeedersOnceRightInBudgetPlan(yearToMoveTo As Integer)
  2.  
  3. 'Desc: When feeders are moved from year to year in the budget '
  4. '       plan, depending on certain factors, records may have  '
  5. '       to be kept on why the feeder(s) was/were moved        '
  6.  
  7. If Forms!frmMoveMultipleFeeders!chkMoveFeeders.value = True Then
  8.     'Save all records that might have been changed
  9.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  10.     Dim CurrentYear As Integer
  11.     'Set CurrentYear to the value of the current year
  12.     CurrentYear = Forms!frmPlan!txtYear.value
  13.  
  14.  
  15.         If Year(Forms!frmPlan!frmPlanList.Form.Controls("PlanDate").value) = yearToMoveTo Or _
  16.             Year(Forms!frmPlan!frmPlanList.Form.Controls("PlanDate").value) <> CurrentYear Then
  17.  
  18.             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."
  19.             Exit Function
  20.         End If
  21.  
  22.  
  23.         'The user is trying to move something into or out of current year, a record needs to be kept on why the
  24.         'feeder is being moved. This is done thru frmPlanMove
  25.         If isPlanLocked(CurrentYear, Forms!frmMain!frameDivision.value) Or isPlanLocked(yearToMoveTo, Forms!frmMain!frameDivision.value) Then
  26.  
  27.             DoCmd.OpenForm "frmPlanMove"
  28.             'Copy values from frmPlanList to frmPlanMove
  29.             Forms!frmPlanMove!FdrID.value = Forms!frmPlan!frmPlanList.Form.Controls("FdrID").value
  30.             Forms!frmPlanMove!lblFeeder.Caption = Forms!frmPlan!frmPlanList.Form.Controls("FeederName").value & " - " & Forms!frmPlan!frmPlanList.Form.Controls("FeederNumber").value
  31.             Forms!frmPlanMove!NewPlanDate.value = DateAdd("yyyy", yearToMoveTo - CurrentYear, Forms!frmPlan!frmPlanList.Form.Controls("PlanDate").value)
  32.             Forms!frmPlanMove!UTMiles.value = Forms!frmPlan!frmPlanList.Form.Controls("UTMiles").value
  33.             Forms!frmPlanMove!UTCost.value = Forms!frmPlan!frmPlanList.Form.Controls("UTCost").value
  34.             Forms!frmPlanMove!RTMiles.value = Forms!frmPlan!frmPlanList.Form.Controls("RTMiles").value
  35.             Forms!frmPlanMove!RTCost.value = Forms!frmPlan!frmPlanList.Form.Controls("RTCost").value
  36.             Forms!frmPlanMove!MTMiles.value = Forms!frmPlan!frmPlanList.Form.Controls("MTMiles").value
  37.             Forms!frmPlanMove!MTCost.value = Forms!frmPlan!frmPlanList.Form.Controls("MTCost").value
  38.             Forms!frmPlanMove!RLMiles.value = Forms!frmPlan!frmPlanList.Form.Controls("RLMiles").value
  39.             Forms!frmPlanMove!RLCost.value = Forms!frmPlan!frmPlanList.Form.Controls("RLCost").value
  40.  
  41.             Forms!frmPlanMove!LowDate.value = "1/1/" & yearToMoveTo
  42.  
  43.             'There are two sets of reasons.
  44.  
  45.             'Moving Feeder out of current year
  46.             If CurrentYear = getPlanStartingYear Then Forms!frmPlanMove!Reason.RowSource = "SELECT tblPlanMoveReason.ReasonID, tblPlanMoveReason.Reason FROM tblPlanMoveReason WHERE (((tblPlanMoveReason.MoveIn)=False));"
  47.                 'Moving Feeder in to current year
  48.                 If CurrentYear <> getPlanStartingYear Then Forms!frmPlanMove!Reason.RowSource = "SELECT tblPlanMoveReason.ReasonID, tblPlanMoveReason.Reason FROM tblPlanMoveReason WHERE (((tblPlanMoveReason.MoveIn)=True));"
  49.                     Forms!frmPlan!frmPlanList.Form.Requery
  50.                 Else
  51.                     Forms!frmPlan!frmPlanList.Form!PlanDate.value = DateAdd("yyyy", yearToMoveTo - CurrentYear, Forms!frmPlan!frmPlanList.Form!PlanDate.value)
  52.         End If
  53.         Exit Function
  54.  
  55.     End If
  56.  
  57. errorHandle:
  58. If Err.Number = 2105 Then
  59.     Exit Function
  60. Else
  61.     MsgBox (Err.Number & " " & Err.Description)
  62. End If
  63.  
  64. End Function
Thanks in advance for your help!!
Jan 14 '08 #1
Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,355
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.
Jan 14 '08 #2

100+
P: 105
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.
Jan 14 '08 #3

Rabbit
Expert Mod 10K+
P: 12,355
Did I misunderstand when you said "How do i check which checkboxes were checked and ONLY move those that were checked?"
Jan 14 '08 #4

100+
P: 105
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!
Jan 14 '08 #5

100+
P: 105
there should be code for this, which i am unsure of still, like....

chkMoveFeeders.SetFocus
Move = chkMoveFeeders.Value

If Forms!frmMoveMultipleFeeders!chkMoveFeeders.Value = True Then


something like that...?
Jan 14 '08 #6

Rabbit
Expert Mod 10K+
P: 12,355
Can't you just have a BudgetYear field that they change? Changing that field should change the results of the queries.
Jan 14 '08 #7

100+
P: 105
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.
Jan 14 '08 #8

Rabbit
Expert Mod 10K+
P: 12,355
Not a problem, good luck.
Jan 14 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.