sql statement needed to view data

Hi I have the following database connection and mysql in my project.

Public myConnection As ADODB.Connection
 Dim rs As New ADODB.Recordset

Public Function Get_My_Connection() As ADODB.Connection
     If myConnection Is Nothing Then
           Set myConnection = New ADODB.Connection
           myConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\andy\Desktop\Production\Planner.accdb;Persist Security Info=False;"
     End If
     Set Get_My_Connection = myConnection
End Function

mysql = "#" & Format(datFriday, "yyyy-mm-dd") & "#"
        mysql _
            = " SELECT Atherstone, Chelmsford, Darlington, Neston, Swindon, DailyTotal" _
            & " FROM Actualorders " _
            & " WHERE Actualorders.[Date] Between" & mysql & " " & -7 & " And " & mysql
        With rs
           .LockType = 2
           .CursorType = 3
           .Open mysql, Get_My_Connection
           If .RecordCount > 0 Then
                txtATHest.Text = .Fields("Atherstone")
                txtCHELest.Text = .Fields("Chelmsford")
                txtDARLest.Text = .Fields("Darlington")
                txtNESest.Text = .Fields("Neston")
                txtSWINest.Text = .Fields("Swindon")
                txtESTtotal.Text = .Fields("DailyTotal")
            Set rs = Nothing
            End If
End With

This works fine as it takes a value from my database from 7 days previous and then places it in a txtbox I use this value as an estimate.

Next to these txtboxs I have further txtboxs which I enter the actual value for that day

Now I want to save the values I enter into the actual values txtboxs to the same database I am viewing the values from the previous 7 days.

I have tried using an INSERT INTO which works but at times I will have a 0 in the txtbox and will need to save it(which it wont let me) and then I need to able to move back to that day and edit the zero cells when I have the actual figures.

Hope someone can understand this as quite hard to explain.

Many Thanks
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.



You might need to alter some tables in the database to accept
NULL values. Then you can do the insert into all the time, then
go back when you have the real stuff to put in.
You should inspect the constraints on the tables and see which
one doesn't allow null values to be entered in.

Can you give us an example on what error does it give when
you try to insert into with 0 values? That might point to the
actual table that needs changing.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RyanProject Engineer, ElectricalCommented:
0s should be allowed unless a validation rule was set?
samandrewAuthor Commented:
Hi sorry posted wrong code where i am getting the error

Private Sub cmdSaveData_Click(Index As Integer)
Dim response As Integer
Dim mysql As String
response = MsgBox("You Are About To Enter Aldi Just Crumpets Orders Into The Database - DO YOU WANT TO CONTINUE?", vbQuestion + vbYesNo)

mysql = " UPDATE Actualorders Values('" & _
txtMonday(0).Text & "'," & txtATHact.Text & "," & txtCHEact.Text & "," & txtDARact.Text & "," & txtNESact.Text & "," & txtSWIact.Text & "," & txtESTtotal.Text & ")"
Debug.Print mysql

MsgBox "Mysql = " & mysql, vbOKOnly
Call Get_My_Connection
myConnection.Execute (mysql)

If response = vbYes Then
    MsgBox "Orders Succesfully Updated"
ElseIf response = vbNo Then
    MsgBox "Operation Has Been Cancelled - NO DATA HAS BEEN CHANGED"
End If
End Sub

Get an error message on my           Connection.Execute (mysql)
syntex error in UPDATE statement

samandrewAuthor Commented:
I think I might need to create a new record in the database which I could do from a command click so I can then use the UPDATE, does anyone know How I can do this please

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.