Sub or function not defined

[ChrisLeicester]

Hi All

I have a main orders form with a order items subform in it.

The order items has fields for the product, the QTY ordered and the selling price, linked to the parent form by order number.

I have an unbound pop up form which comes up if the selling price is double clicked for price adjustment.

I have a textbox in the pop up form where I can enter a new price, ie. i want to lower the usual price for that customer, and then a command button which when pressed I want to change the original selling price to the new price from the pop up form textbox.

After changing, the subform needs to be requeried so the calculated subtotal, Qty * Price can be displayed.

I thought about using an update query command as I struggled with the refresh using a macro and have tried to use vba. my code is;

Private Sub PriceChange_Click()

Dim ChangeSQL As String

    ChangeSQL = "UPDATE " & [SalesTransactionItemsTB]
    Set SalesTransactionItemsTB.RetailItemPrice = [Forms]![PriceAdjustFM]![PriceChange]
    WHERE (((SalesTransactionItemsTB.TransactionItemID) = [Forms]![NewCustOrderMainFM]![CustOrderListSub]![TransactionItemID]))

        CurrentDb.Execute ChangeSQL, dbFailOnError
        
            Me.[Forms]![NewCustOrderMainFM]![CustOrderListSub].Requery
        
        

End Sub

when i press the update button I get an error message 'Compile Error - Sub or function not defined', and 'Private sub' line is yellow.

why is this, I used the on click event to build the code

Thanks
Chris

 

[The_Doc_Man]

I think punctuation is grabbing you here. If that is actually a screen shot or cut/paste of your code, then your statement is missing continuation lines. It should probably look more like this:

    ChangeSQL = "UPDATE [SalesTransactionItemsTB] " & _
    "Set SalesTransactionItemsTB.RetailItemPrice = [Forms]![PriceAdjustFM]![PriceChange] " & _
    "WHERE (((SalesTransactionItemsTB.TransactionItemID) = [Forms]![NewCustOrderMainFM]![CustOrderListSub]![TransactionItemID]))"

The construct of "<ampersand><space><underscore>" leads to a "continuation" line where you can concatenate more to the string you are building. You need explicit quotes for each PHYSICAL line even though you have indicated there is a continuation under way. It is a quirk of VBA that you need to repeat the quotes for each continued line. Note also that there IS a space both before AND after the right-hand-side quotes for the first two lines. The space inside the quotes keeps the concatenation from running two things together that SHOULD be separate.

 

[Gasman]

I always put the space at the beginning of the line. Easier to spot any mistake when forgotten.

 

[ChrisLeicester]

Hi
Thanks
I am not really experienced in using sql so I built the update query in design view and then clicked the sql and copied what access showed.

I will copy your code and go through it line by line and try to understand it.

I did try it with a macro but kept getting a record not saved error so thought I would try the update query way

 

[Uncle Gizmo]

I am not really experienced in using sql

This video might give you a leg up :-

 

[theDBguy]

Hi
Thanks
I am not really experienced in using sql so I built the update query in design view and then clicked the sql and copied what access showed.

I will copy your code and go through it line by line and try to understand it.

I did try it with a macro but kept getting a record not saved error so thought I would try the update query way

If you created the query in design view and it runs fine there, then you could save it as a query object and try this code.

CurrentDb.Execute "QueryName", dbFailOnError

 

[Gasman]

And if you ever need to translate that sql to VBA or vice versa, then this will come in handy.

 

[Gasman]

I think punctuation is grabbing you here. If that is actually a screen shot or cut/paste of your code, then your statement is missing continuation lines. It should probably look more like this:

    ChangeSQL = "UPDATE [SalesTransactionItemsTB] " & _
    "Set SalesTransactionItemsTB.RetailItemPrice = [Forms]![PriceAdjustFM]![PriceChange] " & _
    "WHERE (((SalesTransactionItemsTB.TransactionItemID) = [Forms]![NewCustOrderMainFM]![CustOrderListSub]![TransactionItemID]))"

The construct of "<ampersand><space><underscore>" leads to a "continuation" line where you can concatenate more to the string you are building. You need explicit quotes for each PHYSICAL line even though you have indicated there is a continuation under way. It is a quirk of VBA that you need to repeat the quotes for each continued line. Note also that there IS a space both before AND after the right-hand-side quotes for the first two lines. The space inside the quotes keeps the concatenation from running two things together that SHOULD be separate.

And if you issued a Debug.Print ChangeSQL before even trying to use it, that normally would show any errors.

 

[Mark_]

linked to the parent form by order number

I do hope you mean the Primary key of the parent. Linking by a field that could, potentially, be user changed is not a good idea. Keep the links hidden so users don't try have "Bright ideas" and do things to break your database.

 

[Pat Hartman]

The others have loaded your gun and shown you how to do this the wrong way.

The total price should not be stored. It is a simple calculation and both component parts are stored in the same record. Storing it is a violation of Second Normal Form. Please do some reading on database normalization to understand these concepts better. Therefore, it should always be calculated in the query (including the one bound to this subform). And even if you insist on storing the calculated value (which I hope you will reconsider), there is no need to run an update query.

When you use a popup form that is bound to the mainform record, the procedure is:
1. Save the current record. ALWAYS do this before opening another form or report to make sure that the form/report being opened shows the most recent updates to the data.
2. Open the popup as MODEL. This stops the mainform code from running past this point. Execution pauses until the popup is closed.
3. When the user closes the popup form, code execution resumes at THIS POINT and you use Me.Refresh to show the updated value if you need to.

If the popup is not bound tell us what it is doing and I'll revise the instructions. You probably just need to have the popup put the changed unit price into the bound field on the subform.

 

[MajP]

I am not really experienced in using sql so I built the update query in design view and then clicked the sql and copied what access showed.

This is a nice helper utility from @isladogs

SQL to/from VBA Converter Add-In

This Access add-in is an updated version of my SQL to VBA And Back Again utility which makes it easy to convert query SQL to a VBA string (or vice versa). Additonal functionality includes creating example code from the VBA string and temp queries to test the SQL / VBA output.

isladogs.co.uk

 

[CJ_London]

I don’t agree with Pat

1. OP is not storing a calculated price, just a different price value

2. It may violate the SNF but where you are creating a ‘legal’ document such as an invoice it is ok to store the values because it is a requirement that the document may need to be reprinted years down the line and prices can change, discounts get modified, customers can move address etc. yes you can have a history file but that involves a lot more work and will generally perform slower with large datasets

 

[Pat Hartman]

I don’t agree with Pat

Don't confuse the "storing a calculated value" with the "running an update query" parts.

The OP said the calculated value wasn't being updated. Apparently he meant on the form. OK. Fine, cancel the unneeded lecture on normalization. That doesn't cancel the solution because poking the changed unit price into the unit price control will still cause the record to be refreshed if you follow my directions.

You already have the record open on the subform, there is absolutely no reason to run an update query to update the subform record. You just have the popup form place the replacement value in the unit price control and if the extended price is calculated in the query, it automagically gets refreshed.