Compile error again

[ChrisLeicester]

Hi
Taking advice from here, I am trying to move away from Macro's and use more VBA, although it is a learning curve.

I have a main form with two subforms, one showing current products, and another like a shopping cart.

The idea is the user picks the product, enters in a text box the quantity and presses an 'Add' control button.

Once the add button has been pressed I want it to create a new record on the 'Cart subform' and set the productId, QTY and current price fields.

I have made this code;

' This is to add products to the cart subform

    Dim ProdOrdered As Integer
    Dim QTYProd As Integer
    Dim ProdRetail As Currency

        ProdOrdered = [Forms]![NewCustOrderMainFM]![CustSaleProductSelectFM]![ProductID]
        QTYProd = [Forms]![NewCustOrderMainFM]![CustSaleProductSelectFM]![QtyToAdd]
        ProdRetail = [Forms]![NewCustOrderMainFM]![CustSaleProductSelectFM]![CurrentRetailPrice]
        
            With Me.CustOrderListSub
          .SetFocus
          DoCmd.GoToRecord Record:=acNewRec
          Me.itempurchased = ProdOrdered
          Me.ItemQTYpurchased = QTYProd
          Me.RetailitemPrice = ProdRetail
          DoCmd.RunCommand acCmdSaveRecord
          End With

End Sub

When trying it, I get a 'Compile Error - method or data member not found' and the 'with me.CustOrderListSub' line is yellow highlighting the error

Why am I getting this error?

Thanks again,
Chris

 

[DHookom]

First, check the name of the shopping cart subform.

I would do this a bit differently. Consider building an append SQL statement and then executing it. Then you ismply need to requery the subform.

Dim strAppSQL as String
strAppSQL = "INSERT INTO YourTable (FieldListHere) Values (" & … & ")"
Current DH.Execute strAppSQL, dbFailOnError
Me.subformname.Requery

 

[ChrisLeicester]

Hi
I will give that a go.
Learning something new every day.
Thanks

First, check the name of the shopping cart subform.

I would do this a bit differently. Consider building an append SQL statement and then executing it. Then you ismply need to requery the subform.

Dim strAppSQL as String
strAppSQL = "INSERT INTO YourTable (FieldListHere) Values (" & … & ")"
Current DH.Execute strAppSQL, dbFailOnError
Me.subformname.Requery

I am getting this line as red;

strAppSQL = "INSERT INTO SalesTransactionItemsTB ([ItemPurchased],[ItemQTYpurchased],[RetailitemPrice]) Values ("ProdOrdered & QTYordered& ProdRetail")"

the error is expected end of statement.

i guess I am referencing the fields i want to get the data from wrong

Thanks

 

[The_Doc_Man]

The VALUES list is incorrect. You show ampersands (&) but first, I think you don't need quotes for anything inside that list, and second, the correct separator is a comma (,). The error is because you have mis-handled the quotes.

Here's a debugging tip. You are doing the right thing by building a string. Put a breakpoint on the line following that "strAppSQL =" statement and open the immediate window (from the VIEW options in the VBA menu bar). Type this command:

Debug.Print strAppSQL

You will see that the string isn't what you thought it was.

I'll tell you the error, but do what I suggest anyway so that you will understand a really useful debugging tool. The error is that (a) the quotes inside the VALUE list's parentheses are not correct - not needed, and (b) when you intend to put quoted strings inside of quoted strings, the inner quoted string should be delimited by the single quote / apostrophe (').

 

[moke123]

here's a quick example of the method DHookom suggested

 

[ChrisLeicester]

Hi
I have taken the " off the value section and that now passes.

I now have the Current DB.Execute strAppSQL, dbFailOnError line showing red and a syntax error

I also tried your debug.print line in the immediate area and it didnt show anything after pressing enter. I must be doing something wrong there too

thanks

chris

 

[Gasman]

Use ? in the immediate window. Though Debug.Print still works there?

Use Debug.Print in the code BEFORE you try and use the sql.

 

[The_Doc_Man]

If that line reads like this:

Current DB.Execute strAppSQL, dbFailOnError

then your problem is that "Current DB" is a bad variable name. It should EITHER read CurrentDB (run together) or Current_DB (name parts conjoined using underscore). What happens in the line you showed us is that the SPACE is a syntactic delimiter token, so VBA doesn't know what Current is and then it sees the space and DB.Execute and it doesn't know what DB is to look up the .Execute method.

As a really good practice, NEVER EVER (in a gazillion years) include spaces in variable or object names. Either run the names together or use single underscore characters as "place holders." The underscore is the ONLY special character advisable for object or variable names, though it IS legal to use a dollar sign. However, if you ever had to convert to other DB processing (like ORACE, SYBASE, SQL Server, etc), there is no guarantee that the characters the other vendors would allow in names would include the underscore.

 

[DHookom]

Sorry, my autocorrect wrote Current DH (my current initials) rather that CurrentDB.

 

[ChrisLeicester]

Hi
Thanks everyone, progress is being made.

using the ? in the immediate box, I can see the SQL line and checked each variable and each shows the correct value, so far so good.

This has brought up another problem I didnt see coming due to my lack of experience.

In the unbound subform I list all of our products in a continuous form and in each line/record I have an unbound text box where I can enter the quantity purchased. My intention was that this would be passed to the 'Cart' subform with the above code.

I didnt realise that if I enter for example '4' into one of the record text box, that '4' would appear in all the record text boxes, ie on every line.

Now the code runs, I get an error saying too many variables or such, which may be because every record textbox has the '4'

Googling, I see that this is what unbound textboxes do and will repeat on every record.

I now cant use that idea, and don't think adding another field to the product table will help, as the quantity is only needed to be captured to be added to the Cart

Any ideas I havent thought of?

Thanks
Chris

 

[The_Doc_Man]

Googling, I see that this is what unbound textboxes do and will repeat on every record.

Which is why I asked earlier about unbound forms. Access forms and reports are designed to be bound most of the time. Step back to the drawing board and ask yourself what you want to do with respect to the data you keep. Not what the controls will do at the low level, but what are you trying to do in a high-level overview. Part of the answer appears to be that you want to add data to a list that is somehow relevant to something else in your DB. But it also sounds like you are "shooting from the hip" when you should spend some time deciding your intermediate and end goals, and THEN deciding how to get there from wherever you are starting.

From a somewhat jumbled description, I think you want to be able to select something and then see stuff related to what you selected. You need to apply Julius Caesar's approach - divide and conquer. FIRST concentrate on how to select what you want. Here's a hint: If the main form is the "anchor" to what you selected, it should be bound... but if you read up on combo boxes, you would find that one of the options for the combo box wizard is to navigate a form to a selected record, where the combo box doesn't actually store anything, it diddles with the form's underlying record to move the focus of the form to the record you selected. IF you have a child form (or sub-form), you can bind the child to the parent through the parent and child links in the sub-form control that holds the actual sub-form. If THAT is also bound you can set it up to allow you to edit or add to the list of child-level details. But when things are not bound properly, as you have now seen for yourself, it gets kind of ugly.

 

[ChrisLeicester]

This is what I am trying to achieve, I know I could use a simple cart subform with a combobox on each cart record but thought this would look better.

I will take on board your comments, they make sense and will have a rethink,

 

[Josef P.]

I am getting this line as red;

strAppSQL = "INSERT INTO SalesTransactionItemsTB ([ItemPurchased],[ItemQTYpurchased],[RetailitemPrice]) Values ("ProdOrdered & QTYordered& ProdRetail")"

Tip: First create an SQL statement in a single string. Then add the variable values from VBA.
Then you can immediately see which change raise a syntax error.

First the cumbersome version:
1.

strAppSQL = "INSERT INTO SalesTransactionItemsTB ([ItemPurchased],[ItemQTYpurchased],[RetailitemPrice]) Values (ProdOrdered, QTYordered, ProdRetail)"

2. Concatenate variables
2.1

strAppSQL = "INSERT INTO SalesTransactionItemsTB ([ItemPurchased],[ItemQTYpurchased],[RetailitemPrice]) Values (" & ProdOrdered & ", QTYordered, ProdRetail)"

2.2

strAppSQL = "INSERT INTO SalesTransactionItemsTB ([ItemPurchased],[ItemQTYpurchased],[RetailitemPrice]) Values (" & ProdOrdered & ", " & QTYordered & ", ProdRetail)"

2.3

strAppSQL = "INSERT INTO SalesTransactionItemsTB ([ItemPurchased],[ItemQTYpurchased],[RetailitemPrice]) Values (" & ProdOrdered & ", " & QTYordered & ", " & ProdRetail & ")"

3. Ensure correct conversion to SQL text: (ProdRetail .. decimal values => Str(1.23))

strAppSQL = "INSERT INTO SalesTransactionItemsTB ([ItemPurchased],[ItemQTYpurchased],[RetailitemPrice]) Values (" & ProdOrdered & ", " & QTYordered & ", " & Str(ProdRetail) & ")"

Now the better readable version:
1.

strAppSQL = "INSERT INTO SalesTransactionItemsTB ([ItemPurchased],[ItemQTYpurchased],[RetailitemPrice]) Values (ProdOrdered, QTYordered, ProdRetail)"

2. Mark values as a numeric placeholder

strAppSQL = "INSERT INTO SalesTransactionItemsTB ([ItemPurchased],[ItemQTYpurchased],[RetailitemPrice]) Values ({0}, {1}, {2})"

3. use StringFormat ... see Northwind 2.x

strAppSQL = StringFormat(strAppSQL, ProdOrdered, QTYordered, Str(ProdRetail))

 

[ChrisLeicester]

Thanks for all those examples, I will go through each one, digest them and understand them.
I need to rethink the format as I have now realised I can't use the unbound text box in the products subform due to it putting any typed value in one box into every box. So that idea is out the window