Results 1 to 7 of 7

Thread: dcount issues

  1. #1
    Join Date
    Feb 2013
    Posts
    99

    Unanswered: dcount issues

    I have been trying to get this to work for a while now

    I need to perform a dcount on a table with details entered into a form.

    For example its a car parking database which I need to identify if a vehicle is reofending the it need to be infringed

    I am trying to perform it on a dcount so that if the vehcile has been warned a number of time then a message box with warn the user.

    So i need the dcount to take into account the information within the form.

    I have the following in the control source of a unbound text box with performs a count the first part works with the table colum and table name but cannot get the rest to work

    =DCount("VIN","Parking Violations tbl","vin = " & me.vin.Value)

    Any Ideas ?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you must use spaces in your table/column names tben they must be enclosed i square brackets.
    Eg
    Code:
     [parking violations tbl]
    If you are supplying a text valuethen it must be delimited by ' or "
    Eg
    "Vin ='" & ME.VIN.VALUE &"'"
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2013
    Posts
    99
    Thanks for the reply Healdem but still experencing problems I have tried to run it with in VBA with the following code but this does not work brakets or "" around the parking violations tbl seems to work

    Private Sub VIN_AfterUpdate()

    Set frmCurrentForm = Screen.ActiveForm

    Me.VIN.BackColor = vbBlack
    Me.VIN.BackStyle = 0

    If DCount("*", "Parking Violations tbl", "[VIN]='" & Me![VIN] & "'" = 3) Then

    Me.Filter = "VIN = """ & Me.VIN.Value & """"
    Me.FilterOn = True

    MsgBox "Vehcile Previous Warned", vbOKOnly, "Warning"

    Me.VIN.BackColor = vbRed
    Me.VIN.BackStyle = 1

    Me.VIN.BackColor = vbBlack
    Me.VIN.BackStyle = 0

    Me.FilterOn = False
    DoCmd.GoToRecord acDataForm, "Parking Violations", acLast

    ElseIf DCount("*", "Parking Violations tbl", "[VIN]='" & Me![VIN] & "'" = 0) Then
    DoCmd.GoToRecord acDataForm, "Parking Violations", acLast

    End If

    End Sub

    Thank you

  4. #4
    Join Date
    Feb 2013
    Posts
    99

    Sorry more details

    There is no error its almost as if the scirpt does not run ? but if I put a docmd after the if statments then this runs ?

    Thank you

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Two things:

    First, the Closing Bracket is in the wrong place, in the below and elsewhere:

    If DCount("*", "Parking Violations tbl", "[VIN]='" & Me![VIN] & "'" = 3)

    should be

    If DCount("*", "Parking Violations tbl", "[VIN]='" & Me![VIN] & "'") = 3

    The same thing applies to your other DCount, as well.

    Secondly, the syntax for the WHERE Clause depends on the Datatype:

    If VIN is defined as Text

    "[VIN]='" & Me![VIN] & "'"

    If VIN is defined as a Number

    "VIN = " & Me.VIN

    And while Columns/Fields with spaces in their names require Square Brackets around the names, Tables with spaces do not, they only require the Double-Quotes.

    Having said that, most developers consider it best-practice to not include spaces (or special characters) in the names of any Objects/Fields/Controls.

    Linq ;0)>
    Last edited by Missinglinq; 06-23-13 at 19:56.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Feb 2013
    Posts
    99

    Resolved

    Thanks for the reply all, with some trial and error got it working with the recommendations

    Kind Regards

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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