Datasheet input in Form with Foreign Key and extra table

[adbie]

I have following erd schema and like to insert data into the tables via a form.

vb. Following Test Data
- ProductId: 5(auto), Productcode: PC0001, Productname: PC 1
- BatchId: 1(auto), ProductBatchId:5, Batchnumber: 45797
- ProductinfoBatch: 1(auto), batchId: 1, ProductinfoId:15
- ProductinfoId: 15, Parameter: hdd, Specification: 80GB

Can someone take a look?

 

[RainLover]

So where are you having problems?

Your design appears to be ok

 

[adbie]

Dear,

I don't know how to combine the information in a form & subform to store in the db.

 

[RainLover]

What have you got so far.

Can you post a copy of your Database.

You obviously have done some research otherwise you could not have come up with that design.

Have you looked at the Northwind Database as supplied by Microsoft.

PS If you are using 2007 please convert it to 2003 as I do not have 2007.

 

[adbie]

I also use Office 2003.
My currently version: http://rapidshare.com/files/250801604/db.zip.html

 

[RainLover]

You can't enter information into tblBatch until you have information in tblProduct.

This is because you have Referential Intregity Enforced, which is correct.

So put some information directly into tblProduct and try again.

 

[adbie]

Dear RainLover,

Yes I know therefore I created on the form an append query
but I think the problem is sitting in the 'sub' table like tblprodinfobatch
where the following info must be input prodinfobatchid: 1, batchid: 1, prodinfoid: 1,2,3,4 each on another line.

I tried with an append query(qryaddproduct) 'btnProduct Information' but it doesn't work
and I also prefer if possible, working with a datasheet to put the information in. But how?

Thx in advance

 

[RainLover]

When you enter something in "cboProductBatch" you need to invoke the Event "Not on List"

Do a search on this event.

What should happen if it is not on the list is to open a form to allow you to enter that value.

I don't believe the query you suggested is the correct way to go.

 

[adbie]

Via the append query I add a product record. But how do I
insert product information in the form (currently always get an error - work via query).

 

[RainLover]

I need to write someting for you.

Please tell me where are you from.

I don't recognise the language you are using.

 

[RainLover]

Try this.

Enter something in the cobmo box and follow the instructions.

What I have written is very rough.

You need to clean it up.

 

[adbie]

Wow, it looks great (subform) but after I click on the door (close)
I get an error: The text you entered isn't an item in the list. Select an item from the list, or enter text that matches one of the listed items.

but the information is added in the table.
(Language programmed Dutch)

Can you also take a look at the tab's? (like product info, physical ....)
I like to put some information in it but I always get an error: Field Cannot be updated

thx

 

[RainLover]

First things first.

if you type AA in the main form you must type AA in the secod otherwise you will get the error you described.

Test this.

Also there are other ways of treating this problem.

But let us go one step at a time.

Please post back after testing.

 

[adbie]

works perfect now!
maybe the error can be alterted in a msgbox?
now to stap 2? Lookup productcode and / or productname & editions of both?


thx

 

[RainLover]

< Lookup productcode and / or productname & editions of both? >

Where do you want to do this.

And what do you want to do with the information.

Add it to a record or find a record based upon that value.

 

[adbie]

Hey,

please take a look in frmFormlookup.
I like to select one of the 3 critera to display all the information below the form.
If there are more batchnumbers for 1 'productnaam' (productname) I like to get an overview of all this batchnumbers linked to 'productnaam' and then I can select the one I want.

Is that possible to build in Access?

Cheers

 

[RainLover]

Where do I find frmFormLookup ??????????

 

[adbie]

I forgot to attach the file sorry,
http://rapidshare.com/files/252201800/db.zip.html

 

[RainLover]

http://www.utteraccess.com/forums/s...=482015&page=0&view=collapsed&sb=5&o=&fpart=1

Look at this