Is RemoveCharSingleRec the one you're trying to fix?
Is RemoveCharSingleRec the one you're trying to fix?
Presuming so, see if this makes sense:
Also spaces and symbols in your object names are not worth the bother in the long run, and:Code: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
http://www.baldyweb.com/OptionExplicit.htm
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.
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.
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
In a standard module is the (modified) code for "RemoveCharSingleRec":Code:Private Sub btnDoIt_Click() Call RemoveCharSingleRec(Me) End Sub
Code: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.
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):
You can call it from anywhere in the app to fix strings, like in your before update code just:Code: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
Me.txtMfgPartNumber = ReplaceSpecial(Me.txtMfgPartNumber)
Hi all!
I had the same idea with Paul but with a little a bit different way:
You can use it just like Paul suggested.Code: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
Regards,
John
Last edited by accesstos; 07-23-2019 at 04:20 PM. Reason: Edit code
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!
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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:
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.Code: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
Regards,
john
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
EDIT oops didnt' read most recent stuff
What does "not working" mean exactly? Pretend we don't have the application to play with.
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!
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.