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.

 

[Uncle Gizmo]

I think you should reflect on your structure a bit. An invoicing system is very common in MS Access . The northwind sample database shows an invoicing system, although some versions of it are a bit unreliable! Not be used out of the box for invoicing as it were. In the normal business environment, where you would create an invoice in a paper-based system, you would grab a sheet of paper write in the customer's name, the items purchased, the total and that's it! you've got yourself an invoice. Translate this into the digital environment and MS Access you would start off with an invoice table, this would automatically generate your invoice number. In it's basics form you just add the item purchased. In a more advanced form you'd have a line item table which would store the details of individual items so you would be able to use your invoicing system to sell multiple items in Multiple quantities. I think that's basically the north wind sample. You could add an extra sub-form to this to store the dispatch notes.

So you need a main form which provides the invoice details;- Invoice number, date, customer, maybe ship via and one or two other things. Then on this main form you would have a subform which displays line items, and in your case you could have another subform to display dispatch notes. Taking this route in creating subforms means you don't have to wire anything together with VBA code, it is automatically done for you by MS Access.

 

[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

 

[Uncle Gizmo]

I didn't see the video... Looking at the video now I can see you have what looks like a subform on the main form "frmFactura" and from your information the main form is basically the invoice. The subform is the "despatch note" ... The dispatch note should have another field, a field called "Facturanumero" corresponding to the field on your main form named "Facturanumero" If you give the fields the same name, the Field on the main form and a field in the sub form then I think MS Access will link them together for you automatically when you create the form and subform combination.

 

[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

 

[Uncle Gizmo]

If you follow the link to the YouTube video https://youtu.be/fPq5H8nyNtc?t=1m38s you will see one way of manually linking your main form and subform together. The video also points out an issue which is not obvious until you've done it a few times!

 

[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?

 

[Uncle Gizmo]

I don't think you should post your database to the forum because there are problems with it. I think you need to go back a step and work out exactly what you want the database to do for you. It appears that you have haphazardly added tables without understanding what they are supposed to give you. You have client ID in four separate places, only have it in two places, the actual client table and the invoice table. I can't see any reason to have it anywhere else for the moment. A good start would be to make a new copy of your database and then delete all the tables you don't need. Start off with just a couple of tables, get it working with those and then add in the other tables as and when you see how they fit together. Make sure you make up make backup copies before deleting anything!

 

[Uncle Gizmo]

Also I'm not a great believer in having relationships in the early stages of your development, unless of course you have sat down and worked out exactly how your database fits together. I don't know many people that do it this way, most people just work through it Like you are, by the seat of your pants so to speak. If you are doing it by the seat of your pants then relationships just get in the way and can actually mislead you.

 

[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

 

[Uncle Gizmo]

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

I think you are somewhere between having a complete overview of your dB and a collection of tables you are massaging in to a solution.

I think you need to get it working in a simpler configuration, and build on that.

Sent from my SM-G925F using Tapatalk