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

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
0
Utf
6/24/2007 2:19:01 AM
access.forms 6864 articles. 2 followers. Follow

3 Replies
611 Views

Similar Articles

[PageSpeed] 56

Helenae wrote:
> 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.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


0
Rick
6/24/2007 11:21:21 AM
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" wrote:

> Helenae wrote:
> > 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.
> 
> -- 
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt   at   Hunter   dot   com 
> 
> 
> 
0
Utf
6/25/2007 2:32:06 AM
Helenae wrote:
> 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.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


0
Rick
6/25/2007 1:38:43 PM
Reply:

Similar Artilces:

multiselction/subforms
Unfortuantley I have been trying to create a printer cartridge inventory system for some time now and cant seem to get it to work. basically this is what I want- I want to enter in the bar code from the cartridge with a barcode scanner (which works fine)- I then want to enter quantity amount on hand and such- I also want to be able to enter in what printer(s) the cartridge goes to -which after reading I found that I should not create a list box based on a printer table because although I could set it up to multiselect I could not return more than one value in the table nor would it be proper t...

Using Microsoft Works 4.0b
I am using Microsoft Works 4.0b on my Mac Power PC, OS 9.1. I have never had a problem with this word processor. I would like to use Works on OS 9.2 and OS 10, but Works 4.0b will not work on those two systems. I thought that I could use a later version of Microsoft Works 7.0, but the system requirement for that software only calls for Windows, not a Mac. What later version of Microsoft Works can I purchase to use on Mac OS 9.2 and 10? Thanks for a response. Louis S. Diggs <lsdiggs@bcpl.net> wrote: > I am using Microsoft Works 4.0b on my Mac Power PC, OS 9.1. > I have never had ...

Disabling / enabling entire menu and toolbar if child window open and has focus
Greetings, I have an MFC app within which a user can choose to go to the company's web site. If they select this from the menu, a CMDIChildWnd is created containing a CHtmlView. I need to have it so when this window is opened, all the menus are disabled and the toolbar is disabled. Also, when the window loses / gets focus, the menus are disabled / enabled respectively. Is there an easy way of doing this? How might I go about it? Thanks for your help! ~simon You can't disable *all* the menu items typically, because you want File>Exit, Help>About, other Help items, etc. en...

Master/content pages: prob with passing a control to JS
Hi, my code: .... <script type="text/javascript"> var myfilter = myJSfunction(document.myform.ListBox1); </script> </form> When compiling the web page, because of the master/content, the Listbox1 control gets a different name/id apparantly. So how can I pass my listbox on as an argument of my JS procedure? thx for helping, J "Jokke" <Jokke007007@hotmail.com> wrote in message news:7839C9C3-037F-4550-B5BC-E6BA75D93A99@microsoft.com... > When compiling the web page, because of the master/content, the L...

Can't send a link from IE 6
Installed Outlook 2003 recently, and now I cannot send a link to a page from IE6. I ran Tweakol2003 so I can receive links, etc. Something really simple I'll bet, but I have looked thru the help files and have not hit on it yet. Thanks for any guidance ... John Patch <fred@hotmail.com> wrote: > Installed Outlook 2003 recently, and now I cannot send a link to a > page from IE6. I ran Tweakol2003 so I can receive links, etc. How do you know you can't send it? -- Brian Tillman Usually when the you try and send a link, a Outlook form comes up over the IE screen jus...

Use on load trigger to provide Views for different Roles
I would like to provide different user roles with different views for the Account entity. Therefore I would like to use the on load trigger to check which user is logged on (and his user role) and according to this show a defined set of fields (maybe provide standard values as well). Is it possible to hide complete folders? Thx Ronald Lemmen's blog has code to do this: http://ronaldlemmen.blogspot.com/2006_05_01_archive.html -- Tad Thompson "Sascha Reppel" wrote: > I would like to provide different user roles with different views for the > Account entity. Theref...

How do you get the attribute value using XPath in VB.Net 2003?
Hi, How do you get the attribute value using XPath in VB.Net 2003? Many thanks, aushknotes "aushknotes" <aushknotes@discussions.microsoft.com> wrote in message news:508426F2-1C8A-4AD2-A52E-B80B9798AC0C@microsoft.com... > Hi, > > How do you get the attribute value using XPath in VB.Net 2003? > Prefix @ to the name of the attribute value. XmlAttribute attrib = (XmlAttribute)dom.selectSingleNode("/path/@attributeName"); -- Anthony Jones - MVP ASP/ASP.NET aushknotes wrote: > How do you get the attribute value using XPath in VB.Net 2003? ...

Data Validation using List (But needs unique list in drop down lis
Hi all, In sheet 1, column A is my title name while column B is person name. Sheet 1 is my database where i do data entry in this. In sheet 2, contains my query page. In cell A5, i uses data validation - list, on this cell. Say in sheet 1 : column A column B XXXXXXX Mr A YYYYYYYY Mr A ZZZZZZZZ Mr A AAAAAAA Mr B WWWWW Mr C DDDDDDD Mr C But In sheet 2, cell A5, I saw in the drop down list as follows: Mr A Mr A Mr A Mr B Mr C Mr C But i want to see this in cell A5 instead (Unique name that is) : Mr A Mr B Mr C ...

Employee Master Table error after V10 SP3
i have updated V10 to SP3, from SP1, and all seems to be fine except if I try to access the employee master table. Either from cards>payroll>Employee, or payroll transaction entry. Anytime I need to do a lookup on an employee id. Here is the message: A Get Change operation on table UPR_MSTR failed accessing SQL Data If I go to the more information or details button: [Microsoft][SQL Native Client][SQLServer] Invalid column name 'EMPLSUFF' [Microsoft][SQL Native Client][SQLServer] Invalid column name 'Dex_Row_TS' -- Doug It looks like an upgrade script failed...

Which Controls to Build a Parent/Child Web Form
Hi there, I'm quite new to asp.net, but know exactly how to do this using classic ASP, but would like some pointers for aspnet2. I've got data being returned from an SP which looks like this: HeaderItem ChildItem A A1 A A2 A A3 B B1 etc I want to display the data like this A - This is the header record A1 A2 A3 B - Header 2 B1 etc. In classic ASP I'd write out my header row html when HeaderItem changes value and then write the Child Rows. As I've on...

Use CountIf for a literal string
I am trying to count the number of cells in a range that contain the string <0.1. I don't want the number of cells that contain numbers less than 0.1, I want the actual string. Is there an escape character for comparison operators like the tilde for wild cards? Thanks, Chad Try this... =SUMPRODUCT(--(A1:A10="<0.1")) -- Biff Microsoft Excel MVP "chadkwelch" <chadkwelch@discussions.microsoft.com> wrote in message news:73F17509-1C52-4935-A45D-66DCC63B20C6@microsoft.com... >I am trying to count the number of cells in a range that ...

Posting Date used in Revenue Expense Deferral
I have a PM Invoice with Document Date 16/11/2009 and Posting Date of 01/12/2009. Entered Deferral details starting 01/12/2009. In GL the deferral charges commence 01/12/2009 and are all as expected. The Credit entry for the full invoice is posted to GL with posting date of 01/12/2009. The problem I have is that the Debit entry for the full invoice amount is posted to the document date of 16/11/2009 and I want it to be the same as the invoice posting date (01/12/2009) Is there a way to do this? Thanks Audrey ...

How do I specify an address when using "ActiveDocument.SendMail"
Hi Here is my code: If Not IsNull(([Forms]![Process Bookings]![Booking Form].Form.Email)) Then Options.SendMailAttach = True objWord.ActiveDocument.SendMail How do I specify the recipients address? Stapes ...

Could very much use a Field List
I apologize if this is out there already, but it seems that at least a few field names changed from 1.2 to 1.3. Although I had downloaded a field list before, from here, I cannot even find that original post, let alone any more recent that might contain the correct field names. I need to do some custom SQL and would prefer not to reinvent the wheel. Thanks in advance very much to anyone who could help. Bud Izen Salem Oregon Do you have MS Access? Make a new project and attach to the database as your data source. You will be able to see all the tables and the field names. It has been ...

How to Print string (Windows Printer) using RMS QSRules.
A windows printer is set as a receipt printer for a register. I need to print strings to this printer using RMS QSRules. How can I do this? I tried using Register.ActivePrinter.PrintNormal(Station as Long,Data as String) but it won't work. By the way, what is Station in the parameter? ...

can not receive emails with web links attached
Could someone tell me how to change controls to allow for emails with website links attached to come through? I can not receive emails with website links attached. ...

Deleting Linked Source(s)
In Excel 2000, how do you delete a link Source(s)? I know you can 'clear contents' in each destination cell to achieve this, but is there a simpler way. Can be difficult to determine which cells are in fact destination cells. Thanks, Jim This may help you http://support.microsoft.com/default.aspx?scid=kb;en- us;214127 >-----Original Message----- >In Excel 2000, how do you delete a link Source(s)? I know >you can 'clear contents' in each destination cell to >achieve this, but is there a simpler way. Can be difficult >to determine which cells are in fac...

Link to external workbook
I want to change a formula linking to a cell in a workbook on another server so that the server name is used rather than the drive mapping. I have tried the following but I keep getting 'File Not Found': '\\Server Name:\Drive Name\...... (instead of 'G:\..... Please advise. -- R Ormerod \\Server Name:\Drive Name\ Doesn't look like a valid format... I think I'd use windows start button|run type in \\servername and point and click down the paths to find the correct workbook. If you turn "View|toolbars|address bar", then you may be able to see the path...

Link for downloading online statements not visible Mon 2k3
On my financial institution's(TCF Bank) website, there are instructions for downloading online statements that refer to a link on the BALANCES page for MS Money. The problem is that the link apparently doesn't show up in my version (MS Money 2003 ver. 11). I spent some time on the phone with TCF's customer support and frustratingly tried to argue that there was no link to click on to download my statement both in Money and in Internet Explorer. Therefore I thought it was a problem with their website. However, they say that they recently updated their system to automat...

Determine when Enter Key is pressed in a sheet??
I want to determine when Enter key is pressed in a sheet that is active?? I would like then to run a macro when the Enter key is pressed ?? The Enter Key is not a capturable event. Worksheet change is This added to the individual worksheet in VBA will trigger when ever the sheet is changed. Private Sub Worksheet_Change(ByVal Target as Range) 'Do Stuff End Sub If you are going to make changes to the sheet using this event is is usually a good idea to turn off events at the start and then turn them back on at the end. Otherwise the changes trigger this macro again....

Transposing Links
I'm trying to link one file to another and copy the information. One file lists the info in a column, the other in a row, and I want to be able to drag (copy and paste) the linked info across the row (from the column) in my new file. Is this possible Thanks for your help. I don't think you can do a drag. What you can do is copy the column and do a paste special->transpose. >-----Original Message----- >I'm trying to link one file to another and copy the information. One file lists the info in a column, the other in a row, and I want to be able to drag (copy and p...

Monitoring directories using FindFirstChangeNotification
All, I would like to monitor a few different directories using FindFirstChangeNotification. I have successfully used it in the past to monitor one directory. All the directories I would like to monitor are on the root of a drive, but I do not want to monitor all the directories on the root. Example: the root of the T drive, T:\Dir1 T:\Dir2 T:\Test T:\source T:\Backup T:\Update How can I montor the T:\Test, T:\source, and T:\Backup at the same time using FindFirstChangeNotification? Thanks in advance. Hi, You need to use the following API's to monitor the changes in a specific ...

Tracing Links #2
Frank, That tells me what file I am linking to, but what I want to identify i which cell in my current worksheet is linked to these externa worksheets, as I cannot find a reference to the external documen anywhere in my current workbook. The reason I ask is that I am trying to avoid the "Do you wish t update links" message when opening my file. Regards, Stev -- Stephen Pai ----------------------------------------------------------------------- Stephen Pain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1526 View this thread: http://www.excelforum.co...

Using a OR() like function in an IIF statement
Hello, I am trying to create an IIF statement to test if the first character in a field is a 1,2,8 or 9. Something like the following: IIf(Left([possible_SO_match],1)="1 or 2 or 8 or 9",[Possible_SO_Match],"No Match") Is there a way to create it without going to a 4 level nested IIF statement? Thanks, Kerry -- Message posted via http://www.accessmonster.com kkulakow via AccessMonster.com wrote: > Hello, I am trying to create an IIF statement to test if the first > character in a field is a 1,2,8 or 9. > Something like the following: > > IIf(Left([possib...

Using outlook for email and outlook express for newsgroups
Hi I had outlook set up for my email messages and outlook express set up for reading newsgroups - now when I go to the newsgroups oe is automatically checking for email messages and downloading them - this never happened before and I think the only thing different is that I have installed a broadband modem and am waiting on freeserve activating my account - could something have happened to change this, and if so how do I change it back. Thanks Sharon "Sharon" <sharon@nospam.freeserve.co.uk> wrote in message news:c80adp$dva$1@newsg1.svr.pol.co.uk... > Hi > > I ha...