Results 1 to 10 of 10

Thread: [RESOLVED] Need help to feed textbox array

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

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

    Name:  My pic.jpg
Views: 158
Size:  30.4 KB

    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.

  2. #2
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    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.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    Re: Need help to feed textbox array

    Quote Originally Posted by LaVolpe View Post
    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

  4. #4
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    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.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    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.

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    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

    Name:  pic.jpg
Views: 96
Size:  20.2 KB

  7. #7
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    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.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    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

  9. #9
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    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.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    915

    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
  •  



Click Here to Expand Forum to Full Width