Query no record display (1 Viewer)

[smtazulislam]

Okay,
This is my immediate window copy .

INSERT tblPayroll SET PayrollYear = '11', PayrollMonth = '3', WorkedDays = 30, GrossSalary = 1100, TotalAllowances = 0, TotalAbsentdays = 0, TotalDeductions = 0, TotalOvertime = 0, CashAdvance = 0, WHERE EmployeeID = 1107;

 

[Minty]

Okay - thank you. I had forgotten the intention here, this is not how you write an insert query. The syntax is

INSERT INTO YourTable (FieldStr1, FieldNum2, FieldDate3 , etc)
            VALUES    ("String", 12    , #01/01/2021# , etc )

Or If you are supplying all the fields in the right order

INSERT INTO YourTable 
            VALUES "String", 12    , #01/01/2021# , etc )

 

[smtazulislam]

Okay - thank you. I had forgotten the intention here, this is not how you write an insert query. The syntax is

INSERT INTO YourTable (FieldStr1, FieldNum2, FieldDate3 , etc)
            VALUES    ("String", 12    , #01/01/2021# , etc )

Or If you are supplying all the fields in the right order

INSERT INTO YourTable
            VALUES "String", 12    , #01/01/2021# , etc )

What I enter :

StrSQL = "INSERT tblPayroll (PayrollYear, PayrollMonth, WorkedDays, GrossSalary, TotalAllowances, TotalAbsentdays, TotalDeductions, TotalOvertime, CashAdvance)" & _
         "VALUES (" & StrYear & "," & StrMonth & ", " & Me.txtDayOfMonth & ", " & StrGrossSalary & ", " & StrTotalAllowances & ", " & StrTotalAbsentdays & ", " & StrTotalDeductions & ", " & StrTotalOvertime & ", " & StrCashAdvance & ")" & _
         "WHERE EmployeeID = " & StrEmployeeID & ";"

My Immediate Window Result without INTO :

INSERT tblPayroll (PayrollYear, PayrollMonth, WorkedDays, GrossSalary, TotalAllowances, TotalAbsentdays, TotalDeductions, TotalOvertime, CashAdvance)VALUES (11,3, 30, 2000, 0, 0, 0, 0, 0)WHERE EmployeeID = 4;

My Immediate Window Result with INTO :

INSERT INTO tblPayroll (PayrollYear, PayrollMonth, WorkedDays, GrossSalary, TotalAllowances, TotalAbsentdays, TotalDeductions, TotalOvertime, CashAdvance)VALUES (11,3, 30, 2000, 0, 0, 0, 0, 0)WHERE EmployeeID = 4;

But "Run-time error 3067"

 

[smtazulislam]

can anyone able to help me. ..

 

[Minty]

Your string has no spaces between the Values and Where clause. Try this;

StrSQL = "INSERT tblPayroll (PayrollYear, PayrollMonth, WorkedDays, GrossSalary, TotalAllowances, TotalAbsentdays, TotalDeductions, TotalOvertime, CashAdvance) "
StrSQL = StrSQL & " VALUES (" & StrYear & "," & StrMonth & ", " & Me.txtDayOfMonth & ", " & StrGrossSalary & ", " & StrTotalAllowances & ", " & StrTotalAbsentdays & ", " & StrTotalDeductions & ", " & StrTotalOvertime & ", " & StrCashAdvance & ") "
StrSQL = StrSQL & " WHERE EmployeeID = " & StrEmployeeID & " ;"

 

[smtazulislam]

Your string has no spaces between the Values and Where clause. Try this;

StrSQL = "INSERT tblPayroll (PayrollYear, PayrollMonth, WorkedDays, GrossSalary, TotalAllowances, TotalAbsentdays, TotalDeductions, TotalOvertime, CashAdvance) "
StrSQL = StrSQL & " VALUES (" & StrYear & "," & StrMonth & ", " & Me.txtDayOfMonth & ", " & StrGrossSalary & ", " & StrTotalAllowances & ", " & StrTotalAbsentdays & ", " & StrTotalDeductions & ", " & StrTotalOvertime & ", " & StrCashAdvance & ") "
StrSQL = StrSQL & " WHERE EmployeeID = " & StrEmployeeID & " ;"

same problem sir.........

 

[Minty]

Sorry I missed out the INTO

StrSQL = "INSERT INTO tblPayroll (PayrollYear, PayrollMonth, WorkedDays, GrossSalary, TotalAllowances, TotalAbsentdays, TotalDeductions, TotalOvertime, CashAdvance) "
StrSQL = StrSQL & " VALUES (" & StrYear & "," & StrMonth & ", " & Me.txtDayOfMonth & ", " & StrGrossSalary & ", " & StrTotalAllowances & ", " & StrTotalAbsentdays & ", " & StrTotalDeductions & ", " & StrTotalOvertime & ", " & StrCashAdvance & ") "
StrSQL = StrSQL & " WHERE EmployeeID = " & StrEmployeeID & " ;"

 

[smtazulislam]

Sorry I missed out the INTO

StrSQL = "INSERT INTO tblPayroll (PayrollYear, PayrollMonth, WorkedDays, GrossSalary, TotalAllowances, TotalAbsentdays, TotalDeductions, TotalOvertime, CashAdvance) "
StrSQL = StrSQL & " VALUES (" & StrYear & "," & StrMonth & ", " & Me.txtDayOfMonth & ", " & StrGrossSalary & ", " & StrTotalAllowances & ", " & StrTotalAbsentdays & ", " & StrTotalDeductions & ", " & StrTotalOvertime & ", " & StrCashAdvance & ") "
StrSQL = StrSQL & " WHERE EmployeeID = " & StrEmployeeID & " ;"

Now, Run-time error : 3067

 

[smtazulislam]

can you check again my table. we need add PayRollID - PK or Not problem this field.

 

[Minty]

Okay, your problem is now PayrollYear and Month are text fields and you are trying to insert the values as numbers.

I still don't know why you didn't store a date and work out the Year and month - so much easier.
Your data won't sort correctly if you have text months without leading zeros.

 

[smtazulislam]

Okay, your problem is now PayrollYear and Month are text fields and you are trying to insert the values as numbers.

I still don't know why you didn't store a date and work out the Year and month - so much easier.
Your data won't sort correctly if you have text months without leading years.

I love it your valuable suggestion. If my problem is resolving to delete two fields "Month and Year" can I delete. But I have to change all of the 6 subforms and 3 mainforms.
before I edit I want to see a good demo

 

[smtazulislam]

Strongly, I wanting this system, my all of data also have with setup by "Year and Month"

 

[smtazulislam]

Okay, your problem is now PayrollYear and Month are text fields and you are trying to insert the values as numbers.

I still don't know why you didn't store a date and work out the Year and month - so much easier.
Your data won't sort correctly if you have text months without leading zeros.

can we try to 3 fields to add one field (eg. LIKE txtDays/cbomonth/cboYear) have any formula to add this type of field and it was give result 01/01/1900. data
Edit : I try it. not good result...Ha ha ha .
Result is attached...

 

[NauticalGent]

Excuse me for jumping back in, Minty has this well in hand, but you NEED to heed his advice and change your data type. You will still be able to separate the different date elements as you need to, but you are asking for a few major issues if you do not make the change.

 

[smtazulislam]

Excuse me for jumping back in, Minty has this well in hand, but you NEED to heed his advice and change your data type. You will still be able to separate the different date elements as you need to, but you are asking for a few major issues if you do not make the change.

I said strongly, I want my previous method, But it not able to resolving.
I have to thank you for also you to try it many time from past...
I have thank to @Minty he also try it. My proposal that if not able to resolving this issue, I should need to change my method.
But I dont know when I change this data type then my SEARCH form work or not. Because previous I have one thread for FILTER by "Year and Month" its still pending resolving, someone help me in others platform.
Now I can able to "Year and Monthly " Filter. SEE msg #33.
So if I change what happen I dont know.
Excuse me, long message.

 

[NauticalGent]

I understand your concerns and the amount of work it would take, but if you want an Access application that is efficient and one that you will be able to make changes to in the future, you need to take the time now and learn the basics: Table Structure and Normalization.

Your current DB has so many issues we have not been able to help you solve a single problem because of the cascading effect of your other issues. When the members here offer FREE advice, you should take it. It will only be a matter of time before they start to give up and you will find your questions will be ignored.

Trust me on this...

 

[MajP]

Not sure if this is of any interest, but when I have lots of fields, lots of datatypes, and possibilities of null values I find it a lot easier to do a parameterized insert. I use this functions. You pass in the field names and then all value. If not useful it is something to keep in your toolbox.

All the string and date delimeters '' ## can be a pain.

'----------------------------------------------Parameter Insert-----------------------------------------------
'**************************************************************************************************************
Public Function ParamInsert(TableName As String, TheFields As String, ParamArray TheValues() As Variant) As String
  Dim qdf As QueryDef
  Dim i As Integer
  Dim MyParams As New Collection
  Dim strMyParams As String
  Dim strSql As String
  If Left(TheFields, 1) <> "(" Then TheFields = "(" & TheFields & ")"
 
  For i = 0 To UBound(TheValues)
    MyParams.Add "Param" & i, "Param" & i
    If strMyParams = "" Then
      strMyParams = "[" & MyParams(i + 1) & "]"
    Else
      strMyParams = strMyParams & ", " & "[" & MyParams(i + 1) & "]"
    End If
  Next i
  strSql = "INSERT INTO " & TableName & " " & TheFields & " VALUES ( " & strMyParams & ")"
  ParamInsert = strSql
  Set qdf = CurrentDb.CreateQueryDef("TempQuery", strSql)
   For i = 0 To UBound(TheValues)
    qdf.Parameters(i) = TheValues(i)
  Next i
  qdf.Execute
  CurrentDb.QueryDefs.Delete ("tempquery")
End Function

SoDemo

Public Sub TestParamInsert()
  Dim TheFields As String
  Dim FirstName As Variant
  Dim LastName As Variant

  FirstName = Null
  LastName = "Smith"
  TheFields = "FirstName, LastName, OrderID, OrderDate"

  ParamInsert "MyTable", TheFields, FirstName, LastName, 1, Date

End Sub

 

[smtazulislam]

Thank you so much for your response. I really appreciated.
Sir, exactly I dont understand your demo even dont know, how to I use this demo.....

 

[MajP]

Thank you so much for your response. I really appreciated.
Sir, exactly I dont understand your demo even dont know, how to I use this demo.....

If you want to insert values into a table you need to pass in three things to the procedure
1. The table name, like "TableOne"
2. The Fields as a single string, like "FirstName, LastName, OrderID, ....."
3, Then your values as a parameter array separated by commas like Me.FirstName, Me.LastName, Me.OrderID or literals
"Pete", "Smith", 123

ParamInsert "TableOne","FirstName,LastName, OrderID", Me.FirstName, Me.LastName, Me.OrderID

 

[smtazulislam]

If you want to insert values into a table you need to pass in three things to the procedure
1. The table name, like "TableOne"
2. The Fields as a single string, like "FirstName, LastName, OrderID, ....."
3, Then your values as a parameter array separated by commas like Me.FirstName, Me.LastName, Me.OrderID or literals
"Pete", "Smith", 123

ParamInsert "TableOne","FirstName,LastName, OrderID", Me.FirstName, Me.LastName, Me.OrderID

thank you so much for your reply. I am Sorry for late reply.
I understand what you are explain the method of INSERT procedure. But I am try it many time. I can't able to completed this work.

As per your instruction I do it Please review previous message.
at last I try it by single date :

StrDate = Format(Me.txtGetDate, "dd/mm/yyyy")

but not complete.. its error.
this is my variable code :

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

Dim CrId           As Integer
Dim StrPayRollID   As Long
Dim StrEmployeeID  As Long
Dim StrYear        As Long
Dim StrMonth       As Long
Dim StrWorkedDays  As Long
Dim StrDate        As Date
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
StrPayRollID = [Forms]![frmPayrollMainForm]![SubfrmPayrollDisplay].[Form]![txtPayRollID]
StrEmployeeID = Me.txtEmployeeID
StrYear = Me.cboYear
StrMonth = Me.CboMonth
StrWorkedDays = Me.txtDayOfMonth
StrDate = Format(Me.txtGetDate, "dd/mm/yyyy")


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]

'Firstly I try it.
'By YEAR and MONTH 
strSql = "INSERT INTO tblPayroll (PayRollID, EmployeeID, PayrollYear, PayrollMonth, WorkedDays, GrossSalary, TotalAllowances, TotalAbsentdays, TotalDeductions, TotalOvertime, CashAdvance) "
strSql = strSql & " VALUES (" & StrPayRollID & ", " & StrEmployeeID & ", " & StrYear & ", " & StrMonth & ", " & StrWorkedDays & ", " & StrGrossSalary & ", " & StrTotalAllowances & ", " & StrTotalAbsentdays & ", " & StrTotalDeductions & ", " & StrTotalOvertime & ", " & StrCashAdvance & ") "
strSql = strSql & " WHERE EmployeeID = " & StrEmployeeID & " ;"

'Secondly 
'By Single Date.
strSql = "INSERT INTO tblPayroll (PayRollID, EmployeeID, DatePR, GrossSalary, TotalAllowances, TotalAbsentdays, TotalDeductions, TotalOvertime, CashAdvance) "
strSql = strSql & " VALUES (" & StrPayRollID & ", " & StrEmployeeID & ", " & StrDate & ", " & StrGrossSalary & ", " & StrTotalAllowances & ", " & StrTotalAbsentdays & ", " & StrTotalDeductions & ", " & StrTotalOvertime & ", " & StrCashAdvance & ") "
strSql = strSql & " WHERE EmployeeID = " & StrEmployeeID & " ;"

   Debug.Print strSql
   CurrentDb.Execute strSql

    MsgBox "Enter your data have been updated in the table ", vbInformation + vbOKOnly, "Update Info"

    DoCmd.GoToRecord , , acGoTo, CrId

End Sub

I also try it with Quote LIKE :

" & StrPayRollID & " and "StrPayRollID"

I need from you get me where I have mistake.... ?