Pull data for multiple information

Question

Hi, I would like some help. I am using Access 2007 and as I have just started to create some databases for home use.  The above is a screenshot of a Form I have created, in the background (as tabs) I show the 2 Tables I need help with.

I am trying to have it so I can run a query on one or more ingredients to return all recipes with this information.

At the moment I have 1 column of ingredients in the "Ingredient Listing" table and the same for the units of measure in the "Unit of Measure Listing" table. I think this should stay this way.

I have some basic queries set up, to pull information from my "Recipes" table which holds all the information. I have 15 separate columns to hold the unit of measure information and another 15 to hold the ingredient information. I am willing to have 15 identical/duplicate columns in the tables holding this information (instead of one), but there must be a better way.

Also I have tried to use information I have found to solve this problem, but I am unable to get what I think is the best and easiest solution to work. I found this "set Fieldb = fielda" and have tried to put it in an Expression Builder (with my database details) in the tables to duplicate information from 1 column to 15, no success.

If there is a way to query an ingredient without inputting its name under each (15) column, in separate rows I would be very interested. With the number of ingredients there are in a recipe, there is not enough rows in a query for me to do this.

I have attached this particular screenshot so you can see that some recipes show an ingredient more than once (generally with different units of measure). Also not all ingredients such as salt and pepper have a unit of measure to be recorded.

I feel like it is pointless to continue entering hundreds or thousands of recipes into this database until I can search for want I want properly. Any help with this would be greatly appreciated.

Answer 1

If you attempting to use the NotInList event to insert a row into tblCategories where the NewData value is already present in the CategoryName column, then assuming the column is uniquely indexed as it should be, the INSERT operation will fail due to the index violation.  If such a row does exist, however, it begs the question of why the event is being triggered in the first place.

BTW, I notice that you are also trying to insert a value into the foreign key column ccnFolderID.  This should be possible, but in cases where it is necessary to insert values into columns other than a candidate key I would generally open a little form in dialogue mode to do so.  This is illustrated in my NotInList demo in the case of the City combo box control in the frmContacts form.  Note, however, that this illustrates the methodology only, as I've ignored the fact that city names, like personal names, can legitimately be duplicated, so the use of the NotInList event procedure is in reality ruled out.

Your acknowledgement that you *'don't have proper knowledge of how to make it work'*is commendably honest.  The quote by Richard Feynman below my signature is pertinent.  I do think you are trying a little too hard to run before you can walk, and would be well advised to put this application on the back burner for a while so that you can devote some time to gaining a good basic understanding of both the databases relational model, and of the nuts and bolts of MS Access, VBA and SQL.  Otherwise you will continue to need nursemaiding at every step.  There are quite a few online tutorials which a little bit of googling will throw up, but I still think there is no real substitute to reading the literature, both theoretical and practical.

Answer 2

The principles behind correlated combo boxes are relatively straightforward.

1.  The RowSource property of the referenced combo box should be a simple SQL statement which returns the values of the relevant table's primary key column from which a value is to be selected.  In most, but not necessarily all, cases the value will be that of a hidden column, with the corresponding values from a non-key column visible.

2.  The RowSource property of the referencing combo box should be an SQL statement which again returns the values of another relevant table's primary key column from which a value is to be selected, but in this case the SQL statement includes a WHERE clause which restricts the rows returned to those in which a foreign key column matches the value selected in the referenced combo box.  It does this by referencing the latter as a parameter.

3.  To cause the referencing combo box's list to be  restricted to the relevant rows when a value is selected in the referenced combo box the referenced combo box is requeried in the AfterUpdate event procedure of the referencing combo box.

4.  To cause the referencing combo box's list to be  restricted to the relevant rows when the form is moved to a different record the referenced combo box is requeried in the form's Current event procedure.

Once you fully understand these principles you should have little difficulty applying them in a form, but the prerequisites of this are firstly that the database's  logical model, and its physical expression as tables and relationships, correctly reflect the reality being modelled, and secondly that the logical model is correctly mirrored by the forms and subforms which make up the interface.

Answer 3

From everything that I have looked at, it convinces me that I need to have this (Folder&Category) data referenced in a table together before I can do any kind of filter.

I had an unanswered question earlier, which I think is the only real way to fix this possibly. So I think if I enter 1 of each category in the form, then enter the filter code it should work. I really feel there should be a better way, rather than;

a) deleting my categories, as to be able to add them when 'NotInList' on entry,

b) manually entering them in the tables to make them related to one another, as I have been instructed not to do,

c) removing the filtering information to add the information on the form and then replace it.

Although none of this would fix the current issue of the Folder form field, as it will not allow me to make a selection from the dropdown list (even though it is visible, it beeps). I fixed the Category Name combo box in my form. I currently just have the basic select statements for both the category and folder fields.

I had a look at your NotInList database, that was where I copied and tried to adapt the code for my database. I was aware that if I got the 'NotInList' property to work, it probably should of had an error as it was in the table already. I was hoping I could somehow select the Category to filter on the folder name, without entering a kind of value list.

I had correct 'FolderID' references for my first 5 recipes in the tblRecipeSources, but the form showed no such details. I deleted the data in the table so it will mirror the form, although this did not appear to fix anything.

I am currently locked out of any data entry of the Folder form field even though neither forms nor field are 'locked'. Also all data 'allows' for forms and field are allowed. Everything looks right (to me), any ideas.

I recently opened just the Folder subform and all my details are in the header (Category subform included). The 'Detail' area appears to not be allowing anything to be displayed. This is not necessary in my case as long as everything works, which it is not.

I often see similar problems during my research but find it near impossible to find the solution which would help my issue. This is very frustrating.

I looked at alot of VBA and SQL as well as other nuts and bolts tutorials, as per your instructions. I had previously looked at literature which I had but it only seemed to cover the basics, which is not covering my issues. I did feel like it was at that stage that I was maybe relying on your help a little too much, I apologise for that. I do just want to learn what I need too, to be able to get me database working right.

I believe I found some useful tutorials on appending, although not trying this out yet.

I have also found some tutorials which might become handy in the future with other projects.

Thank you for calling me 'commendably honest', you are very polite.

I hope this all makes sense. I have done alot of learning and do not feel like I have gained anything to help me over my current hurdle. I await your assistance as I am stuck.

Feel free to let me know of any specific sites you think may be useful.

Answer 4

Forms mirror the underlying logical model.  On the basis of the image of the relationship widow which you posted, tblRecipes references tblRecipeSources, which is referenced by tblFolders, so tblRecipeSources is modelling a many-to-many relationship type between tblRecipes and tblFolders.  When inserting a new row into tblRecipes therefore, one or more new rows have to be inserted into tblRecipeSources.  This would be done by means of a subform bound to tblRecipeSources.  In this subform a combo box would be bound to the rsFolderID column.  In this combo box an existing folder can be selected from the list, or a new row can be inserted into tblFolders by invoking the control's NotInList event procedure.

In your relationship window as last posted here, however, the design of tblFolders  is clearly wrong.  There is at present a many-to-many relationship type between tblFolders and tblCategories, modelled by tblCategoryCardNumbers, but there is also a fCategoryID foreign key in tblFolders.  The question therefore is whether there is a many-to-many relationship type between tblFolders and tblCategories, or is there a one-to-many relationship type modelled by the fCategoryID foreign key?  

Whichever is the case the NotInList event procedure of the rsFolderID  control in the folders subform cannot insert a row into tblFolders transparently.   It must open a form base on tblFolders  in dialogue mode.  This form would either contain a subform based on tblCategoryCardNumbers, or a combo box control bound to the fCategoryID column, depending on the answer to the question raised in the previous paragraph.

As regards sites which you might consult to improve your knowledge of relational database design I'd suggest starting with Crystal's tutorial at:

http://www.accessmvp.com/strive4peace

As its title suggests, it is an introduction to the basics of relational database design in MS Access, but it will give you a good grounding.  The important thing is that you gain a good understanding of the principles so that these can be applied to your own databases.  I won't pretend that the learning curve is a shallow one, though.  Relational database design is a complex subject, with a long history of development since Codd's first paper in 1971, and a vast academic and technical literature.  You don't need to become a second Chris Date of course (few of us ever will get anywhere near that level!), but you need to get beyond the nursery slopes if you are going to develop applications beyond the trivial; and yours is far from trivial.

Answer 5

I have deleted 'fCategoryID', thanks for that.

I have read some useful information in the link you supplied and am sure too in future, thanks. I have also obtained some more hard copies of literature which I am hoping I can learn even more from, although it will take a lot of time to get through.

As I checked through information in my form referring to what you mentioned in your last post. I noticed that my subforms 'Record Source' even though referencing the correct tables was doing so in a query. I have updated these to 'tbl' names only, the Folder field now works properly.

With the above fixed, I went to 'tblCategoryCardNumbers' and entered the recipe folder and category combinations availables (for 2 complete folders). My dropdown lists now show all the relevant values (for 1 folder only - as it should). I had duplicate listings so I made the SELECT 'DISTINCT'.

Unfortunately as I expected when I put the SQL statement back in my Category field in my form for the Row Source filter, it went back to as if the requery was not there. I think the issue is the requery.

I put the requery code back in, although I currently have the category name field showing only 1 folders selections regardless of folder chosen. Also it is replacing the category chosen on all that folders category fields throughout all records. As the category field is showing the same list regardless of folder selected, it is allowing incorrect matches of data. I have just also found that I am able to 'Refresh All' to get the correct list showing for the folder selected on the current record, but it then deletes the category info which was selected with the previous folder on the other records and now shows the current list for the newly refreshed folder, which is incorrect.

I have been trying endlessly to find answers to fix my cascading combo boxes but unfortunately after so much reading, I feel I am no closer to finalising this issue.

Do I need to repeat the Requery to synchronise the 2 combo boxes in my Category 'AfterUpdate' or subform 'On Current' event aswell. I have tried various things and have not yet been successful in achieving the desired result (to get it to only show the Categories for the selected Folder in the previous field on each record).