adding new record to a form

[Ayshan]

Hi

I am new to Access and I was trying to create database for chemical inventory handling

I have two tables. one of my tables (chemical info) has information on chemical properties. I used "Cas NO" as primary key. I have another fields such as "chemical name", 'product number" and etc.

In Second table (chemical inventory) i have "ID" field as primary key and I used lookup wizard in order to fill "Chemical Name" field. I thought it will be easy to pick up name rather than typing it because the names were complicated.

After I created two forms. one form is chemicals info form based on chemical info table and other one is LAb_inventory based on chemical inventory table.

I set up Lab inventory form as main form and Chemicals info form as subform. Linking field is chemical name. So that when I chose chemical name from combo box i can see inventory data in main form and chemical properties information in subform. Now i have two problems

One of them is when I add new record to my subform I cant see this new record in main form. I suppose it is because chemicals names of each form is derived from different tables. furthermore in main form instead of chemicals name i see "my primary key CAS NO".

What i want to achieve is add new record to chemicals info form and fill up all chemical information save and in the main form to add new record choose new chemical name which i just added and fill up inventory information.

I tried to use append but was not successful in implementing it.

Could someone please help me to solve this problem?

 

[Gasman]

Not an Access expert, but I think you are getting confused with your structure.

The main form will hold the parent data (chemical).
The subform would hold the children of the parent or linked data.

If you add a record to the subform, it will become linked to the record of the mainform.

Your should add the parent record first (new chemical), and then add the children to it in the subform.

That is the way I always think of main and subforms. There is a 1 to many relationship.

HTH

 

[Ayshan]

I thought it is because my data sources are different. I will try to add information vice versa as you suggested.

What about if I combine my two tables and make it one table and create two forms (parent and subform). Do you think it could solve my problem. I am trying to do it in the main and subform because user doesnt want to see all bunch off information at once.

 

[Gasman]

If the data is just one to one then I would have thought you'd be just as well to keep all the data in one table. Only you know the structure. If you have a subform the user is still going to see the data. I must admit I've only used subforms in data sheet style, but that again is due to one to many relationship.

Have you considered using tabs on one form. That way you can categorise the data items.? It is still one record just split over separate tabs. That way the user will only see data relative to the category you put the data fields in.

 

[Ayshan]

I tried to use tabs but just could not configure relationships between them

 

[Gasman]

Well I've not used them myself yet, but this site is a wealth of info.

http://www.access-programmers.co.uk/forums/showthread.php?t=112197

 

[GinaWhipp]

Hmm, you can't add a new Chemical (Subform) and then go fill out the Lab Inventory (Main Form). (In other words, you can't add to the MANY side if it does not exists on the ONE side.) You would need to reverse that and make it so you could add a new Chemical via the Combo Box from the Lab Inventory, requery and have the information show up in the Subform.