Hi

I am making a little progress in attempting to update 2 controls on a form
the form is populated using a query with no parameters "QComfirm"

I have managed to get the editor to accept the code used by inserting the sql int a string called strSQL

On running the code from a command button it comes up with the error msg

To Few Parameters expected 1.
:::::::::::::::::::::::::::::::::;
What am I doing wrong?
::::::::::::::::::::::::::::::::::

When I try to place the SQL Code directly into the Editor without using a variable strSQL

"UPDATE tblOrder SET tblOrder.Quote = No, tblOrder.ConfirmedOrder = Yes " & vbCrLf & _
"WHERE (((tblOrder.Quote)=Yes) AND ((tblOrder.ConfirmedOrder)=No));"

I cannot get the syntax correct the code turns red and generates a warning for the UPDATE line

The WHERE line seems syntactically correct as is black and no errors
:::::::::::::::::::::::::::::::::::::::::::::::::: :::::::::::::::::

Is there a site I can go to and teach myself when is it correct to use quotes or brackets or apostrophes as appropriate?

Very frustrating!

:::::::::::::::::::::::::::::::::::::::::::::::::: ::::::


Private Sub CmdConfirmOrders_Click()
On Error GoTo Err_CmdConfirmOrders_Click

Dim db As DAO.Database
Dim rstTemp As DAO.Recordset
Dim RecCount As Integer
Dim strSql As String


  Set db = DBEngine(0)(0)
  Set rstTemp = CurrentDb.OpenRecordset _
 ("SELECT TDateRecorded,Quote,ConfirmedOrder FROM QConfirm")

 strSql = "UPDATE tblOrder SET tblOrder.Quote = No, tblOrder.ConfirmedOrder = Yes " & vbCrLf & _
"WHERE (((tblOrder.Quote)=Yes) AND ((tblOrder.ConfirmedOrder)=No));"



 rstTemp.MoveFirst
 Do Until rstTemp.EOF

 db.Execute strSql


 Loop

 'requery the form
 'disable the Cmd Button to avoid an error when no records exist in the temp table
Me.CmdConfirmOrders.Enabled = False


MsgBox "All Records have been successfully Updated", vbOKOnly
rstTemp.Close

Exit_CmdConfirmOrders_Click:
    Exit Sub

Err_CmdConfirmOrders_Click:
    MsgBox Err.Description
    Resume Exit_CmdConfirmOrders_Click

End Sub

The problem may be in these values: Yes, No.

How are they stored? Is this an Access table, or a SQL Server or other DBMS? What are the data types in those fields? Are they Yes/No, Bit, Text, Number? I think that is where the error might be. Please post more info.

mmcdonal

mmcdonal

Hi
 More info as requested I hope it is not to laboured

The origin of query QConfirm is a table in Access it is the many side of a one to many relationship where the One relationship is Order Number and the Many details are the line entries of Many Orders

The Many side holds
1.a date of entry for a record. (Date)Defaults to Current Date
2.Quote (Boolean)tick box Defaults Yes
3.Confirmed Order (Boolean)tick box No

When it is known that the line is a firm order
The quote tick box should become NO

The Confirmed Order tick becomes Yes

The above information will be displayed on a form whose query
will be filtered to include only records between 2 given dates

It will also be filtered to include only those records between those dates that are flagged Yes as quotes

A command button on the form (which is what I am trying to achieve)
the code will go in the click event of the command button and will (I Hope) Change Quote to NO
         Change Confirmed to YES

Thanks for all of your help on previous problems

Man Friday

The origin of query QConfirm is a table in Access it is the One side of a one to many relationship where the One relationship is Order Number and the Many details are the line entries of Many Orders

Apologies Line one should read Access it is the ONE SIDE of the one to many etc etc

Should I be using True and False instead of YES and NO

I actually created an update query and copied the SQL from that I translated it into VBA strSQL using an add-on which I found this afternoon..It does not help me much to understand in all circumstances what the correct syntax should be but I guess it is a start

MF

Can you do this: In the table, set the defaults for Quote and Confirmed to no.

Then change the check boxes to radio buttons, and add this code:

Private Sub Radio1_Click()
If Me.Radio1 = True Then Me.Radio2 = False
End Sub

Private Sub Radio2_Click()
If Me.Radio2 = True Then Me.Radio1 = False
End Sub

The controls will then keep track of the values instead of having to shove them into the table yourself.

Can you implement this?

A better choice would be to have a single text field called "OrderStatus" and then create an option group on the form that pushes "Quote" or "Confirmed" to the field automatically.




mmcdonal

mmcdonal

Hi

Thanks for the reply. I can do that. If push had come to shove I was considering other options.

It was just bugging me that I could not get the code to run inside the loop.

I will implement your suggestions

Thank you

MF

Hi ManFriday

Just for future reference, when using or writing a sql statement such as yours should be written as
strsql = "UPDATE tblOrder SET tblOrder.Quote = No, "
strsql = strsql & "tblOrder.ConfirmedOrder = Yes "
strsql = strsql & "WHERE (((tblOrder.Quote)=Yes) AND "
strsql = strsql & "((tblOrder.ConfirmedOrder)=No));"

You cannot use vbcrlf in SQL as it does not recognise it. In effect it reads off one line of code, but for easy reading it can be split into numerous lines as the example above. One thing to remember is that at the end of each intermediate line, a space is let before the speech makrs. This provides the necessary space for the next line. The last lines speech mark is at the end of the line

Give it a try in your original code.

Martin

Tell them they will hear,
Show them they will see,
Let them experience and they will learn

Martin

Thank you very much. Because the code for use in the VB Editor was constructed using a 'wizard add-on' I actually did not notice the vbcrlf (DOH!)

However having said that I have a wide gap in my knowledgs as to when it wouuld be appropriate to use either

Double quotes,((single quotes:(Apostrophes)) or indeed ampersands(&) to concatenate.

If you have a moment perhaps you could advise me when most appropriatge to use some or any of the punctuation objects above

Regards

Man Friday

Hi Manfriday

I would be more than happy to help where I can. To be honest, when using Access I tend to create the query using the Access query design, then copy the sql from there into VBA. This way you know you query will work. It is difficult to explain over a forum how sql works. The best thing to do, is let me have some of you code and I will get it to work, if possible and explain against that particular one, this way you will pick it up quicker and more reliably

I also notice that your having a few problems with your recordsets. I write DAO recordsets with sql so if you need any help with that, let me know


Tell them they will hear,
Show them they will see,
Let them experience and they will learn