How can I populate Subform efficently?

[AlvaroCity]

Hello there!
I am working on a database and I bump into a huge problem.
I have 2 tables: Dispatches Notes and Invoices and both are linked together in another table "tblDispatchNotesInvoice"
On the form in order to develop the invoices I choose the "dispatch notes ID" using a combobox with a specific criteria depending on the Client and the "InvoicesID" with the main form.
The outcome its shown on the Subform: Table "tblDispatchNotesInvoice"
Link Master fields: "InvoiceID; cboDispatchNoteID"
Link Child Fields: "InvoiceID;DispatchNoteID"

The problem is when I want to populate the subform .
First of all I have to type manually the "InvoiceID" from the Subform when this should be done automatically.
And then the rest of the records are cleared so I can just see the records that are introduced at that precisely moment.
If I reopen the form, All the records are displayed and I check the table "tlbDispatchNotesInvoice" and all the records are perfectly introduced.

I think I have to write some code to requery or to do something but I dont know what it is....
Please If you need more information, let me know.

This video shows my database and the problem much more clearly.

ww.youtube.com/watch?v=bTbcexLFR6E

**Please add an "w" to get the link to youtube

Please note that my database is in Spanish. Some extra help to understand it "Albaran" is "Dispatch Note" and "Factura" is "Invoice" (Everyday is School day )

I hope you can help me.

 

[AlvaroCity]

Dear Unlce Gizmo

The invoicing system that I am developing is quite different. This is for a manufacturing company so for every dispatch note there are a number of "parts" already selected.
There is not possibility that a client gets his items or parts before creating a dispatch note.
The process is the next one:

Client sends order of parts--> Production ---> Creation of dispatch notes and we deliver the parts ----> we gather a few dispatch notes and create an invoice.

It would be also very interesting if I could show the Dispatch note number and the parts number and description at the same time on the subform.

Depending on the dispatch note, all the parts automatically will show on the invoice report.

thank you

 

[jdraw]

It might be useful to readers if you would post a jpg of your tables and relationships.
Based on your posts I see the following processes generally

Customer--->Order-->production--->parts-->Completion-->dispatchNote(s)--->Invoice--->Customer

 

[AlvaroCity]

The subform is the tlbAlbaranFactura which is a table that combines both, the dispatch notes and the invoices.

Here there is a pic of the relations (Sorry, its in spanish....) Pedidos are orders, PedidoDetalle is Orders Detail, Piezas are parts, Albaran is DispatchNote, Factura is Invoice) Sorry for the inconvenience.

 

[AlvaroCity]

The subform is composed of table tblAlbaranFactura (tbl Dispatch note Invoice) so I have used the following links:
Link Master fields: "InvoiceID; cboDispatchNoteID"
Link Child Fields: "InvoiceID;DispatchNoteID"


or do you think i should modify the tlbDispatchnote (TblAlbaran) adding FacturaID (InvoiceID) to the table?????

If I created a table merging both tables was because 1 invoice can have lots of dispatch notes and I didnt know how to manage the forms and the tables to be consistent.
Do you think i could send you the database?

 

[AlvaroCity]

Hi there.
Unfortunately I have done this just by my self as I don't have any other support, only a 1200 pages manual (which I read).
I had a lot of headaches when deciding whether or not I put ClientID in different tables. I took that decision because I consider that, for example:

-Orders need to be identified by which client sends that Order.

-In table Parts, I put ClientID to classify the parts according to the clients without using a query or anything that would depend on the precious orders. something independent from the rest. This was because It could happen that a client sent a drawing of a part not linked to any order yet.

-I also put ClientId within the DispatchNote table in order to choose which Client belongs that DispatchNote to as well as InvoiceTable

-And lastly I put ClientId in Client table although this one is not displayed on the screen shot.

Please let me know If you think I should have done it in a different way.

Thank you