Query no record display

[smtazulislam]

Hello everyone,
I try to create a successfully database for payroll. Before I have 2 threads for payroll resolving issue, and one thread is pending yet . Alhamdullillah, I solved with out side help. Now I have another problem.

I have 3 tables with each table have a "Total" filed. See in the attached sample picture.

Firstly, I try to insert from FORM field "Total" value via the vba code. Like....

Private Sub TotalDeductions_AfterUpdate()
Me.TotalDeductions = Me.txtDeductions.Value
End Sub

But working.

Secondly, I try to create a query tblPayroll table with DELETE filed of "RED COLOR" and JOIN on 3 tables. but here dont display record. Because Maybe some table are not data. e.g. many employee dont have "allowance" and many employee dont have "deduction". so it was sometimes is blank.

Thirdly, I try to a UPDATE button for a vba to INSERT in tblPayroll where sql dont file the ID

Private Sub cmdUpdate_Click()
Dim sql As String

sql = "INSERT INTO tblPayroll.PayRollID, tblPayroll.EmployeeID, tblPayroll.PayrollYear, tblPayroll.PayrollMonth, tblPayroll.WorkedDays, tblPayroll.TotalAbsentdays, tblPayroll.TotalAllowances, tblPayroll.GrossSalary, tblPayroll.TotalDeductions, tblPayroll.TotalOvertime, tblPayroll.CashAdvance, tblPayroll.NetSalary, tblPayroll.PaymentType" & _
      "FROM tblPayrol"
DoCmd.RunCommand acCmdSave
MsgBox "You data has been saved !"
End Sub

I want to your support advice what is the best ways to keep data in the future and how can I resolve my problem ?
Any help will be appreciate.

 

[NauticalGent]

Without looking at your query itself, I can tell you your query is not running becuase of your choices of commands.

Instead of:

Private Sub cmdUpdate_Click()
Dim sql As String

sql = "INSERT INTO tblPayroll.PayRollID, tblPayroll.EmployeeID, tblPayroll.PayrollYear, tblPayroll.PayrollMonth, tblPayroll.WorkedDays, tblPayroll.TotalAbsentdays, tblPayroll.TotalAllowances, tblPayroll.GrossSalary, tblPayroll.TotalDeductions, tblPayroll.TotalOvertime, tblPayroll.CashAdvance, tblPayroll.NetSalary, tblPayroll.PaymentType" & _
      "FROM tblPayrol"
DoCmd.RunCommand acCmdSave
MsgBox "You data has been saved !"
End Sub

' Try:'
Private Sub cmdUpdate_Click()
Dim sql As String

sql = "INSERT INTO tblPayroll.PayRollID, tblPayroll.EmployeeID, tblPayroll.PayrollYear, tblPayroll.PayrollMonth, tblPayroll.WorkedDays, tblPayroll.TotalAbsentdays, tblPayroll.TotalAllowances, tblPayroll.GrossSalary, tblPayroll.TotalDeductions, tblPayroll.TotalOvertime, tblPayroll.CashAdvance, tblPayroll.NetSalary, tblPayroll.PaymentType" & _
      "FROM tblPayrol"
DoCmd.RunSql sql
MsgBox "You data has been saved !"
End Sub

 

[smtazulislam]

Without looking at your query itself, I can tell you your query is not running becuase of your choices of commands.

Instead of:

Private Sub cmdUpdate_Click()
Dim sql As String

sql = "INSERT INTO tblPayroll.PayRollID, tblPayroll.EmployeeID, tblPayroll.PayrollYear, tblPayroll.PayrollMonth, tblPayroll.WorkedDays, tblPayroll.TotalAbsentdays, tblPayroll.TotalAllowances, tblPayroll.GrossSalary, tblPayroll.TotalDeductions, tblPayroll.TotalOvertime, tblPayroll.CashAdvance, tblPayroll.NetSalary, tblPayroll.PaymentType" & _
      "FROM tblPayrol"
DoCmd.RunCommand acCmdSave
MsgBox "You data has been saved !"
End Sub

' Try:'
Private Sub cmdUpdate_Click()
Dim sql As String

sql = "INSERT INTO tblPayroll.PayRollID, tblPayroll.EmployeeID, tblPayroll.PayrollYear, tblPayroll.PayrollMonth, tblPayroll.WorkedDays, tblPayroll.TotalAbsentdays, tblPayroll.TotalAllowances, tblPayroll.GrossSalary, tblPayroll.TotalDeductions, tblPayroll.TotalOvertime, tblPayroll.CashAdvance, tblPayroll.NetSalary, tblPayroll.PaymentType" & _
      "FROM tblPayrol"
DoCmd.RunSql sql
MsgBox "You data has been saved !"
End Sub

Thank you so much. before I try it not work,
RunTime Error message "3134"

Edit :
Also added
Debug.Print

 

[NauticalGent]

Yes, your append query has a few issues. I believe "From tblPayrol" is supposed to be "From tblPayroll". But that isnt going to give you what you are looking for.

Can you tell us exactly what information (Values) you are trying to insert into tblPayroll and where (table, query) this info is coming from? Also, have you tried to simply use the Query Builder/Grid to design it?

 

[smtazulislam]

Yes, your append query has a few issues. I believe "From tblPayrol" is supposed to be "From tblPayroll". But that isnt going to give you what you are looking for.

Can you tell us exactly what information (Values) you are trying to insert into tblPayroll and where (table, query) this info is coming from? Also, have you tried to simply use the Query Builder/Grid to design it?

Exactly have detail in attached picture in msg #1.
but something I information that e.g
tblAllowance = "Total" Allowance Amount Value
tbleDeduction = "Total" Deduction Amount Value
3 tables "Total" value I want to INSERT a QUERY or TABLE.

 

[NauticalGent]

So, to clarify:

You want to INSERT:
tblDeductionProcedure.Total INTO tblPayroll.TotalDeductions
tblEmployeeSalaries.GrossSalary INTO tblPayroll.GrossSalary
tblAllowanceData.Amount INTO tblPayroll.TotalAllowances

WHERE
EmployeeID is equal

Do I have that correctly?

 

[smtazulislam]

So, to clarify:

You want to INSERT:
tblDeductionProcedure.Total INTO tblPayroll.TotalDeductions
tblEmployeeSalaries.GrossSalary INTO tblPayroll.GrossSalary
tblAllowanceData.Amount INTO tblPayroll.TotalAllowances

WHERE
EmployeeID is equal

Do I have that correctly?

Exactly my Query I try..
SELECT tblPayroll.PayRollID, tblPayroll.EmployeeID, tblPayroll.PayrollYear, tblPayroll.PayrollMonth, tblPayroll.WorkedDays, tblEmployeeSalaries.GrossSalary, tblAllowanceData.Amount, tblDeductionProcedure.Total
FROM tblEmployeeSalaries INNER JOIN (tblDeductionProcedure INNER JOIN (tblAllowanceData INNER JOIN tblPayroll ON tblAllowanceData.AllowanceID = tblPayroll.AllowanceID) ON tblDeductionProcedure.DeductionID = tblPayroll.DeductionID) ON tblEmployeeSalaries.SalaryID = tblPayroll.SalaryID;

it was no record showed.
But Where (Criteria) I dont try...see the attached picture

 

[NauticalGent]

Use an Outer Join on tblPayRollID to your other tables. Right click on the "Join" line and choose "Include ALL records from tblPayRoll and only those records from tbl____ where the joined fields are equal".

I may have the exact words jumbled a bit but you should be able to figure it out...

EDIT: Once you get the SELECT query, you should stop and take some adivce about what it is you're trying to do. You do not need an insert query. Lets get the SELECT Query to work first and then we can talk about why an UPDATE query is a bad idea.

 

[NauticalGent]

I have to step away for awhile. I will be back to answer some questions unless somebody else steps in

 

[smtazulislam]

Use an Outer Join on tblPayRollID to your other tables. Right click on the "Join" line and choose "Include ALL records from tblPayRoll and only those records from tbl____ where the joined fields are equal".

I may have the exact words jumbled a bit but you should be able to figure it out...

EDIT: Once you get the SELECT query, you should stop and take some adivce about what it is you're trying to do. You do not need an insert query. Lets get the SELECT Query to work first and then we can talk about why an UPDATE query is a bad idea.

Now have syntax error, please see the sql have any mistake I do.

 

[Minty]

There are a lot of issues here.
You are not including commas between your fields in the select statement, or spaces in your concatenated strings.

Move your Debug.Print before you try and run the SQL and you will see it all is malformed.

You also appear to be storing calculated values which you should never do.
I also wouldn't have the additions and deductions in two separate tables.

As @NauticalGent has said - I think you are running before you can walk here.

 

[smtazulislam]

I try to now another ways. But its also have "Runtime error "3144".

I dont find any mistake. can anyone check and talk me where i have mistake.

Private Sub cmdUpdate_Click()
If Me.Dirty Then Me.Dirty = False

Dim CrId As Integer
Dim StrEmployeeID As Long
Dim StrYear As Long
Dim StrMonth As Long
Dim StrGrossSalary As Long
Dim StrTotalAllowances As Long
Dim StrTotalAbsentdays As Long
Dim StrTotalDeductions As Long
Dim StrTotalOvertime As Long
Dim StrCashAdvance As Long
Dim StrSQL As String


CrId = Me.CurrentRecord
StrEmployeeID = Me.txtEmployeeID
StrYear = Me.cboYear
StrMonth = Me.CboMonth
StrGrossSalary = [Forms]![frmPayrollMainForm]![SubfrmEmployeeSalaries].[Form]![txtGS]
StrTotalAllowances = [Forms]![frmPayrollMainForm]![SubfrmAllowanceData].[Form]![txtTotalAllowances]
StrTotalAbsentdays = [Forms]![frmPayrollMainForm]![SubfrmAbsentDay].[Form]![txtTotalAbsent]
StrTotalDeductions = [Forms]![frmPayrollMainForm]![SubfrmDeductionProcedure].[Form]![txtTotalDeductions]
StrTotalOvertime = [Forms]![frmPayrollMainForm]![SubfrmOverTime].[Form]![txtTotalOverTime]
StrCashAdvance = [Forms]![frmPayrollMainForm]![SubfrmCashAdvance].[Form]![txtTotalCashAdvance]

StrSQL = "UPDATE INTO tblPayroll SET PayrollYear = " & StrYear & ", PayrollMonth = " & StrMonth & ", WorkedDays = " & Me.txtDayOfMonth & ", GrossSalary = " & StrGrossSalary & ", TotalAllowances = " & StrTotalAllowances & ", TotalAbsentdays = " & StrTotalAbsentdays & ", TotalDeductions = " & StrTotalDeductions & ", TotalOvertime = " & StrTotalOvertime & ", CashAdvance = " & StrCashAdvance & "" & _
         "WHERE EmployeeID = " & StrEmployeeID & ";"
          
    MsgBox "Enter your Data have been updated in the table ", vbInformation + vbOKOnly, "Update Info"
      
    Debug.Print StrSQL
    CurrentDb.Execute StrSQL, dbFailOnError

    DoCmd.GoToRecord , , acGoTo, CrId

NOTE : First I try directly like :

StrSQL = "UPDATE tblPayroll SET

 

[Minty]

And the result of your Debug.Print is?

 

[smtazulislam]

And the result of your Debug.Print is?

"Run-time error "3144".

 

[Minty]

No it isn't.

In the immediate window of the VBA editor (Press Ctrl & G to display it) you should have the result of string concatenation for StrSQL

 

[smtazulislam]

No it isn't.

In the immediate window of the VBA editor (Press Ctrl & G to display it) you should have the result of string concatenation for StrSQL

all data what I enter disply successfully in the immediate window. But table dont UPDATE/INSERT.

 

[Minty]

Well sorry to be the bringer of bad news, but if it was 100% successful it would work, and it doesn't.

Please post up the SQL (without any editing) that is generated in the immediate window, as I am very confident that is where your problem is.

 

[smtazulislam]

Well sorry to be the bringer of bad news, but if it was 100% successful it would work, and it doesn't.

Please post up the SQL (without any editing) that is generated in the immediate window, as I am very confident that is where your problem is.

The code is exactly what I enter in my button.

Private Sub cmdUpdate_Click()
If Me.Dirty Then Me.Dirty = False

Dim CrId As Integer
Dim StrEmployeeID As Long
Dim StrYear As Long
Dim StrMonth As Long
Dim StrGrossSalary As Long
Dim StrTotalAllowances As Long
Dim StrTotalAbsentdays As Long
Dim StrTotalDeductions As Long
Dim StrTotalOvertime As Long
Dim StrCashAdvance As Long
Dim StrSQL As String

CrId = Me.CurrentRecord
StrEmployeeID = Me.txtEmployeeID
StrYear = Me.cboYear
StrMonth = Me.CboMonth
StrGrossSalary = [Forms]![frmPayrollMainForm]![SubfrmEmployeeSalaries].[Form]![txtGS]
StrTotalAllowances = [Forms]![frmPayrollMainForm]![SubfrmAllowanceData].[Form]![txtTotalAllowances]
StrTotalAbsentdays = [Forms]![frmPayrollMainForm]![SubfrmAbsentDay].[Form]![txtTotalAbsent]
StrTotalDeductions = [Forms]![frmPayrollMainForm]![SubfrmDeductionProcedure].[Form]![txtTotalDeductions]
StrTotalOvertime = [Forms]![frmPayrollMainForm]![SubfrmOverTime].[Form]![txtTotalOverTime]
StrCashAdvance = [Forms]![frmPayrollMainForm]![SubfrmCashAdvance].[Form]![txtTotalCashAdvance]

StrSQL = "INSERT tblPayroll SET PayrollYear = '" & StrYear & "', PayrollMonth = '" & StrMonth & "', WorkedDays = " & Me.txtDayOfMonth & ", GrossSalary = " & StrGrossSalary & ", TotalAllowances = " & StrTotalAllowances & ", TotalAbsentdays = " & StrTotalAbsentdays & ", TotalDeductions = " & StrTotalDeductions & ", TotalOvertime = " & StrTotalOvertime & ", CashAdvance = " & StrCashAdvance & "" & _
         "WHERE EmployeeID = " & StrEmployeeID & ";"
      
    MsgBox "Enter your data have been updated in the table ", vbInformation + vbOKOnly, "Update Info"
      
    Debug.Print StrSQL
    CurrentDb.Execute StrSQL, dbFailOnError

    DoCmd.GoToRecord , , acGoTo, CrId
End Sub

 

[smtazulislam]

Immediate window picture

 

[Minty]

Thank you - it's the immediate window Debug output we need - your picture what I was after.
You have changed some of your code to make certain things strings from your first post.
That is why I wanted to see the actual StrSQL output, not the code that creates it.

Can you copy and paste the whole thing here please, as I can't see all of it, and a picture is difficult to edit and correct any errors.