Solved

CLear textboxes

Posted on 2004-04-15
31
464 Views
Last Modified: 2011-10-03
Hi
I closed this and did not mean to


Rockiroads what is you email address......

I need some help? I have a timesheet program that works fine except that I have a few text boxs that retain the pervious value?  What happens is that you input the employees timesheet info(works fine) but when you go back to look at an employee records it will retain values in some of the text boxes.  3 of the have a control source the other 4 are unbound and they are the ones that retain the pervious value.

What happens...

If I open the timesheet and have not entered any new timesheets and look at a pervious employee record the txtboxes at the bottom populate ok.  If I have entered a employee and entered data in the 4 unbound, 3 bound textboxes  and then go look at a pervious employee record the 3 bound textboxes populate with the correct data but the 4 unbound text boxes retain the values from the last entery into those text boxes.  They will populate with the correct data after you click the view button.  This is the last piece to fix on my program and I cannot figure out why it is doing this.

Hopefully I have explained this ok????

Thanks for the help

Denise
0
Comment
Question by:neicy62
  • 16
  • 12
  • +1
31 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 10834393
{email removed - ee_ai_construct, cs moderator}
I wondered why it was closed so soon
0
 
LVL 11

Expert Comment

by:phileoca
ID: 10834438
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] = ""
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10834449
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



0
 

Author Comment

by:neicy62
ID: 10835074
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
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10835772
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.
0
 

Author Comment

by:neicy62
ID: 10836017
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:)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10840337
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10840345
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


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10840390
to ee_ai_construct


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

Author Comment

by:neicy62
ID: 10842691
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10842714
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

0
 

Author Comment

by:neicy62
ID: 10842782
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:)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10842854
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

0
 

Author Comment

by:neicy62
ID: 10842958
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10843129
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?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:neicy62
ID: 10843207
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10843341
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
0
 

Author Comment

by:neicy62
ID: 10860118
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10860213
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!!!!!!
0
 

Author Comment

by:neicy62
ID: 10860235
Hi Rockiroads


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

Denise:)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10860296
hoping its not too big either, Ive got about 1.5M space left in my email account
0
 

Author Comment

by:neicy62
ID: 10868895
Hi Rockiroads

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

Denise
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10868958
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?

0
 

Author Comment

by:neicy62
ID: 10869147
Hi Rockiroads

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


Denise
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10870123
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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10870136
is it

txtOvertimehalf
txtOvertimedouble
txtSavedLieu
txtLieuTime

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10870184
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


0
 

Author Comment

by:neicy62
ID: 10870758
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:)
0
 

Author Comment

by:neicy62
ID: 10877756
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:)
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 10877851
no worries

caution: we have placed ClearControls in a lot of places, you need to double check each possible operation on that load is working properly, ensure it is not clearing controls when it shouldnt do. I dont think that will happen but u never know

glad to finally get it going

something to remember though, about unbound controls, you have to maintain it yourself
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now