Correct way to add mysql to exisiting code

Hi
I have a tab which has 14 txtboxs on 7 different command buttons
the first 7 txtboxs are called
txtATHest.Text
txtCHELest.Text
txtDARLest.Text
txtNESest.Text
txtSWINest.Text
txtESTtotal.Text

Thease hold my estimate figures which i recall from a database see code below

Private Sub cmdSunA_Click()

Dim i As Long
Dim j As Long
Dim k As Long
Dim datSunday As Date

datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
For k = 0 To 1
txtJCoutcode(k).Text = Format$(datSunday + 5, "dd-mmm")
Next
For i = 0 To 8
txtmincode(i).Text = Format$(datSunday + 6, "dd-mmm")
Next
For j = 0 To 3
txtPCode(j).Text = Format$(datSunday + 7, "dd-mmm")
Next

cmdOpenStock.Caption = "MONDAYS OPENING STOCK"
cmdNextStock.Caption = "TUESDAYS OPENING STOCK"
cmdProd.Caption = "MONDAYS PRODUCTION"
cmdSaveData(0).Caption = "SAVE MONDAYS ORDERS"

datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
      txtDate.Text = Format$(datSunday, "dd-mmm-yy")
      txtproductioncode(0).Text = Format$(6 + datSunday, "dd-mmm-yy")
      txtPREcode(0).Text = Format$(7 + datSunday, "dd-mmm-yy")
     

Dim mysql As String
       
        mysql = "#" & Format(datSunday, "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")

        Database
   
        Set rs = Nothing
        End If
.Close

Figures

txtSunStan.Text = txtSTANcode.Text
txtSunPC.Text = txtAPcode.Text

If cmdOpenStock.Caption = "MONDAYS OPENING STOCK" And cmdNextStock.Caption = "TUESDAYS OPENING STOCK" Then
txtOUTofcode.Text = txtSatStan.Text
txtSTANcodeA.Text = txtSatPC.Text

End If
End With
End Sub

The other 7 txtboxs are used to enter my actual values in
they are called
txtATHact.Text
txtCHEact.Text
txtDARact.Text
txtMIDact.Text
txtNESact.Text
txtSWIact.Text

Because I use the same txtboxs to show my data I use the 7 command buttons which are one for each day of the week to retrieve the relative data for that day.

However once I save the data into cmdSun txtboxs I do not know how to ensure they show zeros in the txtact txtboxs when I click cmdMon the txtboxs still show the data from cmdSun txtboxs.

I have had a go at creating a function but cannot get it to work right see code below

Function Database()
 
 mysql = ("SELECT * FROM Actualorders WHERE [Date] = #" & txtDate.Text & "#")

        With rs
        Do While Not rs.EOF

        txtATHact.Text = rs!Atherstone
        txtCHEact.Text = rs!Chelmsford
        txtDARact.Text = rs!Darlington
        txtNESact.Text = rs!Neston
        txtSWIact.Text = rs!Swindon
        rs.MoveNext
        Loop

        End With

End Function

Hope you can help

Many Thanks
Andy
samandrewAsked:
Who is Participating?
 
ee_rleeConnect With a Mentor Commented:
try this

Function Database()
mysql = ("SELECT * FROM Actualorders WHERE [Date] = #" & txtDate.Text & "#")
       
        Get_My_Connection.Execute mysql
       
        if not rs.eof then
           Do While Not rs.EOF
           txtATHact.Text = rs!Atherstone
           txtCHEact.Text = rs!Chelmsford
           txtDARact.Text = rs!Darlington
           txtNESact.Text = rs!Neston
           txtSWIact.Text = rs!Swindon
           rs.MoveNext
           Loop
        else
           txtATHact.Text = 0
           txtCHEact.Text = 0
           txtDARact.Text = 0
           txtNESact.Text = 0
           txtSWIact.Text = 0
        end if
 
End Function

or you can also initialize the textbox at the start

Function Database()
mysql = ("SELECT * FROM Actualorders WHERE [Date] = #" & txtDate.Text & "#")
 
        Get_My_Connection.Execute mysql
       
        txtATHact.Text = 0
        txtCHEact.Text = 0
        txtDARact.Text = 0
        txtNESact.Text = 0
        txtSWIact.Text = 0

        Do While Not rs.EOF
 
        txtATHact.Text = rs!Atherstone
        txtCHEact.Text = rs!Chelmsford
        txtDARact.Text = rs!Darlington
        txtNESact.Text = rs!Neston
        txtSWIact.Text = rs!Swindon
        rs.MoveNext
        Loop
 
End Function

0
 
ee_rleeCommented:
hi

the textboxes will not show 0 because of the rs.eof condition. if there are no records in the selected date. nothing will be assigned to the textboxes. try the code below
Function Database()
 
 mysql = ("SELECT * FROM Actualorders WHERE [Date] = #" & txtDate.Text & "#")
 
        txtATHact.Text = 0
        txtCHEact.Text = 0
        txtDARact.Text = 0
        txtNESact.Text = 0
        txtSWIact.Text = 0
 
        With rs
 
        Do While Not rs.EOF
 
        txtATHact.Text = rs!Atherstone
        txtCHEact.Text = rs!Chelmsford
        txtDARact.Text = rs!Darlington
        txtNESact.Text = rs!Neston
        txtSWIact.Text = rs!Swindon
        rs.MoveNext
        Loop
 
        End With
 
End Function

Open in new window

0
 
samandrewAuthor Commented:
Hi have tried this code and does not work am wondering if I need to call the mysql in the function?

Thanks
Andy
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ee_rleeCommented:
yes, you need to use rs.open. i didn't notice that. :D
0
 
samandrewAuthor Commented:
Hi have the following code but need to put an else statement in to alloww the "0" code any ideas?

Function Database()
mysql = ("SELECT * FROM Actualorders WHERE [Date] = #" & txtDate.Text & "#")
 
       
        Get_My_Connection.Execute mysql
       
        If txtESTtotal >= 0 Then
       
        With rs
        Do While Not rs.EOF
 
        txtATHact.Text = rs!Atherstone
        txtCHEact.Text = rs!Chelmsford
        txtDARact.Text = rs!Darlington
        txtNESact.Text = rs!Neston
        txtSWIact.Text = rs!Swindon
        rs.MoveNext
        Loop
       
        End With
        End If

End Function

Thanks
Andy
0
 
ee_rleeCommented:
what do you mean by "0" code?

if what you mean is that if no records are returned for a specific date, try the code below.


Function Database()
mysql = ("SELECT * FROM Actualorders WHERE [Date] = #" & txtDate.Text & "#")
 
       
        Get_My_Connection.Execute mysql
       
        If txtESTtotal >= 0 Then
       
        With rs
        if not rs.eof then
           Do While Not rs.EOF
 
           txtATHact.Text = rs!Atherstone
           txtCHEact.Text = rs!Chelmsford
           txtDARact.Text = rs!Darlington
           txtNESact.Text = rs!Neston
           txtSWIact.Text = rs!Swindon
           rs.MoveNext
           Loop
        else
           'Insert your else statement here
        end if
        End With
        End If
 
End Function

Open in new window

0
 
samandrewAuthor Commented:
Hi
what im trying to do is first check to see if a record exists which matches my date, then if it does exist to show it in my txtboxs. However if there is no record which matches my criteia I want my txtboxs to show zero.

However I try i cant seem to get the else statement<<<<<<<<<<<<< to work

so I know that i have a txtbox called txtESTtotal which adds up all my other txtboxs and gives a total, if this is 0 then there is no record. so i thought i might try using this?

Thanks
Andy
0
 
samandrewAuthor Commented:
Hi got it working using the following code

Function Database()
mysql = ("SELECT * FROM Actualorders WHERE [Date] = #" & txtDate.Text & "#")
 
       
        If txtESTtotal = 0 Then
       
           txtATHact.Text = 0
           txtCHEact.Text = 0
           txtDARact.Text = 0
           txtNESact.Text = 0
           txtSWIact.Text = 0
           
        Else

        Get_My_Connection.Execute mysql
       
        With rs
        Do While Not rs.EOF
 
        txtATHact.Text = rs!Atherstone
        txtCHEact.Text = rs!Chelmsford
        txtDARact.Text = rs!Darlington
        txtNESact.Text = rs!Neston
        txtSWIact.Text = rs!Swindon
        rs.MoveNext
        Loop
       
        End With
        End If

End Function

Thanks for your help

Andy
0
All Courses

From novice to tech pro — start learning today.