Constrain data in a subform

[acc2003]

Ok have the following database which I'm using to basically manage my sandwich labelling.

It needs to include products, their allergens (nuts, gluten, etc) and warning message if the product might include small bones (such as in chicken or fish).

The sandwich will include details of all ingredients, whether the ingredient needs a percentage weight declared and price (possibility to support multiple pricing).

In addition to that I need the type of bread used (if any as this is not applicable to wraps or paninis).

The database is to make the data more consistent (than currently happens in excel) and easier to maintain.

The final result will be a single table which lists

sandwich code, description, type of bread, allergens, warnings & price. This is the format which our current labelling software can use.

When I was creating the Product Entry Screen it was composed of the product form and an allergen subform. I don't know how to stop the subform from allowing more than one of same allergen being added. I suspect a problem in the underlying field properties but I haven't a clue how to go about doing it.

This is my first attempt at creating a database so please forgive my stupidity.

[June7]

I don't see any forms or queries or reports in that project.

[acc2003]

Thanks June7. Yes none of those have been added yet. I did have a form and subform however I removed it when the subform didn't work as intended so that I could correct the underlying field properties in the underlying table (tblProducts).

I have now updated the database to show the form and subform so that you can see what I'm trying to say. As you can see in the newly created Product (BRE001) I was able to add a Gluten allergen in the subform multiple times. I obviously only want to be able to add any particular allergen once for any given product. How can I do that?

[acc2003]

OK,

I can't attach the file as it over the size limits. Is it allowed to link to a Dropbox of the database?

[June7]

Run Compact & Repair, if still large, zip. Zip file up to 2mb allowed. Can upload to fileshare site and post link to the file. I like box.net.

[acc2003]

Thanks June7.

Here's the attachment. No chance of getting too big once zipped. Makes a big diffrence to the file size.

[June7]

Try this as the RowSource for the combobox:
SELECT tblAllergens.pkAllergenID, tblAllergens.Allergen, tblProductAllergens.fkAllergenID
FROM tblAllergens LEFT JOIN tblProductAllergens ON tblAllergens.pkAllergenID = tblProductAllergens.fkAllergenID
WHERE (((tblProductAllergens.fkAllergenID) Is Null));

Then in the AfterUpdate event of the combobox:
DoCmd.RunCommand acCmdSaveRecord
Me.fkAllergenID.Requery

This will work okay as long as you don't want to show the lookup value for the allergen ID.

[acc2003]

Thanks June that works. Not entirely sure I understand it so I guess I will have to learn a lot more of Access than I was hoping.

As regards your last comment re lookup values for allergen ID I can see you have predicted what my next question was going to be. How do I show the name of the allergen rather than the allergen ID which doesn't really mean much to anyone? Is there a way to solve that?

[acc2003]

Just interested as you have had a quick look at my database setup I was wondering if you could comment on my database design. This is my first attempt at any of this and I did do reading on the database design but haven't done much other than the basics on Access itself. Any glaring mistakes?

[June7]

The problem with filtered RowSource in ContinuousView form is that this is setting for all instances of the combobox (or listbox) on the form. There is really only the one control although there is the appearance of more than one when multiple records are displayed. This means that if the RowSource is filtered to exclude a used item, the lookup value will not be available for display, even though the ID value is in the field, the box will appear blank.

Most frequent reason for filtering a combobox is in cascading (dependent) comboboxes. In this case the blank box issue can be handled by code in the GotFocus event of the combobox to requery the combobox, which is filtered based on the value of another combobox. Your situation is different. I don't have a solution for it.

[acc2003]

Thanks June7.

I got round the prob with no allergen names simply by adding another control for the name beside the Allergen ID.

The code above proved very useful. I needed to add the requery code to an On Enter event as the After Update didn't handle deletions of allergens. When I deleted an allergen the dropdown would still exclude that allergen even though it was no longer involved in that product.

I'm glad to get that sorted as it will prove very useful on the sandwich form for adding multiple ingredients to each sandwich.

Thanks again.

[June7]

Nothing about table structure seems glaringly wrong. Looks like a good start. Glad you resolved the issues.

[acc2003]

Nothing about table structure seems glaringly wrong. Looks like a good start

Great. That's good to hear.