Save Button

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • ivonsurf123
    New Member
    • Nov 2017
    • 27

    Save Button

    Hello,

    Can you please help me to figure why is giving me this error: Syntax Error(Missing Operator) in query expression '()'
    What I am trying to do is when user clicks Save, the system will firstly remove all bank holidays in the holiday Flatfile table (for selected country) and replace with what's in the list (selected country). Thank you.

    Code:
    Dim Response
        Dim rstFF As Recordset
        Dim rstAllocated As Recordset
        Dim rstPH As Recordset
        Dim rstEmployee As Recordset
        Dim rstAttend As Recordset
        
        Dim strFF As String
        Dim strAllocated As String
        Dim strPH As String
        Dim strEmployee As String
        Dim strAttend As String
        Dim sSearch As String
      
        
        
        If Me.cboCountry = "BE" Then
                
                'first remove all entries and update with defaults
                strFF = "Select * from tbl_HolidayTracker_Flatfile order by [AttendDate]"
                
                'Debug.Print strFF
                
                Set rstFF = CurrentDb.OpenRecordset(strFF, dbOpenDynaset, dbSeeChanges)
                If rstFF.RecordCount > 0 Then
                    rstFF.MoveLast
                    rstFF.MoveFirst
                    
                    While Not rstFF.EOF
                                                                
                        rstFF.Edit
                        
                        rstFF![Contr Hrs] = Format(rstEmployee![FTE % hours per day], "0.00")
                        rstFF![SDP Hrs] = Format(rstAllocated![Standard hours per day] * rstAllocated![SDP], "0.00")
                        rstFF![Other Hrs] = Format(rstAllocated![Standard hours per day] * rstAllocated![Other], "0.00")
                        rstFF![Hours] = Format(rstFF![SDP Hrs] + rstFF![Other Hrs], "0.00")
                        rstFF![Activity] = "A"
                        rstFF![%FTE] = rstEmployee![% FTE] * 100
                        
                        rstFF.Update
                        
                        rstFF.MoveNext
                    Wend
                    
                End If
    
                rstFF.Close
                Set rstFF = Nothing
                
                'Now update with new values
                strFF = "Select * from tbl_HolidayTracker_Flatfile where [Location]='" & Me.cboCountry.Column(1, Me.cboCountry.ListIndex) & "'"
                Set rstFF = CurrentDb.OpenRecordset(strFF, dbOpenDynaset, dbSeeChanges)
                If rstFF.RecordCount > 0 Then
                    rstFF.MoveLast
                    rstFF.MoveFirst
    
                    While Not rstFF.EOF
    
                        rstFF.Edit
    
                        rstFF![Contr Hrs] = Format(rstEmployee![FTE % hours per day], "0.00")
                        rstFF![SDP Hrs] = Format(rstAllocated![Standard hours per day] * rstAllocated![SDP], "0.00")
                        rstFF![Other Hrs] = Format(rstAllocated![Standard hours per day] * rstAllocated![Other], "0.00")
                        rstFF![%FTE] = rstEmployee![% FTE] * 100
    
    
                         If Me.cboCountry = "BE" Then
                                If InStr(1, Me.cboCountry, "A") = 1 Then
                                    rstFF![Hours] = Format(DLookup("[Hours]", "tbl_Register_Hours", "[HoursType]='" & Me.cboCountry & "' and [CountryCode]='" & rstEmployee!Location & "'"), "0.00")
                                    rstFF![Activity] = "A"
                                End If
    
                                rstAttend.AddNew
                                rstAttend!EmployeeID = rstEmployee!EmployeeID
                                rstAttend!Attend = Me.cboCountry
                                rstAttend![Date] = rstFF!AttendDate
                                rstAttend.Update
    
                        End If
    
                        rstFF.Update
    
                        rstFF.MoveNext
                    Wend
    
                End If
    
                rstFF.Close
                rstEmployee.Close
                rstAllocated.Close
                rstAttend.Close
                rstPH.Close
        End If
  • Seth Schrock
    Recognized Expert Specialist
    • Dec 2010
    • 2965

    #2
    On what line do you get this error?

    Comment

    • ivonsurf123
      New Member
      • Nov 2017
      • 27

      #3
      The error message is gone now what I am receiving on line 34 is "Object variable or With block not set.

      Comment

      • twinnyfo
        Recognized Expert Moderator Specialist
        • Nov 2011
        • 3653

        #4
        I don't see anywhere that you have set rstEmployee to anything. This will throw the described error.

        Comment

        • ivonsurf123
          New Member
          • Nov 2017
          • 27

          #5
          Hello twinnyfo

          I know it's missing as rstAllocated, I am trying to simplify this exercise, but it's getting a mess for me. Thank you.

          Comment

          • twinnyfo
            Recognized Expert Moderator Specialist
            • Nov 2011
            • 3653

            #6
            Neither have you set rstAllocated to anything. These will both cause such an error to pop up.

            Have you included the entire procedure? Or am I missing something?

            Comment

            Working...