Moving a record

Mikeyfyr

Registered User.
Local time
Today, 13:28
Joined
Dec 14, 2003
Messages
18
I have a continuous form consisting of records that have the following fields. Names, phone numbers, and a caculated field of hours worked. I want to add a button, that when clicked will take the specific record and send it to the bottom of the form. This record is not always at the top of the form. I have tried to do this with macros but have been unsuccessful. I do not know visual basic so if this can be done please explain the proccess in steps. I have taken some classes and spoke to some people who are supposed to be experts at Access and they have not been able to help me. You guys might be my last hope. Thanks.
:confused:


Mike Tonner
Firefighter
Sparks, NV, USA
 
I'm not sure what you are talking about. Standard behaviour is to enter data at the "bottom" of a continuous form.
 
The form is essentially a list of names. The number of records only changes as personnel change. We use the list to rotate overtime though the personnel list. Once a person accumulates 24 hours of overtime their name is rotated to the bottom of the list. Right now we use a card file. I thought I could build a personnel list that would function more effectively in Access. Maybe this is not the proper way to accomplish this. Do you have any suggestions. Thanks for the help.

Mike Tonner
 
You could just use the OrderBy property in the underlying query, but that will sort based on Overtime as a value, is that what you want?
 
The sort will almost never be in order unfortunately. A person can have any number of hours of overtime, but will not rotate to the bottom of the list until they accumulate a total of 24 hours.
 
The list has no real order as it changes continuously. I have said that I want to move a record to the bottom after that person accumulates 24 hours. Would it help if I reversed the order of the list and rotate records to the top.
The more I try to make this work, the more I think that Access is the wrong program. I might try to do this in Excel, though I am not at all familiar with that program.
 
Access (or any relational database) orders records based on the sequence of the data value of some field (or fields). "Position" in a recordset is not handled the way it is with flat files such as excel. With something like excel, records can be physically moved from one position to another without any relation to any defined sort order.

You could simulate the process by making your own sequence field and then mechanically changing the value of the sequence field so that the records will sort in the order you want to present them. But there is no way with ANY relational database (this is not an Access limitation) to simply move a row from one position in a recordset to another. You need to sort the recordset on some field that is going to result in sequencing the records in your desired order.
 
Okay. Can I create a "Sort" field that contains a sequetial number to sort the records, then when the "Hours Worked" field reaches 24, an arbitrary number will be added to that "Sort" field so that the specific record will be sorted to the bottom of the form. And then build another macro that renumbers the records starting from 1 again and run this marco when the form closes. Does this sound plausible.
 
Mike,

A firefighter from Sparks ...

I don't know how your OT data is layed out, but you can create
another query that groups by employee name and sums the
OT. You could restrict it to the last month, three months, all
records - it does not matter.

Join this query with your current one that feeds your form and
sorts by the new field Ascending.

Add the new field to your form.

That should do it.

Wayne
 
Thanks Wayne,

I work on Thursday, I will try it then and let you know how it works.

Mike
 
Wayne I tried to do what you said in your earlier post. But the list is not sorted by number of hours. A person who has accumulated 5 hours of overtime can be above or below someone who has accumulated 10 hours. The break point is 24 hours. Once someone accumulates 24 hours or more, they will be rotated from where ever they are in the list to the bottom. Then the next person who exceeds 24 hours goes below them and so on. This is how the order of the list is created.

I can do this with a "sort" field I have added. The "sort" value equals the position on the list. Fifth person on the list has a "sort" value of 5. I have created a macro that adds a number to the "sort" value once 24 hours has been exceeded, and then sorts the form according to that field. i.e. the total number of records for one list is 78, so I add 78 to the "sort" value.

What I need to do now is make the number in the "sort" field match the record position when the form closes. Can I use the SetValue action to make the "sort" value equal the record postion? i.e. the fifth record on the list will have a value of 5.

Or am I going about this all wrong. Please help.

Thanks again.
Mike
 
Mike,

I'm at a little bit of a loss here.

If a person goes over 24-hours then they go "behind" the
last person to go over 24-hours.

Total OT hours don't really mean anything toward "promotion"
to overtime.

What you need is a circular linked-list. The next one on the
list gets the OT. If their OT puts them over 24, then they
are the new last one on the list.

If their OT does not put them over 24, where do they go?
Are they still first?

If we define it a bit more, we can make it work, but it
may get a little complex.

Let me know.

Wayne
 
Wayne,

We have 3 separate lists, one for each rank of Captain, Operator, and Firefighter. People are listed in order of when they last accumulated 24 or more hours of overtime. The most recent person to accumulate 24 hours of overtime will be the last person on the list. When an overtime coverage is needed the BC (battalion chief) will go down the list and offer overtime to people that are available to take overtime. The person first on the list may be working that day or on annual leave or for what ever reason can not work that day. So the BC continues down the list. When he finds a person to accept the overtime, it may be a full shift, 24 hours, in which case they will go to the bottom of the list from where ever their current position on the list is. If the overtime is a partial, say 10 hours, then they will not move from their current position unless they have previously accumulated 14 hours or more and therefore going over 24 hours. At which point they will be moved to the bottom of the list. Once you are moved to the bottom all hours are reset to 0.

This a can be a very lenghthy proccess for the BC. We currenty are using a card file but mistakes can be made trying to track hours and positions.

I have made an inventory progam in Access that seems fairly complicated, to me at least. So my BC was hoping I could put something together for the overtime list. I have been able to get by with just some basic Access training. I have used macros to do most of the code work I guess. I do not know VBA, which is probably where this progam will have to be soved.

Thanks again for your time.
Mike
 
Last edited:
Hi Mike,

Hey, we're almost at the holidays. There isn't much activity
here, so ...

If you can Compact/Repair, Then ZIP, and post a db I'll try to
see what I can do with it.

Wayne
 
Wayne,

Well here is an abbreviated version of the database. It only has a partial Captains list, but it should be enough to play with. I can't tell you how much I appreciate you taking the time and effort to try and help. Thank you very much. I go home tomorrow at 8am but I will check this site from time to time. I will be back at the station on the 23rd. Thanks again.

Mike.
 

Attachments

Wayne,

I have a solution that seems to work on paper but I am unsure how to put it in Access. Have a two part numbering system. The first number will be a "DateDiff" number starting at the previous day. So today would be 001. The second part of the number would be the order in which that person was moved on the list. The first day everyone 001-"their sort number". Below is an example, the x will indicates who accumulates 24 hours that day. The letters correspond to names, they don't change. Only the sort number change.

Day 1 C Accumulates 24 hours
A 001-1 B 001-2 C 001-3x D 001-4 E 001-5

Day 2 B Accumulates 24 hours
A 001-1 B 001-2x D 001-4 E 001-5 C 002-1

Day 3 A and C Accumulates 24 hours
A 001-1x D 001-4 E 001-5 C 002-1x B 003-1

Day 4
D 001-4 E 001-5 B 003-1 A 004-1 C 004-2

Does this make sense. If it does, can you help me make it work. Thanks for your help.

Mike
 
Hi Mike,

Hope you had a good Christmas.

I'm still not quite sure what we're doing, but I think that
we're getting closer.

I changed the input form to include and sort by:

Battalion - Ascending
Rank - Ascending
HoursDue - Descending

The hours due is how much of their 24-hours they are "owed".

The form has only one input - Hours Assigned. This new
field is stored so that you can see how many hours they
had on their last OT shift. We could add the date.

As you enter hours, it will decrement the hours due and
requery the form.

I didn't see a timecard screen (for regular hours worked),
so I think that we're just working on a concept here.

Experiment with it and let me know.

Wayne
 

Attachments

Partial Solution

Wayne,

I played with the program you and it wasn't quite what I needed. I do appreciate your effort. I may have found a solution. I have attach the file. It has been hacked up a bit for security reasons but it should have enough information to work with. If you have time could you please look it. There are two forms specifically. "frmCaptain" works just like I need it to but it is not the proper way to build the form. I would have to built a query and form for each rank. "frmOTList" is the proper way to build the for but I cannot make the updates work between the form and the subform. I will keep searching this forum as I have found it to be very helpful. Any insight will be greatly appreciated. Thanks again for you time.

Mike
 

Attachments

Okay I made some more progress. On the form "frmCaptain" I moved the text boxes for "sequence" and "hours" and the command button for "update" into the header part of the form, DUH. Then I made an unbound combo box of "qryRank" Then I tried to tie to combo box to the form record source by using Forms![frmCaptain]![cboRank]. But it is not working. What am I missing.

Mike
 

Users who are viewing this thread

Back
Top Bottom