Link from access forms to Excel file

[aligahk06]

Dear All,

I have developed a form where primary key is Delivery note.
For each delivery note there is a breakdown in excel sheet i.e contents of the delivery note for the invoicing purpose.
I want to give a link from my access database to excel breakdown for each delivery No in our form.
Since Each delivery No is a record so for each unique delivery no the excel file link can also vary.
I am attaching my form for ur ref.

Please help us.

Rgds,
Aligahk06

[OBP]

Why are you using Excel for the delivery note details?

[aligahk06]

Actually each delivery note contains so many item .I have to make a breakdown for all those items in an excel sheet for billing purpose.
2. Some times i have to track the delivery not is billed or not.

[CreganTur]

Could you not move all of that data into a table in your database? That would make it much easier to work with.

[OBP]

Randy, that was the reason for my question, the detail should be in a subtable, not in Excel.

[aligahk06]

As i m a new in access so plz help me how to make a subtable as we make a breakdown in excel.
Any help or sample file is deeply welcome.

[CreganTur]

If I'm understanding your specifications correctly you should create a new table that has DeliveryNote as your PK and the other field(s) should be your breakdowns. Create a relationship between your original table and this new breakdown table and just pull in the breakdown description that matches the PK value in your queries.

Does that make sense?

[aligahk06]

You mean i have to create a query for this two table.
I have created a new table with primary key as Delivery No and rest of the fields for the breakdown.
But i don't know how to make a auery and a relationship.

Please help,
Rgds,
Aligahk06

[aligahk06]

You mean i have to create a query for this two table.
I have created a new table with primary key as Delivery No and rest of the fields for the breakdown.
But i don't know how to make a query and a relationship.
The other table is DeliveryBrkTable
Could u plz advise me a relationship That querying Delivery No can pull all breakdowns from DeliveryBrkTable.
Is it posible to make another form related with DeliveryBrkTable or we can update in one form or table.
Attachment is for ur ref.
Plz ignore the previous attachment?

Please help,
Rgds,
Aligahk06

[OBP]

You only need a query for the new table and you can use the query wizard to create it.
The primary key field of the new table should NOT be Delivery No, it should be something like DetailID and set to Autonumber. Keep the current Delivery No field and set it to Number Type Long
Open the relationships using Main Menu>Tools>Relationships, add your 2 tables and then join the 2 tables by clicking the first table's Delivery No (key field) and drag it to the second table's Delivery No field.

You then create a form from the new table's query for use as a Subform which you add to the Current Delivery Form.

[Imdabaum]

Are you in Access 2007 or 2003?

2007-Database Tools tab
2003-Tools-->Relationship

Add the tables and connect tables with common field.

[aligahk06]

Dear All,

I wont be able to feed data to my form named (DelNoteBrkForm) which is created from Delivery_Track Query.
The thing is that if i m saving my records to the form (DelNoteBrkForm) record could not be saved.
The eroor encountered was
The changes you requested to the table werenot successful because they would create duplicate values in the index,primary key,or
relationship.Change tha data in the field or field that contain duplicate data, remove the index or redifine the index to permit
duplicate entries and try again.

Actually i want to open this form with all records from the form Delivery_Track ( There is a command button )
OPenSubForm.
Suppose i am searching Delivery No 22568 and once i click on command button opensubform then it will directly redirects us to the
DelNotebrkform and show us the corresponding Delivery No and their fields.

Attachment is for ur reference.

Please assist ?

Rgds,
Aligahk06

[aligahk06]

Plz help me in finding the solution of the new thread posted with attachment.
below is the thread with updated tables and forms.