Master/Child Links for Subform - by not using the Primary Key?

[Helenae]

I am trying to create a very simple form that allows me to enter 'for sale'
data on items from a complete inventory table.

I created 2 tables. 1 with the Master List of collectibles. The other with
more detailed data for the collectibles I want to sell (Sales Data).

I created a one to one 'relationship' between the 2 tables, joined by the
Primary Key# of the Master List and then an ID Key# for the Sales Data (each
item can only be sold once, so there are no duplicate numbers).

I want to be able to pull up the table containing my Master List in the
subform, filtered by the data in a listbox containing 1. for sale, 2. sold.
3. not for sale. I only want the 'for sale' filtered data to show up in the
subform, so I can select 1 record among them, and then add more data for that
one item (plus a photo) in the fields for the Sales Data (2nd table).

What has happened so far is, I used the wizard to create a Form with the
Listing Data as the main formview fields, and the Master List as the subform
in datasheet view. This is fine, except I cannot see a list of my 'for sale'
items, and the only way I get to see anything at all is if I type in the
record ID number, in which case the subform only shows me that one record
(how do I know which items I want to sell to write sales details for them
all?).

It seems like I have to find a spot that will allow me to enter that field
with the 'for sale' filter into the subform, but I cannot figure out how and
where to enter it.

Basicallly, how do you get a subform to pull up all the data completely
unrelated to the primary key field, but filtered by a different field?

Also, in Access 2007, I can Add Fields from 2 different tables into the same
form... EXCEPT photo images. If I try to drag the 2nd photo from the 2nd
table into the same form, I get a text box instead of a photo box. The
Property sheet says it's a photo... but it will not show the photo that is
showing in the single table form, it shows a textbox.

How do you put 2 photos from 2 different tables on the same form? (ie, 1st
table is original, 2nd table is restored condition. You want to see both
photos on the same form when entering sales information).

I can't find any information anywhere on how to do either of these in the
Help or the How To sections. Please please help, this is driving me nuts!

Helen

 

[Rick Brandt]

Basicallly, how do you get a subform to pull up all the data
completely unrelated to the primary key field, but filtered by a
different field?

I didn't follow all of what you are trying to do, but the answer to the question
above is to enter whatever field names you want in the ChildLink property of the
subform control and whatever field or control names you want in the MasterLink
property of the subform control.

The wizard will default to using the fields defined in the relationships window,
but you can manually enter any fields you want in those properties.

 

[Helenae]

Hi Rick. Thank you for responding! I think what I'm asking is more basic,
because I did try that, and it didn't work either... I'm going to see if I
can phrase this a bit better.

I have examined the relationships at length... and what I really want to do
is attach the Item Status (listed) to ANYTHING on the Master list... but I
can't.

I created a Query of the Master List filtering ONLY the Listed records. That
means I took out the filtering requirement altogether by using the query to
connect to the Listing Data on the form.

I linked the Query to the Master list via the Item Status, as well as the
Listing data table via the Bottle ID

All I want to be able to do is see the listed items on a datasheet, so I can
pick one to enter extra data that will record into the Listing Data table.

Everytime I add 1 thing from the Listing Data, all my listed records vanish
and I can only pull up 1 record at a time (because of that 1:1 relationship
between the Bottle ID and the Listing Data).

Absolutely nothing in the help section helps.

This can't be such an odd situation, if say I had an master employee list
table and I wanted to look at all the employees who had overtime, and enter
extra data about only those employees into a different table, it would be
done the same way.

Or if say I had a table of every single plant in my yard, and I wanted to
make a form that included only plants that had leafrot. I want to create a
form where I can see all the plants with leafrot, with new data going to a
2nd table about plant diseases. This way, I could enter specific treatments
tried on those individual records/plants from the master table. How would I
get my filtered list of plants with leafrot to show up so I can choose which
one I want to try treating?

Or say I had a master list of every visible star in the Andromeda Galaxy,
and want to filter out only the visible white dwarfs from that master list so
I could enter individual observations about them to a linked separate table
with data only about white dwarfs. How would I do it?

Or say I were a doctor who had a master list of patients, and I only wanted
to enter additional information on a separate table about patients who were
coughing, but I wanted to be able to see the entire list of coughing patients
for me to select 1 patient to enter additional information about, how would I
do it?

This has to be the single most basic purpose of a database... and I can't
find one thing about it in the Help section, or on the Access forums or
anywhere on the internet.

Any help would be sincerely appreciated!

Helen

 

[Rick Brandt]

Hi Rick. Thank you for responding! I think what I'm asking is more
basic, because I did try that, and it didn't work either... I'm going
to see if I can phrase this a bit better.

I have examined the relationships at length... and what I really want
to do is attach the Item Status (listed) to ANYTHING on the Master
list... but I can't.

I created a Query of the Master List filtering ONLY the Listed
records. That means I took out the filtering requirement altogether
by using the query to connect to the Listing Data on the form.

I linked the Query to the Master list via the Item Status, as well as
the Listing data table via the Bottle ID

All I want to be able to do is see the listed items on a datasheet,
so I can pick one to enter extra data that will record into the
Listing Data table.

Everytime I add 1 thing from the Listing Data, all my listed records
vanish and I can only pull up 1 record at a time (because of that 1:1
relationship between the Bottle ID and the Listing Data).

Absolutely nothing in the help section helps.

[snip]

I'm sorry, but I still can't make much sense out of this. You can see your
database and form whereas I cannot.

The best guess I can make is that you would have a form bound to the table
where you want to enter your new records and on that form would utilize a
ComboBox to make the selection of the ID from the "master table". I don't
believe a main form with a subform is even the correct tool for what you are
trying to do.