SQL Code in vba not update date.

[abzalali]

Dear Expert,
I'm trying to update date with this code:

sSQL = "UPDATE [Dive Crew] " _
            & "Set [Dive Crew].CapByDate = # & [Forms]![frmInitialInvoice]![txtCapByDate] & # " _
            & "WHERE [Dive Crew].InvoiceNumber = '" & [Forms]![frmInitialInvoice]![InvoiceNumber] & "'"
    db.Execute sSQL, dbSeeChanges

Probably I missing something "# & [Forms]![frmInitialInvoice]![txtCapByDate] & # " in this area, please anybody help me.

Thanks in Advance
Mir

 

[namliam]

How is it not working?

The statement by itself is valid, posible concerns
- Why do you not replace in the txtCapByDate like you do with the Invoicenumber one line below ??
- txtCapByDate is that entered in US format?
- Invoicenumber is that a number or a text value?

 

[plog]

Probably I missing something "# & [Forms]![frmInitialInvoice]![txtCapByDate] & # " in this area, please anybody help me.

Yup. You are mixing strings and variables and when you do so, you need to properly end the string, insert the variable information and then restart your string. Look at your second line-- you do the same thing there, but you do it correctly.

 

[abzalali]

Dear Sir,
- no reason to replace in the txtCapByDate like Invoicenumber one line below, it's just control name
- txtCapByDate is dd/mm/yyyy.
- Invoicenumber is text value.

Thanks for helping Sir

 

[vbaInet]

Here:

sSQL = "PARAMETERS [Forms]![frmInitialInvoice]![txtCapByDate] DateTime, [Forms]![frmInitialInvoice]![InvoiceNumber] Text "
       "UPDATE [Dive Crew] " & _
       "SET [Dive Crew].CapByDate = [Forms]![frmInitialInvoice]![txtCapByDate] " & _
       "WHERE [Dive Crew].InvoiceNumber = [Forms]![frmInitialInvoice]![InvoiceNumber]"

Why don't you even execute a query instead? Or bind the form?

 

[abzalali]

Sir Plog,

 sSQL = "UPDATE [Dive Crew] " _
            & "Set [Dive Crew].CapByDate = '" & [Forms]![frmInitialInvoice]![txtCapByDate] & "'" _
            & "WHERE [Dive Crew].InvoiceNumber = '" & [Forms]![frmInitialInvoice]![InvoiceNumber] & "'"
    db.Execute sSQL, dbSeeChanges

I changes as next line but it doesn't update any rows.

Thanks

 

[abzalali]

Sir vbaInet,

 sSQL = "PARAMETERS [Forms]![frmInitialInvoice]![txtCapByDate] DateTime, [Forms]![frmInitialInvoice]![InvoiceNumber] Text "
            "UPDATE [Dive Crew] " & _
            "SET [Dive Crew].CapByDate = [Forms]![frmInitialInvoice]![txtCapByDate] " & _
            "WHERE [Dive Crew].InvoiceNumber = [Forms]![frmInitialInvoice]![InvoiceNumber]"

Last 3 lines shows red in vba. Could you help me else.

Thanks

 

[vbaInet]

It's a simple fix that you should have figured out.

"PARAMETERS [Forms]![frmInitialInvoice]![txtCapByDate] DateTime, [Forms]![frmInitialInvoice]![InvoiceNumber] Text "[COLOR="Red"] & _[/COLOR]

 

[abzalali]

Sir did that fix but it show "syntax error in PARAMETER clause"

 

[vbaInet]

Text (255)

 

[abzalali]

Same error generated, Sir

 

[vbaInet]

I knew you would come back Again simple fixes for you to figure out.

sSQL = "PARAMETERS [Forms]![frmInitialInvoice]![txtCapByDate] DateTime, [Forms]![frmInitialInvoice]![InvoiceNumber] Text (255); " & _

 

[abzalali]

Sir, generating a new error "Too few parameters. Expected 2.

 

[vbaInet]

Use RunSQL.

But like I already mentioned, why are you not using a bound form?

 

[abzalali]

Sir, it shows "Compile error: Argument not optional" when use RunSQL.

Actually I want, to enter a Date as "Cap.byDate" in main form, but "Cap.byDate" field originally in a Subform field, I need when enter "Cap.byDate" in main form then it's automatically populate all rows in Subform. I would not like to enter "CapbyDate" for each row in subform.
That's why I'm not using bound Form.

Thnaks
Mir

 

[vbaInet]

If the field/control is in a subform then you have the wrong reference. And if you're needing to populate the subform using a date value from the main form, then there's something wrong with your table structure.