Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    DLookup doesn't work in Subform (2000)

    I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup doesn't work in Subform (2000)

    You must use the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the database window. To find the control name, open the main form in design view and click once on the subform to select it. Don't click on it a second time, for that will select something within the subform. The name of the subform will be displayed in the title bar of the Properties window, and also in the Name property. Use this name instead of frmSub.

    Note: in the first expression, the main form is frmMaster2, and in the second expression, it is frmMaster. Make sure that you use the correct name.

    Post back if this doesn't solve the problem.

  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: DLookup doesn't work in Subform (2000)

    Hi Hans,
    The subform control name is "frmsub". I used the same name as the subform in the database window. Because I couldn't get it to work in the a subform, I created frmMaster2 just to test the DLookup to see if it worked in the main form which it did. I've checked the names and they appear to be correct. Here's a cut & paste of the actual code:

    Private Sub LivestockUtiliz1_AfterUpdate()
    On Error GoTo HandleErr
    Me.ReturnedValue = DLookup("[Rate]", "tblAgriculturalRates", "[DropDownDesc] = Forms![frmMaster]![frmSub]![LivestockUtiliz1]")
    ExitHere:
    Exit Sub
    HandleErr:
    Select Case Err.Number
    Case Else
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
    End Select
    End Sub

    I was going to send you a copy of this test database but noticed that you can't attach a file over 100k and my mdb is 260k.

    Any other suggestions?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup doesn't work in Subform (2000)

    Is DropDownDesc a text field or a number field?

    If it is a number, try this:

    Me.ReturnedValue = DLookup("[Rate]", "tblAgriculturalRates", "[DropDownDesc] = " & Me.LivestockUtiliz1)

    If it is text, try this:

    Me.ReturnedValue = DLookup("[Rate]", "tblAgriculturalRates", "[DropDownDesc] = " & Chr(34 & Me.LivestockUtiliz1 & Chr(34))

    Chr(34) is the code for a double quote ".

    PS you can't attach .mdb files directly, if you still wish to attach a database, create a zip file from it and attach that. See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: DLookup doesn't work in Subform (2000)

    The DropDownDesc is a text field and your suggestion seems to have worked. I will now incorporate it into the real application.

    Out of curiousity though... why does adding Chr(34) make the difference and why can you refer to the LivestockUtiliz1 field using Me. I thought you had to specify the parent form as well as the subform when refering to fields in a subform.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup doesn't work in Subform (2000)

    Your code is the After Update event for a control on the subform. In its own module, you can refer to a form or report as Me. This is much easier than invoking Forms!..., since you don't need to specify the name of the form. Code with Me will still work if you rename the form, and it doesn't matter whether the form running the code is a main form or a subform or a sub-subform.

    In the code I posted, I took the value of LivestockUtiliz1 out of the quoted string, instead concatenated "[DropDownDesc] = " with the value of LivestockUtiliz1. Text values, however, must be enclosed in quotes, and that is what the Chr(34) provide.

  7. #7
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: DLookup doesn't work in Subform (2000)

    I get it! When I was testing the DLookup, I changed the criteria to "DropDownDesc = 'Beef' " because I knew that was what the value of LivestockUtiliz1 should be. That did work for me and the correct value was returned. So the Chr(34) is basically doing the same thing. That makes sense.

    Once again, thanks so much for your help. I really appreciate it.

    Sue

Posting Permissions

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