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

1. I just left it the way it was when I created the subform from that table. I removed the Control Source field, and the field just reads as Unbound. Unfortunately then as I viewed each form they all had "1" as their MainRecipeReferenceID.

1.  When I have all my information in I just want it to look neat and complete, not like it is waiting for extra information. I don't find in Datasheet View (like Excel), but would prefer the form to be cleaner.

2.  Is there a way to set up a filter for 1 combo box once a selection has been chosen from the previous, from the information in the tables and not that in the form. As I have all my categories and their folders listed in their tables but have not input recipes for them all yet. I think my joins and subforms makes this more complicated.

3.  I would just like to confirm that you would generally do my form off a query with the 2 subforms? (for continuous entry)

4.  Also you would do the bulk of your data entry through forms, right?

1.  MS Access is a relational database management system, not a spreadsheet; you can't expect them to behave in the same way.  If you hide the empty row by setting the AllowAdditions property to False, you will then have to include a way for the user to reset it to True when they want to insert a new row into the subform, and then back to False after the new row has been inserted.  For this you'll need to write the VBA code.  However I'd still question the need to do this.  The cleanness of presentation which you are aiming at is more a matter for a report than the forms interface.  In due course I would envisage your primary medium for the presentation of the recipes being reports rather than forms.  Reports allow you vastly greater flexibility in the control of the layout than a form allows.

2.  You can only correlate combo boxes in a form.  It is done by referencing the first combo box as a parameter in the RowSource of the second, and requerying the second in the AfterUpdate event procedure of the first.

3.  Whether the parent form or the first subform is based on a table or query is not relevant to this issue, but forms are almost invariably based on a query rather than a table, if only to return the rows in a desired order.  The key thing here is that the second subform is based on a query which correlates it with the first by referencing the key of the first as a parameter.  The underlying mechanism is exactly the same as that for correlation combo boxes described in the preceding paragraph, but at subform level rather than control level.  Note that none of the queries need to be saved as querydef objects, the RowSource property can simply be the SQL statement of the query.

4.  Not the bulk of it, ALL of it.  Never enter data directly via a table's datasheet.

Answer 2

I would just like to confirm that you would generally do my form off a query with the 2 subforms? (for continuous entry)

I wanted to comment on this point. I disagree with Ken slightly here. I don't use queries as the Recordsource of forms too often. I will use them generally if I want to restrict the form to a certain set of records. For example; an app that handles sales and I want a salesperson to see only their own sales. I may also use a query when I only want to include a subset of fields. Or if I'm including some calculations that are better done in a query. 

On the other hand, I almost always use queries as the Recordsources for reports. As Ken suggested if you want to present a clean view of the data to the user, a report may be a better tool. 

And I strongly agree with Ken that users NEVER get to see or interact directly with tables. All interaction should be done through forms.

Answer 3

I apologise as I had a word wrong in my last post, which may have confused. 3. should have read 'I don't "mind" in Datasheet View (like Excel), but would prefer the form to be cleaner', not "find".

1. I have not looked at reports yet but will trust your knowledge and I will not worry about control buttons for unlocking my subforms for editing. Thanks for the incite.
2. I had tried the correlation of fields, but it didn't work I think for several reasons the main being I had it the wrong way around.

I need to have at least one record entry of each combination in the form first though right?

I have this;

          Private Sub cboFolder_AfterUpdate()

          Me.cboCategory.Requery

          End Sub

I have the below in the Category Name:, Row Source field. The Control Source is ccnCategoryID. As I don't fully understand it, I can't get it right. Please advise.

          SELECT tblCategories.cCategoryID, tblCategories.cCategoryName, tblFolders.fFolderID FROM tblCategories, tblFolders WHERE (((tblFolders.FFolderID)=[Form]![sfmFolder]![cboFolder])) ORDER BY tblCategories.cCategoryName;

3. I was thinking of table entry for extra information of important data; such as description, type and notes. But I guess I can make a form for these extras.

Answer 4

The tblFolders table should not have a fCategoryID column.  It is related to tblCategories by means of the tblCategoryCardNumbers table, which resolves the many-to-many relationship type into two one-to-many relationship types.  Consequently you need the latter two tables in the query, not tblFolders.  The SQL statement would be like this:

SELECT cCatergoryID, cCategoryName

FROM tblCategories INNER JOIN tblCategoryCardNumbers

ON tblCategories.cCatergoryID = tblCategoryCardNumbers.ccnCatergoryID

WHERE ccnFolderID = [Forms]![YourParentFormName]![sfmFolder]![cboFolder]

ORDER BY cCategoryName;

BTW note that I've maintained the misspellings in cCatergoryID and ccnCatergoryID.

Which description attribute are you referring to?  I see two, one for recipes and one for ingredients.  Notes is an attribute of recipe ingredients, so would be entered in a recipe ingredients subform within a recipes parent form.  Type is an attribute of ingredients, so would be entered in an ingredients form.

Answer 5

1. The Category spelling had been corrected. Thanks for noticing.
2. Ken, that SQL statement cleared some things up for me. Thanks.
3. During the process of fixing issues with these fields I have lost the Category Names which would come up for its field list.

I believe I would need to enter the details into the field and have it add it to the list to link with the folder which was selected.

I have attempted to 'append' previously, with no success, when it came to the actual append stage. It may have been a structure issue at the time, not sure.

I am currently getting the message 'The text you entered isn't an item in the list.'

I tried adapting the below, but again I guess I don't have proper knowledge of how to make it work for me. Not sure if it is an issue with the information already being in the Category table, but I am not getting any message to confirm that. Please advise?

   Private Sub cboCategory_NotInList(NewData As String, Response As Integer)

    Dim ctrl As Control

    Dim strSQL As String, strMessage As String

    Set ctrl = Me.ActiveControl

    strMessage = "Add " & NewData & " to list?"

        strSQL = "INSERT INTO tblCategories INNER JOIN tblCategoryCardNumbers ON tblCategories.cCategoryID=tblCategoryCardNumbers.ccnCategoryID _

               (cCategoryName, ccnFolderID) VALUES(""" & NewData & """," & Me.cboFolder & ")"

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        CurrentDb.Execute strSQL, dbFailOnError

        Response = acDataErrAdded

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

End Sub

' { I added the "ON" information to inform of the joining fields, but it is returning a "Compile Error: Expected:end of statement" message on the beginning of the 2nd line of the strSQL}'

4. Regarding the description attributes, I was just pointing out the general type of extra information I would probably need to enter at some point.