asked on

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

ee_rlee

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

samandrew

ASKER

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

Thanks
Andy

ee_rlee

yes, you need to use rs.open. i didn't notice that. :D

samandrew

ASKER

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

ee_rlee

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

samandrew

ASKER

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

ASKER CERTIFIED SOLUTION

ee_rlee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

samandrew

ASKER

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