Display value after "-"

Try this:

Val(StrReverse(WireDiagDwgNo))

mx

that value is returning 0 when it should be 2.

k

Dashno = Val(StrReverse(Me.WireDiagDwgNo))

If the WireDiagDwgNo= 61Y13861-2
& the user changes the DataSystem then the next number in the sequence should be
61Y13861-3

k

Val(StrReverse("6111111-2"))

returns 2 for me.

Try in Immediate window ...

What value of WireDiagDwgNo is it returning a zero ?

Well ... ok ...
1) How many chars can the dash number be?
2) Can it be zero?

mx

Sorry ... missed the Increment part.

Dashno = Val(StrReverse(Me.WireDiagDwgNo))  + 1

mx

I forgot to mention I need to get the maximum number from all records that contain the same WireDiagDwgNo and just increment the DashNo.

So if WireDiagDwgno = 6111122-1 for part A and  6111122-2 for part b then Part C would be 6111122-3.

so I will need some process to determine the maximum DashNo for WireDiagDwgNo 6111122.

What would I need to do to determine maximum Dashno?

K

ok here my code so far

      DashNo = Val(StrReverse(Me.WireDiagDwgNo))
      'NextDashNo = Max(DashNo) + 1
      DwgNo = Forms![frmA]![cboDataSys].Column(1)
      WDwgNo = Mid(Me.WireDiagDwgNo, 1, InStr(1, Me.WireDiagDwgNo, "-") - 1)
      NextDashNo = DLookup("DwgNo", "tableA", WireDiagDwgNo = " & DwgNo & ")

Select allOpen in new window

I know this is not quite right yet
"NextDashNo = DLookup("DwgNo", "TableA", WireDiagDwgNo = " & DwgNo & ")"

Do to the fact the WireDiagDwgNo field in the table is the complete number including the dashNo.

So I need some method to get a list of the need wire diagDwgNo and determine the maximum dash no and increment the next Dashno.

Any suggestions?

K

I have to run off to a meeting, sorry.  Off the top of my head ... a SELECT query that ... has a subquery for a field ... and that subquery Groups on WireDiagDwgNo - Sort Descending ... and does the TOP 1.  So, from the Main query ... you pass the WireDiagDwgNo to match in subquery - and get the max for each WireDiagDwgNo group.

mx

ok I am attempting to do like you suggested, however, my data is not cooperating.  I am getting #Errors in some of the values.

I found code for Parsing the text:

Public Function ParseText(TextIn As String, myDelimiter, Position As Byte) As Variant
    On Error Resume Next
    ParseText = Nz(Split(TextIn, myDelimiter)(Position))
    
End Function

Select allOpen in new window

and I am using the following to strip the main dwg no ( data in front of the "-")

WDwgNo = Mid(Me.WireDiagDwgNo, 1, InStr(1, Me.WireDiagDwgNo, "-") - 1)

I am using both these methods in the query, however, like I said my data is not cooperating due to not all drawing numbers are in the same format, not all have dashes, some are strictly text, some are alphanumeric with dashes.

How do I handle the unknown factors and use these functions?

Here is query 1

SELECT WireDiagDwgNo
FROM TblA
GROUP BY WireDiagDwgNo
HAVING (((WireDiagDwgNo) Is Not Null And (WireDiagDwgNo) Like "61*"));

qry2 - based on query1

SELECT QryDwgNo_1.WireDiagDwgNo, ParseText([WireDiagDwgNo],"-",1) AS Dash_NO, Mid([WireDiagDwgNo],1,InStr(1,[WireDiagDwgNo],"-")-1) AS WDwg
FROM QryDwgNo_1
GROUP BY QryDwgNo_1.WireDiagDwgNo
ORDER BY QryDwgNo_1.WireDiagDwgNo, ParseText([WireDiagDwgNo],"-",1) DESC;

Results of Qry2:


How should i handle the dwgno that does not contian a dash number?

K

A problem I could see with using the StrReverse function in this case is when your suffix goes over 9.  A suffix of 12 would end up returning 21.

Here's a function you could try. It should work whether or not there is a dash in the drawing number.  There may be something simpler but this does work.

Function GetNextDwgNo(strDwgNo As String)
    Dim intSeq As Integer
        
    strDwgNo = Split(strDwgNo, "-")(0)
    
    intSeq = CurrentDb.OpenRecordset("SELECT Max(Val(IIf(InStr([WireDiagDwgNo],'-')=0,0,Right([WireDiagDwgNo],Len([WireDiagDwgNo])-InStr([WireDiagDwgNo],'-'))))) AS MaxSeq " & _
        "FROM TblA WHERE (((IIf(InStr([WireDiagDwgNo],'-')=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo],'-')-1)))='" & strDwgNo & "'))").Fields(0)
    
    GetNextDwgNo = strDwgNo & "-" & intSeq + 1
End Function

Select allOpen in new window

"I forgot to mention I need to get the maximum number from all records that contain the same WireDiagDwgNo and just increment the DashNo.'
Yep, afraid so.  That's what happens when you try to get tricky. Got me before in this - forgetting to reverse it back again.
So ...

Dashno = StrReverse(Val(StrReverse(Me.WireDiagDwgNo)))  + 1

should fix that.  EG:

StrReverse(Val(StrReverse("6111111-12")))
returns 12

mx

One-liners rules!

Pick any DwgNo with a base of, say, 61Y11321, for example:

DwgNo = "61Y11321-13"

If the highest recorded DwgNo is 61Y11321-82, then

DwgNoNext = Split(DwgNo, "-")(0) & "-" & Split(DMax("WireDiagDwgNo", "tblA", "WireDiagDwgNo Like '" & Split(DwgNo, "-")(0) & "*'"), "-")(1) + 1

will return: 61Y11321-83

/gustav

LOL. I like! but sadly the DashNo -9 will be higher than -82!

    Const WireDiagDwgNo = "61Y13861"
    
    
    Dim BaseNo As String
    Dim DashNo As Integer
    
    BaseNo = Left(WireDiagDwgNo, InStr(WireDiagDwgNo + "-", "-") - 1)
    ' for testing only:
    DashNo = Val(Mid(WireDiagDwgNo, InStr(WireDiagDwgNo + "-", "-") + 1))
    ' next dashno:
    DashNo = DMax( _
        Expr:="Val(Mid(WireDiagDwgNo, InStr(WireDiagDwgNo+'-', '-') + 1))", _
        Domain:="YourTableNameHere", _
        Criteria:="WireDiagDwgNo Like '" & BaseNo & "*'" _
        ) + 1
    
    Debug.Print BaseNo & "-" & DashNo

Select allOpen in new window

And now as a one-liner (recycling the Split() idea):

    Debug.Print Split(WireDiagDwgNo, "-")(0) & "-" & DMax( _
        Expr:="Val(Mid(WireDiagDwgNo, InStr(WireDiagDwgNo+'-', '-') + 1))", _
        Domain:="YourTableNameHere", _
        Criteria:="WireDiagDwgNo Like '" & Split(WireDiagDwgNo, "-")(0) & "*'" _
        ) + 1

Select allOpen in new window

(°v°)

> DashNo -9 will be higher than -82!

I think not Markus. The questioneer wrote about -3 and so on, but the example data shows a leading zero like:  -06

But then, of course, the next no. should be -07 and not -7, thus I need to modify my one-liner to include a leading zero:

DwgNoNext = Split(DwgNo, "-")(0) & "-" & Right("0" & Split(DMax("WireDiagDwgNo", "tblA", "WireDiagDwgNo Like '" & Split(DwgNo, "-")(0) & "*'"), "-")(1) + 1, 2)

You are right, the sample data uses leading zeroes. I should probably now change my one-liner as well, but of course using Format for variety and robustness! — (°v°)

Hope this works as well for you as it does for me.

SELECT IIf(InStr([WD_Dwg],"-")=0,[WD_Dwg],Left([WD_Dwg],InStr([WD_Dwg],"-")-1)) AS Main, Max(IIf(InStr([WD_Dwg],"-")<>0,Mid([WD_Dwg],InStr([WD_Dwg],"-")+1),"")) AS AfterDash
FROM Q_27812163
GROUP BY IIf(InStr([WD_Dwg],"-")=0,[WD_Dwg],Left([WD_Dwg],InStr([WD_Dwg],"-")-1));

Select allOpen in new window

Note: [WD_Dwg] is name I gave to the [WireDiagDwgNo] field in your table when I created it in my test database.  The name of the table will need to be changed to your table name.

gustav, I like your one liner.  However, I noticed that it won't work with drawing numbers that don't have dashes as the OP mentioned.  

@kfschaefer1  I've also worked with technical drawings before and I know that the number after the dash (if there is one) can come in a variety of ways such as these:
-2
-02
-0012
Do you have a limit to the size of the number after the dash?

True, those details must be accounted for.
However, we don't have other info than the example data.

I stole the split idea from you ...

/gustav

Thanks to everyone for their input, however, no matter which method I try I am getting a data type mismatch.

Note: the datatype is Text.  Yes the dash number can be greater than -82, this can grow to more then a 3 digit placeholder, and the leading Zero will also need to be included.


i tried the GetNextDwgNO code and ID: 38245691, got the mismatch error

I also tried:
DwgNoNext = Split(DwgNo, "-")(0) & "-" & Split(DMax("WireDiagDwgNo", "tblA", "WireDiagDwgNo Like '" & Split(DwgNo, "-")(0) & "*'"), "-")(1) + 1

in a query and it did not like the "Split(DwgNo, "-")(0)" )(0) portion of the code.

What am I missing?

K

did you try my query?

Yes - it was the first one I tried and I am getting the type mismatch error.

K

here is what I have:

SELECT IIf(InStr([WireDiagDwgNo],"-")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo],"-")-1)) AS Main, Max(IIf(InStr([WireDiagDwgNo],"-")<>0,Mid([WireDiagDwgNo],InStr([WireDiagDwgNo],"-")+1),"")) AS AfterDash
FROM tblA
GROUP BY IIf(InStr([WireDiagDwgNo],"-")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo],"-")-1));

it is somewhere in the As Main portion of the query.

Do you have Null values in your WireDiagDwgNo field?  If so, use

SELECT IIf(InStr([WireDiagDwgNo],"-")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo],"-")-1)) AS Main, 
Max(IIf(InStr([WireDiagDwgNo],"-")<>0, Mid([WireDiagDwgNo],InStr([WireDiagDwgNo],"-")+1),"")) AS AfterDash
FROM tblA
GROUP BY IIf(InStr([WireDiagDwgNo],"-")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo],"-")-1)) 
Where [WireDiagDwgNo] Is Not Null

Select allOpen in new window

Is the [WireDiagDwgNo] column defined as a text or a memo data type?

I also need to limit the sql to the selected Dwgno from the CboDataSys.column(1), which contains only DwgNo left of the dash.

What would be the proper syntax.  The 38246902 code seems to be on the right track.  I am incorporating it into the code behind the cboDataSys.afterupdate.

K

Here is my current code and I am still getting type mismatch on the sql string.

Private Sub DataSysUpdate()
    
Dim DwgNo As String
Dim NextDashNo As String
Dim DwgAndDashNo As String
Dim DashNo As String
Dim WDwgNo As String
Dim intAnswer As Integer
Dim curDB As Database
Dim strSQL As String
Dim rs As Recordset

On Error GoTo DataSysUpdate_Error

Set curDB = CurrentDb()
' and WireDiagDwgNo = " & WDwgNo & "

intAnswer = MsgBox("Would you like this database to generate a" & vbLf & "Wire Diagram Drawing and Dash number?" _
    & vbLf & vbLf & "This will replace the 'Wire Diagram Dwg #.", _
    vbYesNo + vbQuestion, "Create Drawing and Dash number?")

    If intAnswer = vbYes Then
        WDwgNo = Me.cboDataSys.Column(1)
        Debug.Print WDwgNo
        strSQL = "SELECT IIf(InStr([WireDiagDwgNo]," - ")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")-1)) AS Main," & _
                        " Max(IIf(InStr([WireDiagDwgNo]," - ")<>0,Mid([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")+1),'')) AS AfterDash" & _
                    " FROM tblA" & _
                    " WHERE (((TA_AirplaneInfo.[WireDiagDwgNo]) Is Not Null))" & _
                        " AND (((IIf(InStr([WireDiagDwgNo]," - ")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")-1)))=" & WDwgNo & "" & _
                    " GROUP BY IIf(InStr([WireDiagDwgNo]," - ")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")-1))"
        Debug.Print strSQL
        Set rs = curDB.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then Me.WireDiagDwgNo = rs.Fields("main") & " - " & rs.Fields("AfterDash")
    End If

'ExitProcedure:
'    Exit Sub
'
'Err_DataSysUpdate:
'    If Err.Number = 94 Then
'        Me.WireDiagDwgNo = "61Yxxxxx" 'Error 94 says there is no match to [TL_SysVsDwgNo4Diag] to pick a real drawing number
'    Else
'        MsgBox "Error Number ==> " & Err.Number & vbLf & "Error Description ==> " & Err.Description
'            Resume ExitProcedure
'    End If
'
'   On Error GoTo 0
'   Exit Sub

DataSysUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboDataSys_AfterUpdate of VBA Document Form_frmAirplaneInfo"

End Sub

Select allOpen in new window

If you are matching against a set value, then we can replace the Is Not Null check with a value (pattern) matching check.

SELECT IIf(InStr([WireDiagDwgNo],"-")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo],"-")-1)) AS Main, 
Max(IIf(InStr([WireDiagDwgNo],"-")<>0, Mid([WireDiagDwgNo],InStr([WireDiagDwgNo],"-")+1),"")) AS AfterDash
FROM tblA
GROUP BY IIf(InStr([WireDiagDwgNo],"-")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo],"-")-1)) 
Where [WireDiagDwgNo] Like CboDataSys.column(1) & "*"

Select allOpen in new window

or

Where [WireDiagDwgNo] = CboDataSys.column(1)

Note: if you reference a column in a query, you will need to use the complete name, including the form name.  If you are setting the SQL in code, you will need to concatenate the   CboDataSys.column(1) value to the current SQL.

Why do you place your Where statement after the Group by?

K

I replied before you posted the code.  I think the following might be the solution you seek.

        WDwgNo = Me.cboDataSys.Column(1)
        Debug.Print WDwgNo
        strSQL = "SELECT IIf(InStr([WireDiagDwgNo]," - ")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")-1)) AS Main," & _
                        " Max(IIf(InStr([WireDiagDwgNo]," - ")<>0,Mid([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")+1),'')) AS AfterDash" & _
                    " FROM tblA" & _
                    " WHERE (((TA_AirplaneInfo.[WireDiagDwgNo]) ='" & WDwgNo & "'))" & _
                    " GROUP BY IIf(InStr([WireDiagDwgNo]," - ")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")-1))"

Select allOpen in new window

Will the Me.cboDataSys.Column(1) value be the leading (main) part of the part number, prior to any hyphen character?

still getting type mismatch:

Here is my latest

Private Sub DataSysUpdate()
    
Dim WDwgNo As String
Dim intAnswer As Integer
Dim curDB As Database
Dim strSQL As String
Dim rs As Recordset

On Error GoTo DataSysUpdate_Error

Set curDB = CurrentDb()

intAnswer = MsgBox("Would you like this database to generate a" & vbLf & "Wire Diagram Drawing and Dash number?" _
    & vbLf & vbLf & "This will replace the 'Wire Diagram Dwg #.", _
    vbYesNo + vbQuestion, "Create Drawing and Dash number?")

    If intAnswer = vbYes Then
        WDwgNo = Me.cboDataSys.Column(1)
        Debug.Print WDwgNo
        strSQL = "SELECT IIf(InStr([WireDiagDwgNo]," - ")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")-1)) AS Main," & _
                        " Max(IIf(InStr([WireDiagDwgNo]," - ")<>0,Mid([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")+1),'')) AS AfterDash" & _
                    " FROM TblA" & _
                    " WHERE [WireDiagDwgNo] Like me.CboDataSys.column(1) & " * "" & _
                    " GROUP BY IIf(InStr([WireDiagDwgNo]," - ")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")-1))"
        Debug.Print strSQL
        Set rs = curDB.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then Me.WireDiagDwgNo = rs.Fields("main") & " - " & rs.Fields("AfterDash")
    End If

Select allOpen in new window

Please ignore the earlier post.  The Where statement should appear after the From clause and before any Group By, Having, or Order By clauses.  I was in the process of editing the post when I saw your code post.

When I posted my earlier comment, I didn't know if your SQL was static or dynamic.  In your case, it is dynamic.  Therefore, the actual value of the combobox/listbox column will be concatenated to the string.

still getting type mismatch on you suggested changes 38247077

K

What does your code currently look like? (post 38247077 changes)

Private Sub DataSysUpdate()
    
Dim WDwgNo As String
Dim intAnswer As Integer
Dim curDB As Database
Dim strSQL As String
Dim rs As Recordset

On Error GoTo DataSysUpdate_Error

Set curDB = CurrentDb()

intAnswer = MsgBox("Would you like this database to generate a" & vbLf & "Wire Diagram Drawing and Dash number?" _
    & vbLf & vbLf & "This will replace the 'Wire Diagram Dwg #.", _
    vbYesNo + vbQuestion, "Create Drawing and Dash number?")

    If intAnswer = vbYes Then
            WDwgNo = Me.cboDataSys.Column(1)
        Debug.Print WDwgNo
        strSQL = "SELECT IIf(InStr([WireDiagDwgNo]," - ")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")-1)) AS Main," & _
                        " Max(IIf(InStr([WireDiagDwgNo]," - ")<>0,Mid([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")+1),'')) AS AfterDash" & _
                    " FROM tblA" & _
                    " WHERE [WireDiagDwgNo] like " & WDwgNo & "*" & "" & _
                    " GROUP BY IIf(InStr([WireDiagDwgNo]," - ")=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo]," - ")-1))"
        Debug.Print strSQL
        Set rs = curDB.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then Me.WireDiagDwgNo = rs.Fields("main") & " - " & rs.Fields("AfterDash")
    End If

DataSysUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboDataSys_AfterUpdate of VBA Document Form_frmAirplaneInfo"

End Sub

Select allOpen in new window

It does not get pass the sql string.

k

If you are going to use the Like operator, the line should look like this:

" WHERE [WireDiagDwgNo] like '" & WDwgNo & "*'" &  _

Select allOpen in new window

Your version is missing some apostrophe characters.

change the code to the following - replacing all double quotes with single quotes and replacing the Where statement per you last post and getting the following error:

Private Sub DataSysUpdate()
    
Dim WDwgNo As String
Dim intAnswer As Integer
Dim curDB As Database
Dim strSQL As String
Dim rs As Recordset

On Error GoTo DataSysUpdate_Error

Set curDB = CurrentDb()

intAnswer = MsgBox("Would you like this database to generate a" & vbLf & "Wire Diagram Drawing and Dash number?" _
    & vbLf & vbLf & "This will replace the 'Wire Diagram Dwg #.", _
    vbYesNo + vbQuestion, "Create Drawing and Dash number?")

    If intAnswer = vbYes Then
            WDwgNo = Me.cboDataSys.Column(1)
        Debug.Print WDwgNo
        strSQL = "SELECT IIf(InStr([WireDiagDwgNo],' -  ')=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo],' -  ')-1)) AS Main," & _
                        " Max(IIf(InStr([WireDiagDwgNo],' -  ')<>0,Mid([WireDiagDwgNo],InStr([WireDiagDwgNo],' -  ')+1),"")) AS AfterDash" & _
                    " FROM TblA" & _
                    " WHERE [WireDiagDwgNo] like '" & WDwgNo & "*'" & _
                    " GROUP BY IIf(InStr([WireDiagDwgNo],' -  ')=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo],' -  ')-1))"
        Debug.Print strSQL
        Set rs = curDB.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then Me.WireDiagDwgNo = rs.Fields("main") & " - " & rs.Fields("AfterDash")
    End If

DataSysUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboDataSys_AfterUpdate of VBA Document Form_frmAirplaneInfo"

End Sub

Select allOpen in new window

Only the Where clause needed to be changed.  Everything else should be ok.

the problem lies somewhere in the Max statement for AfterDash portion of the sql statement.

I tried the query without it and it ran successfully.  Could you take another look, I just don't see it?

K

it doesn't lie the MAX function on that portion of the sql - When I removed it the query ran successfully - I was have a similar issue last night.  is the Max function limited to a particular data type?

k

Here is the finally working code - Thanks for all your assistance.

'---------------------------------------------------------------------------------------
' Procedure : DataSysUpdate
' Date      : 8/1/2012
' Purpose   : Sets the incremental Dash No for WireDiagram Number
'---------------------------------------------------------------------------------------
'
Private Sub DataSysUpdate()

Dim nDashNo As String
Dim WDwgNo As String
Dim intAnswer As Integer
Dim curDB As Database
Dim strSQL As String
Dim rs As Recordset

   On Error GoTo DataSysUpdate_Error

Set curDB = CurrentDb()

intAnswer = MsgBox("Would you like this database to generate a" & vbLf & "Wire Diagram Drawing and Dash number?" _
    & vbLf & vbLf & "This will replace the 'Wire Diagram Dwg #.", _
    vbYesNo + vbQuestion, "Create Drawing and Dash number?")

    If intAnswer = vbYes Then
        WDwgNo = Me.cboDataSys.Column(1)
        strSQL = "SELECT IIf(InStr([WireDiagDwgNo],'-')=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo],'-')-1)) AS Main," & _
                    " Max(IIf(InStr([WireDiagDwgNo],'-')<>0,Mid([WireDiagDwgNo],InStr([WireDiagDwgNo],'-')+1),'')) AS AfterDash" & _
                    " FROM TA_AirplaneInfo" & _
                    " WHERE [WireDiagDwgNo] like '" & WDwgNo & "*'" & _
                    " GROUP BY IIf(InStr([WireDiagDwgNo],'-')=0,[WireDiagDwgNo],Left([WireDiagDwgNo],InStr([WireDiagDwgNo],'-')-1))"
        Set rs = curDB.OpenRecordset(strSQL)
        If rs.RecordCount > 0 Then
            nDashNo = rs.Fields("AfterDash") + 1
            Me.WireDiagDwgNo = WDwgNo & "-" & nDashNo
        End If
    End If

   On Error GoTo 0
   Exit Sub

DataSysUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DataSysUpdate of VBA Document Form_frmAirplaneInfo"

End Sub

Select allOpen in new window

THANKS FOR THE GREAT ASSIST.

Another approach to this solution would be to use the DMax() function.  The max WireDiagDwgNo value still has to be parsed to increment the value after the hyphen.

'---------------------------------------------------------------------------------------
' Procedure : DataSysUpdate
' Date      : 8/1/2012
' Purpose   : Sets the incremental Dash No for WireDiagram Number
'---------------------------------------------------------------------------------------
'
Private Sub DataSysUpdate()

  Dim WDwgNo As String
  Dim intAnswer As Integer
  Dim strMax As String

  On Error GoTo DataSysUpdate_Error

  intAnswer = MsgBox("Would you like this database to generate a" & vbLf & "Wire Diagram Drawing and Dash number?" _
    & vbLf & vbLf & "This will replace the 'Wire Diagram Dwg #.", _
    vbYesNo + vbQuestion, "Create Drawing and Dash number?")

  If intAnswer = vbYes Then
        WDwgNo = Me.cboDataSys.Column(1)
        strMax = DMax("Wirediagdwgno", "tblA", "Wirediagdwgno Like '" & WDwgNo & "*'")
        If InStr(strMax, "-") = 0 Then
            Me.WireDiagDwgNo = WDwgNo & "-01"
        Else
            Me.WireDiagDwgNo = WDwgNo & "-" & Format(Val(Split(strMax, "-")(1)) + 1, "00")
        End If
  End If

  On Error GoTo 0
  Exit Sub

DataSysUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DataSysUpdate of VBA Document Form_frmAirplaneInfo"

End Sub

Select allOpen in new window

I missed all the fun.

The final code works for most cases (it fails for a brand new drawing number), but it is needlessly complicated. There was also the matter of the leading zeroes. Finally, I would implement it as a function and not a procedure, so you can test it in a query easily and use it directly in your procedures.

This is the function:

'-------------------------------------------------------------------------------
' Function  : NewWireDiagDwgNo
' Date      : 8/2/2012
' Purpose   : Returns the incremental Dash No for a WireDiagram Number
' Reference : Experts-Exchange Question http://e-e.com/Q_27812163.html
'-------------------------------------------------------------------------------
'
' Input     : An existing or new number, with or without dash
' Output    : Same base number with the next available dash number
' Uses      : TA_AirplaneInfo.WireDiagDwgNo
'
Function NewWireDiagDwgNo(DwgNo)
    
    Dim intDashNo As Integer
    
On Error GoTo Failure
    ' return Null on Null input or error
    NewWireDiagDwgNo = Null
    If IsNull(DwgNo) Then Exit Function
    
    ' strip dash if present
    DwgNo = Split(DwgNo, "-")(0)
    ' find next DashNo *by value*, defaults to -01
    intDashNo = Nz(DMax( _
        "Val(Mid(WireDiagDwgNo, InStr(WireDiagDwgNo+'-', '-') + 1))", _
        "TA_AirplaneInfo", _
        "WireDiagDwgNo Like '" & DwgNo & "*'" _
        ), 0) + 1
    ' use a leading zero if less than 10
    NewWireDiagDwgNo = DwgNo & Format(intDashNo, "\-00")
    
Failure:
    If Err Then Err.Clear
End Function

Select allOpen in new window

Which you can use anywhere, for example like this:

'---------------------------------------------------------------------------------------
' Procedure : DataSysUpdate
' Date      : 8/1/2012
' Purpose   : Sets the incremental Dash No for WireDiagram Number
'---------------------------------------------------------------------------------------
'
Private Sub DataSysUpdate()

Dim WDwgNo As String
Dim intAnswer As Integer

    On Error GoTo DataSysUpdate_Error
   
    WDwgNo = NewWireDiagDwgNo(Me.cboDataSys.Column(1))
    intAnswer = MsgBox("Would you like to insert '" & WDwgNo & "'" & vbLf _
        & "as new Wire Diagram Drawing and Dash number?" & vbLf _
        & vbLf _
        & "This will replace the 'Wire Diagram Dwg #." _
        , vbYesNo + vbQuestion _
        , "Replace Drawing and Dash number?")

    If intAnswer = vbYes Then Me.WireDiagDwgNo = WDwgNo
    Exit Sub

DataSysUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure " _
        & "DataSysUpdate of VBA Document Form_frmAirplaneInfo"

End Sub

Select allOpen in new window

Getting something to work is not the final step in programming. Once it works, it's very useful to simplify and modularise (where appropriate).

Cheers!
(°v°)

Nice Markus.

/gustav

@kfschaefer1

The reason you encountered this problem is that your table is not fully/sufficiently normalized.  You are storing two data (wirediagram# and generation) in the same field.  There should be a separate field for the generation number.

aikimark,

Yes, I know that it is not normalized, I inherited the problem, and I cann't make changes.  the company is in a long process of replacing this tool eventually.

Thanks again to everyone for their input.

Karen