-
Nov 18th, 2017, 01:49 PM
#1
Thread Starter
Fanatic Member
[RESOLVED] Need help to feed textbox array
Hello experts of vbforums
I want to develop a planner and I 'm completely stuck at populating my 31 indexed textboxes.
This is my query:
Code:
sSQL = "SELECT * FROM patient WHERE " & _
"year(Date_Appointment) = " & cboYear.Text & " and Month(Date_Appointment) = " & cboMonth.ListIndex
This is what I need to do but I couldn't find a way to feed my textboxes as they appear on the picture.
As you can see, text1 (2) .text =2 appointments because in my database I have two appointments on 02/12/2017
text1(4) .text = 1 appointment because in my database I have one appointment on 04/12/2017
text1(6) .text = 3 appointments because in my database I have 3 appointment on 06/12/2017
Thanks in advance for any idea
Last edited by newbie2; Nov 18th, 2017 at 01:56 PM.
-
Nov 18th, 2017, 01:51 PM
#2
Re: Need help to feed textbox array
So you want your textboxes to reflect the number of appointments for that day, if any? Suggestion: rewrite your query using "Group By" the date so you can get the counts, per day.
-
Nov 18th, 2017, 02:52 PM
#3
Thread Starter
Fanatic Member
Re: Need help to feed textbox array
Originally Posted by LaVolpe
So you want your textboxes to reflect the number of appointments for that day, if any? Suggestion: rewrite your query using "Group By" the date so you can get the counts, per day.
Thanks sir for your interest.
You helped me to make my need clearer.
Yes I want my textboxes to reflect the number of appointments in that specific month.
Example:
I want Text1(1).text to reflect the appointments of the 1st day of the month and year displayed in the comboxes.
Text1(2).text to reflect the appointments of the 2 nd day .
Text1(3).text to reflect the appointments of the 3 rd day .
and so on.
This what I'm trying to do but in vain.
Code:
sSQL = "SELECT * FROM patient WHERE " & _
"year(Date_Appointment) = " & cboYear.Text & " and Month(Date_Appointment) = " & cboMonth.ListIndex + 1
RS.Open sSQL, DB, adOpenForwardOnly
If Not RS.EOF Then
For i = 1 To Text1.Count - 1
Do While Not RS.EOF
x = Day(RS!Date_Appointment)
Text1(i).Text = x
RS.MoveNext
Loop
Next
-
Nov 18th, 2017, 03:04 PM
#4
Re: Need help to feed textbox array
Try this query and see what you get. Best to test it in your DB first before making any changes to your code.
Code:
sSQL = SELECT Date_Appointment, COUNT(Date_Appointment) AS AppCount FROM patient" & _
" WHERE year(Date_Appointment) = " & cboYear.Text & " and Month(Date_Appointment) = " & cboMonth.ListIndex + 1 & _
" GROUP BY Date_Appointment
The 2nd field should return the total appointments. If it works, bone up a bit on Group By clauses. Can be very useful
Above is air-code, apologize for any typos
Edited: If no appointments exist for any particular day of the month, then no record will be returned. This is a simple matter for your calendar... clear all textboxes first, then run the query and update only those relative to the query results.
Edited yet again... Regarding your code
Code:
x = Day(RS!Date_Appointment)
Text1(i).Text = x
Think this is what you want
Code:
Text1(Day(RS!Date_Appointment)).Text = RS!AppCount & " appointments"
The above assumes your Text1() array is 1-bound. If not, offset it to zero-bound:
Code:
Text1(Day(RS!Date_Appointment)-1).Text = RS!AppCount & " appointments"
Last edited by LaVolpe; Nov 18th, 2017 at 03:16 PM.
-
Nov 18th, 2017, 04:33 PM
#5
Thread Starter
Fanatic Member
Re: Need help to feed textbox array
LaVolpe
You are a great gentelman.
I really can't find the words to thank you.
Without your help, I would stay a month without succeeding to find the appropriate query.
Thanks million times.
-
Nov 18th, 2017, 04:41 PM
#6
Thread Starter
Fanatic Member
Re: Need help to feed textbox array
Excuse me for bothering you again
I hope to add an array of comboboxes to my calendar.
The comboboxes are to hold the names of patients concerned with the appointments.
I don't know if that possible in the same query or I have to run a new query.
I also wonder if the second query is so complicated?
Thanks again
-
Nov 18th, 2017, 04:58 PM
#7
Re: Need help to feed textbox array
No the first query doesn't really fit there. But a second query would not be terribly difficult...
Code:
sSQL = "SELECT Date_Appointment, Patient_Name FROM patient WHERE " & _
"year(Date_Appointment) = " & cboYear.Text & " and Month(Date_Appointment) = " & cboMonth.ListIndex + 1 & _
"ORDER BY Patient_Name"
I've used Patient_Name as the field name (both in query above & air code below), obviously replace it with the appropriate info you are returning.
Notice that I've included the date along with the names. You can use the date to determine which combo to fill. Air code follows... I've added the -1 for a zero-bound combo1() array. Remove that if your array is one-bound.
Code:
...
RS.Open sSQL, DB, adOpenForwardOnly
Do Until RS.EOF = True
Combo1(Day(RS!Date_Appointment) - 1).AddItem RS!Patient_Name
RS.MoveNext
Loop
RS.Close
On second thought, you might want to sort the query by appointment time and you can add that into the combo box along with the patient name, something like following, making assumption that the appointment date has the time also, i.e., 1 Dec 2017 8:30 AM (or whatever date/time format)
Code:
Combo1(Day(RS!Date_Appointment) - 1).AddItem Format(RS!Date_Appointment, "hh:nn") & ": " & RS!Patient_Name
Last edited by LaVolpe; Nov 18th, 2017 at 05:09 PM.
-
Nov 18th, 2017, 05:49 PM
#8
Thread Starter
Fanatic Member
Re: Need help to feed textbox array
Million thanks sir
You are so so helpful
Not only you helped me but you taught me precious things in database queries.
I'll never forget your kindness
-
Nov 18th, 2017, 06:00 PM
#9
Re: [RESOLVED] Need help to feed textbox array
You're welcome.
As a side note. I think your design could be better. You are attempting to display would could be large amounts of data in a limited space. Maybe a call-out type of window (think of a multiline tooltip for example) may be a better choice? There are examples of multiline tooltips on this forum. The combobox for example is probably not going to be wide enough to display long patient names without being clipped. Of course you could subclass them and make the dropdown list wider, but that's another task. This is all just a thought.
-
Nov 18th, 2017, 06:22 PM
#10
Thread Starter
Fanatic Member
Re: [RESOLVED] Need help to feed textbox array
Code:
you could subclass them and make the dropdown list wider
thanks sir for this pertinent remark.
This is exactly what I intend to do.
thank you again
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|