Results 1 to 10 of 10
  1. 05-08-2020, 10:53 PM #1
    onlylonely
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110

    Reduce coding...

    Hi Guys,



    Would appreciate if you can give me a hint on how to reduce the coding. I think by using below coding it makes the program abit slow. Correct me if im wrong.

    Code:
    sSQL = "Update F2CAPAtbl Set [F2CAPAtbl].[Containment_Action] = '" & Forms!F2CAPAForm.txtCA & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"CurrentDb.Execute (sSQL)


sSQL1 = "Update F2CAPAtbl Set [F2CAPAtbl].[CA_Date] = '" & Forms!F2CAPAForm.txtcad & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL1)


sSQL2 = "Update F2CAPAtbl Set [F2CAPAtbl].[Root_Cause] = '" & Forms!F2CAPAForm.txtrc & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL2)


sSQL3 = "Update F2CAPAtbl Set [F2CAPAtbl].[Corrective_Action] = '" & Forms!F2CAPAForm.txtCAN & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL3)


sSQL4 = "Update F2CAPAtbl Set [F2CAPAtbl].[CAN_Date] = '" & Forms!F2CAPAForm.txtcand & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL4)


sSQL5 = "Update F2CAPAtbl Set [F2CAPAtbl].[Preventive_Action] = '" & Forms!F2CAPAForm.txtPA & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL5)


sSQL6 = "Update F2CAPAtbl Set [F2CAPAtbl].[PA_Date] = '" & Forms!F2CAPAForm.txtpad & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL6)


sSQL7 = "Update F2CAPAtbl Set [F2CAPAtbl].[Responder] = '" & Forms!F2CAPAForm.txtresp & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL7)


sSQL8 = "Update F2CAPAtbl Set [F2CAPAtbl].[Responder_date] = '" & Forms!F2CAPAForm.txtrespdate & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL8)


sSQL9 = "Update F2CAPAtbl Set [F2CAPAtbl].[Reviewer] = '" & Forms!F2CAPAForm.txtrev & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL9)


sSQL11 = "Update F2CAPAtbl Set [F2CAPAtbl].[WPPL_QM] = '" & Forms!F2CAPAForm.txtqm & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL11)


sSQL12 = "Update F2CAPAtbl Set [F2CAPAtbl].[Failure_Type] = '" & Forms!F2CAPAForm.cmbft & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL12)


sSQL13 = "Update F2CAPAtbl Set [F2CAPAtbl].[Sub_category] = '" & Forms!F2CAPAForm.cmbsub & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL13)


sSQL14 = "Update F2CAPAtbl Set [F2CAPAtbl].[Remarks] = '" & Forms!F2CAPAForm.txtremarks & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
CurrentDb.Execute (sSQL14)
    Reply With Quote Reply With Quote
  2. 05-09-2020, 12:00 AM #2
    June7's Avatar
    June7
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,931
    Yes, can combine all these updates to a single action. Can SET as many fields as you want in one action. Date/time field parameters should be delimited with #. Number type fields do not need delimiter.
    Code:
    sSQL = "Update F2CAPAtbl Set "

sSQL = sSQL & "[Containment_Action] = '" & Forms!F2CAPAForm.txtCA & "',"

sSQL = sSQL & "[CA_Date] = #" & Forms!F2CAPAForm.txtcad & "#,"

sSQL = sSQL & "[Root_Cause] = '" & Forms!F2CAPAForm.txtrc & "',"

sSQL = sSQL & "[Corrective_Action] = '" & Forms!F2CAPAForm.txtCAN & "',"

sSQL = sSQL & "[CAN_Date] = #" & Forms!F2CAPAForm.txtcand & "#,"

sSQL = sSQL & "[Preventive_Action] = '" & Forms!F2CAPAForm.txtPA & "',"

sSQL = sSQL & "[PA_Date] = #" & Forms!F2CAPAForm.txtpad & "#,"

sSQL = sSQL & "[Responder] = '" & Forms!F2CAPAForm.txtresp & "',"

sSQL = sSQL & "[Responder_date] = #" & Forms!F2CAPAForm.txtrespdate & "#,"

sSQL = sSQL & "[Reviewer] = '" & Forms!F2CAPAForm.txtrev & "',"

sSQL = sSQL & "[WPPL_QM] = '" & Forms!F2CAPAForm.txtqm & "',"

sSQL = sSQL & "[Failure_Type] = '" & Forms!F2CAPAForm.cmbft & "',"

sSQL = sSQL & "[Sub_category] = '" & Forms!F2CAPAForm.cmbsub & "',"

sSQL = sSQL & "[Remarks] = '" & Forms!F2CAPAForm.txtremarks & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
    But why are you doing this and not just using a bound form?
    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.
    Reply With Quote Reply With Quote
  3. 05-09-2020, 12:24 AM #3
    onlylonely
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Thanks June.
    I'm not using bound form because if anything happen is very difficult to figure what's the issue.


    Quote Originally Posted by June7 View Post
    Yes, can combine all these updates to a single action. Can SET as many fields as you want in one action. Date/time field parameters should be delimited with #. Number type fields do not need delimiter.
    Code:
    sSQL = "Update F2CAPAtbl Set "

sSQL = sSQL & "[Containment_Action] = '" & Forms!F2CAPAForm.txtCA & "',"

sSQL = sSQL & "[CA_Date] = #" & Forms!F2CAPAForm.txtcad & "#,"

sSQL = sSQL & "[Root_Cause] = '" & Forms!F2CAPAForm.txtrc & "',"

sSQL = sSQL & "[Corrective_Action] = '" & Forms!F2CAPAForm.txtCAN & "',"

sSQL = sSQL & "[CAN_Date] = #" & Forms!F2CAPAForm.txtcand & "#,"

sSQL = sSQL & "[Preventive_Action] = '" & Forms!F2CAPAForm.txtPA & "',"

sSQL = sSQL & "[PA_Date] = #" & Forms!F2CAPAForm.txtpad & "#,"

sSQL = sSQL & "[Responder] = '" & Forms!F2CAPAForm.txtresp & "',"

sSQL = sSQL & "[Responder_date] = #" & Forms!F2CAPAForm.txtrespdate & "#,"

sSQL = sSQL & "[Reviewer] = '" & Forms!F2CAPAForm.txtrev & "',"

sSQL = sSQL & "[WPPL_QM] = '" & Forms!F2CAPAForm.txtqm & "',"

sSQL = sSQL & "[Failure_Type] = '" & Forms!F2CAPAForm.cmbft & "',"

sSQL = sSQL & "[Sub_category] = '" & Forms!F2CAPAForm.cmbsub & "',"

sSQL = sSQL & "[Remarks] = '" & Forms!F2CAPAForm.txtremarks & "' WHERE [F2CAPAtbl].[SCAR] = '" & Forms!F2CAPAForm!txtscar & "'"
    But why are you doing this and not just using a bound form?
    Reply With Quote Reply With Quote
  4. 05-09-2020, 01:00 AM #4
    moke123's Avatar
    moke123
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,654
    I'm not using bound form because if anything happen is very difficult to figure what's the issue.
    Is there something in particular that is happening that causes a problem with a bound form?

    Where is this code? If its in the form module then you can also use the Me. keyword instead of the form reference.
    Me.txtCA instead of Forms!F2CAPAForm.txtCA
    Reply With Quote Reply With Quote
  5. 05-09-2020, 01:22 AM #5
    accesstos's Avatar
    accesstos
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    More reduction:
    Code:
    With Forms!F2CAPAForm
    sSQL = "Update F2CAPAtbl SET " _
           & " [Containment_Action] = '" & .txtCA & "'," _
           & " [CA_Date] = #" & .txtcad & "#," _
           & " [Root_Cause] = '" & .txtrc & "'," _
           '...
           '...
           '...
           & " WHERE [SCAR] = '" & .txtscar & "'"
End With

CurrentDb.Execute sSQL, dbFailOnError
    Also, with a function in place of control's value providing a level of check:
    Code:
    Sub cmdUpdateTable()
    Dim sSQL As String
    
    With Forms!F2CAPAForm
        sSQL = "Update F2CAPAtbl SET " _
               & " [Containment_Action] = " & SQLValue(.txtCA, "Text") & ", " _
               & " [CA_Date] = " & SQLValue(.txtcad, "Date") & ", " _
               & " [Root_Cause] = " & SQLValue(.txtrc, "Text") & ", " _
               '...
               '...
               & " WHERE [SCAR] = '" & .txtscar & "'"
    End With

    CurrentDb.Execute sSQL, dbFailOnError

End Sub

Function SQLValue(FormControl As Control, Optional DataType As String = "Number") As String
    SQLValue = "Null"
    If Not IsNull(FormControl) Then
        Select Case DataType
            Case "Text"
                SQLValue = "'" & Replace(FormControl, "'", "''") & "'"
            Case "Date"
                If IsDate(FormControl) Then
                    SQLValue = "#" & Month(FormControl) & "/" & Day(FormControl) & "/" & Year(FormControl) & "#"
                End If
            Case "Number"
                SQLValue = CStr(FormControl)
            Case Else
        End Select
    End If
End Function
    Last edited by accesstos; 05-09-2020 at 01:33 AM. Reason: make DataType argument optional
    Reply With Quote Reply With Quote
  6. 05-09-2020, 02:42 AM #6
    onlylonely
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Quote Originally Posted by moke123 View Post
    Is there something in particular that is happening that causes a problem with a bound form?

    Where is this code? If its in the form module then you can also use the Me. keyword instead of the form reference.
    Me.txtCA instead of Forms!F2CAPAForm.txtCA
    Besides that issue. I found that is difficult to control the bound form.
    If i using coding, once i click submit, the coding will help me to check the password and if password correct all those information can insert/store to table.
    But if i use bound form, all the information will bound with the table when input the form, even if i didn't submit it, it will store the information in the table.
    Reply With Quote Reply With Quote
  7. 05-09-2020, 02:50 AM #7
    onlylonely
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Wow...... this is expert....
    I wish to learn the "Case" function so bad but until now i still not yet learn this.
    I'm not sure the coding below is for what purpose. can you briefly explain it?

    Code:
    Function SQLValue(FormControl As Control, Optional DataType As String = "Number") As String    SQLValue = "Null"
    If Not IsNull(FormControl) Then
        Select Case DataType
            Case "Text"
                SQLValue = "'" & Replace(FormControl, "'", "''") & "'"
            Case "Date"
                If IsDate(FormControl) Then
                    SQLValue = "#" & Month(FormControl) & "/" & Day(FormControl) & "/" & Year(FormControl) & "#"
                End If
            Case "Number"
                SQLValue = CStr(FormControl)
            Case Else
        End Select     End If
    Quote Originally Posted by accesstos View Post
    More reduction:
    Code:
    With Forms!F2CAPAForm
    sSQL = "Update F2CAPAtbl SET " _
           & " [Containment_Action] = '" & .txtCA & "'," _
           & " [CA_Date] = #" & .txtcad & "#," _
           & " [Root_Cause] = '" & .txtrc & "'," _
           '...
           '...
           '...
           & " WHERE [SCAR] = '" & .txtscar & "'"
End With

CurrentDb.Execute sSQL, dbFailOnError
    Also, with a function in place of control's value providing a level of check:
    Code:
    Sub cmdUpdateTable()
    Dim sSQL As String
    
    With Forms!F2CAPAForm
        sSQL = "Update F2CAPAtbl SET " _
               & " [Containment_Action] = " & SQLValue(.txtCA, "Text") & ", " _
               & " [CA_Date] = " & SQLValue(.txtcad, "Date") & ", " _
               & " [Root_Cause] = " & SQLValue(.txtrc, "Text") & ", " _
               '...
               '...
               & " WHERE [SCAR] = '" & .txtscar & "'"
    End With

    CurrentDb.Execute sSQL, dbFailOnError

End Sub

Function SQLValue(FormControl As Control, Optional DataType As String = "Number") As String
    SQLValue = "Null"
    If Not IsNull(FormControl) Then
        Select Case DataType
            Case "Text"
                SQLValue = "'" & Replace(FormControl, "'", "''") & "'"
            Case "Date"
                If IsDate(FormControl) Then
                    SQLValue = "#" & Month(FormControl) & "/" & Day(FormControl) & "/" & Year(FormControl) & "#"
                End If
            Case "Number"
                SQLValue = CStr(FormControl)
            Case Else
        End Select
    End If
End Function
    Reply With Quote Reply With Quote
  8. 05-09-2020, 03:42 AM #8
    moke123's Avatar
    moke123
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,654
    The SQLValue code that Accesstos provided is a like a helper function. It returns the proper delimiters for the specific datatype passed to it.
    For instance if it is text datatype it will add the quotation marks around the text. If it is a date it will add the octothorpes (#) around the date.

    Besides that issue. I found that is difficult to control the bound form.
    If i using coding, once i click submit, the coding will help me to check the password and if password correct all those information can insert/store to table.
    But if i use bound form, all the information will bound with the table when input the form, even if i didn't submit it, it will store the information in the table.
    Form data is only saved once you either close the form, move to another record , or explicitly save the data. You can use validation to discard any changes if needed.

    here's some info on select case . . . https://www.techonthenet.com/access/...anced/case.php
    Reply With Quote Reply With Quote
  9. 05-09-2020, 04:28 AM #9
    onlylonely
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Thx moke.. It's really helpful.

    I know use bound form is good and fast. I'm learning to use bound form as well.
    But it's difficult and keep getting unpredictable errors. But it will not stopping me to learn...
    Cause recently i have an issue on retrieve the data when i click "Back". If bound form it will be easy, if unbound then i need to use Dlookup and it will be very slow.

    Quote Originally Posted by moke123 View Post
    The SQLValue code that Accesstos provided is a like a helper function. It returns the proper delimiters for the specific datatype passed to it.
    For instance if it is text datatype it will add the quotation marks around the text. If it is a date it will add the octothorpes (#) around the date.



    Form data is only saved once you either close the form, move to another record , or explicitly save the data. You can use validation to discard any changes if needed.

    here's some info on select case . . . https://www.techonthenet.com/access/...anced/case.php
    Reply With Quote Reply With Quote
  10. 05-09-2020, 04:33 AM #10
    onlylonely
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Maybe i should quote an example.
    For unbound form. i've putting coding below , no issue apppear.

    msgbox(Lvalue) showing 2005 , but when i bound with table, lvalue showing empty and i can't figure out why it happened

    Code:
    Private Sub Frame97_AfterUpdate()

Dim LValue As String


LValue = Format(Date, "YYMM")


MsgBox (LValue)


'Me.txtscar = Nz(DMax(" SCAR ", "F1NCtbl", SCAR = LValue), 0) + 1
Me.txtsequence = Format(Nz(DMax("Sequence", "F1NCtbl", "YYMM = '" & LValue & "'"), 0) + 1, "00")


Me.txtscar = "ANC" & LValue & Me.txtsequence


'Me.txtsequence = Nz(DMax("Sequence", "F1NCtbl", "YYMM = '" & LValue & "'"), 0) + 1


Me.Dirty = False


End Sub


    Quote Originally Posted by moke123 View Post
    The SQLValue code that Accesstos provided is a like a helper function. It returns the proper delimiters for the specific datatype passed to it.
    For instance if it is text datatype it will add the quotation marks around the text. If it is a date it will add the octothorpes (#) around the date.



    Form data is only saved once you either close the form, move to another record , or explicitly save the data. You can use validation to discard any changes if needed.

    here's some info on select case . . . https://www.techonthenet.com/access/...anced/case.php
    Reply With Quote Reply With Quote
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. Can we try to reduce cross posting?
    By Micron in forum Forum Suggestions
    Replies: 36
    Last Post: 05-12-2022, 12:45 PM
  2. hot to reduce the option in combo box
    By harrie in forum Access
    Replies: 1
    Last Post: 12-08-2016, 04:43 AM
  3. Trying to Reduce Database Size
    By MarcLiq in forum Access
    Replies: 1
    Last Post: 12-29-2011, 03:55 PM
  4. Reduce PDF File Size
    By Phil Knapton in forum Programming
    Replies: 11
    Last Post: 02-16-2011, 09:10 AM
  5. How to reduce ribbon height
    By alaric01 in forum Access
    Replies: 2
    Last Post: 11-09-2010, 08:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Other Forums: Microsoft Office Forums