Form error and database advice

[gaby424]

Hello everybody ! This is my firs post.
My experience with access is 2 weeks ...internet tutorials.

I`m an young architect who need to records different suppliers that sell differend things in the construction industry(like materials/finishes or maybe just services).
I like to make a small database to keep all the info I get, organised.

The scenario is that I visit a lot of architectural materials exhibitions where I get a lot of brochures from different suppliers.
Usualy they sell materials of different brands and/or they sell services like installing that kind of materils.

So I have a table tblSuppliers and a table tblBrands.
Like the Supplier X sell bricks which belong to the Brand Y.
A supplier can sell a lot of brands or a brand can be sold by a lot of suppliers.
Also a supplier can sell services (which can`t be associated to a brand).

The info(materials and services) I received from these suppliers brochures came in a very big variety so I`ve made a structure 3 levels deep so I can categrize everithing in this industry. These 3 level deep are made from 3 tables

tblCategories
tblSubcategories
tblChapers

ex:
Finishes>Walls>Wallpapers
Materials>Masonry>Bricks

The info I want to record (materials and/or services) is placed in another table tblArticles. Here i have a mix of foreignKeys from all the other tables.
The way i record things is through a form that has 3 dropdowns to identify
where the info belogs to. These 3 dropdowns map those 3 tables

tblCategories
tblSubcategories
tblChapers

the info in those 3 tables is read only (the user will not expand it). It is just a lookup procedure.

Also in this form I have a fields for Supplier Name and For Brand Name.
Here the user should insert new entries and associate those entries in the Article form. So I wish something like a text field for imput new things but also like a dropdown for "lookup" if those values are already in tables from another article(like a Brand that is already sold by a supplier from another article or a supplier which is already recorded to provideing another services).

The last thing I have to say is that an article can have the info very limited so I like to give the user the posibility to enter just what he has (like just the name of the supplier and just the first level of organization...ex:Supplier Z sell Finishes(level 1 in my structure))
I attached the schema of my tables.


Is it ok?

Also I attached the database.

Can anybody help me in the form frmArticle (which record in the tblArticles) to setup an working mecanism for inserting the things for Suppliers and or Brands like I discrabed above(if they exist to have like a lookup thing and if they aren`t to create new entries)



Thank you

p.s. excuse my poor english

 

[Pat Hartman]

Brands and suppliers need to be combo boxes on the form. Remove the two subforms.

If you want to allow people to Add new brands and suppliers as well as chose from a list of existing ones, use the NotInList event of the combo. Newer versions of Access allow you to specify a form to open. There are code examples in help.

Personally, I don't like using the NotInList event to allow people to add new entries. I believe it defeats one of the main benefits of using combos because it allows lazy people to type in a new value rather than searching a little harder for the correct existing one. In my apps, I have separate forms that only admisistrators can get to that allow code table maintenance. Of course, you may decide that your users do need to add items on the fly and can be trusted to not create duplicates.

 

[gaby424]

Hello, and thank you for your advice.
I will try to study this event you say (Not in List)
I trust me and my girlfriend to add new entries (we are the users)
I`ll come back if I can`t do it alone

thx again