Populate Calendar listbox

[mgmslate]

I have a calendar form to show appointments.There is a textbox to show the day and a listbox for each day to view the appointments. When the user clicks the company name the appointments form opens to show the customer info & notes.
This is the code to open the calendar
Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
Dim x%, ctl$
For x = 1 To 42


If month(Me(&quot;Day&quot; & x)) <> month(Me![Date]) Then
Me(&quot;Day&quot; & x).Visible = False
Me(&quot;list&quot; & x).Visible = False
Else
Me(&quot;Day&quot; & x).Visible = True
Me(&quot;day&quot; & x).Visible = True
End If

Next x
Set Today = Me(&quot;Day&quot; & Trim(str$(DateDiff(&quot;day&quot;, Me!WD, Date))))
Today.ForeColor = RGB(255, 0, 0)

Me!Back.SetFocus
End Sub

This is the listbox rowsource.
SELECT ContactLog.LogID, Customers.CompanyName, ContactLog.Callbackdate, ContactLog.Rep, ContactLog.Reminder
FROM Customers INNER JOIN ContactLog ON Customers.CustomerID = ContactLog.Customerid
WHERE (((ContactLog.Callbackdate)=[forms]![frmcal]![day]) AND ((ContactLog.Rep)=CurrentUser()) AND ((ContactLog.Reminder)=Yes));
Since I have 37 days the form runs 37 queries when it opens.
Can I remove the rowsource in each listbox and populate each listbox by running 1 query when the forms opens to speed it up. Any help to point me in the right direction would be appreciated.
Thanks

 

[gusbrunston]

Hi:

I didn't know that each list box would run it's query when the form opened. Learn something every day. But can't you use the On Got Focus event to run the query? So the query doesn't run until you tab to the list box or click on it? Maybe you'd have to leave the listboxes unbound to do that.

1) You might be able to use a crosstab query. I've only done one, so I'm no expert.

2) If you want more speed, you can do nothing that will speed up your forms more than to go to Tools => Options => General => and take the check mark out of the &quot;Track name Auto Correct info.&quot; (Then you have to be careful about renaming tables and things.)

Hope something works for you.

Cheers Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[mgmslate]

I want the sales reps to see the appointments they have scheduled when the forms loads. I had the Track name auto correct info unchecked. Did not know this would affect renaming.
Thanks
Mike