Check availability (97/2000)

Topic

New Reply

I have a form called tbl-memvid which stores customer number, video number and date. Records are inputted from frmRental. There are five copies of every video and I want a way of indicating whether a copy of a video is available before I save the record to the table. I know it should be embarrassingly simple but any help would be greatly appreciated.
Rob

Reply | Quote

Replies

Buld a query that counts the number of copies of the selected video that are on loan at the moment.
You can retrieve this number on the form using a dlookup, then 5 – this number is the number available.
Display this number in an unbound control on the form.

Reply | Quote

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

Reply | Quote

OK, lets assume your query is called qryCountsLoans, and that the field it returns is called countofVideoID.

Create an unbound text box. Use the textbox tool from the toolbox. Open the properties box for that control and set its controlsource to be:
= dlookup(“[CountofVideoID]”,”qryCountLoans”)
The structure of a dlookup is “field to return”, “qry or table to look in”, “any criteria”
In your case the query will only have one record so we don’t need any criteria.
If this correctly displays the number of loans, then change the control source to be:
= 5 – dlookup(“[CountofVideoID]”,”qryCountLoans”)
It should display the number available.
There is one extra step. If there are no loans of a particular video, your query probably won’t return zero. Instead it won’t return anything at all ( a null). This needs to be dealt with using the NZ function Null to Zero
= 5 – nz(dlookup(“[CountofVideoID]”,”qryCountLoans”),0)

Reply | Quote

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?

Reply | Quote

Is numavail the name of your query? If not that could well be your problem. Remember the syntax for DLookup is (“[FieldName]”,”TableOrQueryName”,”Criteria”)

Another approach that we often recommend for this kind of problem is to use a bound form where you could step through amd see all of the info displayed about all videos, and then use an unbound combo box to set a filter to restrict the data being viewed to a specific video. This has some performance benefits compared to using DLookup functions.

Reply | Quote

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?

Reply | Quote

I suspect the problem lies in how the query is getting it’s criteria. Try using the table the query is based on and the same criteria you’re using in the query in a DCount() function instead:

=DCount(“[VideoID]”, “VideoTableName”, “[VideoID] = ” & Forms!FormName!VideoID & ” And [Rented] = ” & False) ‘or whatever your criteria needs to be.

Reply | Quote

We really need a bit more detail to troubleshoot this for you, so let me ask some questions:

1. Is your form actually bound to something – perhaps the table? (Being bound means there is an entry in the RecordSource property of the table.)
2. If the form is bound, is the query numavail the query the form is bound to?
3. What does the syntax of your query look like? (You can switch to the SQL view and post the string – screen captures of the query grid are too large to readily post.)

Your suspicion may be correct, in that the there may be a circular reference involved, but without seeing the details it’s hard to tell.

Reply | Quote

I think Jerry’s suggestion is right on the money.
A problem I think you have is that the query references a control on the form and the dlookup references the query, does this have a problem? Charlotte would know this for sure.
If you can, why don’t you post your DB without any confidential info and see if we can work it out (old Beatles song, boy that dates me !!).
Pat

Reply | Quote

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

Reply | Quote

Rob
Just for interest in created a db to see if the dlookup method really would work, and didn’t have any problems.
I enclose a sample in Access 2000 format.

Reply | Quote

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

Reply | Quote