Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33

    Unanswered: Using check boxes in DCount function

    Hi.

    I'm using the DCount function to check for duplicates in tblBookings matching text in textboxes from frmNewBooking. I've managed to get this to work fine with text, but I then added check boxes to my form and table. I want know to see if some text equals that of in the table (like I originally had) but also, using And, to check whether the checkbox was ticked in a previous booking or not. Here's my guess at what it would be, I know that all of the criteria works up to the bit where I've added chkExtension and onwards. I have also checked that all the data types are correct in my table too.

    Code:
     
    Dim NumOfDuplicates As Integer
        NumOfDuplicates = DCount("*", "tblBookings", "(tblBookings.EventDate = cboEventDate And tblBookings.Session = cboSession And tblBookings.Extension = chkExtension And tblBookings.SpecialEvent = chkSpecialEvent)")
        If NumOfDuplicates > 0 Then
            MsgBox "Sorry, this session is already taken.", vbInformation, "Session taken"
        Else
            MsgBox "This session is available.", vbInformation, "Session Available!"
        End If
    Any ideas why this shouldn't work??

    Thanks,
    Tom

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    this criteria:

    yourFilter = "tblBookings.EventDate = cboEventDate"

    is drastically different from this criteria:

    yourFilter = "tblBookings.EventDate = " & cboEventDate

    Try using the intermediate window and debugging those values, see if you can indentify what's happeing...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    this criteria:

    yourFilter = "tblBookings.EventDate = cboEventDate"

    is drastically different from this criteria:

    yourFilter = "tblBookings.EventDate = " & cboEventDate

    Try using the intermediate window and debugging those values, see if you can indentify what's happeing...
    I've never used debugging, etc, before...so I wouldn't really know what you're talking about, sorry.

    Is there perhaps some sort of reference quoting that I have to use (i.e. you have to wrap dates in #'s). Is there one for boolean expressions?

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    The point is your are that long string as the criteria for the dcount function. Try putting the expression in a select query and see if it works. I suspect it wont because it will read cboEventDate as exactly that - a string of 12 characters.

    So instead of sending "tblBookings.EventDate = cboEventDate" to the function you really want to send:
    "tblBookings.EventDate = #1/1/6#"

    I suggest you create a string variable called say myCriteria and build it up bit by bit to read the criteria you need.

    So a start might be:
    myCriteria="tblBookings.EventDate = #" & me.cboEventDate & "#"

    I've assumed that cboEvent is a field on your form (?). I'm fairly certain you have to use the # but not 100%. Anyway, the point (the point Teddy was making) is that the cboEventDate has to be used as a variable not as a string. Note how I've added strings together using &. Note also that if your criteria is a string it needs to be enclosed in single quotes. So you might have something like:
    myCriteria="tblBookings.Session ='" & me.cboSession & "'"

    note the single quotes which will result in:
    "tblBookings.Session ='session1'"
    Get the point ?

    The next point made by Teddy was to use debug. I think the intermediate window depends on which version of access i.e. it's called intermediate in later versions and the debug window (ctrl-G) in earlier versions. So after the above line of code, if you put in the following line you will get the value of myCriteria output to the intermediate window and you can see if it looks like a proper criteria that you would write in SQL:
    Debug.Print myCriteria

    hth
    Chris

  5. #5
    Join Date
    Jan 2006
    Location
    Brighton, UK
    Posts
    33
    OK, great information. I'll give it a shot.

    Regards,
    Tom

Posting Permissions

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