Results 1 to 10 of 10
  1. #1
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125

    VBA to transfer data from query to table using a form command.

    There is a form called “paymentform” in the uploaded db. On the form, there is a command button with caption “Carryout Payment” and with name “CmdValidate”.


    There is also a table called “payment” and the fields in this table are identical in spelling and datatype with the query “salaryextended”.


    What I want is a VBA embedded on the On Click event of the command button “CmdValidate” which acts as follows:

    “Transfer all the items in “Salaryextended” to the table “payment” where [salaryextended[.[monthname]=[forms]![paymentform].[monthname] or where [salaryextended].[monthname] Is Null then add to each of the fields transferred,

    Estid=[forms]![paymentform].[EstId]

    Paydate=[forms]![paymentform].[paydate]

    Payfrom=[forms]![paymentform].[payfrom]

    Payto=[forms]![paymentform].[payto]



    Paymonth=[forms]![paymentform].[monthname] ”

    But before transfering, it should first verify the table “payment” to see that [forms]![paymentform].[monthname] is not existing in the table “[payment].[paymonth]” having the same year as in [forms]![paymentform].[paydate] Thanks in advance.
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The Payment and OtherPayItems tables are not a normalized data structure because of the multiple payment fields. Repeating the salary information in Payment table is also not conventional relational db design.

    However, code to accomplish what you want could be like:

    If IsNull(DLookup("payID", "payment", "paymonth=" & Me.monthname & " AND Year(payfrom)=" & Year(Me.payfrom))) Then
    CurrentDb.Execute "INSERT INTO Payment(EmployeeID, Gross, pit, act, pensionscheme, crtv, hlf, salaryadvance, loanrepayment, foodrepayment, courtdeduction, housingrepayment, doe, paymonth, paydate, payfrom, payto, estid) SELECT EmployeeID, Gross, pit, act, pensionscheme, crtv, hlf, salaryadvance, loanrepayment, foodrepayment, courtdeductions, housingrepayment, Date()," & Me.monthname & " AS MN, #" & Me.paydate & "# AS PD, #" & Me.payfrom & "# AS PF, #" & Me.payto & "# AS PT, " & Me.estid & " AS EID FROM SalaryExtended WHERE (monthname=" & Me.monthname & " AND Year(payfrom)=" & Year(Me.payfrom) & ") OR monthname Is Null"
    End If

    I see different spelling for couple of fields.

    courtdeductions in SalaryExtended

    courtdeduction in payment

    monthname in SalaryExtended

    paymonth in payment
    Last edited by June7; 03-05-2014 at 04:47 PM.
    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
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    I have copied the code and when i click on the command button "Carryout Payment" I get the following error msg

    Run-error '3075':

    Syntax error (missing operator ) in query expression '4AS MN'.

    Please if you have embedded it into the sample db, please help me upload it.

    Thanks.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I did not test the code in your database. It was typed 'from-the-hip' into post.

    Looks like I missed a space in the construct. I have corrected the earlier post.

    & Me.monthname & " AS MN
    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.

  5. #5
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    I have corrected it. I tested again by entering the following data on paymentform:

    Establishment: PCHS
    Pay, From : 1 march 2014
    Pay, To : 30 March 2014
    Pay Date : 27 march 2014

    Now I get the following error msg

    Run-time error '3075'
    Syntax error in date in query espression '3 #.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    3 is not a date value. The code expects a date (mm/dd/yyyy).

    Debug your code.

    Declare string variable: strSQL

    Set the variable to the sql string: strSQL = "SELECT ..."

    Debug.Print strSQL

    CurrentDb.Execute strSQL

    Comment the CurrentDb line until confirm the SQL properly constructs for the data.

    Set a breakpoint on the End If line.

    Analyze the string printed to the immediate window. What's wrong?
    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.

  7. #7
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    I am not really good in VBA but from what you said I tried but to no avail. I have done it couple of ways but no good results. This is a sample of what i have done:
    Code:
    Private Sub Cmdvalidate_Click()
    Dim strSQL As String
    
        strSQL = "SELECT EmployeeID, Gross, pit, act, pensionscheme, crtv, hlf, salaryadvance, loanrepayment, foodrepayment, courtdeductions, housingrepayment," & Me.monthname & "# AS MN, #" & Me.paydate & "# AS PD, #" & Me.payfrom & "# AS PF, #" & Me.payto & "# AS PT, #" & Me.Estid & " AS EID FROM SalaryExtended WHERE (monthname=" & Me.monthname & " AND Year(payfrom)=" & year(Me.payfrom) & " AND EstID=" & Me.Estid & ") OR monthname Is Null AND estID=" & Me.Estid & ""
    
    If IsNull(Me.Estid) Or IsNull(Me.payfrom) Or IsNull(Me.payto) Or IsNull(Me.monthname) Then
            MsgBox "All the fields are mandatory. Please fill all of them before proceeding to carryout payment", vbOKOnly, "For your promt action"
            
        ElseIf IsNull(DLookup("payID", "payment", "paymonth=" & Me.monthname & " AND Year(payfrom)=" & year(Me.payfrom) & " AND estID=" & Me.Estid)) Then
            CurrentDb.Execute strSQL And "INSERT INTO Payment(EmployeeID, Gross, pit, act, pensionscheme, crtv, hlf, salaryadvance, loanrepayment, foodrepayment, courtdeduction, housingrepayment,paymonth, paydate, payfrom, payto, estid)"
        Else
            MsgBox "The payments for this month in this establishment have already been done.", vbOKOnly, "PAYroll Informer"
    End If

  8. #8
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    when I click the CmdValidate button, nothing happens.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I don't see a Debug.Print line. The CurrentDb.Execute line is not commented. Refer to link at bottom of my post for debugging guidelines.

    Also, try another pair of parens to enclose the two sides of the OR criteria, syntax like: ((this AND this AND this) OR this) AND this

    Unless you want:

    (this AND this AND this) OR (this AND this)

    Parens are critical when mixing AND and OR operators.
    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.

  10. #10
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    Hello June7. Finally i was able to accomplish the task. Here is the code i use:

    Code:
     
    Private Sub Cmdvalidate_Click()
    Dim PayrollManagerDb As Database
    Dim strSQL As String
    Dim SalaryExtendedRs As Recordset
    Dim SalaryExtendedLoopRs As Recordset
    Dim insertStrSql As String
    
    ' SalaryExtended field indices
    Dim Se_EmployeedId_Idx As Integer
    Dim Se_Gross_Idx As Integer
    Dim Se_Pit_Idx As Integer
    Dim Se_Act_Idx As Integer
    Dim Se_PensionScheme_Idx As Integer
    Dim Se_Crtv_Idx As Integer
    Dim Se_Hlf_Idx As Integer
    Dim Se_SalaryAdvance_Idx As Integer
    Dim Se_LoanRepayment_Idx As Integer
    Dim Se_FoodRepayment_Idx As Integer
    Dim Se_CourtDeductions_Idx As Integer
    Dim Se_HousingRepayment_Idx As Integer
    Dim Se_MonthName_Idx As Integer
    Dim Se_EstId_Idx As Integer
    
    
    If IsNull(Me.Estid) Or IsNull(Me.payfrom) Or IsNull(Me.payto) Or IsNull(Me.monthname) Then
            MsgBox "All the fields are mandatory. Please fill all of them before proceeding to carryout payment", vbOKOnly, "For your promt action"
            
        ElseIf IsNull(DLookup("payID", "payment", "paymonth=" & Me.monthname & " AND Year(payfrom)=" & year(Me.payfrom) & " AND estID=" & Me.Estid)) Then
                strSQL = "SELECT * FROM SalaryExtended WHERE (EstId=" & Me.Estid & " AND monthname=" & Me.monthname & ")" _
                 & " OR (monthname Is Null AND EstId=" & Me.Estid & ");"
                   'Print report of original data
                Debug.Print strSQL
                Set PayrollManagerDb = CurrentDb
                Set SalaryExtendedRs = PayrollManagerDb.OpenRecordset(strSQL)
                Se_EmployeeId_Idx = 0
                Se_Gross_Idx = 1
                Se_Pit_Idx = 2
                Se_Act_Idx = 3
                Se_PensionScheme_Idx = 4
                Se_Crtv_Idx = 5
                Se_Hlf_Idx = 6
                Se_SalaryAdvance_Idx = 7
                Se_LoanRepayment_Idx = 8
                Se_FoodRepayment_Idx = 9
                Se_CourtDeductions_Idx = 10
                Se_HousingRepayment_Idx = 11
                Se_MonthName_Idx = 12
                Se_EstId_Idx = 13
                
                With SalaryExtendedRs
                    Do While Not .EOF
                        insertStrSql = "INSERT INTO payment (employeeid,gross,pit,act,pensionscheme,crtv,hlf,salaryadvance,loanrepayment" _
                        & ",housingrepayment,foodrepayment,courtdeduction,paydate,payfrom,payto,doe,paymonth,estid)" _
                        & "VALUES (" _
                        & .Fields(Se_EmployeeId_Idx) & "," _
                        & .Fields(Se_Gross_Idx) & "," _
                        & Nz(.Fields(Se_Pit_Idx), "0") & "," _
                        & Nz(.Fields(Se_Act_Idx), "0") & "," _
                        & Nz(.Fields(Se_PensionScheme_Idx), "0") & "," _
                        & Nz(.Fields(Se_Crtv_Idx), "0") & "," _
                        & Nz(.Fields(Se_Hlf_Idx), "0") & "," _
                        & Nz(.Fields(Se_SalaryAdvance_Idx), "0") & "," _
                        & Nz(.Fields(Se_LoanRepayment_Idx), "0") & "," _
                        & Nz(.Fields(Se_HousingRepayment_Idx), "0") & "," _
                        & Nz(.Fields(Se_FoodRepayment_Idx), "0") & "," _
                        & Nz(.Fields(Se_CourtDeductions_Idx), "0") & "," _
                        & "#" & Me.paydate & "#," _
                        & "#" & Me.payfrom & "#," _
                        & "#" & Me.payto & "#," _
                        & "#" & Date & "#," _
                        & Me.monthname & "," _
                        & .Fields(Se_EstId_Idx) & ")"
                        PayrollManagerDb.Execute (insertStrSql)
                        'Debug.Print insertStrSql
                        .MoveNext
                    Loop
                End With
            MsgBox "Payment succesfully done", vbOKOnly, "PAYroll Informer"
        Else
            MsgBox "The payments for this month in this establishment have already been done.", vbOKOnly, "PAYroll Informer"
    End If
    
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 09-13-2012, 12:07 PM
  2. Replies: 2
    Last Post: 06-15-2012, 07:41 PM
  3. Replies: 2
    Last Post: 11-02-2011, 08:31 AM
  4. Transfer data from ListView to Table
    By Zyckie in forum Access
    Replies: 1
    Last Post: 12-15-2009, 11:23 AM
  5. Replies: 3
    Last Post: 04-23-2006, 09:09 PM

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