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
.Close
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
Andy
samandrewAsked:
Who is Participating?
 
adrpoConnect With a Mentor Commented:

Hi,

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.

Cheers,
za-k/
0
 
RyanProject Engineer, ElectricalCommented:
0s should be allowed unless a validation rule was set?
0
 
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

Thanks
Andy
0
 
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

Thanks
Andy
0
All Courses

From novice to tech pro — start learning today.