Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    11

    Angry Unanswered: Using VB to edit records

    Hi all,

    I am building a database system for a video shop and I want to be able to view all the videos I have in the shop (so not the ones that are currently on loan.) To do this, I need a field in the video table that says if it's in the shop or not, which I have called abStatus and made a boolean check box.

    Therefore if a video is in the shop the box in the table is ticked, if it's out then it's unchecked. I need this so I can then run a query on all the videos with ticks.

    Want I want to know is how do I change the status of the checkbox when i click a button in a form for example? So that when the video is loaned, the checkbox gets unticked so that the video doesnt appear in queries for the remaining videos.

    I think I need to use visual basic to do this, but I'm not familiar with the functions for access and I'm not sure how to do it.

    Any help is greatly appreciated

    Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Use a WHERE clause in your query.

    SELECT *
    FROM yourTable
    WHERE abStatus = 0

    assuming unchecked means it's in stock, -1 if it's the other way around
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2004
    Posts
    11
    Hi thanks for your response.

    It's not the query I need the help with, It's the actual changing the status of the checkbox for the record in the table. I've think i've established what I need to do :

    1 - To call in the unique key for the video that needs to be changed and put it into a variable.

    2 - To use some kind of function to select the right record from the table, using the variable in 1.

    3 - To change the status of the now selected record from a ticked to an unchecked textbox.

    I know what I need to do, just a bit unsure of the code.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    well... that seems like a lot of work..

    What happens during the checkout process? Logic seems to dictate it would be much easier to set your flags at that time.

    Truth be told, I don't really thing you should be using flags at all, I think you should consider using table schema and queries to return current inventories.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2004
    Posts
    11
    When a user processes a loan, a record is created in another table with all the details, customer name, ID, which video they have loaned etc...

    When a video is returned, the loan record is deleted, this is the way we are required to do it as part of the criteria.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's some pretty bizarre criteria. Not applicable to the real world at all.

    How do you pull a rental history for a given patron?

    The elegant (and fast) solution is to put a return date on the loan record. Then query for loan records where the return date is null to see everything that's checked out. Or you can outer join the tables together based on video_id where loan record is not null to return current inventory.

    Anywho, whatever you do to delete the loan record, you could run an update statement using the DoCmd.RunSQL to execute an update statement.

    I assume you have some sort of key or something for the video, so you could use a statement similar to this:

    DoCmd.RunSQL "UPDATE videos SET abStatus = 0 WHERE video_id = " & txtBoxWithVideoID.Value

    I'm not sure what your process for returning videos is, so I'm not sure exactly how to implement this, but that's the basic idea I would suggest.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Apr 2004
    Posts
    11
    As i said, i'm a beginner, we don't need to be able to view a history of what people have loaned. I'll try that and see where I get, thanks for your help;

  8. #8
    Join Date
    May 2004
    Posts
    16

    this works for me

    try adding a field to you table, call it qty in.

    every time a video is hired use an update query to remove 1 from the qty in record for that film title.

    every time one is returned use an update query to add 1 to the qty in for that title.

    this is much easier than using a check box and has the added advantage of allowing the user to see how many of a certain title are "in" at any one time.
    (assuming you will have more than 1 copy of some titles.)


    regards

    andy

Posting Permissions

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