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

Transferring cells to a new field, but shifting the down one row

P: 4
Hi,

I have created a test table with 10 different codes in 10 different cells (image below) in a field.

The goal is to develop a code that creates a new field where the cells in the previous field are shifted down and the cells that were previously at the very bottom are shifted up to the cell.

The code I have so far can create the new field, but I have no idea how to make it so the pattern of cells shift down from field1 to the new field as shown in the image.



Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3. Dim curDatabase As Database
  4. Dim tblTooAddToo As TableDef
  5. Dim FieldTest As Field
  6.  
  7. ' Get a reference to the current database
  8. Set curDatabase = CurrentDb
  9.  
  10. ' Get a reference to a table named TestTable - NOTE: The table MUST exist
  11. Set tblTooAddToo = curDatabase.TableDefs("Table1")
  12.  
  13. 'define the fields using the CreateField method
  14. ' Syntax: .CreateField("FIELD NAME", TYPE, [Length])
  15. ' I used the predefined types already in Access
  16. Set FieldTest = tblTooAddToo.CreateField("", dbText, 12)
  17.  
  18. 'use the APPEND method to add the fields to the table
  19. With tblTooAddToo.Fields
  20. .Append FieldTest
  21. End With
  22.  
  23. End Sub
Please let me know if you have any ideas.

Thank you!
May 17 '17 #1

✓ answered by PhilOfWalton

Here is a Micky Mouse Db that may give you a start.

Using the Employee form, you have to set up the first week's Schedule and sequnece (This is the order in which the employees are processed (It won't be alphabetical as in your example)

I have allowed for 7 days, so on the Form 1, if you select Day 1 (Monday?), it will create a shifted list for the following Monday and so on. So do that for days 1 to 6.

The results can be seen in Query1_Crosstab. Have a look at that without modifying the database. Then Use form 1 to add the next week's times and you will see how it progresses.

It takes a bit of time to set up the Employees Week 1 times, so if you manually delete any records from the TblJoinEmpTimeSlots, don't delete any records where the day is from 1 to 7. That's the base for the next weeks calculation.

As I say it's a possible starting point. I haven't looked at new employees or changes to someones schedule.

Hope that helps

Phil

Share this Question
Share on Google+
8 Replies


PhilOfWalton
Expert 100+
P: 1,430
I presume the code you have posted creates the new field successfully.

I think in order to accomplish what you want you are going to need to define a sort order, and whilst it is alphabetical in Field1, it no longer is alphabetical in field2.

Are you intending to continue the process to add Field3 based on Field2, if so where do you stop.

I must say it's an unusual question and a little background information on what you are trying to achieve might help.

Phil
May 19 '17 #2

P: 4
Hi Phil,

Thank you for taking the time to reply to my odd request .

I would like to create a button on a form that continues this process each time I click it.

So yes - once the cells are shifted done from field 1 to field 2 I would like to be able to shift field 2's cells down one again and have the bottom cell shifted to the top of field 3.

The reason I am trying to figure this is out is because I will be creating a staff schedule for a week, and to be fair, I want the schedule (new field) for the following week to shift down one cell and have the bottom cell shift to the top of the new field created.

The code below works to create a new field if you have a table named "TableTest":
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.  
  3. 'Dim curDatabase As Database
  4. 'Dim tblTooAddToo As TableDef
  5. 'Dim Day1 As Field
  6. 'Dim Day2 As Field
  7. 'Dim Day3 As Field
  8. 'Dim Day4 As Field
  9. ''Dim Day5 As Field
  10.  
  11. ' Get a reference to the current database
  12. 'Set curDatabase = CurrentDb
  13.  
  14. ' Get a reference to a table named TestTable - NOTE: The table MUST exist
  15. 'Set tblTooAddToo = curDatabase.TableDefs("Table1")
  16.  
  17.  
  18. 'define the fields using the CreateField method
  19. ' Syntax: .CreateField("FIELD NAME", TYPE, [Length])
  20. ' I used the predefined types already in Access
  21. 'Set Day1 = tblTooAddToo.CreateField("Day1", dbText, 12)
  22. 'Set Day2 = tblTooAddToo.CreateField("Day2", dbText, 2)
  23. 'Set Day3 = tblTooAddToo.CreateField("Day3", dbDouble)
  24. 'Set Day4 = tblTooAddToo.CreateField("Day4", dbText, 10)
  25. 'Set Day5 = tblTooAddToo.CreateField("Day5", dbText, 10)
  26.  
  27. 'use the APPEND method to add the fields to the table
  28. 'With tblTooAddToo.Fields
  29. '.Append Day1
  30. '.Append Day2
  31. '.Append Day3
  32. '.Append Day4
  33. '.Append Day5
  34. 'End With
  35.  
  36. Dim curDatabase As Database
  37. Dim tblTooAddToo As TableDef
  38. Dim FieldTest As Field
  39. 'Dim Day2 As Field
  40. 'Dim Day3 As Field
  41. 'Dim Day4 As Field
  42. ''Dim Day5 As Field
  43.  
  44. ' Get a reference to the current database
  45. Set curDatabase = CurrentDb
  46.  
  47. ' Get a reference to a table named TestTable - NOTE: The table MUST exist
  48. Set tblTooAddToo = curDatabase.TableDefs("TableTest")
  49.  
  50.  
  51. 'define the fields using the CreateField method
  52. ' Syntax: .CreateField("FIELD NAME", TYPE, [Length])
  53. ' I used the predefined types already in Access
  54. Set FieldTest = tblTooAddToo.CreateField("FieldTest", dbText, 12)
  55.  
  56.  
  57. 'use the APPEND method to add the fields to the table
  58. With tblTooAddToo.Fields
  59. .Append FieldTest
  60. End With
  61.  
  62. End Sub
Thanks again.
May 20 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
Sorry, I'm probably being a bit thick, and , if I may, I'm going to have to probe a bit deeper into what you are trying to achieve form the staff point of view, NOT the program point of view.

So say you have 12 staff, lets call them A,B,C,D,E,F,G,H,I,J,K,L.
It would appear from your code that they do a 5 day week.
Do all of then work every day, or do say A,G & L work on Monday of the first week and the rest have the week off, then B,E & I work on Tuesday of the following week, and everyone else has all the other days off?

That doesn't sound very likely, so hopefully that scenario is wrong.
Could you mean that on Week 1, A,G & L have to get in early to unlock, and on week 2, B,E and I have to get in early?

I think, it would be helpful if you sent a "table" (perhaps a spreadsheet) to show how your work schedule is laid out.

You may be on the right track, but my gut feeling is more in the lines of a Staff table and some sort of pointer as to which week they are "On Call"

Phil
May 20 '17 #4

P: 4
No worries at all Phil - I thought it would be best to not go too in depth so that's my fault. I am very grateful that you took the time to reply at all.


REG-O refers to a day off, and when Saturday cell is empty it means the employee is not scheduled.

So this is an example of one work week. I was trying to develop a code where I can create 6 new fields, and just shift the cells from the previous 6 fields down so that eventually everyone would have worked the same shift pattern.

To better explain - for the next week starting Monday, May 15th: Alexis will have Albert's shifts from the previous week, Bernie will get Alexis' shifts from the previous week.... and finally - Albert will get Terri's shifts from the previous week. The for the week starting May 22nd, the shifts would rotate again in the same way if I were to click the button.

Example of rotation from previous month:



Thank you.
May 20 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
Thanks for the info, that now makes perfect sense.

Give me a few days to look at it, and I will come back early next week. I think I can see a much neater solution, but I want to check it out

Phil
May 20 '17 #6

zmbd
Expert Mod 5K+
P: 5,287
This isn't a normalized database structure...
https://bytes.com/topic/access/insig...ble-structures
which may be adding to your frustrations.
May 22 '17 #7

PhilOfWalton
Expert 100+
P: 1,430
Here is a Micky Mouse Db that may give you a start.

Using the Employee form, you have to set up the first week's Schedule and sequnece (This is the order in which the employees are processed (It won't be alphabetical as in your example)

I have allowed for 7 days, so on the Form 1, if you select Day 1 (Monday?), it will create a shifted list for the following Monday and so on. So do that for days 1 to 6.

The results can be seen in Query1_Crosstab. Have a look at that without modifying the database. Then Use form 1 to add the next week's times and you will see how it progresses.

It takes a bit of time to set up the Employees Week 1 times, so if you manually delete any records from the TblJoinEmpTimeSlots, don't delete any records where the day is from 1 to 7. That's the base for the next weeks calculation.

As I say it's a possible starting point. I haven't looked at new employees or changes to someones schedule.

Hope that helps

Phil
May 22 '17 #8

P: 4
Thank you all for taking the time to help. I will work with the database you created for me Phil and go from there.
May 22 '17 #9

Post your reply

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