Update Query help!

[SamCrow]

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

[ItsMe]

Maybe something like this

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

[SamCrow]

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

[ItsMe]

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.

[SamCrow]

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?

[ItsMe]

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.