Help with DMax. 1

[Xenos132]

I am trying to generate work order numbers based on a type. Maintence is Mn… Construction Cn.. and Service is Sn..

My table is Called “TblPO_numbers” my type field is “Type” and my stored numbers are in “WOSeqNum”

In my “Type” field I have M,C,S… and I get these from a combo box once I have selected the one I want I click on a command button that runs the following.


WOSeqNum = Nz(DMax("WOSeqNum", "TblPO_numbers", "Type = '" & Me.Type & "'"), 0) + 1

This does generate a new number with for each type however the type is not displayed. Like Ie. M1,M2,M3… instead I just get 1,2,3, for each type.

Can some one lend a tip ?

 

[VBslammer]

WOSeqNum = Me.Type & (Nz(DMax("WOSeqNum", "TblPO_numbers", "Type = '" & Me.Type & "'"), 0) + 1)

VBSlammer

Unemployed in Houston, Texas

 

[Xenos132]

It will generate the next alailable number on the first run. However I get a “type mismatch” error once a Bn.. instead of just a number is in my WOSeqNum field.

Can I not use DMax with letters?

 

[VBslammer]

You're probably better off using DCount() instead of DMax() if the numbers are absolutely ordered, i.e. 1,2,3,4,5...

BUT, If there are gaps in the sequence, i.e. 1,3,4,5...it won't work, and you'll have to parse the string value to get at the maximum number. A quick function will find the next PO code:

Function GetNextPO(ByVal strType As String) As String
On Error GoTo ErrHandler
  Dim rst As Recordset
  Dim strSQL As String
  
  strSQL = "SELECT Mid(WOSeqNum,2) As MaxNum FROM TblPO_numbers WHERE " & _
           "[Type]='" & strType & "' ORDER BY Mid(WOSeqNum,2) DESC"
           
  Set rst = CurrentDb().OpenRecordset(strSQL, dbOpenSnapshot)
  
  If Not rst.EOF Then
    GetNextPO = strType & CLng(rst("MaxNum")) + 1
  Else
    GetNextPO = strType & 1
  End If
  
ExitHere:
  On Error Resume Next
  rst.Close
  Set rst = Nothing
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

When the user changes the Type field, you can call the function to get the next one:

Private Sub Type_AfterUpdate()
  If Not IsNull([Type]) Then
    Me!WOSeqNum.Value = GetNextPO([Type])
  End If
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

The SQL in the function could be improved to return only the maximum number by changing it to this:

strSQL = "SELECT Max(Mid(WOSeqNum,2)) As MaxNum FROM TblPO_numbers WHERE " & _
           "[Type]='" & strType & "'"

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

FYI, it would be easier to help on these types of questions if you would show your table schema like this:

fldName1(PK)  fldName2   fldName3   fldName4
--------------------------------------------
1             Smith      100.00     C1 
2             Jones      195.00     M1
3             Allen      45.04      S1
4             Smith      200.00     C2

...so we know what extremes of data to expect in the table. If your WOSeqNum field has numeric and alpha-numeric values, then you have to check for that using IsNumeric(fldVal) or the like. In working your problem, I assumed each entry was in the form "?###" where the ?'s are all letters and the #'s are all numbers.

VBSlammer

Unemployed in Houston, Texas

 

[SJMcAbney]

Xenos, it is considered even more helpful that if you are cross posting your questions across different sites that you make the people helping you aware that you have posted the question elsewhere.

I am referring to your post on Access World Forums.

By not declaring this you have people on two different sites trying to work out a solution for you oblivious to those helping on the other site. And when the solution is found on one site there are others wasting their time on the other. Please consider this in future.

 

[Xenos132]

Thanks guys everythig is operating as it should now.