Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55

    Unanswered: MS Access VB Form_Load and test checkbox

    Hello, I have very little progamming experience,(Please be gentle). I am trying toenter todays date into a textbox, if a checkbox is true, whenever the Form is loaded or at the event of the checkbox getting checked. I am using Form_Load but it only updates the first record in the data base(Grrr), the other records do not update.
    I am pulling what is left of my hair out. Any help would be appreciated.

    Here is my code

    Private Sub Form_load()
    If Me.Check101 = True Then
    Me.UPDATE = Date
    End If
    End Sub

    Since I am not a 'real' programmer, if you have a suggestion, please display the code... thank

  2. #2
    Join Date
    Aug 2006
    Posts
    21
    Private Sub Form_AfterUpdate()
    If Me.Check101 = True Then
    Me.UPDATE = Date
    End If
    End Sub


    Private Sub Form_Current()
    If Me.Check101 = True Then
    Me.UPDATE = Date
    End If
    End Sub

    That should do it

  3. #3
    Join Date
    Aug 2006
    Posts
    21
    However as you did not change the Me.Check101 to False after entering the date. So you need to update that field by me.check101 = false after you entered the date else it will change each time as it is still True

  4. #4
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Thanks totally-pbm, I appreciate your help.

    However I have a problem, I entered your suggestions and Access started acting irratically... ie, would not let me swing back and forth between the design and view mode. And in the view mode, it would not respond...
    I commented out one routine and then that appeared to allow entering between the two modes. It appears the routines are in a loop of some sort..

    I did get some results but because of the 'loop' the results are probably not valid for analyzing the code.

    Question in you 2nd statement.... you said I need to update that field by me.check101=false... where would that go? Could you give me the chunk of code for that?

    I thank you very much for your attention and especially your patience for Oldie Newbie....

  5. #5
    Join Date
    Aug 2006
    Posts
    21
    Change to the following:

    Private Sub Form_Current()
    If Me.Check101 = True Then
    Me.UPDATE = Date
    me.check101 = false
    End If
    End Sub

    What this does is each time you move to a record it will check to see if the Check101 is true and then add the date.

    I added this one as a primary check.

    The other one should change to this:

    Private Sub Form_AfterUpdate()
    If Me.Check101 = True Then
    Me.UPDATE = Date
    me.check101 = false
    End If
    End Sub

    I mentioned that in my later email, sorry should have been more precise.

  6. #6
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Hi totally-pbm.

    BTW Your last code, un-looped, now Access operates. However
    I think I may have mis lead.... please let me state my need again.

    There is a text field (Me.UPDATE) that is used for manually entering a date.
    There is also a check box (Me.Check101) that ONLY the user controls.
    When it is checked, the date box should immediatly and subsequently always display the current (today's) date, even if the box was checked 3 days ago the box should display todays date. ONLY the user can check or un-check the box.

    I have two routines that almost work:...
    #1
    Private Sub AfterUpdate_load() 'when the box is checked, this sets the date
    If Me.Check101 = True Then 'problem is, tomorrow, the box will have yesterdays date unless I un-check/check the box
    Me.UPDATE = Date
    End If
    End Sub
    #2
    Private Sub Form_load() 'this almost works but on opening the form, it only updates the first record. The other checked records do not get updated.
    If Me.Check101 = True Then
    Me.UPDATE = Date
    End If
    End Sub

    When I replace Form_Load with (your Current_Load) it works the same except then I un-check, it updates the UPDATE to todays date.. (odd)

    I removed the Me.Check101_False becaue I do not want the boxes changed excepty by the user.
    Hope this is clear, thanks again.

  7. #7
    Join Date
    Aug 2006
    Posts
    21
    OK I understand, you will need to do some SQL run by entering the form:

    Something like this:

    Private Sub Form_load()
    Dim TodayDate as date

    Todaydate = Now

    docmd.setwarnings false
    docmd.runsql ("UPDATE [Table_Name] Set [Update] = #" & TodayDate & "# WHERE [Check101] = True;"
    docmd.setwarnings True

    end sub

    That will update all of the items that are ticjked to todays date and leave it unticked.

    I hope that this gives you waht you want.

    David

  8. #8
    Join Date
    Aug 2006
    Posts
    21
    Sorry typo, it will leave it ticked

  9. #9
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Thanks for your deep knowledge.

    It appears to almost worked now,
    I removed an extra space after #" and I added my tbl_name and I changed TodayDate to Date (TodayDate displays Date and Time).

    This SQL changed ALL dates to today's date, not only the 'ticked' ones. It is like it is ignoring the ticks. The event of changing the ticks does not seem to effect it the date.

    Any thoughts?

    Thanks again(you must be getting tired of responding but it is greatly appreciated.)
    Floyd

  10. #10
    Join Date
    Aug 2006
    Posts
    21
    You need to leave the space in before the WHERE, and if that does not work then change TRUE to -1 and try that.

    Post the code you are using please.

  11. #11
    Join Date
    Aug 2006
    Posts
    21
    Its definately the issue of not leaving a space before WHERE

  12. #12
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    I removed the space between #" and the & (there were 2spaces there and the code would not run). I did not delete the space before the WHERE.

    changing TRUE to -1 does not change anything. Todays date is still entered wheter the tick is there or not...

    Here is the code as it is right now.

    Private Sub Form_load()
    Dim TodayDate As Date

    TodayDate = Now

    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE [tblPropertyLists] Set [LASTUpdate] = #" & Date & "# WHERE [Check101] = -1;"
    DoCmd.SetWarnings True

    End Sub

  13. #13
    Join Date
    Aug 2006
    Posts
    21
    Is the field [Check101] in the table [tblpropertyLists] or just in the form? If its in the Form only and not attached to the table then the WHERE does not work.

    Can you list the fields in your table and the fields in your form and check whether the field in teh form is actually attached to a field in the table.

  14. #14
    Join Date
    Aug 2006
    Posts
    21
    Try this and then see if there is an error message

    Private Sub Form_load()
    Dim TodayDate As Date

    TodayDate = Now

    'DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE [tblPropertyLists] Set [tblPropertyLists].[LASTUpdate] = #" & Date & "# WHERE [tblPropertyLists][Check101] = -1;"
    'DoCmd.SetWarnings True

    End Sub

  15. #15
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    whew, your throwing it at me pretty fast and hey, I really appreciate it, Iv been working on this solid for 3 days and my ignorance prevailed

    Here the good news.
    It is now working.
    I had to add the routine for checking the tick..

    Here is what is working
    '---
    Private Sub Form_load()
    Dim TodayDate As Date
    TodayDate = Now
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE [tblPropertyLists] Set [LASTUpdate] = #" & Date & "# WHERE [AUTOUPDATE] = -1;"
    DoCmd.SetWarnings True
    End Sub

    Private Sub Check101_AfterUpdate()
    If Me.Check101 = True Then
    LASTUPDATE = Date
    End If
    End Sub
    '---

    The code you just send previous to this did not work, and I did not try to debug it (what little I could do)

    Do you want me to try to work on it?? I would be happy to.

    All in all you were right about last problem, my checkbox was not tied to the table... that did it.

    Thanks again totally-pbm for helping the old guy.
    Kindest regards.
    Floyd

Posting Permissions

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