Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check availability (97/2000)

    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
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Check availability (97/2000)

    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.
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check availability (97/2000)

    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
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Check availability (97/2000)

    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)
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check availability (97/2000)

    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?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Check availability (97/2000)

    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.
    Wendell

  7. #7
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check availability (97/2000)

    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?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  8. #8
    New Lounger
    Join Date
    Oct 2002
    Location
    Chattanooga, Tennessee, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check availability (97/2000)

    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.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Check availability (97/2000)

    We really need a bit more detail to troubleshoot this for you, so let me ask some questions:
    1. <LI>Is your form actually bound to something - perhaps the table? (Being bound means there is an entry in the RecordSource property of the table.)
      <LI>If the form is bound, is the query numavail the query the form is bound to?
      <LI>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.
    Wendell

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Check availability (97/2000)

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check availability (97/2000)

    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
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Check availability (97/2000)

    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.
    Attached Files Attached Files
    Regards
    John



  13. #13
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check availability (97/2000)

    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
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •