update list box from SQL

[ninja_imp]

HI

I am trying to populate 3 list boxs with data from a table. I have the below code but all i seem to get is the table name or sql statement entered into the listbox. I wonderd if anyone had any ideas? Code follows:

Option Compare Database





Private Sub Form_Load()

Dim daysinmonth As Integer, NextMonth As Integer, EndOfMonth As Integer, currentDate As Date, i As Integer, daysofthemonth As Date
Dim DDay As Integer, DYear As Integer, Dmonth As Integer, DToday As Date
Dim intI As Integer, DDate As Date, todaydate As Date
Dim MySQL As String

NextMonth = DateAdd("m", 1, currentDate)
EndOfMonth = NextMonth - DatePart("d", NextMonth)
daysinmonth = DatePart("d", EndOfMonth)

DDay = Day(Now())
Dmonth = Month(Now())
DYear = Year(Now())

DToday = DateSerial((DYear), (Dmonth), (DDay))

todaydate = DToday - DDay

DDate = todaydate

For i = 1 To 3
DDate = DDate + 1
MySQL = "SELECT tbl_Appt.ApptDate FROM tbl_Appt Where tbl_Appt.ApptDate = " & (DDate) & " ORDER BY tbl_Appt.ApptTime"

Me("list" & CStr(i)).RowSourceType = "value List"
Me("list" & CStr(i)).RowSource = MySQL
Me("list" & CStr(i)).Value = Me("list" & CStr(i)).ItemData(0)
Next
End Sub


many thanks

 

[pbaldy]

Perhaps a different selection here would be more appropriate.

Me("list" & CStr(i)).RowSourceType = "value List"

 

[ninja_imp]

hi

im sorry but im not quite sure what you mean - im still really learning the ropes!!!

regards

 

[pbaldy]

What options are available for that setting in design view? Is one of them more appropriate to your situation?

 

[ninja_imp]

hi

im having a really stupid day and still cant quite get my round what you mean?!! Sorry

I have 3 list boxes and i want each one to list results from the tbl_Appt based on the date contained in DDate. But instead of the list box showing the result - it just displays the sql statement itself or the name of the table! I have tried changing the rowsource type to 'Field List', 'Value List' & 'Table/Quey' but none of them give the reults.

Could it be a problem with the SQL or something?

oh, and many thansk for taking the time to have a look at this for me

regards

 

[pbaldy]

You want Table/Query for starters. I would set it in design view and take it out of the code. There's no reason to set it dynamically if it never changes. What happens when you do that? You may need to make this change too:

MySQL = "SELECT tbl_Appt.ApptDate FROM tbl_Appt Where tbl_Appt.ApptDate = #" & (DDate) & "# ORDER BY tbl_Appt.ApptTime"

 

[ninja_imp]

hi

just made the change on the #" & (DDate) & "#

and the first list box is now returning the correct results - but the other two are still empty.

The plan for this is to have 31 listboxs and have them populated by this one function - but because i need the date to increment by one each time until it reaches the total days in the month i put it into the procedure. wasnt really sure what else to do?

regards

 

[ninja_imp]

GOT IT - slightly amended to #" & Format(DDate, "yyyy-mm-dd") & "# and now works across all fields - many thanks for your help

 

[pbaldy]

31 listboxes? That will be one mother of a form! What is the business problem you're trying to solve?

 

[ninja_imp]

Im trying to build a dynamic calendar - which will show appointments against the relevant date. but then the listboxs will display only for the current days in the month and so on......Well thats the idea - not sure if its the best one though!

Regards