Solved UPDATE QUERY THROUGH VBA

[nectorch]

I'm currently using cloud database, so to improve the speed I need to use a pass-through query from MS Access so that I can deal with the server directly. I'm trying to open the record source in VBA, that is where I'm getting it wrong. See below why the code is not changing the date.

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM [QryApproveSales] WHERE [tblCustomerInvoice].[InvoiceID] = " & [Forms]![frmCustomerInvoice]![CboDocument]
UPDATE tblCustomerInvoice SET tblCustomerInvoice.ShipDate = #02/11/2023#
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryApproveSales"

Or to allow users to change the date I want to change from #02/11/2023# to Me.ShipDate

 

[CJ_London]

Not clear what your code is attempting to do from your description- perhaps the date is too early?(11th Feb)

looks like you code has plenty of syntax errors and wouldn’t run anyway

 

[nectorch]

The date is 2 November 2023 English date not American

Its just the code below where I'm confusing myself:

UPDATE tblCustomerInvoice SET tblCustomerInvoice.ShipDate = #11/02/2023#

 

[ebs17]

In order for the date string to be recognized as a date by SQL, it must be formatted in a certain way. SQL is very restrictive and only accepts the American format or the ISO format.

#2/11/2023#
#2023-02-11#

 

[nectorch]

What if I do it this way :

UPDATE tblCustomerInvoice SET tblCustomerInvoice.ShipDate = #"Format(( [Forms]![frmCustomerInvoice]![txtChangeShipeDate]),"YYYY\/MM\/DD")&"#

Sorry I'm just guessing here, and so corrections will be required!

 

[nectorch]

Here is my final try:

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM [tblCustomerInvoice] WHERE tblCustomerInvoice.InvoiceID)= & [Forms]![frmCustomerInvoice]![CboShipDateChange]"
strSQL = "UPDATE tblCustomerInvoice SET tblCustomerInvoice.ShipDate = #" & Format(([Forms]![frmCustomerInvoice]![txtChangeShipeDate]), "YYYY\/MM\/DD") & "#"
DoCmd.SetWarnings False
MsgBox "Shipping Date Changes Posting successful", vbInformation, "Please Proceed"

 

[cheekybuddha]

Since you are tweeting a cloud database via a pass-through query you will need to pass dates in the native format for that type of database, which is likely to be:

I need to use a pass-through query

You don't appear to be using a pass-through query with the code you posted.

Also, your SQL is totally wrong - what is the SELECT portion supposed to do? Please describe in words what the query is supposed to do

You will need code like:

  Dim db As DAO.Database

  Set db = CurrentDb
  With db.CreateQueryDef(vbNullString)
    .Connect = db.TableDefs("tblCustomerInvoice").Connect
    .ReturnsRecords = False
    .SQL = "UPDATE tblCustomerInvoice SET ShipDate = " & Format(Forms.frmCustomerInvoice.txtChangeShipeDate, "\'YYYY\-MM\-DD\'") & ";"
    .Execute dbFailOnError
    If .RecordsAffected > 0 Then
      MsgBox "Shipping Date Changes Posting successful", vbInformation, "Please Proceed"
    Else
      MsgBox "Hmmm... No recoreds were inserted", vbInformation, "Please Check"
    End If
  End With

(Untested!)

 

[MarkK]

UPDATE tblCustomerInvoice SET tblCustomerInvoice.ShipDate = #02/11/2023#

This SQL will update every row in the table. Are you missing a WHERE clause?

 

[cheekybuddha]

My guess is that the SQL should be more like:

' ...
    .SQL = "UPDATE tblCustomerInvoice SET ShipDate = " & Format(Forms.frmCustomerInvoice.txtChangeShipeDate, "\'YYYY\-MM\-DD\'") & _
           " WHERE InvoiceID = " & Forms.frmCustomerInvoice.CboShipDateChange & ";"
' ...

 

[nectorch]

Well I have finally given up the query below changes the date to 22/11/1900 fortunately we had just captured 30 invoices and we had printed the hard copies , I had to go into studio management to correct the dates again. This means that I have to stick with the original code below though slightly slow:

  Dim db As DAO.Database

  Set db = CurrentDb
  With db.CreateQueryDef(vbNullString)
    .Connect = db.TableDefs("tblCustomerInvoice").Connect
    .ReturnsRecords = False
    .SQL = "UPDATE tblCustomerInvoice SET ShipDate = " & Format(Forms.frmCustomerInvoice.txtChangeShipeDate, "\'YYYY\-MM\-DD\'") & ";"
    .Execute dbFailOnError
    If .RecordsAffected > 0 Then
      MsgBox "Shipping Date Changes Posting successful", vbInformation, "Please Proceed"
    Else
      MsgBox "Hmmm... No recoreds were inserted", vbInformation, "Please Check"
    End If
  End With

Original query code

UPDATE tblCustomerInvoice SET tblCustomerInvoice.ShipDate = [Forms]![frmCustomerInvoice]![txtChangeShipeDate]
WHERE (((tblCustomerInvoice.InvoiceID)=[Forms]![frmCustomerInvoice]![CboShipDateChange]));

Calling the above code in VBA

DoCmd.SetWarnings False
DoCmd.OpenQuery "QryShipdate"
MsgBox "Shipping Date Changes Posting successful", vbInformation, "Please Proceed"

 

[cheekybuddha]

Did you read Posts #8 and #9 ?