By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,636 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,636 IT Pros & Developers. It's quick & easy.

vb6 and mysql or loop to select certain dates in the database

P: 14
I have been scratchin my head all night. I want to program holiday reminders as when you book a holiday you could be reminded that is comming up for your employees.

to explain it shortly what I want to is:

-join two tables in the database Table 1 and table 2 which is also related table1 (1 to many) table 2
-select last record in table 2 accordingly to table 1 id

now what I have managed to do is select last record on any id where I can specify using a string variable in VB6
Now I could get this done if I came up with a loop that would change the string variable and add new item in the list box. Have a look what code i done for this and see if you could help me solve this before it kills me. Thanks guys

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.  
  4. Dim sql As String
  5. Dim sql2 As String
  6.  
  7. Dim varid As Integer
  8. Dim strname As String
  9. Dim intid As Integer
  10. Dim dtholiday As Date
  11. Dim intdaystaken As Integer
  12. Dim myarray(0 To 100) As String
  13.  
  14.  
  15. Private Sub Command1_Click()
  16.  
  17.  
  18. Call idnumbers
  19.  
  20.  
  21.  
  22.  
  23. End Sub
  24. Private Sub idnumbers()
  25. Dim myaaray(0 To 10) As String
  26. Dim x As Integer
  27. sql2 = "SELECT tblHoliday.absenceID, tblmain.fullname FROM tblmain INNER JOIN tblHoliday ON tblmain.ID = tblHoliday.AbsenceID;"
  28. Data1.RecordSource = sql2
  29. Data1.Refresh
  30.  
  31. List1.Clear
  32. Data1.Recordset.MoveFirst
  33. Do
  34. List1.AddItem Data1.Recordset!absenceid
  35. For x = 1 To 7
  36.          myarray(x) = Data1.Recordset!absenceid
  37.          MsgBox myaaray(x)
  38. Next x
  39. Data1.Recordset.MoveNext
  40.  
  41.  
  42.  
  43.  
  44.  
  45.  
  46. Loop Until Data1.Recordset.EOF
  47.  
  48. Dim i As Long
  49. Dim j As Long
  50.     With List1
  51.         For i = 0 To .ListCount - 1
  52.             For j = .ListCount To (i + 1) Step -1
  53.                 If .List(j) = .List(i) Then
  54.                     .RemoveItem j
  55.                 End If
  56.             Next
  57.         Next
  58.     End With
  59.  
  60. End Sub
  61.  
  62. Private Sub Command2_Click()
  63.  
  64. Call gethols
  65.  
  66. End Sub
  67.  
  68. Private Sub Form_Load()
  69.  
  70. Data1.DatabaseName = App.Path & "\Database97.mdb"
  71.  
  72.  
  73.  
  74.  
  75.  
  76.  
  77.  
  78.  
  79.  
  80.  
  81.  
  82. End Sub
  83. Private Sub gethols()
  84.  
  85.  
  86. sql = "SELECT TOP 1 tblmain.ID, tblmain.fullname, tblHoliday.Holidays, tblHoliday.DaysTaken FROM tblmain INNER JOIN tblHoliday ON tblmain.ID = tblHoliday.AbsenceID WHERE (((tblmain.ID) LIKE '*" & varid & "*'))ORDER BY tblHoliday.Holidays;"
  87. Data1.RecordSource = sql
  88. Data1.Refresh
  89. intid = Data1.Recordset!id
  90. dtholiday = Data1.Recordset!Holidays
  91. strname = Data1.Recordset!fullname
  92. intdaystaken = Data1.Recordset!daystaken
  93.  
  94. 'List1.AddItem Data1.Recordset!ID
  95.  
  96. List2.AddItem intid & " " & strname & " " & dtholiday & " " & intdaystaken
  97. End Sub
  98.  
  99.  
  100.  
  101.  
Feb 8 '13 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,365
As far as your code goes, you need to populate your varid variable.

And as for your method, it would probably be easier if you just did it using SQL without all this code. You could join it to an aggregate query that will let you return a single record. I don't know your table layouts or what the data looks like but it's probably a lot simpler to use just SQL.
Feb 9 '13 #2

P: 14
the problem is that I am not very good on SQL all i was able to do is come up with inner join and return top 1 record descending so it selects last record. can you show me an example how you would do this sql code? say my first table is tblmain and another 1 is tblholidays which is related tblmain (1 to many) tblholidays.

thank you I would be apreciated if you could help me.
Feb 10 '13 #3

Rabbit
Expert Mod 10K+
P: 12,365
Like I already said, I don't know your table layouts or what the data looks like so there's only so much I can say and I've already said it. Unless you provide me with more information, there's not much else I can do to help.
Feb 10 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.