Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dlookup not working? (A2k (9.0.4402) SR-1)

    Dlookup not working?

    A2k (9.0.4402) SR-1

    The following Dlookup is not working for me.

    StrPermitNo is indexed.

    Duplicates are allowed because old data from a previous system allowed duplicates, however in the new Access system no more duplicates are allowed.

    What do I have to do to get this code to work?

    Private Sub strPermitNo_BeforeUpdate(Cancel As Integer)

    ' Verify whether Permit Number already exist

    If Not IsNull(DLookup("strPermitNo", "tblBPermit", "strPermitNo=" & Me!strPermitNo)) Then
    MsgBox "Permit Number " & Me!strPermitNo & " Already Registered! Please choose another Permit Number!", vbOKOnly, "Warning!"
    Cancel = True
    End If

    End Sub

    Thanks, John Graves

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dlookup not working? (A2k (9.0.4402) SR-1)

    Well, if the field is text, which is what your variable name implies, you need to do the DLookup like this:

    <pre>DLookup("strPermitNo", "tblBPermit", "strPermitNo='" & Me!strPermitNo & "'")</pre>

    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dlookup not working? (A2k (9.0.4402) SR-1)

    When I do this in Access xp it puts brackets around the "me" and then I get a message that the acces automation object does not recognize "me" in this context. I always thought that in a domain aggregrate function you need to reference the form specifically, ie Forms![frm]![fld]? I wish that there was finally a builder for these functions!
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dlookup not working? (A2k (9.0.4402) SR-1)

    It depends on where you're running the dlookup from. If it's from within a form, then Me is a valid keyword to refer to a control on that form. If you're running it from outside a form, say within a query, you have to fully reference the form because Me is only valid within the class it represents.
    Charlotte

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dlookup not working? (A2k (9.0.4402) SR-1)

    I was doing it to give a Dsum on a text box in the footer of a form, which was referncing a text box in the detail area of the form.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dlookup not working? (A2k (9.0.4402) SR-1)

    Are you trying to put the DSum in as the controlsource for the textbox? If so, then you can't use the Me keyword when you refer to the other textbox, only the control name.
    Charlotte

Posting Permissions

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