Access Question

I have a table with various information about work orders done in our shop.

When I am adding a new job, I would like access to look at the last record or highest work order number and add 1 to the number for the next job, I thought you could possibly do a query and put +1 in the criteria, but what type of query would it be. Or maybe I am totally off.

Thanks

Jay Morlan

 

Just add the field data type in your table definition of AutoNumber, this automatically increments by one; call it something like OrderID.

 

As MRdNK says the easiest way is to use the built in Autonumber feature, it does have some drawbacks though.
If you create and then cancel a new record, or delete the last record the Autonumber can't go back and use that number again, so you have blanks in your Work Order Numbers. If you are not allowed Blanks in the Work Order sequence you have to resort to VBA Code to increment the number instead.

 

Or use VBA to re-use old deleted record ID numbers.

 

I dont have any experience with VBA Code, but willing to try. How would I go about using VBA to get the numbers to move in increments of one.

Thanks

Jay Morlan

 

Jay, what form do you want the Work Order Number to take, is it a special Format, do you want to show preceding zeroes?
i.e. 0001, 0002 to 9999
or
2008 - 0001 to 2008 - 9999
changing to
2009 - 0001 to 2009 - 9999
next year.

 

I do not have to have the date in front of the work order, currently we use a four digit number such as 7468, which is the number that we are currently on as the weekend begins. I just would like the work order field in the table and form to automatically go to 7469 and so on.

Thanks

Jay Morlan

Also while I am on here, I have yet another problem, in my Work Orders Table I have two hyperlinks that take me directly to the pictures of the unit that correspond with the work order and I have another hyperlink that takes me to the quote that has been saved for the work order. The picutures being located in the S:\Work Order Pictures\Work Order # Folder and the Quotes being saved under F:\2008 Quotes\OCT 2008\Work Order #. How can I get these two hyperlink fields to automatically fill in the first part of the file location and then read off the work order number and fill that part in as well.

Thanks

Jay Morlan \

 

Jay, I will be able to do something for you on the VBA for the automatic incrementing of the PO number, possibly by Monday.
I do not use Hyperlinks, I have a Field on the Form that shows the photo or Document which when double clicked opens the actual Photo or Document.
I use a VBA "Browser" to browse to the required file.

Are you in the USA?

 

I am located in the US

I appreciate you helping me with these questions.

Thanks

Jay Morlan

 

Jay, what is the actual Name of your PO Number Field in the table and on the Form?
I also need the name of the Table as well.
It would be easier still if you could take a Copy of your database, delete all the records and then Compact & Repair it, Zip it and post it on here as an Attachment.

 

Field Name- Work Order
Table- Lead Time Dates
Form - Lead Time Dates

 

Jay, try this in the After Update Event of the First Field on the Form that is not the Work Order Field, the Work Order Field Property "Enabled" should be set to "No".

Dim rs As Object, sql As String, code As String, last As Integer
sql = "SELECT Lead_Time_Dates.* " & _
"FROM Lead_Time_Dates "
Set rs = CurrentDb.OpenRecordset(sql)
rs.MoveLast
last = rs![Work Order]
rs.Close
Set rs = Nothing
Me.Work_Order = last + 1

Note the Underscores "_" in the Table's Name, you will need to add those to your Table's name as well, VBA created SQL does not like Table names with spaces in them.