Show data in txtbox based on mysql

Hi
I have the following code which I cant seem to get to work correctly, it always shows me the last recordset instead of a zero when the recordset does not exist.

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

       
       
      Get_My_Connection.Execute mysql

         If Not rs.EOF Then
           Do While Not rs.EOF
           txtMorActS.Text = rs!DailyTotal
           rs.MoveNext
           Loop
        Else
           txtMorActS.Text = "0"
           
        End If
End Function

Many Thanks
Andy
samandrewAsked:
Who is Participating?
 
Jinesh KamdarConnect With a Mentor Commented:
Try this.
Function Database()
        
Dim response As Integer
Dim mysql As String
 
For i = 0 To 6
    mysql = "SELECT * FROM Morrisons WHERE [Date] = #" & txtDateS.Text & "#"
    rs.Open mysql, Get_My_Connection
    
    If Not rs.EOF Then
       Do While Not rs.EOF
          txtMorActS(i).Text = rs!DailyTotal
          rs.MoveNext
       Loop
       Set rs = Nothing
       rs.Close
    Else
        txtMorActS(i).Text = "0"
        Set rs = Nothing
        rs.Close
    End If
Next i
 
End Function

Open in new window

0
 
Jinesh KamdarCommented:
Do you mean to say that the function returns a non-zero value even if the query actually does not fetch any rows from the DB ?
0
 
samandrewAuthor Commented:
Hi
It seems to fetch the value from the last recordset which does not match mysql date which I am after, infact I know at this stage I do not have a recordset which matches my mysql statement hence I expect to get a 0 in my txtbox.

Many Thanks
Andy
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Jinesh KamdarCommented:
webtubbs - Thanx.

Andy - Does it show the right recordset when for those cases when it actually exists in the DB?
0
 
samandrewAuthor Commented:
Hi

No it seems not to

Thanks
Andy
0
 
Jinesh KamdarCommented:
Which means there's something wrong with the query. Does the query work properly when fired directly on the DB console ?
0
 
samandrewAuthor Commented:
Not sure what you mean here is my full code for the form I am using.

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
Private Sub Command63_Click()
End
End Sub
Private Sub ResetDates()
Dim datSunday As Date
   datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
      txtDate.Text = Format$(datSunday, "dd-mmm-yy")
 End Sub
     
Private Sub Form_Load()
Dim i As Long
Dim j As Long
Dim k As Long
Dim datSunday As Date
Dim datMonday As Date
Dim datTuesday As Date
Dim datWednesday As Date
Dim datThursday As Date
Dim datFriday As Date
Dim datSaturday As Date

datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
txtDateS.Text = Format$(datSunday + 6, "dd-mmm")
datMonday = DateAdd("d", vbMonday - Weekday(Date), Date)
txtDateM.Text = Format$(datMonday + 6, "dd-mmm")
datTuesday = DateAdd("d", vbTuesday - Weekday(Date), Date)
txtDateT.Text = Format$(datTuesday + 6, "dd-mmm")
datWednesday = DateAdd("d", vbWednesday - Weekday(Date), Date)
txtDateW.Text = Format$(datWednesday + 6, "dd-mmm")
datThursday = DateAdd("d", vbThursday - Weekday(Date), Date)
txtDateTh.Text = Format$(datThursday + 6, "dd-mmm")
datFriday = DateAdd("d", vbFriday - Weekday(Date), Date)
txtDateF.Text = Format$(datFriday + 6, "dd-mmm")
datSaturday = DateAdd("d", vbSaturday - Weekday(Date), Date)
txtDateSa.Text = Format$(datSaturday + 6, "dd-mmm")


 Dim mysql As String
    For i = 0 To 6
   
        mysql = "#" & Format(datSunday, "yyyy-mm-dd") & "#"
        mysql _
            = " SELECT Gadbrook, Latimer, Stockton, Wakefield, Yate, DailyTotal" _
            & " FROM Morrisons " _
            & " WHERE Morrisons.[Date] Between" & mysql & " " & i - 7 & " And " & mysql
   
        With rs
           .LockType = 2
           .CursorType = 3
           .Open mysql, Get_My_Connection
     
           If .RecordCount > 0 Then
                txtMorEst(i).Text = .Fields("DailyTotal")
               
            Database
               
            Set rs = Nothing
            End If
.Close
End With

Next i


End Sub
Function Database()
 mysql = ("SELECT * FROM Morrisons WHERE [Date] = #" & txtDateS.Text & "#")

       
       
      Get_My_Connection.Execute mysql

         If Not rs.EOF Then
           Do While Not rs.EOF
           txtMorActS(0).Text = rs!DailyTotal
           rs.MoveNext
           Loop
        Else
           txtMorActS(0).Text = "0"
           
        End If
End Function

Private Sub txtMorActS_Change(Index As Integer)
    Dim i As Integer
   
    For i = 0 To 5
        If IsNumeric(txtMorEst(i).Text) And IsNumeric(txtMorActS(i).Text) Then
            If CDbl(txtMorActS(i).Text) < 0.00001 Then
                txtPercent(i).Text = "0"
            Else
                txtPercent(i).Text = Format(Val(txtMorActS(i).Text) - Val(txtMorEst(i).Text) / Val(txtMorActS(i).Text) * 100, "#0") & "%"
               
            End If
        End If
   
    Next i



    For j = 0 To 5
        If IsNumeric(txtMorEst(j).Text) Then
            If CDbl(txtMorEst(j).Text) < 1 Then
                txtMorMixS(j).Text = "0"
            Else
                txtMorMixS(j).Text = Format(txtMorEst(j).Text * 16 * 6 / 1925, "0.0")
               
            End If
        End If
   
       
    Next j





End Sub



Let me know what you think

Thanks
Andy
0
 
Jinesh KamdarCommented:
How about this - mysql = "SELECT * FROM Morrisons WHERE [Date] = #" & txtDateS.Text & "#"
i.e. without the parentheses.
0
 
samandrewAuthor Commented:
Hi
Sorry am at work now but will give it a go when I get home

Many Thanks
Andy
0
 
Jinesh KamdarCommented:
Ok. Will check tomo. morn. IST.
0
 
samandrewAuthor Commented:
Hi
Tried that but still did not work so have tried the following code which does retrieve the recordset when it is there but does not return a zero in the txtbox when there is no record.

Function Database()
Dim response As Integer
Dim mysql As String

mysql = "SELECT Count(*) AS RecExists FROM Actualorders WHERE [Date] = #" & txtDate.Text & "#"
 
        rs.Open mysql, Get_My_Connection
 
        If rs!RecExists > 0 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
       
        Loop
       
        Else
       
        txtATHact.Text = "0"
        txtCHEact.Text = "0"
        txtDARact.Text = "0"
        txtNESact.Text = "0"
        txtSWIact.Text = "0"


rs.Close

End If

End Function

Many Thanks
Andy
0
 
Jinesh KamdarCommented:
What i don't understand here is that how were you able to assign rs!Atherstone, rs!Chelmsford, etc. when you have not included these columns in the SELECT list of the SQL ?
0
 
samandrewAuthor Commented:
Hi
Maybe it is because I am calling the function in another command click which has a mysql statement?

please 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")
      txtProdCode.Text = Format$(6 + datSunday, "dd-mmm-yy")
      txtPreCode.Text = Format$(7 + datSunday, "dd-mmm-yy")
     

Dim mysql As String
Dim mysql9 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

Thanks
Andy
0
 
Jinesh KamdarCommented:
I guess we might hv to debug a bit there. Referring to ur previous code, try below.
Function Database()
Dim response As Integer
Dim mysql As String
 
mysql = "SELECT Count(*) AS RecExists FROM Actualorders WHERE [Date] = #" & txtDate.Text & "#"
 
        rs.Open mysql, Get_My_Connection
 
        // print the value of rs!RecExists here
 
        If rs!RecExists > 0 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
        
        // Don't u think there's a "rs.Next" missing here ???
 
        Loop
        
        Else
        
        txtATHact.Text = "0"
        txtCHEact.Text = "0"
        txtDARact.Text = "0"
        txtNESact.Text = "0"
        txtSWIact.Text = "0"
 
rs.Close
 
End If
 
End Function

Open in new window

0
 
samandrewAuthor Commented:

Hi am really sorry about this but bizarley I have posted the wrong code, little sleep and too much work!!, the code I postedworks well in my program and does have rs!next in it

Function Database()
 

mysql = ("SELECT * FROM Morrisons WHERE [Date] = #" & txtDateS.Text & "#")
 
      Get_My_Connection.Execute mysql

         If Not rs.EOF Then
           Do While Not rs.EOF
           txtMorActS.Text = rs!DailyTotal
           rs.MoveNext
           Loop
        Else
           txtMorActS.Text = "0"
           
        End If
End Function
0
 
Jinesh KamdarCommented:
Ok, so problem solved right?
0
 
samandrewAuthor Commented:
Hi Sorry
at work will have to relook at it tommorrow and will let you know

Many Thanks
for your patience
Andy
0
 
Jinesh KamdarCommented:
Ok.
0
 
samandrewAuthor Commented:
Hi
Am now using this code which is giving the following error

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

Private Sub ResetDates()
Dim datSunday As Date
   datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
      txtDate.Text = Format$(datSunday, "dd-mmm-yy")
 End Sub
     
Private Sub cmdEXIT_Click()
End
End Sub

Private Sub Form_Load()
Dim i As Long
Dim j As Long
Dim k As Long
Dim datSunday As Date
Dim datMonday As Date
Dim datTuesday As Date
Dim datWednesday As Date
Dim datThursday As Date
Dim datFriday As Date
Dim datSaturday As Date

datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
txtDateS.Text = Format$(datSunday, "dd-mmm")
datMonday = DateAdd("d", vbMonday - Weekday(Date), Date)
txtDateM.Text = Format$(datMonday, "dd-mmm")
datTuesday = DateAdd("d", vbTuesday - Weekday(Date), Date)
txtDateT.Text = Format$(datTuesday, "dd-mmm")
datWednesday = DateAdd("d", vbWednesday - Weekday(Date), Date)
txtDateW.Text = Format$(datWednesday, "dd-mmm")
datThursday = DateAdd("d", vbThursday - Weekday(Date), Date)
txtDateTh.Text = Format$(datThursday, "dd-mmm")
datFriday = DateAdd("d", vbFriday - Weekday(Date), Date)
txtDateF.Text = Format$(datFriday, "dd-mmm")
datSaturday = DateAdd("d", vbSaturday - Weekday(Date), Date)
txtDateSa.Text = Format$(datSaturday, "dd-mmm")


 Dim mysql As String
    For i = 0 To 6
   
        mysql = "#" & Format(datSunday, "yyyy-mm-dd") & "#"
        mysql _
            = " SELECT Gadbrook, Latimer, Stockton, Wakefield, Yate, DailyTotal" _
            & " FROM Morrisons " _
            & " WHERE Morrisons.[Date] Between" & mysql & " " & i - 7 & " And " & mysql
   
        With rs
           .LockType = 2
           .CursorType = 3
           .Open mysql, Get_My_Connection<<<<<<<<<<<Operation is not allowed when the object is open
     
           If .RecordCount > 0 Then
                txtMorEst(i).Text = .Fields("DailyTotal")
               
           
               
            Set rs = Nothing
            End If
.Close
End With

Next i

Database
End Sub
Function Database()
 
       
Dim response As Integer
Dim mysql As String
 
For i = 0 To 6

mysql = "SELECT * FROM Morrisons WHERE [Date] = #" & txtDateS.Text & "#"
 
        rs.Open mysql, Get_My_Connection
 
         If Not rs.EOF Then
           Do While Not rs.EOF
 
        txtMorActS(i).Text = rs!DailyTotal
       
        rs.MoveNext
        Loop
        Else
       
        txtMorActS(i).Text = "0"
       
       
      Set rs = Nothing

rs.Close
       
End If

 Next i
End Function

Many Thanks
Andy
0
 
Jinesh KamdarCommented:
Sorry, I'm not a VB guy, so can't help u there. However, your dynamic query doesn't seem right to me :

For i = 0 To 6
   
        mysql = "#" & Format(datSunday, "yyyy-mm-dd") & "#"
        mysql _
            = " SELECT Gadbrook, Latimer, Stockton, Wakefield, Yate, DailyTotal" _
            & " FROM Morrisons " _
            & " WHERE Morrisons.[Date] Between" & mysql & " " & i - 7 & " And " & mysql

After this step, mysql will be evaluated for the first iteration as -
SELECT Gadbrook, Latimer, Stockton, Wakefield, Yate, DailyTotal FROM Morrisons
WHERE Morrisons.[Date] Between" & "#" & Format(datSunday, "yyyy-mm-dd") & "#" & " " & 0 & " And " & "#" & Format(datSunday, "yyyy-mm-dd") & "#"
i.e. SELECT Gadbrook, Latimer, Stockton, Wakefield, Yate, DailyTotal FROM Morrisons
WHERE Morrisons.[Date] Between#2008-02-14# 0 And #2008-02-14#

Is that what u want? Why not just try this directly?        
mysql _     = " SELECT Gadbrook, Latimer, Stockton, Wakefield, Yate, DailyTotal" _
            & " FROM Morrisons " _
            & " WHERE Morrisons.[Date] Between #" & Format(datSunday - i), "yyyy-mm-dd") & "# AND #" & Format(datSunday), "yyyy-mm-dd") & "#"

Open in new window

0
 
samandrewAuthor Commented:
Hi
Thanks for the reply I will have to try this tommorow, the first query works fine as I am viewing a record which is from 7 days previous on the same day it is only when I use the function Database when I get the error.

Many Thanks
Andy
0
 
Jinesh KamdarCommented:
Is Format() a VB function or a MySQL function ?
0
 
samandrewAuthor Commented:
Hi Sorry for delay

Format is a vb function Im sure, the first part of my code works fine its the second part below I cant seem to get working.

Function Database()
 
       
Dim response As Integer
Dim mysql As String
 
For i = 0 To 6

mysql = "SELECT * FROM Morrisons WHERE [Date] = #" & txtDateS.Text & "#"
 
        rs.Open mysql, Get_My_Connection
 
         If Not rs.EOF Then
           Do While Not rs.EOF
 
        txtMorActS(i).Text = rs!DailyTotal
       
        rs.MoveNext
        Loop
        Else
       
        txtMorActS(i).Text = "0"
       
       
      Set rs = Nothing

rs.Close
       
End If

 Next i
End Function

Many Thanks
Andy
0
 
Jinesh KamdarCommented:
Oops, I just realised that the parentheses were not placed properly. Try this.
mysql = "SELECT Gadbrook, Latimer, Stockton, Wakefield, Yate, DailyTotal" _
      & " FROM Morrisons" _
      & " WHERE Morrisons.[Date] Between #" & Format(datSunday - i, "yyyy-mm-dd") & "# AND #" & Format(datSunday, "yyyy-mm-dd") & "#"

Open in new window

0
 
samandrewAuthor Commented:
Hi
Thanks but in my Function I only need to SELECT DailyTotal not Gadbrook, Latimer etc.

Many Thanks
Andy
0
 
samandrewAuthor Commented:
Hi Also ther is a syntax error in the code above ref date

Thanks
Andy
0
 
samandrewAuthor Commented:
Hi get error message on line below

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
 Function Database()
       
Dim response As Integer
Dim mysql As String
 
For i = 0 To 6
    mysql = "SELECT * FROM Morrisons WHERE [Date] = #" & txtDateS.Text & "#"
    rs.Open mysql, Get_My_Connection<<<<<<<<<Operation is not allowed when object is open
   
    If Not rs.EOF Then
       Do While Not rs.EOF
          txtMorActS(i).Text = rs!DailyTotal
          rs.MoveNext
       Loop
       Set rs = Nothing
       rs.Close
    Else
        txtMorActS(i).Text = "0"
        Set rs = Nothing
        rs.Close
    End If
Next i
 
End Function
0
 
samandrewAuthor Commented:
Hi There
Good News have got it working with the following code.

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

Private Sub ResetDates()
Dim datSunday As Date
   datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
      txtDate.Text = Format$(datSunday, "dd-mmm-yy")
 End Sub
     
Private Sub cmdEXIT_Click()
End
End Sub

Private Sub Form_Load()
Dim i As Long

Dim datSunday As Date


datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
txtDateS(0).Text = Format$(datSunday, "dd-mmm")
datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
txtDateS(1).Text = Format$(datSunday + 1, "dd-mmm")
datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
txtDateS(2).Text = Format$(datSunday + 2, "dd-mmm")
datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
txtDateS(3).Text = Format$(datSunday + 3, "dd-mmm")
datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
txtDateS(4).Text = Format$(datSunday + 4, "dd-mmm")
datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
txtDateS(5).Text = Format$(datSunday + 5, "dd-mmm")
datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
txtDateS(6).Text = Format$(datSunday + 6, "dd-mmm")


 Dim mysql As String
    For i = 0 To 6
   
        mysql = "#" & Format(datSunday, "yyyy-mm-dd") & "#"
        mysql _
            = " SELECT Gadbrook, Latimer, Stockton, Wakefield, Yate, DailyTotal" _
            & " FROM Morrisons " _
            & " WHERE Morrisons.[Date] Between" & mysql & " " & i - 7 & " And " & mysql
   
        With rs
           .LockType = 2
           .CursorType = 3
           .Open mysql, Get_My_Connection
     
           If .RecordCount > 0 Then
                txtMorEst(i).Text = .Fields("DailyTotal")
               
   
               
            Set rs = Nothing
            End If
.Close
End With
 Database
Next i


End Sub
Function Database()
 
       
Dim response As Integer
Dim mysql As String
 
For i = 0 To 6
    mysql = "SELECT * FROM Morrisons WHERE [Date] = #" & txtDateS(i).Text & "#"
    rs.Open mysql, Get_My_Connection
   
    If Not rs.EOF Then
       Do While Not rs.EOF
          txtMorActS(i).Text = rs!DailyTotal
          rs.MoveNext
       Loop
       Set rs = Nothing
       
    Else
        txtMorActS(i).Text = "0"
        Set rs = Nothing
       
    End If
Next i
 

End Function

Private Sub frmSwitchboard_Click()
frmSwitchboard
End Sub

Private Sub frmSwitchboard1_Click()
frmSwitchboard.Show
frmMorrisons.Hide
End Sub

Private Sub txtMorActS_Change(Index As Integer)
    Dim i As Integer
   
    For i = 0 To 6
        If IsNumeric(txtMorEst(i).Text) And IsNumeric(txtMorActS(i).Text) Then
            If CDbl(txtMorActS(i).Text) < 0.00001 Then
                txtPercent(i).Text = "0"
            Else
               
                txtPercent(i).Text = Format((CDbl(txtMorActS(i).Text) - CDbl(txtMorEst(i).Text)) / (CDbl(txtMorActS(i).Text)), "#0" & "%")

            End If
        End If
   
    Next i

    For j = 0 To 6
        If IsNumeric(txtMorEst(j).Text) Then
            If CDbl(txtMorEst(j).Text) < 1 Then
                txtMorMixS(j).Text = "0"
            Else
                txtMorMixS(j).Text = Format(txtMorEst(j).Text * 16 * 6 / 1925, "0.0")
               
            End If
        End If
   
       
    Next j

End Sub

Many Thanks For All your Help

Andy


0
 
Jinesh KamdarCommented:
Glad to be of help :)
0
All Courses

From novice to tech pro — start learning today.