Results 1 to 6 of 6
  1. #1
    SamCrow is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    11

    Update Query help!

    Hey guys,

    I have 3 tables: Items, Location, and Employee.
    Items contain: ItemID, ItemName, ItemDescription, Qty, Price
    Location contains: Aislenumber, Floor, ShelfNumber, EID, ItemID
    Employee contains: EID, EFName, ELName, EPhone, EAisle

    The database is a customer related database, where they go into the supermarket and search for the item that they want.
    So let's say a customer walks into that supermarket, and wants to know where a 'kitkat' is located.
    So he types in 'kitkat' and it will tell him the location of that item with the price and the quantity on hand.



    But when the customer types in a specific number of kitkats, let's say he wants 5 kitkats... and the Qty of kitkats is 150 in total, I want to make an update query, where when he searches for a specific number of an item, the Qty of that item will automatically be updated. So let's say he wants 5 kitkats, then the total number would be 145 kitkats.

    How can I make that query?

    Thanks

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe something like this

    Code:
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    Dim intItemCount As Integer
    Dim intItemReq As Integer
    intItemReq = Me.NameOfControlWithCustomerRequest
    Set rst = dbs.OpenRecordset("Items", dbOpenDynaset)
    rst.FindFirst "ItemID = " & Me.ItemID
        If rst.EOF = False Then
        
            If rst![Qty] >= intItemReq Then  'Go ahead and process the request
                intItemCount = rst![Qty]
                rst.Edit
                rst![Qty] = intItemCount - intItemReq
                rst.Update
            Else
                MsgBox "There are not enough items to fulfill the order"
            End If  'rst![Qty] >= intItemReq
        
        End If
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

  3. #3
    SamCrow is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    11
    Where can I type this as I am working on Microsoft Access, and I am still a bit new to it.
    This is a project that I am doing for my professor and we still did not learn how to code.

    Thanks

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    The way you word the question I would not use SQL exclusively. You should implement some sort of verification of inventory. For this you will need interaction with the user and a proper way would be to use a form and VBA. The code I provided would work in an event handler within a VBA module. You could place it in a click event of a command button for instance.

    What is the assignment? Create an update query? What tools are you supposed to use? Access is going to need to know things about the existing inventory and the User's request before running an UPDATE query.

    You can create an UPDATE query using the query builder and then view the SQL.

  5. #5
    SamCrow is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    11
    My assignment is simply to create a database. I had to learn Access all by myself since the course is only an SQL course. We only learn how to code SQL statements.
    The course has nothing to do with Access, it is strictly SQL and normalization of tables.
    The update query is something I wanted to do to give my database a better image; but, I guess this is still too advanced for me right now isn't it?

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Access uses Object Oriented Programming. It is all about classes and objects. To get this done, Access relies heavily on VBA. I took a look at some of your other threads. The code I provided will address a couple issues you mentioned. YOu could build a new form and place a couple controls on it to test it out.

    This line needs a bound control to the PK
    rst.FindFirst "ItemID = " & Me.ItemID
    The first itemid represents a field name in the table and the second the bound control name


    This line needs an unbound control for the user input.
    intItemReq = Me.NameOfControlWithCustomerRequest

    The code can go behind a click event if a control button. In the property sheet for the control, you can click the elipses(...) to the right of the On Click field and then select "Code Builder". Available Event Handlers are found in the "Event" tab of the properties sheet.

    Try it out and see if it is confusing or not. You can incorporate SQL strings directly int VBA, even into the DAO example I provided.




Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  2. Replies: 1
    Last Post: 05-17-2013, 12:53 PM
  3. Replies: 2
    Last Post: 08-30-2012, 07:59 AM
  4. Replies: 4
    Last Post: 11-10-2011, 03:41 PM
  5. Replies: 1
    Last Post: 08-19-2011, 12:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums