Shrink code for singe parts number creation without going through entire db

[pbaldy]

Is RemoveCharSingleRec the one you're trying to fix?

[pbaldy]

Presuming so, see if this makes sense:

Sub RemoveCharSingleRec()
  Dim strNewMfg As String
  Dim strNewVendor As String
  Dim strNewPart As String
  Dim strNewTCost As String
  Dim myMStr As String
  Dim myVStr As String
  Dim myPStr As String
  Dim char As Variant
  Dim TCost As String
  Dim myTCost As String




  myMStr = Forms![New Part].[MfgPartNumber]
  myVStr = Forms![New Part].[VendorPartNumber]
  myTCost = Forms![New Part].[TCost]
  strNewMfg = myMStr
  strNewVendor = myVStr
  strNewPart = myPStr
  strNewTCost = myTCost
  '-----------For Each----------
  For Each char In Split(SpecialCharacters, ",")
    strNewMfg = Replace(strNewMfg, char, "")
    strNewVendor = Replace(strNewVendor, char, "")
    strNewTCost = Replace(strNewTCost, char, "")
    '-------------Next For Each----------
  Next


  If strNewMfg = strNewVendor Then
    Forms![New Part].txtMfgPartNumber = strNewMfg & " - " & Right("0000" & strNewTCost, 7)
  Else
    Forms![New Part].txtMfgPartNumber = strNewVendor & " - " & strNewMfg & " - " & Right("0000" & strNewTCost, 7)
  End If

End Sub

Also spaces and symbols in your object names are not worth the bother in the long run, and:

http://www.baldyweb.com/OptionExplicit.htm

[RosaCat]

Yes, but basically it's a cut and paste of the RemoveChar.

And if there's an easier way to do this, I'll take it. I feel like I have a lot of unnecessary things going on with this. But I've never been trained with Access or VBA. I am learning this on my own and with the help of all the great people on this site and internet.

[pbaldy]

Did you see post 17? I'd probably do it differently, with a more generic function that accepted an input string and returned a string without the undesired characters. Then you can call it from anywhere.

[ssanfu]

The following is based on Paul's code in Post #17


I created a button on the form that has the controls for MfgPartNumber, VendorPartNumber and TCost. I named the button "btnDoIt".
The code for the button click event is

Private Sub btnDoIt_Click()
    Call RemoveCharSingleRec(Me)
End Sub

In a standard module is the (modified) code for "RemoveCharSingleRec":

Option Compare Database   '<<-- these two lines should be at the top of EVERY code module
Option Explicit           '<<-- these two lines should be at the top of EVERY code module

Const SpecialCharacters As String = "!,@,#,$,%,^,&,*,(,),{,[,],},?,-,_, ,<,>,/,\,.,+"

Sub RemoveCharSingleRec(theForm As Form)

    Dim MyFrm As Form
    Dim strNewMfg As String
    Dim strNewVendor As String
    Dim strNewTCost As String
    Dim char As Variant
    Dim TCost As String

    Set MyFrm = theForm

    'get entered data
    strNewMfg = MyFrm![MfgPartNumber]
    strNewVendor = MyFrm![VendorPartNumber]
    strNewTCost = MyFrm![TCost]

   '-----------------------------------------
   ' you might enter checks here to ensure that there are values 
   ' entered in the TCost, and the MfgPartNumber and/or VendorPartNumber fields/controls
   ' before proceeding
   '-----------------------------------------

    'remove Special Characters
    For Each char In Split(SpecialCharacters, ",")
        strNewMfg = Replace(strNewMfg, char, "")
        strNewVendor = Replace(strNewVendor, char, "")
        strNewTCost = Replace(strNewTCost, char, "")
    Next

    ' now that the Special Characters are removed, save the cleaned NewMfg & NewVendor values
    MyFrm![MfgPartNumber] = strNewMfg
    MyFrm![VendorPartNumber] = strNewVendor
    '    MyFrm![TCost] = strNewTCost   ' <<--this removed the $ sign and the decimal point from the cost value on the form so it is not updated

    ' then update the Part Number
    If strNewMfg = strNewVendor Then
        MyFrm![PartNumber] = strNewMfg & " - " & Right("0000" & strNewTCost, 7)
    Else
        MyFrm![PartNumber] = strNewVendor & " - " & strNewMfg & " - " & Right("0000" & strNewTCost, 7)
    End If

    'cleanup and exit sub
    Set MyFrm = Nothing
End Sub

There were several variables that were unused in the code, so I removed them and their declarations.

[pbaldy]

I'd make it even more dynamic, simply passing a string back and forth. Here's one I've used (opposite philosophy of "what do I want to allow" rather than "what do I want to exclude", which is a matter of preference):

Public Function ReplaceSpecial(varInput As Variant) As Variant
  Dim strString               As String
  Dim x                       As Integer
  For x = 1 To Len(varInput)
    Select Case Asc(Mid(varInput, x, 1))
      '32 = space, 48-57 = 0-9, 65-90 = A-Z, 97-122 = a-z
      Case 32, 48 To 57, 65 To 90, 97 To 122
        strString = strString & Mid(varInput, x, 1)
    End Select
  Next x
  ReplaceSpecial = strString
End Function

You can call it from anywhere in the app to fix strings, like in your before update code just:

Me.txtMfgPartNumber = ReplaceSpecial(Me.txtMfgPartNumber)

[accesstos]

Hi all!

I had the same idea with Paul but with a little a bit different way:

Const SpecialCharacters  As String = "!@#$%^&*(){[]}?-_ <>/\.+"

Function WithoutSpecChars(ByVal strIn As String) As String
    Dim i As Integer
    
    For i = 1 To Len(strIn)
        If InStr(1, SpecialCharacters, Mid(strIn, i, 1)) > 0 Then
            Mid(strIn, i, 1) = " "
        End If
    Next i
    WithoutSpecChars = Replace(strIn, " ", "")
End Function

You can use it just like Paul suggested.

Regards,
John

[RosaCat]

I was going through all the strings again and I didn't notice that I had overlooked #17 which was very helpful. I ended up using Steve's code because it cleaned up all the useless code I had in there, but instead of a button, I used it as an after update code.

I'm a little confused about the Public Function. I like it, but I don't know where to put a Public Function. Do I create a new module? And what is happening? What is the difference between the 2?
You guys are all AWESOME! I am so grateful to have such brilliant minds helping me out! Thank you! Thank you!

[June7]

Public function or sub in general module allows procedure to be called from anywhere within VBA. Public function (not sub) can even be called within query or textbox or event property or Conditional Formatting.

[accesstos]

Hi Rosa!

Yes, you have to put it in a standard code module if you want to call it from anywhere, but in your case is not necessarily. If you want to call it only from your form, put it in your form's code module.
You can use my function as seems in code below:

Option Compare Database
Option Explicit

Declare Function GetTickCount Lib "kernel32" () As Long
Const SpecialCharacters As String = "!@#$%^&*(){[]}?-_ <>/\.+"

Function WithoutSpecChars(ByVal strIn As String) As String
    'Returns a new instance of strIn without specials characters but leaves the strIn as it is.
    Dim i As Integer

    For i = 1 To Len(strIn)
        If InStr(1, SpecialCharacters, Mid(strIn, i, 1)) > 0 Then
            'Found in the SpecialCharacters text.
            'Replace it with white space.
            Mid(strIn, i, 1) = " "
        End If
    Next i
    'Remove all white spaces the strIn and return.
    WithoutSpecChars = Replace(strIn, " ", "")
End Function

Sub BuildAllPartNumbers()
    Dim rstTbl As Recordset
    Dim lngTicks As Long

    On Error GoTo ErrHandler
    lngTicks = GetTickCount()
    Set rstTbl = CurrentDb.OpenRecordset("Parts", dbOpenDynaset)
    With rstTbl
        .MoveFirst
        While Not .EOF
            .Edit
            If IsNull(![MfgPartNumber]) Or IsNull(![TCost]) Then
                'If something is Null the result is Null.
                ![PartNumber] = Null
            Else
                ![PartNumber] = WithoutSpecChars(![MfgPartNumber]) & "-" & _
                                Right("0000" & WithoutSpecChars(![TCost]), 7)
                If IsNull(![VendorPartNumber]) Then
                    'Nothing to do.
                Else
                    If ![MfgPartNumber] <> ![VendorPartNumber] Then
                        ![PartNumber] = WithoutSpecChars(![VendorPartNumber]) & "-" & ![PartNumber]
                    End If
                End If
            End If
            .Update
            .MoveNext
        Wend
    End With
ExitHere:
    On Error Resume Next
    Debug.Print "Build part numbers for " & rstTbl.RecordCount & " records in " _
                & (GetTickCount() - lngTicks) / 1000 & "sec"
    rstTbl.Close
    Set rstTbl = Nothing
    Exit Sub
ErrHandler:
    Select Case Err
            '
        Case Else
            MsgBox "Årror (" & Err & ")" & vbCrLf & Err.Description, vbExclamation, "Build part numbers"
    End Select
    Resume ExitHere
End Sub

Because the subroutine BuildAllPartNumbers() rebuildes all PartNumbers of the table, put this code in a new standard code module (in a copy of your db!), so you would can run it from anyware.

Regards,
john

[RosaCat]

Hi Everyone,

Miss me? I haven't had a chance to work on this project. Now I am back, and it's not working. What am I doing wrong? I printed all the VBA code for this database and attached it. Please help!

Thanks,
Rosa
Microsoft Visual Basic for Applications - Forms and Modules Code.pdf

[rpeare]

EDIT oops didnt' read most recent stuff

[pbaldy]

What does "not working" mean exactly? Pretend we don't have the application to play with.

[RosaCat]

You were not in the conversation I was having in my head?

It's not updating the part number when I add a new part or when I purchase a part with a new purchase price. I had it working, but it was the "long" way. I was kind of hoping you could wave your magic wand and it'll be perfect. (And while you were at it, magic wand my house. )

Help me!

[pbaldy]

Well, I may have missed something while trying to hunt through a PDF, but I see you calling a sub and passing the form as a parameter:

Call RemoveCharSingleRec(Me)

but that sub doesn't expect a parameter:

Sub RemoveCharSingleRec()

As mentioned in post 21, I'd go with a function that wasn't form-specific, which I don't think your situation needs. Pass it a value and it passes back a value without the undesired characters.