Results 1 to 10 of 10
  1. #1
    flwrgrl is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2011
    Posts
    17

    relationship issue or filter by subform?


    Depending on the answer, this is either a Form or a Query question.

    I am creating a DB from a linked table to monitor and record production. I CANNOT change this original table (SalesOrderLine). I need to filter my main form (form_SalesOrderLine_qry) based on a field (CardComplete) found in another table (tbl_NotesComplete) that I created based on the PK in my linked table. The2 tables have a one-to-one relationship.

    My question is how can I relate the two?

    My 1st attempt I first added my CardComplete field to my SalesOrderLine_qry, upon which my form is created. It worked wonderfully but my query did not include newly entered orders found in my linked table because they were not yet in tbl_NotesComplete. I played with the relationship join type, to no avail. Perhaps I didn't do something correctly?

    My second attempt took the CardComplete field out of SalesOrderLine_qry, which allowed for all orders from the linked table. I then created a subform (subform2_tbl_NotesComplete) on form_SalesOrderLine_qry. This allows me to see the information but I need to filter form_SalesOrderLine_qry based on the CardComplete field, which is located in the subform. I found some information about changing the RecordSource but before I got too far into that, I thought I would seek professional help.

    Attached is a picture of my form.

    I intended to finish the project sooner, but I am learning as I go. I am leaving this job in just a few weeks and really want to leave a finished product behind. Your help is very much appreciated.

    Mindy

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    ok, so I don't completely follow your post; but generically speaking if you want data from 1 table appearing in a form that has its own record source that does not include that table; then you do use a subform assuming there is a cross referencing relationship value between the 2.

    so your second approach is probably correct - but I don't follow what your issue is with it so can't advice further.

    Hope this helps.

  3. #3
    flwrgrl is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2011
    Posts
    17
    OK. How do I got about filtering my form for the subform field?

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a picture of the structure of the tables involved?
    The secret to linking the tables is to have some common field between then.
    We're looking for a common field that relates the 2 tables. From there we should be able to create some sort of filter.

  5. #5
    flwrgrl is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2011
    Posts
    17
    Thanks SO much for looking orange.

    I would like to have the CardsComplete field and the Notes field on my subform. The filtering, though needs to be based on the CardComplete field.

    I created the Notes table using a make table query. Does that make any difference?

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    So, SalesOrderLine is the main table, and FQPrimaryKey exists in tblShipping and tblProduction at all times (1:1 relation); and there is only a record for FQPrimaryKey in tblNotes.. if CardComplete is filled??

    What exactly does CardComplete mean (its purpose)?

  7. #7
    flwrgrl is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2011
    Posts
    17
    So, SalesOrderLine is the main table, and FQPrimaryKey exists in tblShipping and tblProduction at all times (1:1 relation); and there is only a record for FQPrimaryKey in tblNotes.. if CardComplete is filled??

    What exactly does CardComplete mean (its purpose)?
    SalesOrderLine to tblNotes is 1:1; and yes, there is only a record for FQPrimaryKey in tblNotes.. if CardComplete (or Notes) is filled on the form.


    The others are one:many since they may have multiple production dates and multiple shipping dates.

    Card Complete field is exactly that. When the item is produced, shipped, and invoiced, the card is complete. I did have the query filter out those that are invoiced in full, but I want to be able to pull up old orders if necessary without altering the query. Do you have another suggestion?

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you create a new query, using the relationships you have and select fields to give you what you want?
    Not the form, first lets get a query using your tables.
    You can restrict the query to a few records if that's important. The key is to identify exactly what you want to retrieve.

  9. #9
    flwrgrl is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2011
    Posts
    17
    Thanks orange. Sad to say, I am leaving for a week away this afternoon. I will look at your suggestions when I come back. Maybe I just wasn't meant to create this database! Who knew it would be so complicated!

    Thanks again!

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy in 2003 format? It can be dumbed down--remove confidential info.
    Just need table structures and some data.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pass Subform filter to subform in report
    By camftm in forum Programming
    Replies: 16
    Last Post: 07-19-2011, 07:12 AM
  2. Relationship screen Issue!
    By Lincoln in forum Access
    Replies: 8
    Last Post: 07-14-2011, 04:16 AM
  3. Replies: 1
    Last Post: 03-31-2010, 11:57 PM
  4. Replies: 0
    Last Post: 02-16-2006, 09:11 AM
  5. command button to filter a subform issue -
    By countdrako in forum Forms
    Replies: 1
    Last Post: 12-09-2005, 11:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums