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 materials.
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

[ssanfu]

Welcome to the forum

I do not have A2K10 so the attached mdb I created is in A2K format. Is this what you are wanting to do???? You should add a few more suppliers, brands, categories, etc

[gaby424]

Hy ssanfu
THANK YOU!!

Your example is a big step in what I`m try to acomplish I own you!

I attached the rearanged form in your database file.
Also I made a picture with some questions I have. If you can give me some advice about them.

Again , thank you for your time!

Edit later:
I fixed a small thing to make the 3 combo working by adding this in the event text:

Private Sub Form_Current()

cboSubcategory.Requery
cboChapter.Requery

End Sub

Also renamed some comboboxes and some TblArticle parameters (small letter errors in the CategoryID and SubcategoryID parameters)
I mention this just to know what i modified inside your file
Re-uploaded the database

[ssanfu]

OK, here we go

I fixed a small thing to make the 3 combo working by adding this in the event text:

Hmmm, shouldn't have to use the Form_current event since the combo boxes are bound to the record source.

Also renamed some comboboxes and some TblArticle parameters (small letter errors in the CategoryID and SubcategoryID parameters)

They weren't errors. That's the way I roll!!
The 3 prefix letters are typically lower case. It makes it easier to read the control name, IMO. (Google "Naming conventions)
The prefix letters usually tell you what data type the object is.
Having said that, it is your database and your naming convention, so you can do what you want!

Also I made a picture with some questions I have. If you can give me some advice about them.

Man, you are really working me!!! But it never hurts to ask. HaHaHaHaHa
Check the attachment

The reason nothing happened when you added a new Brand then clicked the button is that the new record wasn't saved. You can tell by the pencil at the left of the record. Fixed now.
Moving the buttons off of the subforms to the main form.... Done
Removing the black lines.... called Dividing lines (on the Form property dialog box - Format Tab) between records.=>> Subform Brand has no lines, subform Suppliers has red lines. (Well you didn't specify the color the line should be)



BTW, in the forms/subforms, if you see a control with a yellow background, that is how I know that the control is not visible.
In the subforms (and the two controls on the main form), the PK/FK fields should not be visible because they are autonumbers..... and NOT meaningful data.! They provide NO information to anyone except the programmer. But they do need to be on the form/subforms for linking purposes.

[gaby424]

OMG, thank you!!!
You are right I`m a newbe I have to read more about those DB things (like naming hahaha)

Now I`ve got ideas. Let me study your improved database so I can insert a little more data(one day is not enough... for a baby mind). If you are bored to wait for me, I don`t mind to help me more You are the steroid man!!

I will write what is going in my mind abott this BUHAHAHA

I`m thinking that the ContactPerson in the TblSupplier to be just an FK related to a TblPerson
You know, in these days networking with people is important. So maybe it`s a good ideea when I insert a contact person from the suppliers to also make an contacts list from their names and related person info(like birthday and stuff like that).

That means that inside the sfSupplier I will try to nest a sfSupplierContactPerson that should look from the start like a form for inserting directly a new entry.
Now I have to say , I found on internet that the iPhones have a specific number of parameters for the contacts list. So I`ve made a the tblPerson to map those knowen parameters.

Now i will make a querry to only pick the work info for a person. Like qryPersonWork. So my sfSupplierContactPerson will be based on this qryPersonWork.
Also, because the numbers of parameters inside the tblSupplier become a little big (as a number), I tested (for the first time) inside the sfSupplier the "TAB" feature. Which looks like an wonderfull tool to save space. So I`m thinking that sfSupplier should have 3 tabs.
tab1: Supplier general info
tab2: Contact Person 1
tab3: Contact Person 2

In each of the contact tabs (number 2 & 3) i will try to nest an instance of sfSupplierContactPerson.
So every time I add a supplier, i also add 1 or 2 contact persons which will populate in fact the tblPerson. I will put a WhereFrom parameter in the tblPerson which will have a default value like "Construction Industry", only if that Person will be added via sfSupplierContactPerson nested form. So this way, my tblPerson will have persons that are from Construction Industry or that are Relative or that are Friends, depends from which form i add the entry...etc.

Is this even posible? I have to give it a try to to see what can I handle
Lol.... I have to stop thinking !This database structure is exapanding to fast

Anyway, I`ve uploaded the under construction situation And some pictures for the lazy lurkers

Thx for reading this! I`m moderator on an other forum where I usually give the hints (architecture software related). Now I realy feel weird to only put questions (almost zero know how here..). Hopely I can give back my knoledge sometime in the future, here too !
Cheers man.