-1

I am trying to create a Work Order System for a company and I am limited to using MS Access. I am wanting to code in a Work Order ID column. This column will be based on 2 combobox options:

BuildingName TargetDepartment

I need some VBA code to query the WOID column in the table to retrieve the next number. The conditions will be as the below example:

WOID                BuildingName     TargetDepartment
BUILDA-DEPTA-1      BUILDA           DEPTA
BUILDA-DEPTB-1      BUILDA           DEPTB
BUILDA-DEPTA-2      BUILDA           DEPTA

The VBA code would query the WOID column, and find out if there is a work order for the same building and department and then increment the number at the end by 1. But if there is no WOID that matches the buildingname and targetdepartment, it would create the first entry for that.

So if it finds a matching buildingname and targetdepartment: MaxNumber +1 If it doesn't find a matching buildingname and targetdepartment: 1

Thanks for the help!

user3805734
  • 1
  • 1
  • 1
    Does it really have to work that way? Why not just use an autonumber across all buildings and departments? By the time you are 20,000 deep in work orders, no one really cares what the number is, or whether it is sequential. These kinds of things aren't very reliable in multi user systems. – Nick.Mc Jul 04 '14 at 14:22
  • It's the request of the company. They will use the WOID to show reports and they bill each department differently so they said they can't have a universal ID Number, it has to be unique to the department. This is the only way they want it. :( – user3805734 Jul 04 '14 at 14:29
  • You can have an autonumber and a department, as @ElectricLlama suggests, just do not use the same field / column. What difference does BuildA-DeptB-201945 make? – Fionnuala Jul 04 '14 at 14:30
  • I do understand there are other ways to accomplish this. I expressed those concerns to the company, but this is the way requested. This is why I need the help of the StackOverflow community. Thank you – user3805734 Jul 04 '14 at 14:36
  • You are going to have to do a lot of coding. here is a start: http://stackoverflow.com/questions/11949603/access-vba-find-max-number-in-column-and-add-1/11950647#11950647 DO NOT use DMax in a multiuser system, it is asking for horrible problems. – Fionnuala Jul 04 '14 at 14:40
  • If you are using Access 2010 (or later) you could assign the number using a data macro as illustrated [here](http://stackoverflow.com/a/19785633/2144390). – Gord Thompson Jul 04 '14 at 18:34
  • 1
    The 'proper' (normalised) way to associate a work order to a department is keep a department id and building id on the work order record, not have special ranges of work order numbers. Do your best to satisfy the companies requirements without them dictating the technical design. However if they are going to dictate the technical design then I guess you have to grit your teeth and do it the wrong way. – Nick.Mc Jul 04 '14 at 23:53

1 Answers1

-1

You can do this using DLookUp :

where_condition = "[WOID] Like '" & Me.[BuildingNameCombo] & "-" & Me.[TargetDepartmentCombo] & "-*'"
existing_woid = Nz(DLookUp("[WOID]","[TableName]", where_condition),"")
If(existing_woid = "") Then
    next_id = 1
Else
    next_id = DMax("Mid([WOID], InStrRev([WOID],""-"")+1)","[TableName]", where_condition) + 1
End If
woid = Me.[BuildingNameCombo] & "-" & Me.[TargetDepartmentCombo] & "-" & next_id

You can do it in one line as well, but I think it is better to see the way of thinking behind this.

Edit (with record locking)

Dim s as String, rs as Recordset
s = " Select [WOID] From [TableName] " & _
    " Where [WOID] Like '" & Me.[BuildingNameCombo] & "-" & Me.[TargetDepartmentCombo] & "-*'" & _ 
    " Order By 1 Desc"

'This will restrict table access
Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset, dbDenyRead + dbDenyWrite) 
If rs.RecordCount > 0 Then
    next_ind = Mid(rs(0), InStrRev(rs(0), "-") + 1) + 1
Else
    next_ind = 1
End If

rs.AddNew
rs.Fields("WOID") = Me.[BuildingNameCombo] & "-" & Me.[TargetDepartmentCombo] & "-" & next_ind
rs.Update
rs.Close

Set rs = Nothing
parakmiakos
  • 2,994
  • 9
  • 29
  • 43
  • Please do not use Max or DMax to get an identity. You will end up with duplicates. – Fionnuala Jul 06 '14 at 22:51
  • @Remou Can you explain to me why that is? I am not using `dmax` by itself, I determine if there is a matching record first using `dlookup`. – parakmiakos Jul 07 '14 at 07:08
  • Because you have a race condition. User one gets a number and goes off for tea without saving, user two gets the same number. – Fionnuala Jul 07 '14 at 10:10
  • @Remou Right, wasn't thinking about that... Thanks. – parakmiakos Jul 07 '14 at 10:51
  • A few notes http://stackoverflow.com/questions/12517498/insert-query-with-sequential-primary-key/12528222#12528222, if you want to update your answer, I will undo the down vote :) – Fionnuala Jul 07 '14 at 11:01
  • @Remou I am not used to ADO recordsets, I think this is how you do with a DAO recordset... – parakmiakos Jul 07 '14 at 12:48
  • Unfortunately, you need ADO if you are running against the current (autonumber). The important thing is the locks. You might also have noticed that the locked table is a seed table. – Fionnuala Jul 07 '14 at 12:49