Solved

SQL Update Statement

Posted on 2011-02-28
3
468 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 119

Accepted Solution

by:
Rey Obrero 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now