Replies

Hi Andrew
Many thanks for your quick response. I used the vba code as a This Workbook On_Open procedure and it works beautifully. It’s exactly what I’ve been looking for.
Regards
Rob

Thanks John for the sample db. Apparently, my problem was in the query. Essentially, the only difference between yours and mine was that in the videoid column (the second in the QBE grid) you had inserted “Group by” whereas mine did not. I changed my query accordingly and it now works. Very odd.
Still, your help has been much appreciated. Sorry about the naff terminology – I’m still learning.

Rob

Thanks for the responses.
On reboot I was “allowed” to delete the .ldb file. The original now opens and closes without problems.
Again thanks. This is a fantastic forum. More power to Woody’s elbow! (Colloquial expression)

Problem is half-solved. I have been able to copy the original file and paste it in a diiferent folder from where it opens with no problem. However, I am still unable to delete either the original file or its associated .ldb file – Access denied! Any suggestions?

Thanks guys for your time and suggestions. At last, I’ve achieved what I set out to do. The query I used in my original DLookup was a [CountOf video number] based on a selected video in combo box. I set up another query (qry_available)which just listed instances of the selected video stored in the member_video table. In the unbound text box I used a DCount function to display the count of videos returned by qry_available. In the Name of Video combo box I requeried qry_available when a selection was made and now it works perfectly. What I don’t understand is that the Dlookup I originally used should have combined the two-stage process (query then count) into one (display CountOfvideo number from qry_numavail). Still, It now does its job. Again, many thanks, all.
Rob

Thanks for your response. Yes, numavail is the name of the query which displays the accurate value of 3 in the CountOfvideo field when the query is run and the name of the test video is typed into the parameter message box. This is what is driving me mad. The combo box with the drop down list of videos and the text box in which I want to display availability is situated on the same form. Would this create a problem?

Thanks for your time. I’ve done as you’ve suggested but an error#, appears in the box. I’ve checked all names used and the syntax but I’ve drawn a blank. What’s so frustrating is that the query, on its own works fine.
The expression I’ve used is: =DLookup(“[CountOfvideo]”,”numavail”).
If you have the time(or the inclination) do you have any suggestions?

Thanks for the response. I have managed to build a query that correctly returns the count of video numbers based on a selected video in a combo box. However, I am having difficulty in displaying this result on an unbound control as you suggested. Would you run through the steps to accomplish this? Also, I don’t know how to use a dlookup on a form. I would be grateful for your help.
Rob

Take a look at this site which offers a very elegant solution to your problem. Although it is a doctor’s appointment system, it is essentially the same idea, ie, to avoid booking clashes. This solution provides a drop down list displaying only those appointments which are free.

http://www.advanced-ict.info/databases/clash.html%5B/url%5D Edited by Wendell to activate link 09-Oct-02 17:34 MDT (UTC-7)

Rob

Hi Mark
Your first suggestion on the list worked a treat. I wish all problems were so easily solved.
Again, many thanks for your help and time.
Rob

Hi MarkJ
Many thanks for your previous help and the useful splash screen. However another problem has emerged with the calendar control. I have used it successfuly over the weekend at home but, after downloading it today for use in my school, on double-clicking the date text box to call up the popup, I get an error message. This reads “Compile error: Can’t find project or library” and the offending code is presented. The Date argument is highlighted of the IIf function and is supposedly not recognized in the definitions. The same applies to the other popups. Any ideas?
Regards
Rob

Hi MarkJ
Many thanks for the quick response. I eventually sorted out my problem. My date field in my appointments table is formatted for British dates (dd,mm,yy) whilst the calendar assumes American dates. I simply changed the calendar code from “mm,dd,yy” to “dd,mm,yy” and everything works perfectly. The pop up certainly adds a touch of class to the form and I can see that it will be so useful for other projects. Have you any other little gems? Again many thanks.

Rob

Hi MarkJ
I,ve downloaded the brilliant calendar db you recommended to another lounger. I’ve managed to get the popup working when I select my date box on a form but when I select a date from the calendar, an apparently random date is placed in the box. When I try again, the calendar pops up at the last date and, after selecting a required date, the wrong date is input. At least the year is correct but month and day is not. Any suggestions? Many thanks for your time.

Rob

Many thanks for your help. Unfortunately, I am getting syntax error messages when I use the expression you supplied. I substituted the table and field names for those in my database. A little more guidance as to using the expression would be greatly appreciated.