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?
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.

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
ee_rleeCommented:
yes, you need to use rs.open. i didn't notice that. :D
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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
ee_rleeCommented:
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

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
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
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.