Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fields from different tables (2k)

    I don't know why I'm having trouble with this, but I am. I have a form based off of a table. All of the fields linked to this table work fine. 2 of my fields are populated through another table though. How come I can't just take the data from the other table? =[OtherTable]![StatusField] do i have to use Link Criteria for that to work correctly?

    I also tried making up a query and adding all of the fields that I would need from the different tables and basing the form off of that, however thats not giving me any results, probably because of key fields or something, I'm not really sure. Even if I did that, I don't know if the data entered would trickle back to the tables that the query was based off of.

    I don't think my brain is working right today. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Fields from different tables (2k)

    You cannot refer to a table that is not in the record source of the form like =[OtherTable]![StatusField]. Instead, you can use the DLookup function. The general format is

    =DLookup("FieldName","TableName")

    or

    =DLookup("FieldName", "TableName", "Criteria")

    The former will look up the first value of FieldName it encounters in TableName. The latter will apply criteria to narrow down the lookup. If the other table has some kind of link to the table that acts as Record Source of the form, use this to specify the criteria. For a numeric link field it would look like this:

    =DLookup("StatusField", "OtherTable", "IDField = " & [IDField])

    and for a text field like this:

    =DLookup("StatusField", "OtherTable", "IDField = " & Chr(34) & [IDField] & Chr(34))

    Chr(34) represents the double quote character ".

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fields from different tables (2k)

    Do I need relationships between these tables for this to work correctly? Because I'm getting a #error right now
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Fields from different tables (2k)

    No, you don't necessarily need a relationship, although it would be desirable. If you get #Error, there is an error in the expression. Can you provide details?

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fields from different tables (2k)

    I put this in my control source for the text box on the form that needs to dislpay the status field:

    =DLookUp("Status","Status_Table","DWG_NO =" & [DWG_NO])

    which gives me the #error

    each table in my DB has 5 key fields. i also made a relationship that connects the 5 key fields together (seems unnecessary). the text box with the status field isn't editable, so i just need to be able to view it. if i *HAVE* to, I'll make a subform for the field to be viewed.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Fields from different tables (2k)

    1. Is Status_Table the correct name of the other table?
    2. If so, is DWG_NO a field in Status_Table?
    3. If so, is it a number field?
    4. Is DWG_NO a control on your form?

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fields from different tables (2k)

    ahh it was a text field, sorry about that. Thanks again Hans!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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