CLear textboxes

{email removed - ee_ai_construct, cs moderator}
I wondered why it was closed so soon

when you go to change records you can update all of thoses text boxes to equal nothing

[Forms]![YourForm]![Textbox1] = ""
[Forms]![YourForm]![Textbox2] = ""
[Forms]![YourForm]![Textbox3] = ""
[Forms]![YourForm]![Textbox4] = ""

r u sending DB or code
if DB, I am using Access 2000,
If you do send DB, zip it up, I hope its small

I got to catch a train in about 25mins, so hopefully I get it by then
If I dont get it quick but by 25mins I will work on it on the train and post results to you later

Hi Here is all the code in the timesheet

Option Compare Database
Private Function CheckDate(emp_id As String, paystart As String, payend As String)

    Dim con As Object
    Dim rs As Object
    Dim stSql As String
   
    Set con = Application.CurrentProject.Connection
    stSql = "SELECT * FROM[Timeperiod]"
    stSql = stSql & " WHERE [id] =  '" & emp_id & " 'and [date_worked] >= #" & paystart & "#"
    stSql = stSql & " and [date_worked] <= #" & payend & "#;"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con, 1 ' 1 = adOpenKeyset
   
    'check wheather date has been choose before or not
    If (rs.EOF) Then
        CheckDate = "OK"
    Else
        CheckDate = "BAD"
    End If
   
    'Close the recordset
    rs.Close
    Set rs = Nothing
    Set con = Nothing
   
End Function
Private Function GetTimesheet()

    Dim payovertime
    Dim lieutime
    Dim overtimedouble
    Dim savedlieutime
    Dim overtimehalf


    payovertime = DLookup("payovertime", "qrytimesheetpay")
    lieutime = DLookup("lieutime", "qrytimesheetpay")
    overtimedouble = DLookup("overtimedouble", "qrytimesheetpay")
    savedlieutime = DLookup("savedlieutime", "qrytimesheetpay")
    overtimehalf = DLookup("overtimehalf", "qrytimesheetpay")
   
    If IsNull(payovertime) Then
        payovertime = 0
        overtimehalf = 0
        savedlieutime = 0
        overtimedouble = 0
        lieutime = 0
    End If
   
    Form_timesheet.txtPaidOvertime = CStr(payovertime)
    Form_timesheet.txtOvertimehalf = CStr(overtimehalf)
    Form_timesheet.txtSavedLieu = CStr(savedlieutime)
    Form_timesheet.txtOvertimedouble = CStr(overtimedouble)
    Form_timesheet.txtLieuTime = CStr(lieutime)





End Function

Private Sub DateOK_Click()

Dim payperiodstart
Dim payperiodend
Dim currentDate
Dim theSQL As String
Dim db As DAO.Database
Dim addDays As Integer
Dim currentEmp
Dim funreturn



'lookup your date range based on the listbox
Set db = CurrentDb
payperiodstart = DLookup("payperiodstart", "pay periods", "[pk] = " & Me.timeperiod)
txtstartperiod = payperiodstart
payperiodend = DLookup("payperiodEnd", "pay periods", "[pk] = " & Me.timeperiod)
txtendperiod = payperiodend
currentEmp = Me.ID
currentDate = payperiodstart

funreturn = CheckDate(CStr(currentEmp), CStr(payperiodstart), CStr(payperiodend))
MsgBox (funreturn)

If funreturn = "OK" Then

    Do Until currentDate > payperiodend
    'insert into the timeperiod table
    theSQL = "INSERT INTO Timeperiod (ID, Date_Worked ) " & _
    "SELECT " & currentEmp & " as employee, #" & currentDate & "# AS newDate"
    currentDate = DateAdd("d", 1, currentDate)
    db.Execute theSQL
    Loop
End If


Me.RecordSource = "filtertimeperiod query"
Me.Refresh
Set db = Nothing
Me.RecordSource = "timesheet"
Me.Refresh
DoCmd.GoToRecord , , acLast
Set db = Nothing

End Sub

Private Sub cmdSwitchboard_Click()
On Error GoTo Err_cmdSwitchboard_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Switchboard"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdSwitchboard_Click:
    Exit Sub

Err_cmdSwitchboard_Click:
    MsgBox Err.Description
    Resume Exit_cmdSwitchboard_Click
   
End Sub


Private Sub Form_Load()

    txtOvertimehalf = ""
    txtOvertimedouble = ""
    txtLieuTime = ""
    txtSavedLieu = ""
    txtPaidOvertime = ""
    DoCmd.GoToRecord , , acLast
   
   
End Sub

Private Sub NextEmployee_Click()
On Error GoTo Err_NextEmployee_Click

    txtOvertimehalf = ""
    txtOvertimedouble = ""
    txtLieuTime = ""
    txtSavedLieu = ""
    txtPaidOvertime = ""
    DoCmd.GoToRecord , , acNext

Exit_NextEmployee_Click:
    Exit Sub

Err_NextEmployee_Click:
    MsgBox Err.Description
    Resume Exit_NextEmployee_Click
   
End Sub

Private Sub cmdViewTimesheet_Click()

Dim payperiodstart
Dim payperiodend
Dim theSQL As String
Dim db As DAO.Database


    Dim payovertime
    Dim lieutime
    Dim overtimedouble
    Dim savedlieutime
    Dim overtimehalf
    Dim funreturn
 
 
       
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    payperiodstart = DLookup("payperiodstart", "pay periods", "[pk] = " & Me.timeperiod)
    txtstartperiod = payperiodstart
    payperiodend = DLookup("payperiodEnd", "pay periods", "[pk] = " & Me.timeperiod)
    txtendperiod = payperiodend
 
   
       
Me.RecordSource = "filtertimeperiod query"
Me.Refresh
Set db = Nothing
Me.RecordSource = "timesheet"
Me.Refresh
DoCmd.GoToRecord , , acLast
Set db = Nothing

 TotalReg.SetFocus

If IsEmpty(TotalReg.Text) = False Then

    funreturn = GetTimesheet()
End If

    On Error GoTo Err_cmdViewTimesheet_Click

Exit_cmdViewTimesheet_Click:
    Exit Sub

Err_cmdViewTimesheet_Click:
    MsgBox Err.Description
    Resume Exit_cmdViewTimesheet_Click
   
 
   

End Sub
Private Sub Save_Employee_Record_Click()


Dim theSQL As String
Dim db As DAO.Database


On Error GoTo Err_Save_Employee_Record_Click

    'MsgBox ("Hello")
   
    Set db = CurrentDb
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    'Update into the timeperiod table
    theSQL = "UPDATE Timesheet SET [Total Regular Hours Worked] =" & TotalReg & _
    " , [Total Hours Shift Premium] =" & Nz(TotalShiftPrem, 0) & _
    " , [Total Calculated Hours] = " & Nz(totalovertime, 0) & _
    " , [PayOvertime] = '" & Nz(txtPaidOvertime, 0) & "'" & _
    " , [SavedLieutime] = '" & Nz(txtSavedLieu, 0) & "'" & _
    " , [Overtimehalf] = " & Nz(txtOvertimehalf, 0) & _
    " , [Overtimedouble] = " & Nz(txtOvertimedouble, 0) & _
    " , [LieuTime] = " & Nz(txtLieuTime, 0) & _
    " Where id ='" & Me.ID & "'"
    db.Execute theSQL
   
    Set db = Nothing

   
Exit_Save_Employee_Record_Click:
    Exit Sub

Err_Save_Employee_Record_Click:
    MsgBox Err.Description
    Resume Exit_Save_Employee_Record_Click
   
End Sub


What I have is a sub-form inside a form.  The 3 bound and 4 unbound text boxes are on the main form.  Like I said it all works expcet when you go to look at an employees record the 4 unbound text boxes do not clear out. They only put the correct information in when you click on the view button.


Thanks

Dim payovertime
    Dim lieutime
    Dim overtimedouble
    Dim savedlieutime
    Dim overtimehalf


    payovertime = DLookup("payovertime", "qrytimesheetpay")
    lieutime = DLookup("lieutime", "qrytimesheetpay")
    overtimedouble = DLookup("overtimedouble", "qrytimesheetpay")
    savedlieutime = DLookup("savedlieutime", "qrytimesheetpay")
    overtimehalf = DLookup("overtimehalf", "qrytimesheetpay")
   
    If IsNull(payovertime) Then
        payovertime = 0
        overtimehalf = 0
        savedlieutime = 0
        overtimedouble = 0
        lieutime = 0
    End If
   
    Form_timesheet.txtPaidOvertime = CStr(payovertime)
    Form_timesheet.txtOvertimehalf = CStr(overtimehalf)
    Form_timesheet.txtSavedLieu = CStr(savedlieutime)
    Form_timesheet.txtOvertimedouble = CStr(overtimedouble)
    Form_timesheet.txtLieuTime = CStr(lieutime)

<------------------->
The above section can be shortened to
<------------------->

    If IsNull(DLookup("payovertime", "qrytimesheetpay")) Then
      Form_timesheet.txtPaidOvertime = 0
      Form_timesheet.txtOvertimehalf = 0
      Form_timesheet.txtSavedLieu = 0
      Form_timesheet.txtOvertimedouble = 0
      Form_timesheet.txtLieuTime = 0
    else
      Form_timesheet.txtPaidOvertime = nz(DLookup("payovertime", "qrytimesheetpay"),0)
      Form_timesheet.txtOvertimehalf = nz(DLookup("overtimehalf", "qrytimesheetpay"),0)
      Form_timesheet.txtSavedLieu = nz(DLookup("savedlieutime", "qrytimesheetpay"),0)
      Form_timesheet.txtOvertimedouble = nz(DLookup("overtimedouble", "qrytimesheetpay"),0)
      Form_timesheet.txtLieuTime = nz(DLookup("lieutime", "qrytimesheetpay"),0)
    End If
<------------------->

all variables can be removed
the unbound fields can be used in place of the variables.
if you keep the var payovertime, the dlookup for it can run just once also.
the dlookups are only run if needed as indicated by the first,...
  If IsNull(DLookup("payovertime", "qrytimesheetpay")) Then

You can use the FormCurrent to update the status of these unbound text boxes to make them correct for conditions.

How would you describe the trigger mechanism that turns the controls "on and off" as it were?  We can include that in the updated code.

HI

What happens is that if I enter employee timesheet and employee xyz calls and you have to look at his timesheet by going to the id combo box and finding his id #, name click on it his timesheet comes up the 3 bound text boxes(they have the control source fill in)they come up with the correct infor in them but the 4 unbound text boxes they are the ones that are coming up with the previous data that is in them until you click the view button than all is well data is correct in all text boxes.  This is the mystery could it be the code could it be the query I am totally lost on how to fix problem!!!

Denise:)

Ok, lets start by doing this

its clear to me that u need to clear these controls in more than one place so rather than doing them all over the shop,
create a procedure on your main form which clears these unbounded controls
Easier for maintenance as well, imagine if you created more unbounded controls, to clear its easy just to add the one line in this new procedure

You can also use the procedure to perform other intialisations as well

private sub ClearControls()

    txtOvertimehalf.Value = ""
    txtOvertimedouble.Value = ""
    txtLieuTime.Value = ""
    txtSavedLieu.Value = ""
    txtPaidOvertime.Value = ""

end sub




Now in the code, you need to call ClearControls

e.g.

in the procedure cmdViewTimesheet_Click

call this (can add it before this line -     DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70)

    ClearControls
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
   
   
Now, everytime you click view to view a timesheet, not only does it load details but will clear unbounded controls first



In DateOK_Click, you seem to be resetting the form's rowsource so you need to make a call there as well, again put that at the start


In the Form_Load, you can remove the blanking of the unbounded controls, just call ClearControls instead


Same applies to extEmployee_Click, call ClearControls instead of clearing individual controls    




Basically what u need to look at, is if you have any code which resets the form filter like in ViewTimesheet_Click and DateOK,
you need to clear the boxes, so just call ClearControls

On any type of initialisation, just call ClearControls

here is some sample code, see if this makes sense to you



Option Compare Database
Option Explicit


'XXX - general tip, prefix your variable names (dont need to do this now, for next time u do anything)
'e.g.  txtAAA refers to text control or sAAA refers to string
'e.g.  lngAAA refers to long or lAAA or iAAA (integer) or dAAA (double)


'XXX - tip instead of returning OK or BAD, you could set the return type to boolean and return True or False instead (not necessary though)
Private Function CheckDate(emp_id As String, paystart As String, payend As String)

    Dim con As Object
    Dim rs As Object
    Dim stSql As String
   
'XXX - put a error handler here
    On Error GoTo Err_CheckDate
   
    Set con = Application.CurrentProject.Connection
    stSql = "SELECT * FROM[Timeperiod]"
    stSql = stSql & " WHERE [id] =  '" & emp_id & " 'and [date_worked] >= #" & paystart & "#"
    stSql = stSql & " and [date_worked] <= #" & payend & "#;"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con, 1 ' 1 = adOpenKeyset
   
    'check wheather date has been choose before or not
    If (rs.EOF) Then
        CheckDate = "OK"
    Else
        CheckDate = "BAD"
    End If
   
    'Close the recordset

Exit_CheckDate:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set con = Nothing
    Exit Function

Err_CheckDate:
    MsgBox Err.Description
    Resume Exit_CheckDate

End Function

Private Function GetTimesheet()

    Dim payovertime
    Dim lieutime
    Dim overtimedouble
    Dim savedlieutime
    Dim overtimehalf
   
   
   
    ClearControls
   
    payovertime = DLookup("payovertime", "qrytimesheetpay")
    lieutime = DLookup("lieutime", "qrytimesheetpay")
    overtimedouble = DLookup("overtimedouble", "qrytimesheetpay")
    savedlieutime = DLookup("savedlieutime", "qrytimesheetpay")
    overtimehalf = DLookup("overtimehalf", "qrytimesheetpay")
   
    If IsNull(payovertime) Then
        payovertime = 0
        overtimehalf = 0
        savedlieutime = 0
        overtimedouble = 0
        lieutime = 0
    End If
   
   
'XXXX - Recommendation - replace above code with this, Reason? saves doing unnecessary calls
    payovertime = DLookup("payovertime", "qrytimesheetpay")
    If IsNull(payovertime) Then
        payovertime = 0
        overtimehalf = 0
        savedlieutime = 0
        overtimedouble = 0
        lieutime = 0
    Else
        lieutime = DLookup("lieutime", "qrytimesheetpay")
        overtimedouble = DLookup("overtimedouble", "qrytimesheetpay")
        savedlieutime = DLookup("savedlieutime", "qrytimesheetpay")
        overtimehalf = DLookup("overtimehalf", "qrytimesheetpay")
    End If
   
   
    Form_timesheet.txtPaidOvertime = CStr(payovertime)
    Form_timesheet.txtOvertimehalf = CStr(overtimehalf)
    Form_timesheet.txtSavedLieu = CStr(savedlieutime)
    Form_timesheet.txtOvertimedouble = CStr(overtimedouble)
    Form_timesheet.txtLieuTime = CStr(lieutime)

'XXXX - Is Form_timesheet the name of the main form?
        'In that case you case use the keyword Me e.g.
        'This saves explicity naming the form (easier if you ever decide to move or change form name)
    Me.txtPaidOvertime = CStr(payovertime)
       
End Function


Private Sub DateOK_Click()

    Dim payperiodstart
    Dim payperiodend
    Dim currentDate
    Dim theSQL As String
    Dim db As DAO.Database
    Dim addDays As Integer
    Dim currentEmp
    Dim funreturn
   
   
   
    ClearControls
   
    'lookup your date range based on the listbox
    Set db = CurrentDb
    payperiodstart = DLookup("payperiodstart", "pay periods", "[pk] = " & Me.timeperiod)
    txtstartperiod = payperiodstart
    payperiodend = DLookup("payperiodEnd", "pay periods", "[pk] = " & Me.timeperiod)
    txtendperiod = payperiodend
    currentEmp = Me.ID
    currentDate = payperiodstart
   
    funreturn = CheckDate(CStr(currentEmp), CStr(payperiodstart), CStr(payperiodend))
    MsgBox (funreturn)
   
    If funreturn = "OK" Then
   
        Do Until currentDate > payperiodend
            'insert into the timeperiod table
            theSQL = "INSERT INTO Timeperiod (ID, Date_Worked ) " & _
            "SELECT " & currentEmp & " as employee, #" & currentDate & "# AS newDate"
            currentDate = DateAdd("d", 1, currentDate)
            db.Execute theSQL
        Loop
    End If
   
   
    Me.RecordSource = "filtertimeperiod query"
    Me.Refresh
    Set db = Nothing
    Me.RecordSource = "timesheet"
    Me.Refresh
    DoCmd.GoToRecord , , acLast
    Set db = Nothing

End Sub

Private Sub cmdSwitchboard_Click()
   
    On Error GoTo Err_cmdSwitchboard_Click
   
    Dim stDocName As String
    Dim stLinkCriteria As String
   
    stDocName = "Switchboard"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
   
Exit_cmdSwitchboard_Click:
    Exit Sub
   
Err_cmdSwitchboard_Click:
    MsgBox Err.Description
    Resume Exit_cmdSwitchboard_Click

End Sub


Private Sub Form_Load()

    ClearControls
    DoCmd.GoToRecord , , acLast

End Sub

Private Sub NextEmployee_Click()

    On Error GoTo Err_NextEmployee_Click
   
   
    ClearControls
    DoCmd.GoToRecord , , acNext

Exit_NextEmployee_Click:
    Exit Sub

Err_NextEmployee_Click:
    MsgBox Err.Description
    Resume Exit_NextEmployee_Click

End Sub

Private Sub cmdViewTimesheet_Click()

    Dim payperiodstart
    Dim payperiodend
    Dim theSQL As String
    Dim db As DAO.Database
   
    Dim payovertime
    Dim lieutime
    Dim overtimedouble
    Dim savedlieutime
    Dim overtimehalf
    Dim funreturn
   
   
   
    ClearControls
   
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    payperiodstart = DLookup("payperiodstart", "pay periods", "[pk] = " & Me.timeperiod)
    txtstartperiod = payperiodstart
    payperiodend = DLookup("payperiodEnd", "pay periods", "[pk] = " & Me.timeperiod)
    txtendperiod = payperiodend
   
    Me.RecordSource = "filtertimeperiod query"
    Me.Refresh
    Set db = Nothing
    Me.RecordSource = "timesheet"
    Me.Refresh
    DoCmd.GoToRecord , , acLast
    Set db = Nothing
   
    TotalReg.SetFocus
   
    If IsEmpty(TotalReg.Text) = False Then
        funreturn = GetTimesheet()
    End If
   
'XXX - tip for one line if statements, you can also do this
    If IsEmpty(TotalReg.Text) = False Then funreturn = GetTimesheet()
   
   
    On Error GoTo Err_cmdViewTimesheet_Click
   
Exit_cmdViewTimesheet_Click:
    Exit Sub
   
Err_cmdViewTimesheet_Click:
    MsgBox Err.Description
    Resume Exit_cmdViewTimesheet_Click
End Sub


Private Sub Save_Employee_Record_Click()


    Dim theSQL As String
    Dim db As DAO.Database
   
   
    On Error GoTo Err_Save_Employee_Record_Click
   
    'MsgBox ("Hello")
   
    Set db = CurrentDb
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    'Update into the timeperiod table
    theSQL = "UPDATE Timesheet SET [Total Regular Hours Worked] =" & TotalReg & _
    " , [Total Hours Shift Premium] =" & Nz(TotalShiftPrem, 0) & _
    " , [Total Calculated Hours] = " & Nz(totalovertime, 0) & _
    " , [PayOvertime] = '" & Nz(txtPaidOvertime, 0) & "'" & _
    " , [SavedLieutime] = '" & Nz(txtSavedLieu, 0) & "'" & _
    " , [Overtimehalf] = " & Nz(txtOvertimehalf, 0) & _
    " , [Overtimedouble] = " & Nz(txtOvertimedouble, 0) & _
    " , [LieuTime] = " & Nz(txtLieuTime, 0) & _
    " Where id ='" & Me.ID & "'"
    db.Execute theSQL
   
    Set db = Nothing
   
   
Exit_Save_Employee_Record_Click:
    Exit Sub
   
Err_Save_Employee_Record_Click:
    MsgBox Err.Description
    Resume Exit_Save_Employee_Record_Click

End Sub


'Denise, please double check this list of controls are they the ones to clear?
Private Sub ClearControls()
    txtOvertimehalf = ""
    txtOvertimedouble = ""
    txtLieuTime = ""
    txtSavedLieu = ""
    txtPaidOvertime = ""
End Sub

to ee_ai_construct


noted, I shall not put my email address in my posts in the future

Hi Rookiroad

I will try this is does make sense I will let you know how I make out.

Thanks for all the help

Denise

no worries, Im here for a couple for hours before I have to leave to catch my train
hopefully we get this resolved by then

Hi RookiRoads

I have tried what you have said and it works the same.  Could it be maybe my querys that are not working right.  I mean what happens is employees been entered I have looked at employee 123 there data is fine.  Now I go click on the id box for emp 456 and the timesheet(the subform) comes up but it has changed my 3 bound txtboxes to the correct data but the four unbound retains that data form employee 123 until I click the view button.  Those 4 bound text boxes still do not clear out I am lost.

Thanks Denise:)

when you click on the box, you make calls to load the timesheet right? can you not put ClearControls in there?

does ClearControls function list the 4 unbound controls? pls check that the right controls are being cleared

I am assuming all this happens on the main form and not the subform

Hi Rockiroads

This is code in switchboard

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.

    Dim dbs As Database
    Dim rst As Recordset

       
    ' Minimize the database window.
    DoCmd.SelectObject acForm, "Switchboard", True
    DoCmd.Minimize
       
       
       
    ' Move to the switchboard page that is marked as the default.
    Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
    Me.FilterOn = True
   


   
End Sub

Private Sub Form_Current()
' Update the caption and fill in the list of options.

    Me.Caption = Nz(Me![ItemText], "")
    FillOptions
   
End Sub

Private Sub FillOptions()
' Fill in the options for this switchboard page.

    ' The number of buttons on the form.
    Const conNumButtons = 8
   
    Dim con As Object
    Dim rs As Object
    Dim stSql As String
    Dim intOption As Integer
   
    ' Set the focus to the first button on the form,
    ' and then hide all of the buttons on the form
    ' but the first.  You can't hide the field with the focus.
    Me![Option1].SetFocus
    For intOption = 2 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("OptionLabel" & intOption).Visible = False
    Next intOption
   
    ' Open the table of Switchboard Items, and find
    ' the first item for this Switchboard Page.
    Set con = Application.CurrentProject.Connection
    stSql = "SELECT * FROM [Switchboard Items]"
    stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
    stSql = stSql & " ORDER BY [ItemNumber];"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con, 1   ' 1 = adOpenKeyset
   
    ' If there are no options for this Switchboard Page,
    ' display a message.  Otherwise, fill the page with the items.
    If (rs.EOF) Then
        Me![OptionLabel1].Caption = "There are no items for this switchboard page"
    Else
        While (Not (rs.EOF))
            Me("Option" & rs![ItemNumber]).Visible = True
            Me("OptionLabel" & rs![ItemNumber]).Visible = True
            Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
            rs.MoveNext
        Wend
    End If

    ' Close the recordset and the database.
    rs.Close
    Set rs = Nothing
    Set con = Nothing

End Sub

Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.

    ' Constants for the commands that can be executed.
    Const conCmdGotoSwitchboard = 1
    Const conCmdOpenFormAdd = 2
    Const conCmdOpenFormBrowse = 3
    Const conCmdOpenReport = 4
    Const conCmdCustomizeSwitchboard = 5
    Const conCmdExitApplication = 6
    Const conCmdRunMacro = 7
    Const conCmdRunCode = 8
    Const conCmdOpenPage = 9

    ' An error that is special cased.
    Const conErrDoCmdCancelled = 2501
   
    Dim con As Object
    Dim rs As Object
    Dim stSql As String

On Error GoTo HandleButtonClick_Err

    ' Find the item in the Switchboard Items table
    ' that corresponds to the button that was clicked.
    Set con = Application.CurrentProject.Connection
    Set rs = CreateObject("ADODB.Recordset")
    stSql = "SELECT * FROM [Switchboard Items] "
    stSql = stSql & "WHERE [SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
    rs.Open stSql, con, 1    ' 1 = adOpenKeyset
   
    ' If no item matches, report the error and exit the function.
    If (rs.EOF) Then
        MsgBox "There was an error reading the Switchboard Items table."
        rs.Close
        Set rs = Nothing
        Set con = Nothing
        Exit Function
    End If
   
    Select Case rs![Command]
       
        ' Go to another switchboard.
        Case conCmdGotoSwitchboard
            Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs![Argument]
           
        ' Open a form in Add mode.
        Case conCmdOpenFormAdd
            DoCmd.OpenForm rs![Argument], , , , acAdd
                       

        ' Open a form.
         Case conCmdOpenFormBrowse
            DoCmd.OpenForm rs![Argument]
           

        ' Open a report.
        Case conCmdOpenReport
            DoCmd.OpenReport rs![Argument], acPreview

        ' Customize the Switchboard.
        Case conCmdCustomizeSwitchboard
            ' Handle the case where the Switchboard Manager
            ' is not installed (e.g. Minimal Install).
            On Error Resume Next
            Application.Run "ACWZMAIN.sbm_Entry"
            If (Err <> 0) Then MsgBox "Command not available."
            On Error GoTo 0
            ' Update the form.
            Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
            Me.Caption = Nz(Me![ItemText], "")
            FillOptions

        ' Exit the application.
        Case conCmdExitApplication
            CloseCurrentDatabase

        ' Run a macro.
        Case conCmdRunMacro
            DoCmd.RunMacro rs![Argument]

        ' Run code.
        Case conCmdRunCode
            Application.Run rs![Argument]

        ' Open a Data Access Page
        Case conCmdOpenPage
            DoCmd.OpenDataAccessPage rs![Argument]

        ' Any other command is unrecognized.
        Case Else
            MsgBox "Unknown option."
   
    End Select

    ' Close the recordset and the database.
    rs.Close
   
HandleButtonClick_Exit:
On Error Resume Next
    Set rs = Nothing
    Set con = Nothing
    Exit Function

HandleButtonClick_Err:
    ' If the action was cancelled by the user for
    ' some reason, don't display an error message.
    ' Instead, resume on the next line.
    If (Err = conErrDoCmdCancelled) Then
        Resume Next
    Else
        MsgBox "There was an error executing the command.", vbCritical
        Resume HandleButtonClick_Exit
    End If
   
End Function

------------------this is code in subform

Private Sub Form_Current()

    If DCount("[ID]", "timeperiod", "[ID]" = "' & Me!id'") = 14 Then
        Me.AllowAdditions = False
        MsgBox "You have reached the maxium records for this employee for this payperiod"
    Else
        Me.AllowAdditions = True
    End If

End Sub


This is all the code I have.  So should I try putting it in with my switch board code with the form open???

Denise

you should place it in all conditions where the record changes

is switchboard your mainform? with a subform?

which is the main form for viewing the timesheets?

you can put it in its Form_load there, do you have navigation code? go back,forwards
a search button, you need to add ClearControls in there


were the control names in CLearControls correct?

Hi Rockiroads

Yes the control names where correct.  The database open with a switchboard the user clicks on the open employee timesheet, when this opens this is the form with a sub-form, the main form has id combo box, timeperiod, 4 buttons and the text boxs at the bottom.  The subfrom is where the user will enter the employee's timesheet information
example time in,timeout, total regualr hours, total overtime that type of thing, get the idea.  I will see what I can do and let you know

Denise

ok, switchboard loads up the timesheet form

on that timesheet form, (main form) exists the unbounded controls

in that code, just try add it to all functions which change the record (load, navigation etc)

put in in the Form_Load and Form_Current functions as well

Hi RockiRoads

Have zipped up database and sent to you could you please take a look at it for me.  Just a quick recap there are 2 employees in there that have data 15314 and 11592 if you load up say 15314 all data ok but when you choose the second employee 11592 it does not clear out the data in unbound textboxes.

Thanks for the help

ok, what version Access u using, I am 2000, if you are post 2000, you need to ensure you have converted it to 2000

I'll be monitoring my email!!!!!!

Hi Rockiroads


I have access 2000 and sent a new copy because I sent old copy without changes.

Denise:)

hoping its not too big either, Ive got about 1.5M space left in my email account

Hi Rockiroads

Wondering if you had a chance to look at my database that I sent.

Denise

Hi Denise, I have not received anything yet,

just send me a email without any attachments to confirm that I can receive from you

How big is the DB after zipped?

Hi Rockiroads

Just sent email after being zipped the DB is 750kb is that still to big.  I never got email returned


Denise

Right Im on the case

Got to catch my train in 20mins, try do it by then, else will complete on the train, post solution later this evening or tomorrow morning first thing

just to confirm, what are the names of the four unbound textboxes

is it

txtOvertimehalf
txtOvertimedouble
txtSavedLieu
txtLieuTime

add a AfterUpdate function to the id combobox
and put ClearControls in there

everytime you select a new employee, it should clear the textboxes


e.g.

Private Sub ID_AfterUpdate()
    ClearControls
End Sub

Hi RockiRoads

Thats the name of the txtboxs there are 5 the one you forgot was txtpaidovertime

I will try your suggestion and let you know

Thanks

Denise:)

Hi RockiRoads,

It apprears to be working.  I have tested and it seems to be working.  
Thanks for all your help you have been fantastic!!!

Denise:)