Passing Invoice number (1 Viewer)

Derek Girven

New member
Local time
Today, 20:04
Joined
Feb 13, 2020
Messages
10
Hi,
I am trying to pass the invoice number of my form to a table where the criteria is MakeInvoice is yes.
So all the records in the Subform of this form will have this invoice number.
I hope that makes sense ?

Private Sub Invoice_Number_AfterUpdate()
Dim strSQL As String

strSQL "INSERT INTO tblOrderFormDetails ( InvoiceNo )SELECT qryInvoiceDetails.InvoiceNo FROM qryInvoiceDetails WHERE (((qryInvoiceDetails.MakeInvoice)=Yes));"
DoCmd.RunSQL
End Sub
 

namliam

The Mailman - AWF VIP
Local time
Today, 19:04
Joined
Aug 11, 2003
Messages
11,696
the question that needs asking is WHY? You can link the subform and main form to do this for you.
Or if need be apply the invoice number into the sql of the form to achieve the same.

Storing search information is rarely a good thing :(
 

Derek Girven

New member
Local time
Today, 20:04
Joined
Feb 13, 2020
Messages
10
the question that needs asking is WHY? You can link the subform and main form to do this for you.
Or if need be apply the invoice number into the sql of the form to achieve the same.

Storing search information is rarely a good thing :(
I Have a link the subform and the main form. but I need it to pass the invoice number then to all the records in that form subform (I have a invoice number column in the subform).I just need the code or an idea how to pass that number then .can you help?
 

GinaWhipp

AWF VIP
Local time
Today, 14:04
Joined
Jun 21, 2011
Messages
5,901
Hmm, aren't the Form\Subform linked together? If so it should automatically update based on the fields that link them. If they are not then the question is why not?
 

namliam

The Mailman - AWF VIP
Local time
Today, 19:04
Joined
Aug 11, 2003
Messages
11,696
Plus you shouldnt be using Invoice number as a value in both tables, invoice number has a meaning in the day to day business .
As such it has no business meaning anything in your database other than beeing an attribute.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:04
Joined
Feb 19, 2002
Messages
42,971
If the master/child records are linked by the PK/FK which is NOT the Invoice Number, then the Invoice Number belongs ONLY in the master record and NOT in the child records. I would expect it to be quite rare that you would be looking at the invoice details separate from the invoice so the invoice number would always be in the main form or the main report and should not also need to be in the subform/subreport.
 

Derek Girven

New member
Local time
Today, 20:04
Joined
Feb 13, 2020
Messages
10
Dear Namliam I respect that is what you do but that is not what I want to achieve. so if you can help great ...!!
 

namliam

The Mailman - AWF VIP
Local time
Today, 19:04
Joined
Aug 11, 2003
Messages
11,696
There is a difference between what you want and what you should do, going against my advice and that of @Pat Hartman, this will come back to bite your rear end. You want to solve this in a query not by storing it in your table.
 

Derek Girven

New member
Local time
Today, 20:04
Joined
Feb 13, 2020
Messages
10
There is a difference between what you want and what you should do, going against my advice and that of @Pat Hartman, this will come back to bite your rear end. You want to solve this in a query not by storing it in your table.
I have a query that populates the subform working off a check box (make Invoice) .. I looked at the form /subform they are not linked by Invoice number as that seemed to stop the query working to populate the subform. I just want to save the invoice number for those records in that form. I have a column in a table that stores invoice number.
 

GinaWhipp

AWF VIP
Local time
Today, 14:04
Joined
Jun 21, 2011
Messages
5,901
Tip toeing in...

If connecting them stopped the subfrom from working then I would review the recordsource of the subform as this is the tried and true way to do this. That is not to say there isn't another way but your way suggest other issues with the underlining design.

...tip toeing out. :sneaky:
 

Derek Girven

New member
Local time
Today, 20:04
Joined
Feb 13, 2020
Messages
10
thanks for tip toes ..so the query may have a problem. I just went to your access diva page I see you had an Unbound control to subform tip would that work ?
 

GinaWhipp

AWF VIP
Local time
Today, 14:04
Joined
Jun 21, 2011
Messages
5,901
Okay, but the Invoice number should not be unbound on the Main Form as you will be creating new Invoices. My example is based on exiting employee's being linked to their to-do lists. You are creating new invoices, not a good idea to do it that way. Not only is it so far outside the box it requires a lot more work it's will cause issues down the road.

I would ask that you please explain, as others have asked, why you are doing it this way?
 

Derek Girven

New member
Local time
Today, 20:04
Joined
Feb 13, 2020
Messages
10
Okay, but the Invoice number should not be unbound on the Main Form as you will be creating new Invoices. My example is based on exiting employee's being linked to their to-do lists. You are creating new invoices, not a good idea to do it that way. Not only is it so far outside the box it requires a lot more work it's will cause issues down the road.

I would ask that you please explain, as others have asked, why you are doing it this way?
I have many orders in production and as I finish and ship what is ready on a friday , not all of that order may be complete so I invoice part order if that makes sense ?
 

GinaWhipp

AWF VIP
Local time
Today, 14:04
Joined
Jun 21, 2011
Messages
5,901
Okay, I have done this before and how you would handle that is what is invoiced gets tagged as invoiced and therefore no longer shows up on the *to be invoiced* list. Then when it does you can create a new invoice and invoice. You should have a table of the items that have completed production which in essence becomes the subform to the Invoice main form.
 

Derek Girven

New member
Local time
Today, 20:04
Joined
Feb 13, 2020
Messages
10
I have a production form that work off a query of Invoiced so If its no it shows up , when invoiced it goes out of production , the invoice list works off these,I hope that makes sense , I can attach if you have free time.
 

namliam

The Mailman - AWF VIP
Local time
Today, 19:04
Joined
Aug 11, 2003
Messages
11,696
There is never a need to store information twice, unless you go into complex things like performance and/or datawarehouse issues.
 

Users who are viewing this thread

Top Bottom