Update table data in on a click?

[jdean]

Hi all, I'm new to Access programming so this could be very simple for some of you.

I have a table with field name: OrderStatus which has several values:
Payment Requested
Payment Received
Order Shipped
Completed

I currently change each status manually. But more often I need to change ALL order with status "Payment Received" into "Order Shipped" at once. It is kind of stupid for me to do this manually since it doesn't require individual customization.

If someone kindly tell me what should I do. I realize it will need a lil bit of script.

Thank alot!

 

[jdean]

Here is my current script, but it won't work. It said Error at this line: rstOrders.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic

>>>>>>>>>>>>>>>>>>>>>>
Input:

txtCurrentOrderStatus = Payment Received
txtNewOrderStatus = Order Shipped

>>>>>>>>>>>>>>>>>>>>>>


Private Sub ChangeOrder_Click()

Dim rstOrders As ADODB.Recordset
Dim strSQL As String

Set conDatabase = CurrentProject.Connection
strSQL = "SELECT * FROM Orders WHERE OrderStatus = " & txtCurrentOrderStatus

Set rstOrders = New Recordset
rstOrders.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic

With rstOrders
Do While Not .EOF
!OrderStatus = txtNewOrderStatus
.Update
.MoveNext
Loop
End With

MsgBox "Order status has been changed to " & txtNewOrderStatus

rstOrders.Close
conDatabase.Close
Set rstOrders = Nothing
Set conDatabase = Nothing

End Sub

 

[jdean]

I fixed the error by changing:

strSQL = "SELECT * FROM Orders WHERE OrderStatus = " & txtCurrentOrderStatus

into

strSQL = "SELECT * FROM Orders WHERE OrderStatus = 'Payment Received'"

But this is not what I want. I need a dynamic input from txtCurrentOrderStatus. How do I solve this? Anyone?

 

[pbaldy]

Look at what you did differently when you hardcoded it, and the answer should be obvious. You surrounded the value with single quotes. Therefore:

strSQL = "SELECT * FROM Orders WHERE OrderStatus = '" & txtCurrentOrderStatus & "'"

 

[jax]

I am trying to do something similar to this. Can someone help me with some of the vba code that JDEANS has copied above.....

set conDatabase = CurrentProject.Connection

what is the code looking at? Is (conDatabase) the name of JDEANS database?? I just cant get this bit right.

Thanks