Solved

SQL Update Statement

Posted on 2011-02-28
3
489 Views
Last Modified: 2012-05-11
Hi,

I'm trying to update a table that will be used in other queries; using data that the user has selected on a combo box form.

I don't get an error at all but when I open the table, it is blank.

Below is my code, can anyone spot where I am going wrong?
.................................................................
Private Sub CmdOK_Click()

DoCmd.SetWarnings False


If IsNull(cbovaldate) Or IsNull(cboNZRG0date) Or IsNull(cboNBDate) Or IsNull(cboBOYStart) Then
    MsgBox "Valuation dates may not be blank!", vbExclamation + vbOKOnly
    Exit Sub
    End If
   
   

DoCmd.RunSQL "UPDATE zzvaldate SET zzvaldate.Valuation_Date = " & Forms!Update_Valdate!cbovaldate & " ;"

DoCmd.RunSQL "Update zzvaldate set zzvaldate.Valuation_Date_yyyymmdd = " & Forms!Update_Valdate!cboNZRG0date & " ; "

DoCmd.RunSQL "Update zzvaldate set zzvaldate.NB_start_date = " & Forms!Update_Valdate!cboNBDate & " ; "

DoCmd.RunSQL "Update zzvaldate set zzvaldate.BOY_Date = " & Forms!Update_Valdate!cboBOYDate & " ; "

MsgBox "Valuation dates have been updated", vbOKOnly

DoCmd.Close acForm, "Update_Valdate"

DoCmd.SetWarnings True


End Sub
0
Comment
Question by:DB_newbie_NZ
  • 2
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 35002950
if Valuation_Date field is Date/time Data type, use this

DoCmd.RunSQL "UPDATE zzvaldate SET zzvaldate.Valuation_Date = #" & Forms!Update_Valdate!cbovaldate & "# ;"

if Valuation_Date field is Text Data type, use this

DoCmd.RunSQL "UPDATE zzvaldate SET zzvaldate.Valuation_Date = '" & Forms!Update_Valdate!cbovaldate & "'"
0
 

Author Comment

by:DB_newbie_NZ
ID: 35003064
Thanks capricorn1, I've tried that but it still doesn't work.

The dates in the table are date/time format except for one which is long integer because it is in yyyymmdd format.

The combo box is taking the possbile valuation dates from a table which in turn has formates that match the table to be updated.

I have several of these forms and none of the update queries are working, including the one where I enter text into a text box.
0
 

Author Comment

by:DB_newbie_NZ
ID: 35003211
Capricorn1, your solution works, thanks a mill! The table being updated was null but when i put in dummy dates in there, the table updated.

So I guess  I must specify to update even when the table is null?

Ok, so now I've updated my dates successfully but say next time I want to open the Update_date form just to make sure my valuation dates are correct. Do i have to change the combo boxes to bound so that data entered will be saved in the combo box till I update it?
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question