Solved Creating transaction records via OnClick - (Sales Order to Invoice conversion)

[DJL910]

I am updating a legacy Product Management Database to include Sales Order and Invoicing functionality.

I am struggling on how best to create the invoice detail records (converting SO detail records to INVC detail records) and could use some advice on approach and/or specific code to include in my onclick event.

Background
I created a header and a detail table for Sales Orders and a form to manage a given Sales Order (Frm_SalesOrder). It includes a pop-up sub-form (AddForm_SODetail) linked by SalesOrderID so the user can add/edit/save the SO detail (select part number, add quantity, confirm price, etc.). Similiarly, I created a header and a detail table for Invoices with a form to manage a given Invoice (Frm_INVC) and a sub-form to manage the detail (SubForm_INVCDetail).

On my SO form, I have added a "Ship & Invoice" button (Button_CreateINVOICE) with an On-Click Event to open the Frm_Invoice in add-mode and pre-populate various Invoice Header values (PK, FK and other invoice header constants that pull from the given Sales Order), thereby linking this new Invoice Header record to the open Sales Order. Where I have existing INVC detail records, they display correctly. SO, THIS ALL WORKS AS INTENDED!

Now, I am trying to add functionality to the Button_CreateINVOICE so that it also creates the required Invoice Detail records
> I want to take the SalesOrder Detail records tied to the Invoice's SalesOrder and create each of them as an Invoice Detail record, but only if if there are items still to be shipped on a given line (Tbl_SODetail.SOd_QtyOPEN > 0)

My first thought was to build an Append Query with the relevant fields and then execute that query within the Button_CreateINVOICE onclick event. However, I'm not sure how to add the FK (InvoiceID) to these new records. So, I am wondering if it's better to enter VBA code directly into the OnClick to bascially match the Append Query and update the InvoiceID, using the already defined variable (NextInvID) in the on-click procedure. I looked at the SQL of the append query with the INSERT / SELECT / FROM / WHERE statements, but wasn't successful at getting my syntax correct.

Does this approach make sense? Any suggestion on how to write the code? I'd be grateful for any advice.

GOAL:
A. Grab the associated SO_Detail records for the SalesOrderID selected in the current form
WHERE [Tbl_SODetail].[SOd_SalesOrderIDREF] = [Frm_SalesOrder].[SalesOrderID])
AND [Tbl_SODetail].[SOd_QtyOPEN] > 0.

B. Populate several fields
InvoiceIDREF = NextInvID (this is a variable created earlier in the code to populate the Invoice Header's PK [InvoiceID])
INVCd_SKU = [Tbl_SODetail].[SOd_SKU]
INVCd_PricePP = [Tbl_SODetail].[SOd_PricePP]

A few other comments:
> My SO/Invoice forms are tied to queries (i.e. SO_HeaderQuery, SO_DetailQuery, etc.) and not the actual tables so that I can pull in other associated data.
> I have separate SO and Invoice tables because I create several invoices against a given Sales Order.
> I know I could name my PK/FK the same between tables, but I didn't.

My tables/fields
Tbl_SOHeader
PK = SalesOrderID
Tbl_SODetail
PK = SOd_LineID
FK = SOd_SalesOrderIDREF (links to Sales Order Header)
SOd_SKU
SOd_PricePP

Tbl_INVCHeader
PK = InvoiceID
FK = SalesOrderIDREF (links to Sales Order Header)
Tbl_INVCDetail
PK = INVCd_LineID
FK = InvoiceIDREF (links to Invoice Header)
FK = SOd_LineIDREF (link to Sales Order Detail)
INVCd_SKU
INVCd_PricePP

 

[XPS35]

Why do you want to save the same data twice? An invoice is nothing more than a message to a customer that he must pay. You can compose that message based on the sales order and its details.

 

[DJL910]

Why do you want to save the same data twice? An invoice is nothing more than a message to a customer that he must pay. You can compose that message based on the sales order and its details.

An invoice is a record of a shipment, not just an order. I'm not saving the same data twice because the data can be different. Example: Customer A orders 5 units of Product X. On Day 1, we ship and invoice 2 units at price A+5% for same day-turn around. On Day 18, we ship and invoice 2 additional units at price A. There is then one remaining unit of X to be shipped.

 

[plog]

Agree with XPS.

Further I don't know if this is a data issue or a form issue. You can't flesh out how the form is going to work until you know how the data is going to work. Help me understand the data part of this:

I have separate SO and Invoice tables because I create several invoices against a given Sales Order.

That's fine, but how do your details work? 2 tables--one for invoices and 1 for sales? Can we see a screenshot of the relationships of the relevant tables along with all the fields in them?

 

[DJL910]

Agree with XPS.

Further I don't know if this is a data issue or a form issue. You can't flesh out how the form is going to work until you know how the data is going to work. Help me understand the data part of this:



That's fine, but how do your details work? 2 tables--one for invoices and 1 for sales? Can we see a screenshot of the relationships of the relevant tables along with all the fields in them?

I've confirmed the data part is working - I built the tables, pre-populated invoice detail data and it shows up, as required in the forms.

The 'data part of this' was described in my original post listing the tables, fields, primary/foreign keys. But to clarify in words:
> SalesOrder Header table and a SalesOrder Detail table, in a one to many, by SalesOrderID
> Invoice Header table and a Invoice Detail table, in a one to many, linked by InvoiceID
> Invoice Header links to Sales Order Header, in a one to many, by SalesOrderID
> Invoice Detail links to Sales Order Detail, in a one to many, by SOd_LineID

The relevant fields are included in the original post.

My issue is how to write the VBA code to define an INSERT/SELECT statement that mirrors the append query AND uses a dim variable to define the record's foreign key.

 

[pbaldy]

Does this help?

Microsoft Access tips: Duplicate the record in form and subform

How to duplicate the current record on a form in a Microsoft Access database, along with the related records in the subform.

allenbrowne.com

 

[plog]

> SalesOrder Header table and a SalesOrder Detail table, in a one to many, by SalesOrderID
> Invoice Header table and a Invoice Detail table, in a one to many, linked by InvoiceID
> Invoice Header links to Sales Order Header, in a one to many, by SalesOrderID
> Invoice Detail links to Sales Order Detail, in a one to many, by SOd_LineID

The data part isn't correct. You've got one big circular relationship. That's not right. There should only be 1 way to trace a path between tables. From InvoiceHeader to SalesDetail I can either go clockwise thru SalesHeader or counterclockwise through InvoiceDetail. How are you to know which of those 2 tables to use in a query when you need to JOIN InvoiceHeader to SalesDetail?

Back to the data moving part. Do your InvoiceDetails and SalesDetails tables share 90%+ of the same fields? Do the header tables? APPEND queries are usually a hack around tables that shouldn't exist. I don't think the data part is fully fleshed out. This form issue needs to be set aside until you get the data right.

 

[DJL910]

Hi Paul, so I tried a modification of the above code, but throw a run-time error '424' Object Required on the db.Execute strSql, dbFailonError.

   Dim NextInvID as String
   Dim strSql As String
   NextInvID = 'code not shown but it generates a value 24V-#####
   strSql = "INSERT INTO [Tbl_INVCDetail] ( InvoiceIDREF, INVCd_SKU, INVCd_PricePP ) " & _
            "SELECT " & NextInvID & " As InvoiceIDREF, SOd_SKU, SOd_PricePP " & _
            "FROM [Tbl_SODetail] WHERE ((SOd_SalesOrderIDREF = " & Me.SalesOrderID & ") AND (SOd_QtyOPEN > 0));"
    db.Execute strSql, dbFailOnError

I used the immediate window to grab the result of strSQL (pasted below). When I pasted this into the SQL section of a new query, I'm prompted for the two orange pieces (NextInvID) and (Me.SalesOrderID). When I enclose them in " " then the Query runs and generates the correct result set.

SQL string: INSERT INTO [Tbl_INVCDetail] ( InvoiceIDREF, INVCd_SKU, INVCd_PricePP ) SELECT V24-10262 As InvoiceIDREF, SOd_SKU, SOd_PricePP FROM [Tbl_SODetail] WHERE ((SOd_SalesOrderIDREF = SO-100005) AND (SOd_QtyOPEN > 0));

So, I suspect I have to correct the syntax in my code around these two values so that it populates correctly. I've tried a few derivations with added " ' " and "" but without success.

 

[pbaldy]

You did not declare or set the db variable. You could use CurrentDb instead.

Edit: Or the DBEngine(0)(0) that Allen used.

 

[DJL910]

.... Added the CurrentDb, and then enclosed the two fields with a set of " ' " so the SQL statement read correctly. Put a save in to create the header detail record so that the children could be populated, and it now works great. Thanks for the help!

   Dim NextInvID as String
   Dim strSql As String
   
   DoCmd.RunCommand acCmdSaveRecord

   NextInvID = 'code not shown but it generates a value 24V-#####
   strSql = "INSERT INTO [Tbl_INVCDetail] ( InvoiceIDREF, INVCd_SKU, INVCd_PricePP ) " & _
            "SELECT " & "'" & NextInvID & "'" & " As InvoiceIDREF, SOd_SKU, SOd_PricePP " & _
            "FROM [Tbl_SODetail] WHERE ((SOd_SalesOrderIDREF = " & "'" & Me.SalesOrderID & "'" & ") AND (SOd_QtyOPEN > 0));"
    Currentdb.Execute strSql, dbFailOnError

 

[pbaldy]

Happy to help!