Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    3

    Unanswered: Hiding a button on a form based on a query result (Access 2003)

    Hi Guys,

    Been going round and round on this fairly simple thing.

    All I want is for a button to be greyed out on a form if the week number exists in a query.

    I've got a a query called "currentweek" which has the column headings:
    DATE
    WEEK

    2 buttons:
    Button1
    Button2
    And a form called "disablebuttons"

    In my head this should happen:
    When the form is opened and On Current event should run.
    This event should look for the current date (date()) in the DATE column in query "currentweek" then return that WEEK number.
    Based on that it should then disable or enable a "button1" on the form.

    This is what I have so far (I'm not exactly well versed in VBA):

    Private Sub Form_Current()
    If DCount("currentWeek", "week") = "58" Then "<- this is the line annoying me"
    Me.Button1.Enabled = False
    Else
    Me.Button1.Enabled = True
    End If
    End Sub

    Any help would be greatly appreciated.

    Cheers,
    Colin

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you are comparing a value (from DCOUNT) to a string. it will never EVER match
    however using DCOUNT for this doesn't feel correct
    should you be using DLOOKUP instead?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I second healdem. DCount returns a count of records from within a domain that meet specified criteria (if any are supplied). Access VBA is fairly good at implicitly converting data types, so you might get a hit on that one if you ever have 58 records in the table, but otherwise not.
    Also, domain functions require the field to be examined before the name of the domain (table or query), so your problem line is telling the VBA compiler to return the number of records in field "currentweek" that is contained in table "week". I suspect that the following will solve the problem:
    Code:
    Private Sub Form_Current()
       If Nz(DLookup("[Week]", "currentweek", "[Date] = #" & Now() & "#"), 0) = 58 Then
          Me.Button1.Enabled = False
       Else
          Me.Button1.Enabled = True
       End If
    
    End Sub
    As an aside, be careful how you name fields. Words like "week" and "date" are reserved by Access, and using them in field names can cause problems. This is why I've added to the brackets - it explicitly defines the contents as a named object rather than a function or method call.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Oct 2012
    Posts
    3
    Thanks guys, I see the problem now.

    Only thing that's stopping me now is the "NOW()" statement as it's looking up date and time and I don't have that in the date value but I'm on the right track now.
    Should be able to change that to Date() and work from there.

    Talk about forrest from the trees!

    Much appreciated for the pointers and help.

    Cheers

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is 'all' you want the current date.. if so use date() in place of now()
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Sorry; should've thought of that one!

    Glad it helped, though!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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