Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Update Statement

Posted on 2011-02-28
3
Medium Priority
?
528 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 1000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

916 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