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.

 

[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