Append Query

[Henley12]

Ok.......let's see if I can explain this. I have an append query that gets records from an equipment schedule table based on a date range. These records get appended into a work order table. There is, however, a field in the work order table (work order number) that needs to be incremented sequentially starting with the last record in the work order table. The work order number is not an autonumber field. How can I accomplish this in the query?

 

[RossWindows]

You would have to write a function that returns the last used number of that field +1. Then refer that field in your append query to the function.
I.e
If the name of your function is "GetNextWorkorderNumber()" then put that in the append query in the 'Field' section.

There should be plenty of examples of this type of function in the forum.

 

[Henley12]

That already sounds like it may not work for multiple records. Wouldn't it just keep getting the same number?

 

[RossWindows]

That's a good point. Now that you mention it, I'm not sure how that would work in runtime.
In that case, you might have to write the function to do the appending as well.

 

[David R]

If multiple people might be entering records at the same time, it could indeed cause duplication errors. Put the code to generate the WO# in the OnSave event, I believe, and it should avoid that problem (the chances of two users putting it in at the exact same processor 'moment' are very small in the real world).

Make sure your code doesn't try to generate a new number each time, though! Only if the WO# field was previously blank...

 

[Henley12]

I have the code on the form itself.........this is a query that is run by only one person. When this is run, there would not be anyone else running it. Is this an impossible thing in Access?

 

[David R]

I have the code on the form itself.........this is a query that is run by only one person. When this is run, there would not be anyone else running it. Is this an impossible thing in Access?

Hrm? The code should be on the form, I'm just saying if you put it directly into the OnSave event it'll save the record (and thus provide a new marker number for the next user) before there's a duplication problem. If you calculate it when the record is opened/started, it can try to duplicate, or you can end up with holes in the WO# list, etc.

Or am I somehow misunderstanding your question? The phrase "gets records from an equipment schedule table based on a date range. These records get appended into a work order table" makes it sound like you're trying to automagically add several records at a time, perhaps when the Equipment's Next Service Date occurs? That won't work with the code above, no... can you describe why it's not using an Autonumber? Maybe we can figure out a solution that does what you want easier...

 

[David R]

If they must all run at the same time, one option would be to, rather than using a single Append Query that a user must trigger, have your DB's Front-End run code automatically when it first loads that first triggers the Append Query, then cycles through the new records and assigns a new WO# to each of them. A little more complex, but as long as it error-checks itself appropriately, it may decrease your chances for human error.

 

[Henley12]

We have a maintenance database that keeps track of work orders, pieces of equipment, job instructions, and so forth. On the Equipment form, you can view another form that shows the schedule for that piece of equipment. From this form, you can generate work orders for that one piece of equipment. This works fine. I have another form that is called Scheduled Work Orders. In this form, I can chose a date range....for instance, show me all the scheduled work orders for next week. This brings up a list of the scheduled pieces of equipment. The work orders have not been created yet. When I press the "Generate Work Orders" button, it runs a series of queries to generate all the listed work orders. Previously, I had the Work Order number field in my work order table as an autonumber field, and this worked like a charm. However, because of a flaw in the autonumber type (leaving gaps when records are deleted), I have changed the work order number field to just a numeric field that will be incremented sequentially. This is easy to do in VBA code, but in a query, this is where I run into the problem. What I need the query to do is take the last work order number and increment it sequentially for each record present in the query.

Wow........that is confusing.

 

[David R]

We have a maintenance database that keeps track of work orders, pieces of equipment, job instructions, and so forth. On the Equipment form, you can view another form that shows the schedule for that piece of equipment. From this form, you can generate work orders for that one piece of equipment. This works fine. I have another form that is called Scheduled Work Orders. In this form, I can chose a date range....for instance, show me all the scheduled work orders for next week. This brings up a list of the scheduled pieces of equipment. The work orders have not been created yet. When I press the "Generate Work Orders" button, it runs a series of queries to generate all the listed work orders. Previously, I had the Work Order number field in my work order table as an autonumber field, and this worked like a charm. However, because of a flaw in the autonumber type (leaving gaps when records are deleted), I have changed the work order number field to just a numeric field that will be incremented sequentially. This is easy to do in VBA code, but in a query, this is where I run into the problem. What I need the query to do is take the last work order number and increment it sequentially for each record present in the query.

Wow........that is confusing.

Not too bad.

Does your query ever need to generate more than one WO at a time? If not, I would use Max([WO#])+1 as that field? Maybe you've tried that, in which case the only answer really is going to be to do it programatically.

Are (newly) Scheduled WOs flagged in any way as being automatically generated? You could step through a recordset clone and update the record for each to the correct WO# one at a time, as part of your button's final 'cleanup' code...

 

[RossWindows]

You'll definately have to write code to loop through each record in Scheduled Work Orders, call the function to grab the next workorder number, append it to the Workorders table, save the record, and continue to the next record in Scheduled Work Orders. Using this methdology, if the workorder number happens to get duplicated (preventing the record from being saved) you can add an error handler to grab another new workorder number.

You can also write the function that grabs a new workorder number to fill in gaps if the numbers skip (like you saw with the autonumber field)

 

[Henley12]

Not too bad.

Does your query ever need to generate more than one WO at a time? If not, I would use Max([WO#])+1 as that field? Maybe you've tried that, in which case the only answer really is going to be to do it programatically.

Are (newly) Scheduled WOs flagged in any way as being automatically generated? You could step through a recordset clone and update the record for each to the correct WO# one at a time, as part of your button's final 'cleanup' code...

Now this might be interesting. You're saying go ahead and generate the work orders with the query, but it will leave off the work order number. Then go back and generate work order numbers for them in code?