Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2002
    Posts
    3

    Unanswered: DCount not working...anything look fishy?

    Private Sub Billing_address_BeforeUpdate(Cancel As Integer)
    Dim Counter As Integer
    Counter = DCount("[Club number]", "Member addresses billing query", _
    "Club number = '& Forms!Spa member tracking![Club number]'")
    If Counter > 0 Then
    Dim Msg, Style, Title, Response
    Msg = "There is already an address marked as the billing address."
    Style = vbOKOnly + vbExclamation + vbApplicationModal
    Title = "Billing address conflict"
    Response = MsgBox(Msg, Style, Title)
    If Response = vbOK Then
    Me.Undo
    End If
    End If
    End Sub

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    DCount("[Club number]", "Member addresses billing query", _
    "Club number = '& Forms!Spa member tracking![Club number]'")

    Has problems. Should be:

    DCount("[Club number]", "[Member addresses billing query]", _
    "[Club number] = '" & Forms![Spa member tracking]![Club number] & "'")

    First, get in to a practice of never using spaces in object names. In other words, "Club number" should be ClubNumber. "Spa member tracking" should be SpaMemberTracking.

    If you insist on keeping spaces in name, then you are going to have to learn to use the [ and ] regularly, and completely.

    Lastly, the form of the WHERE clause of the DLookUp, in this case, should be:

    [FieldName] = single-quote double-quote & [CriteriaFieldName] & double-quote single-quote double-quote.

    Ken

Posting Permissions

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