Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unanswered: doesn't show the desire field

    I have 6 fields in my table: StartDate, EndDate, StartTime, EndTime, DateTimeStart and DateTimeEnd

    DateTimeStart is a field to hold the value of StartDate and StartTime after both fields (StartDate and StartTime) are filled.
    DateTimeEnd is a field to hold the value of EndDate and EndTime after both fields (StartDate and StartTime) are filled.


    Both StartDate and EndDate receive their value from the calendar the program provided.

    Both StartTime and EndTime are a combox. User pick the value from the table tblHours. The first record in tblhour is 12:00AM, the second record in tblhour is 12:10AM, the third record in tblhour is 12:20AM and so for.


    Say here are the parameters:

    StartDate: 12/8/2007
    EndDate: 12/9/2007
    StartTime: 12:00AM (In the tblhour table, it is the first record, 1)
    EndTime: 12:10AM (In the tblhour table, it is the second record, 2)

    ************************************************
    When user want to edit the StartTime after they fill out the form for whatever reason, I need to update the DateTimeStart and DateTimeEnd immediately and I use the AfterUpate on the StartTime field to accomplish it. Say the user change the StartTime to 12:10AM, I want to change the EndTime to match the StartTime and update both DateTimeStart and DateTimeEnd immediately. So for the above parameters, the
    DateTimeStart would read 12/8/2007 12:10AM
    DateTimeEnd would read 12/9/2007 12:10AM

    And it is what I want. Here is the code I used
    *****************************
    Private Sub ccfStartTime_AfterUpdate()

    'Everytime when Start time change, set the End Time to match the Start Time.
    ccfEndTime = Me.StartTime

    'Fill the DateTimeStart field immediately after the Start Time Change

    ccfDateTimeStart = Me.StartDate & " " & ccfStartTime.Text

    'Fill the DateTimeEnd field immediately after the Start Time Change

    ccfDateTimeEnd = Me.EndDate & " " & ccfStartTime.Text

    End Sub
    ************************************************** *
    My problem arise when people want to update the StartDate field after they filled the form.

    First, for whatever reason my AfterUpdate have absolutely NO effect on the field. This maybe caused by the calendar code or other unknown reason which I cannot understand. However, I find out that the GotFocus will work for me. Here is the code I use

    ************************************************** **
    Private Sub ccfStartDate_GotFocus()


    'Fill the DateTimeStart field immediately after the Start Date Change

    ccfDateTimeStart = Me.StartDate & " " & Me.StartTime

    End Sub

    *********************************************

    The above code work partially. Instead of getting

    DateTimeStart would read 12/8/2007 12:00AM

    I got

    DateTimeStart would read 12/8/2007 1


    See that "1" at the end is my 12:00AM First record in the tblhour.

    I try Me.StartTime.value, ccfStartTime.text, they don't work. ccfStartTime is the label of the control.

    Any help is greatly appreciated.
    Last edited by sweetmail; 10-19-07 at 14:07.

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    It looks like you are taking the value from the combo box that you have bound to the first column of the combo box. The second column of the combo box is where, I think, you have the actual time of 12:00 or 12:10, etc. So, rather than using the Me.StartTime.Value, you would need Me.StartTime.Columns(1). The columns are counted from zero "0", so in this case, .Columns(1) is actually the second column.

    Now, to your other problem that you did not ask about. You are keeping 6 fields in your table, when you really should only have two, DateTimeStart and DateTimeEnd. Whenever you need to edit these fields, you can place the date into the start or end date fields with this: Me.StartDate = Format(DateTimeStart, "mm/dd/yyyy") (of course you need the actual format of dates you use at your company). You can get the time the same way. This will work for a much cleaner database.

  3. #3
    Join Date
    Sep 2007
    Posts
    148
    Thank you Vic,

    I will work on it and let you know.

    Thanks

  4. #4
    Join Date
    Sep 2007
    Posts
    148
    Vic,

    Me.StartTime.Columns(1). works like magic.

    I am going to try your 2 fields suggestion. Thanks

  5. #5
    Join Date
    Sep 2007
    Posts
    148
    I was doing a lot testing on comparison statement. Unfortunately, I discover the following promblem.

    cboEndTime and cboStartTime are both combo box, they got the list of hours from the tblhours table. The first column in tblhours is the primary key autonumber and the second column is a Date/Time field holding the hours: 12:00AM, 12:10AM, 12:20AM, ......1:00AM, 1:10AM ........, 11:00PM, 11:10PM.......11:50PM

    The following is one of the comparison statements I have in my VBA code. I want it to be truth when Me.cboEndTime.Column(1) = 3:00AM < Me.cboStartTime.Column (1)= 2:00AM. It works 95% of the time except it thinks Me.cboEndTime.Column(1) = 1:00AM is an older time then Me.cboStartTime.Column(1) = 12:00AM. It also thinks that Me.cboEndTime.Column(1) = 1:00PM is an older time then Me.cboStartTime.Column(1) = 12:00AM.

    Me.cboEndTime.Column(1) < Me.cboStartTime.Column(1)

    **************************************************

    When I put a plause on the VBA code and run the form again, I check the value in Me.cboEndTime.Column(1) and Me.cboStartTime.Column(1), they both show the correct values but the result of the comparison is wrong.

    Any help is greatly appreciated it.

    Thanks
    Last edited by sweetmail; 10-21-07 at 00:24.

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    You need to be dealing with the Date/Time data type, but formatting the field to be the time format you want. The comparison works just fine when the data type of the fields are Date/Time. But you are currently dealing with a text field, so when you compare "1:00 AM" with "12:00AM", the "1:" comes before the "12". That is why you must use a Date/Time field for comparisons, but use the Format property or the Format() function when displaying the time.

  7. #7
    Join Date
    Sep 2007
    Posts
    148
    Vic,

    Thanks for the reply. I did try to change the StartTime and DateTime to be a Date/Time fields in my table. However, if I do that, I cannot have a combo box to get the hours from the table tblhours. I try to avoide to have user to put in the Time because any format I choose for the StartTime and EndTime field, it is quite difficult to put in the time right at the first time. I think I do need to set the input mask for the field right? I am going to work on it some more and maybe I do need to have user to manually put in the times so that my comparison statement would be right.

    I am setting up another database just to see how can I have only the DateTimeStart and DateTimeEnd field only for my table and still can use the calendar to input the date.

    Thanks for all your help.

  8. #8
    Join Date
    Sep 2007
    Posts
    148
    I now have only two fields in my table, DateTimeStart and DateTimeEnd.

    In my form I use

    1) 2 text box (unbound) for my Start Date and End Date input and they are getting their value from the pop up calendar
    2) 2 combo box (unbound) for my Start Time and End Time input. This box get the Time (hour:minute) from the table hour (tblhour)

    I am able to use the Start Date and Start Time to give me the DateTimeStart (Date/Time field) for my table. I am also able to use the End Date and End Time to give me the DateTimeEnd (Date/Time field).

    I see the correct input of the DateTimeStart and DateTimeEnd in my table and I am able to retrive the values in the form.

    When I open the form the next time, the 4 unbound field just show the default value of course. The ElapseTime is still correct from the last time because it get the value from the DateTimeStart and DateTimeEnd which are saved in the table.

    **********************************************
    My problem arise when I open the form and look at the old record, all the 4 unbound field are enabled. How can I disable those fields after the record is saved? I don't want people be able to change those fields as it will change the original DateTimeStart and DateTimeEnd field.

    I try to set the Locked to yes under the Data page of the property of the DateTimeStart field but it is no use. It doesn't stop people from editing the 4 unbound fields and change everything.

    *********************************
    Any help is greatly appreciated it.

    Thanks

  9. #9
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    When the form is ready for a new record to be input, the OnCurrent event can be used to test the DateTimeStart field to be Null, or zero. If this field is not Null or zero, then this is an existing record and you can disable the two date and two time input fields.

  10. #10
    Join Date
    Sep 2007
    Posts
    148
    Thank you much Vic, I will work on that tomorrow.

  11. #11
    Join Date
    Sep 2007
    Posts
    148
    I come up with the following code to disable 4 input unbound fields for any existing record

    ************************************************

    Private Sub Form_Current()

    'If both txtDateTimeStart and txtDateTimeEnd have value, it is an existing record,
    'disable these 4 fields = txtStartDate, txtEndDate, cboStartTime and cboEndTime

    If IsNull(Me.txtDateTimeStart) = False And IsNull(Me.txtDateTimeEnd) = False Then

    Me.txtStartDate.Enabled = IsNull(Me.txtDateTimeStart)
    Me.txtEndDate.Enabled = IsNull(Me.txtDateTimeEnd)
    Me.cboStartTime.Enabled = IsNull(Me.txtDateTimeStart)
    Me.cboEndTime.Enabled = IsNull(Me.txtDateTimeEnd)

    Else

    'If it is a new record, enable these 4 fields = txtStartDate, txtEndDate, cboStartTime and cboEndTime

    Me.txtStartDate.Enabled = True
    Me.txtEndDate.Enabled = True
    Me.cboStartTime.Enabled = True
    Me.cboEndTime.Enabled = True

    'Clear up all the following fields


    If IsNull(txtChangeDuration) = True Then

    Me.txtStartDate = ""
    Me.txtEndDate = ""
    Me.cboStartTime.RowSource = ""
    Me.cboEndTime.RowSource = ""
    Me.txtChangeDuration = ""

    End If

    Exit Sub
    End If


    End Sub

    *********************************************

    The code works wonder for existing record but when it comes to a new record, here are my problem in sequence

    1) the txtChangeDuration field show #Error (Note, the data control source=ElapsedTimeString(CDate([txtDateTimeStart]),CDate([txtDateTimeEnd]))

    2) When I click txtStartDate the FIRST time, a run time error show up. It said Run-time error '-2147352567 (80020009)'; The value you entered isn't valid for this field.
    I then check the debug, it goes to
    txtDateTimeStart = Me.txtStartDate & " " & Me.cboStartTime.Column(1)

    The above is where I store the actual date (txtDateTimeStart) for the table.

    Obviously, the following code is not working the way I want them to
    **********************************
    'Clear up all the following fields


    If IsNull(txtChangeDuration) = True Then

    Me.txtStartDate = ""
    Me.txtEndDate = ""
    Me.cboStartTime.RowSource = ""
    Me.cboEndTime.RowSource = ""
    Me.txtChangeDuration = ""

    End If
    ************************************************** ********
    3) Well, I then choose "End" the run-time error and click the txtStartDate again, all things are working fine from then on, almost!!!!!!

    4) However, the system seems to remember the txtStartDate, txtEndDate, cboStartTime and cboEndTime value in all future and existing record. Of courst the fields are disable in the existing record, it doesn't affect much but it would be nice to get rid of it. In all the future record, since there is a value in all these 4 fields, the system doesn't complain when I click the control the FIRST time in the new record. Is there a way to clear the unbound field for new record?

    Any advise is greatly appreciated it.

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sweetmail,

    To test for a new record
    if me.NewRecord = true then.....

    To test for a value in a field
    if len(me!txtStartDateTime) > 0 then.... <- if the length is greater than 0, there is a value. I like to use this verses the isnull statement.

    Hope that helps. If you upload an example, several of us can then give you some good feedback examples which may help.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Offhand without seeing the code in action, I would say...

    1) the txtChangeDuration field show #Error (Note, the data control source=ElapsedTimeString(CDate([txtDateTimeStart]),CDate([txtDateTimeEnd]))

    answer: are the txtDateTimeStart and txtDateTimeEnd fields populated with values when you go to a new record? You may be getting an error because there are no values in this. You may need to set the default value of these fields or you may need to set the value of this field in code somewhere verses in the controlsource of the field.

    2) When I click txtStartDate the FIRST time, a run time error show up. It said Run-time error '-2147352567 (80020009)'; The value you entered isn't valid for this field.
    I then check the debug, it goes to
    txtDateTimeStart = Me.txtStartDate & " " & Me.cboStartTime.Column(1)

    answer: Do you have a mask for this field? If you have a mask or the field is set for a date/time value, check to make sure you've encapsulated the value with #. Perhaps: txtDateTimeStart = "#" & me!txtStartDate & " " & me.cboStartTime.column(1) & "#" or something to that effect. Remember to use ! instead of .
    . refers to the field itself
    ! or me.somefield.value refers to the value in the field.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Sep 2007
    Posts
    148
    Thank you pkstormy,

    I will try you suggestion. Thanks

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    As a note, sometimes it's easier to do a simple msgbox txtDateTimeStart in your code and look at the result to see if it conforms to the input mask or what the field is formatted to accept.

    msgbox "Code here" or msgbox "Calling OnCurrent event here." or msgbox "My XXX value = " & MyXXXVariable is a great troubleshooting technique you can add in your code to see where your code is executing that statement and what the values being transposed are. You can utilize the debug method but I've often found it just as easy to put in a msgbox in the code.
    Last edited by pkstormy; 10-23-07 at 17:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •