Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22

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

    Hi,



    I am trying to edit this code so I can make it run while filling a form. I currently am using this code, but it seems like a waste of time to run completely when I just want to add a new record. There's over 600 records now, but as it grows, it'll only take longer. I hope you can help!

    Code:
    Option Compare Database
    Const SpecialCharacters As String = "!,@,#,$,%,^,&,*,(,),{,[,],},?,-,_, ,<,>,/,\,.,+"
    Sub RemoveChar()
    Dim rstTbl As DAO.Recordset
    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


    Set rstTbl = CurrentDb.OpenRecordset("Parts", dbOpenDynaset)


    '----------------------------------With---------------------------
    With rstTbl
    .MoveFirst
    '-----------------------------If 2------------------------
    If Not rstTbl.BOF And Not rstTbl.EOF Then
    '--------------------DO Until----------------
    Do Until rstTbl.EOF = True
    myMStr = rstTbl![MfgPartNumber]
    myVStr = rstTbl![VendorPartNumber]
    myTCost = rstTbl![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, "")
    '------If 1-----
    If strNewMfg = strNewVendor Then
    rstTbl.Edit
    rstTbl![PartNumber] = strNewMfg & " - " & Right("0000" & strNewTCost, 7)
    rstTbl.Update
    Else
    rstTbl.Edit
    rstTbl![PartNumber] = strNewVendor & " - " & strNewMfg & " - " & Right("0000" & strNewTCost, 7)
    rstTbl.Update
    End If
    '---End If 1-----


    '-------------Next For Each----------
    Next


    rstTbl.MoveNext
    '--------------------Loop----------------
    Loop


    '-------------------------End If 2------------------------
    End If


    '------------------------------End With-------------------------


    End With


    End Sub


    Is there a way to refer to the current information on the form?

    Thanks,
    Rosa

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Please post lengthy code between CODE tags to retain indentation and readability.

    What exactly are you trying to accomplish? Why would you even need to cycle through all existing records?

    Can certainly reference form current record data. Use AfterUpdate event of appropriate field to save the constructed value. Me!fieldname = something

    Why would some part numbers have additional prefix?
    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.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by RosaCat View Post
    Is there a way to refer to the current information on the form?
    Sure:

    myMStr = Me.MfgPartNumber

    presuming "MfgPartNumber" is the name of the textbox on the form. I suspect the before update event of the form would be the place to call this from, which should handle new or edited records. You certainly shouldn't have to loop the whole table every time, just the current record.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Rosa,

    It helps readers understand your issue when you put it in context using simple terms, plain English.
    A short overview of what you are trying to accomplish with little/no database terms or jargon can
    get focused responses quickly (most of the time).
    Good luck with your project.

  5. #5
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22
    Quote Originally Posted by June7 View Post
    Please post lengthy code between CODE tags to retain indentation and readability.

    What exactly are you trying to accomplish? Why would you even need to cycle through all existing records?

    Can certainly reference form current record data. Use AfterUpdate event of appropriate field to save the constructed value. Me!fieldname = something

    Why would some part numbers have additional prefix?

    Thanks for the code tag info. I didn't know that.

    I need to create a Part number with certain pieces of information from the form. Originally, I created the code to create the Part number of all existing records. It was a new field.

    The Part Number is part of a difference table that this form pulls from.

    The part numbers need different suffixes because of price changes when purchasing. I had a hard time figuring out how to best resolve that issue.

    Thanks,
    Rosa

  6. #6
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22
    Quote Originally Posted by pbaldy View Post
    Sure:

    myMStr = Me.MfgPartNumber

    presuming "MfgPartNumber" is the name of the textbox on the form. I suspect the before update event of the form would be the place to call this from, which should handle new or edited records. You certainly shouldn't have to loop the whole table every time, just the current record.
    Thanks Paul. The Part Number is part of a difference table that this form pulls from.

  7. #7
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22
    Quote Originally Posted by orange View Post
    Rosa,

    It helps readers understand your issue when you put it in context using simple terms, plain English.
    A short overview of what you are trying to accomplish with little/no database terms or jargon can
    get focused responses quickly (most of the time).
    Good luck with your project.
    Sorry about that. By the time I ask for help here, my brain is hurting from not being able to resolve it myself.

    I'm not always good at expressing myself. Basically, when a have to add a new part or the part has a new price, I need to run the code to create a part number that will allow me to find it no matter if the tech writes down the mfg# or the vendor#. The original code was created when I created the part# field to do this, so I had to go through all existing records. Now, I only need to run it when I get a part that currently doesn't exist.

  8. #8
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22
    Here's the code with indents:

    Code:
    Option Compare DatabaseConst SpecialCharacters As String = "!,@,#,$,%,^,&,*,(,),{,[,],},?,-,_, ,<,>,/,\,.,+"
    Sub RemoveChar()
    Dim rstTbl As DAO.Recordset
    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
    
    
    Set rstTbl = CurrentDb.OpenRecordset("Parts", dbOpenDynaset)
    
    
    '----------------------------------With---------------------------
    With rstTbl
        .MoveFirst
    '-----------------------------If 2------------------------
    If Not rstTbl.BOF And Not rstTbl.EOF Then
    '--------------------DO Until----------------
        Do Until rstTbl.EOF = True
            myMStr = rstTbl![MfgPartNumber]
            myVStr = rstTbl![VendorPartNumber]
            myTCost = rstTbl![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, "")
    '------If 1-----
            If strNewMfg = strNewVendor Then
                rstTbl.Edit
                rstTbl![PartNumber] = strNewMfg & " - " & Right("0000" & strNewTCost, 7)
                rstTbl.Update
            Else
                rstTbl.Edit
                rstTbl![PartNumber] = strNewVendor & " - " & strNewMfg & " - " & Right("0000" & strNewTCost, 7)
                rstTbl.Update
            End If
    '---End If 1-----
    
    
    '-------------Next For Each----------
        Next
    
    
            rstTbl.MoveNext
    '--------------------Loop----------------
        Loop
    
    
    '-------------------------End If 2------------------------
    End If
    
    
    '------------------------------End With-------------------------
    
    
    End With
    
    
    End Sub

  9. #9
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22
    Quote Originally Posted by pbaldy View Post
    Sure:

    myMStr = Me.MfgPartNumber

    presuming "MfgPartNumber" is the name of the textbox on the form. I suspect the before update event of the form would be the place to call this from, which should handle new or edited records. You certainly shouldn't have to loop the whole table every time, just the current record.
    I tried your suggestion and I got an error message. See picture (never added pic, so not sure how you will get it. Please let me know if you can't see it)

    Click image for larger version. 

Name:	Capture.PNG 
Views:	30 
Size:	16.1 KB 
ID:	39121

    Thanks,
    Rosa
    Last edited by RosaCat; 07-18-2019 at 07:26 AM. Reason: Picture didn't upload. Message box needs a clear button or something. THanks

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The Me keyword is only valid in the code behind a form or report. In a standard module you'd use the full reference:

    Forms!FormName.txtMfgpartNumber

    You also need to drop all the recordset stuff so you're only working on the single record displayed on the form.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22
    Thanks for your help! I just don't know what I am doing. I messed everything up.

    Thank God I know how to restore previous versions. I know it very well!! lol.

    Can you please highlight the part I need to remove?

    Thanks,
    RosaCat

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Everything in red:

    Code:
    Option Compare DatabaseConst SpecialCharacters As String = "!,@,#,$,%,^,&,*,(,),{,[,],},?,-,_, ,<,>,/,\,.,+"
    Sub RemoveChar()
    Dim rstTbl As DAO.Recordset
    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
    
    
    Set rstTbl = CurrentDb.OpenRecordset("Parts", dbOpenDynaset)
    
    
    '----------------------------------With---------------------------
    With rstTbl
        .MoveFirst
    '-----------------------------If 2------------------------
    If Not rstTbl.BOF And Not rstTbl.EOF Then
    '--------------------DO Until----------------
        Do Until rstTbl.EOF = True
            myMStr = rstTbl![MfgPartNumber]
            myVStr = rstTbl![VendorPartNumber]
            myTCost = rstTbl![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, "")
    '------If 1-----
            If strNewMfg = strNewVendor Then
                rstTbl.Edit
                rstTbl![PartNumber] = strNewMfg & " - " & Right("0000" & strNewTCost, 7)
                rstTbl.Update
            Else
                rstTbl.Edit
                rstTbl![PartNumber] = strNewVendor & " - " & strNewMfg & " - " & Right("0000" & strNewTCost, 7)
                rstTbl.Update
            End If
    '---End If 1-----
    
    
    '-------------Next For Each----------
        Next
    
    
            rstTbl.MoveNext
    '--------------------Loop----------------
        Loop
    
    
    '-------------------------End If 2------------------------
    End If
    
    
    '------------------------------End With-------------------------
    
    
    End With
    
    
    End Sub
    Anything referring to the recordset that's left would be switched to referring to the form, and instead of using Edit/Update you'd just set the form values:

    Forms!FormName.ControlName = VariableName

    Oh, and there's no need to update the form values within the For/Next loop like you are, just do it once at the end.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22
    This is all really above my head. I tried to take a break from it and revisit it after the weekend, but it's not sinking in for me.

    I just wanted to thank you for all your help!

    Have a Great Day!
    Rosa

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    RosaCat is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    22
    DB NO DATA.zip

    Took me a while, but I finally figured it out. I hope is works on your end.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Parts List on Parts Requisition Report
    By billgyrotech1 in forum Access
    Replies: 16
    Last Post: 06-03-2019, 01:17 PM
  2. Replies: 12
    Last Post: 09-22-2016, 02:59 PM
  3. Replies: 3
    Last Post: 08-02-2012, 11:37 AM
  4. How to loop code to run through entire table
    By kmajors in forum Reports
    Replies: 9
    Last Post: 04-23-2010, 09:27 AM
  5. creation of reports with fixed number of rows
    By georgia in forum Reports
    Replies: 1
    Last Post: 02-17-2010, 10:40 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums