Add Values in Table of Existing Data

[Hardik Patel]

Hi guys,

I have Form OrderLaptops and table laptops. I have fields in Laptops table like Description,Quantity,Location,Department,Part number etc....

and Description is a Primary Key so you cann't enter same data.

If I have Quantity(3) for Description(Dell),Location(Canada),Department(HR) and If I order to 2(Quantity) more Laptops of Dell(Description) in HR (department) of Canada(Location). so if i enter data in OrderLaptops form it should be automatically update data in table Laptops and there should be just one record like 5(quantity) and other fields are same.



So how can i do in access 2007?

Thank you very very much

[Adam Tippelt]

How does your form handle your 'input' to tell it that you are ordering two more? Once you've filled out the form, is there some sort of confirmation/update button?

If that's the case, you could put an update query in the _Click event of the confirmation button.

The query should be something along the lines of:

UPDATE TableLaptops SET TableLaptops.Quantity = TableLaptops.Quantity + Forms!OrderLaptops!QuantityField
WHERE TableLaptops.Description = Forms!OrderLaptops!Description;

That works on the assumption that the quantity you put into the form is how much you're increasing the quantity by. If you instead want to declare the new TOTAL, then the query can easily be changed to something like this:

UPDATE TableLaptops SET TableLaptops.Quantity = Forms!OrderLaptops!QuantityField
WHERE TableLaptops.Description = Forms!OrderLaptops!Description;

The where clause is important as it's used to identify that the record you update is only the record that you're looking at on the form, and not all the records.

If you want a more certain reply, you really could do with giving a bit more information about how the form actually functions (is the form bound to the table and you could just edit the records directly, or is it an unbound form that requires something like an update query)

Hope that helps.

Adam.

[Hardik Patel]

Actually I have Form OrderLaptops that have all data about table laptops means bound to laptops table and I have created ribbon in that there is one button "InsertData" if you click then it will open OrderLaptops form but it will not show all data it will just open new blank record and I do not have to confirmation button. you can not go or previous in that. So i am thinking if i got same data in table laptops then it should just change quantity.and if you open form OrderLaptops directly from elsewhere then it will show all data.

Thank you very very much

[Hardik Patel]

thanks dear,
i made button in the OrderLaptops form and in click event i write update query and its working exactlt that i want but
I want that if i write anything in form OrderLaptops it should add data in laptop table but if same record exist then it should update record.

Suppose I have Laptop 3(Quantity), HR(department),Canada(Location),HP(Description) then it should add new record in table and if I have that record exist in table then it should just add quantity so there should be quantity is 6 and every thing is same.

How can i do?
Thanks a Lot

[Adam Tippelt]

You should be able to merge your AddData button with the Update button, so one button does all:

I don't know what code you've got behind your AddData button but I'm assuming it's along the lines of:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec

(or similar - something that contains a save new record function)

You could change this to include an if statement based on a Dlookup:

If IsNull(Dlookup("YourTableIDName", "TableLaptops", "[Quantity]=" & Me.Quantity & " AND " & "[Department]='" & Me.Department & "'" & " AND " & "[Location]=' & Me.Location & "'" & " AND " & "[Description]='" & Me.Description & "'")) then
 
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
(Or whatever your current AddData procedure is for saving new records)
 
Else
 
DoCmd.OpenQuery "YourUpdateQuery"
 
End If

(Take note of the additional single quotes round string based fields in the Dlookup criteria)

This code is written under the assumption that the input fields for Quantity, Department, Location and Description on the form are named the same as the table columns - if not, change the Me.<field> areas to the correct names.

You might also want to change your update query so that the WHERE clause checks that all fields are the same, instead of just the description:

UPDATE TableLaptops SET TableLaptops.Quantity = TableLaptops.Quantity + Forms!OrderLaptops!QuantityField
WHERE (((TableLaptops.Quantity)=Forms!OrderLaptops!Quantity) AND ((TableLaptops.Department)=Forms!OrderLaptops!Department) AND ((TableLaptops.Location)=Forms!OrderLaptops!Location) AND ((TableLaptops.Description)=Forms!OrderLaptops!Description));

I think that should handle what you're after - that way when you click the AddData button, it'll decide if a record already exists for that combination of quantity/location etc, and just update it, or if it doesn't find an existing record, it'll add a new one.

Hope that helps.

Adam.