Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    May 2004
    Posts
    80

    Unanswered: OnCurrent function not working with code - how to?

    I received this great-looking code from Joel Dixon (if this is he reading this, hello!) But the problem is that it's meant to be placed in the Code Builder under the OnCurrent form control for MenuItems. I finally located the OnCurrent control and pasted the code into the Code Builder, but despite the fact that I've saved it and modified the names of fields, tables, etc appropriately, nothing happens when I return to Access, even though in the OnCurrent control (under form Properties) there is [Event Procedure].

    What should happen is that whatever Month value in the DateAdded field (identified by [DatePart]="m") is set to a clause - i.e if the month is 12, 1, or 2, then the MenuSeason field displays Summer, and so forth. Instead, even after entering a date, the MenuSeason field remains blank. There are no error messages, no nothing. Very annoying.

    Private Sub Form_OnCurrent()

    Dim strSeasonName As String

    If IsNull(DateAdded.Value) Or Len(DateAdded.Value) = 0 Then
    SeasonNameLabel.Caption = ""
    Exit Sub
    End If

    Select Case DatePart("m", DateAdded.Value)
    Case 12 Or 1 Or 2:
    strSeasonName = "Summer"
    Case 3 Or 4 Or 5:
    strSeasonName = "Autumn"
    Case 6 Or 7 Or 8:
    strSeasonName = "Winter"
    Case 9 Or 10 Or 11:
    strSeasonName = "Spring"
    End Select

    SeasonNameLabel.Caption = strSeasonName
    End Sub

    Private Sub Form_Current()

    End Sub


    This makes me wonder if one of the following has happened:

    a) I have modified the code incorrectly and there is a bug somewhere - for example, should Private Sub Form_OnCurrent() be, in fact, Private Sub MenuItems_OnCurrent(), or perhaps even Private Sub Form_MenuItems_OnCurrent()?

    b) Should it be placed somewhere other than control source? Should it relate to the form or the field itself? It makes me wonder, because it just doesn't acknowledge a thing.

    c) Access is just playing silly buggers on me and it really CAN work, but it just won't, in which case somebody should pass me a brick so I can throw it at my monitor.

    P.S Don't worry, I'm Australian, which explains the weird season setup for any Northern Hemispherian.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Grace

    I hope I'm not stepping on Joel's toes coz I don't know exactly what he told you. You have found the OnCurrent Event in the Form Porperties sheet. the Form_Current() is the event's handler (the code that gets fired when the OnCurrent event occurs). Therefore your code should read:

    Private Sub Form_Current()

    Dim strSeasonName As String

    If IsNull(DateAdded.Value) Or Len(DateAdded.Value) = 0 Then
    SeasonNameLabel.Caption = ""
    Exit Sub
    End If

    Select Case DatePart("m", DateAdded.Value)
    Case 12 Or 1 Or 2:
    strSeasonName = "Summer"
    Case 3 Or 4 Or 5:
    strSeasonName = "Autumn"
    Case 6 Or 7 Or 8:
    strSeasonName = "Winter"
    Case 9 Or 10 Or 11:
    strSeasonName = "Spring"
    End Select

    SeasonNameLabel.Caption = strSeasonName


    End Sub


    If you aren't sure if an event's code is firing, try popping a message box into the code to reassure yourself.

    e.g.
    Private Sub Form_Current()

    Dim strSeasonName As String

    msgbox "all ok"

    to run through your specifics:
    a) No bug, but better to let VB generate any automatic code for event handler names. The best you can do doing it yourself is enter exactly the same as VB would enter anyway, and the worst is enter the wrong info as you did.
    b) no
    c) I have accused Access of playing silly buggers more times than I can remember. Most of the time it turns out to be me.
    "Don't worry, I'm Australian" - lol, I like that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2004
    Posts
    80

    Anybody got that brick?!

    Nope, still doesn't like me. Doesn't do a thing, not even the MsgBox.

    Why oh why?!

    - Grace

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Try,

    Case 3 or Case 4 or Case 5 rather than

    Case 3 or 4 or 5

  5. #5
    Join Date
    Jul 2003
    Posts
    73
    Howdy Grace - don't know if I should be happy to see you here (if I didn't give you dodgy code in the first place it wouldn't have stuffed up )

    Yeah - I normally test everything I post on these boards - but I was quite busy this afternoon and that OnCurrent must have slipped through

    Hey Pootle - no worries about toe stepping - I don't get attached to forum postings. If someone can get the problem solved everyone's happy

    I'm a little surprised that the Message box didn't popup when you put it in the OnCurrent event. That event should be called immediately when you open the form - as the first record that is loaded fires it. A few quick questions:

    1. Are there any records in the table that the form is bound to? I know it's a silly question - but I've done sillier!

    2. Is the MsgBox the very first line in the Module? If it happens after the Null check for example - you may be exiting the sub before you get a chance to see the box.

    3. Perhaps there was an issue when you copied and pasted the code. Try clearing the whole OnCurrent module from VB. Go back to form design and look at the form properties. Go to the On Current event - and click build to get the Code Builder. Only write the Msgbox "Please show this message to prove I'm not mad" line. Save and close the form, reopen it and see if it works.

    4. Spin around three times quickly and yell at your monitor / keyboard / computer. It really helps sometimes!

    Good luck
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  6. #6
    Join Date
    Jul 2003
    Posts
    73
    And also - thanks Mike - you're probably right about the Case Statement syntax. Again - should have tested it. I work with Java / Cobol / SQL / Perl at work - and really get confused when it comes to remembering specific syntax! My poor spelling also (doesn't) help!
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Joel,

    I only suggested Case 1 or Case 2 or Case 3 etc because IIf functions need the field name to be repeated when there is Or or And

    Maybe it should be

    Case 1
    Case 2
    Case 3 down to Case 12 and if three of them are right the answer is still "summer"

    You are up late, what is your excuse

    Mike

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Grace

    Just to be certain - you noticed that what I did was move ALL of the code out of the
    Private Sub Form_OnCurrent()
    .....
    End Sub

    into

    Private Sub Form_Current()
    .....
    End Sub
    ?
    Basically the Form_Current part should replace Form_OnCurrent.

    Just want to be explicit because that was the whole key to why I think nothing was happening, especially since you say that the message box wasn't firing either. Also, you can fire the current event by moving from one record to the next if you want to test the code. But run through Joel's stuff too - he's talking sense.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2003
    Posts
    73
    Just checked it in Access. It should be:

    Case 1, 2, 12
    Case 3 To 5
    Case 6 To 8
    Case 9 To 11

    Sorry - really should have tested that one.

    Re being up late - I don't know really. Got home not too long ago - but I really should be going to bed! I think I might just do that :yawn:

    Is your excuse for being up late better than mine (not that I really have one!)
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How can I resist getting involved in the case discussion?

    how about:
    Select Case DatePart("m", DateAdded.Value)
    Case =12

    strSeasonName = "Summer"
    Case >=9
    strSeasonName = "Spring"

    Case >=6
    strSeasonName = "Winter"
    Case >=3
    strSeasonName = "Autumn"

    Case >=1
    strSeasonName = "Summer"

    case else
    msgbox "Seasonal error"
    End Select
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jul 2003
    Posts
    73
    Perhaps:

    Select Case DatePart("m", DateAdded.Value)
    Case =12
    strSeasonName = "Christmas"
    Case >=9
    strSeasonName = "Hockey"
    Case >=6
    strSeasonName = "Footy"
    Case >=3
    strSeasonName = "Leaf Watching"
    Case >=1
    strSeasonName = "Cricket (Boo-Yeah)"
    case else
    msgbox "What you talkin' 'bout Willis?"
    End Select

    (Yes, sleep would be a good idea!)
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    All this talk of Cases makes me retreat to macros and queries

    Mike

  13. #13
    Join Date
    May 2004
    Posts
    80

    Lol

    I probably don't have much of an excuse...but Joel, don't worry about not having originally tested it in Access - you know, you're not obligated to all this forum stuff, and the fact that you did something at all and then tried to help later was enough...

    I will definitely try those options (thanks to everyone who offered a solution). I think I might have gone mad without this messageboard...hehe...well, madder than I already am, anyway...

    Get some sleep people, and thanks.

    - Grace

  14. #14
    Join Date
    May 2004
    Posts
    80

    Almost there....

    OK, I managed to get somewhere. I deleted my old form, rebuilt the table and then saved it to a new database, creating a new form so it's all nice and clean. I ran the module with the MsgBox and it did it! I was so happy. Then I pasted the actual code, still inserting a MsgBox. It did work. The only problem is that after the MsgBox runs, it tells me I've got to debug. Here I'll paste all the code I used and highlight the line that's playing up. I also made sure I still named the field SeasonName and then the caption as SeasonName, so that's not the problem so far as I know.

    So we've almost got it. Just, not quite.

    Private Sub Form_Current()

    Dim strSeasonName As String


    I put the message box here when I tested it.

    If IsNull(DateAdded.Value) Or Len(DateAdded.Value) = 0 Then
    SeasonNameLabel.Caption = ""
    Exit Sub
    End If

    Select Case DatePart("m", DateAdded.Value)
    Case 1, 2, 12
    strSeasonName = "Summer"
    Case 3 To 5:
    strSeasonName = "Autumn"
    Case 6 To 8:
    strSeasonName = "Winter"
    Case 9 To 11:
    strSeasonName = "Spring"
    End Select


    SeasonNameLabel.Caption = strSeasonName

    <-- that part in red always gets highlighted in the debugger. When I hold my mouse over the yellow highlight, the caption that comes up is strSeasonName = "Autumn"

    End Sub

    I also tested pootleflump's code:

    Select Case DatePart("m", DateAdded.Value)
    Case =12
    strSeasonName = "Summer"
    Case >=9
    strSeasonName = "Spring"
    Case >=6
    strSeasonName = "Winter"
    Case >=3
    strSeasonName = "Autumn"
    Case >=1
    strSeasonName = "Summer"
    case else
    msgbox "Seasonal error"
    End Select


    ...along with the Case 2 Or Case 3 Or Case 4 idea, but it didn't like that one either, it just turned it into red text and had a hissy fit.

    Any suggestions?

    - Grace

    P.S Pootleflump, that validation rule thing isn't working. I don't know why, but it just doesn't acknowledge anything. Is it anything to do with the fact that the default values are =Date() and =Time() for the fields respectively? That's all I can come up with.

  15. #15
    Join Date
    May 2004
    Posts
    80

    Whoa...hang on...

    Just wondering...I flicked around with something which seemed to work...is this code set to place the season name in the SeasonName label box, or in the actual textbox? Because when I added SeasonName_Label to the last bit that wasn't working (found when I looked up the form objects in the properties bar) I went back to the form and found the season name in the label!! If that was the purpose of the code, then that's great that it's doing something, but how to modify that slightly so that it will appear in the textbox rather than the label??

Posting Permissions

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