Update value with dcount (1 Viewer)

[hardik_088]

hi guys,
i have laptops form and that has description,[quantity ordered],location,[suggested supplier] ,[part number] fields and i have form "Insert_data" that have textbox and i want to insert data and also want to check this fields descritption,[suggested supplier],[quantity ordered],[part number],location and if same data is there in table then it should update otherwise insert new data and i have tried following code but when it is checking fields it is checking just description field and updating or inserting data So what i should do to check that fields.

If (Forms!Insert_Data![Type].Value = "Laptops") Then

If DCount("Description", "Laptops", "Description = " & Chr(34) & Forms!Insert_Data.Description & Chr(34)) > 0 Then
If DCount("Location", "Laptops", "Location = " & Chr(34) & Forms!Insert_Data.Location & Chr(34)) > 0 Then
If DCount("[part number]", "Laptops", "[part number] = " & Chr(34) & Forms!Insert_Data.[part number] & Chr(34)) > 0 Then
DoCmd.RunSQL "UPDATE Laptops SET Laptops.[quantity ordered] = Laptops.[quantity ordered] + " & Forms!Insert_Data![quantity ordered] & " WHERE Laptops.Description = " & Chr(34) & Forms!Insert_Data.Description & Chr(34)
DoCmd.Close acForm, "Insert_Data"
End If
End If
Else

DoCmd.RunSQL "INSERT INTO Laptops ([requsition type],[type],[purchase requisition],Location,department,requestor,[suggested supplier],[quantity ordered],[part number] ,Description,[privincial tracking number],[po number],[status],[billing code],[itscc code],[itscc task number]) " & _
" VALUES (" & Chr(34) & Forms!Insert_Data![requsition type].Value & Chr(34) & "," & Chr(34) & Forms!Insert_Data![Type].Value & Chr(34) & "," & Chr(34) & Forms!Insert_Data![purchase requisition].Value & Chr(34) & "," & Chr(34) & Forms!Insert_Data!Location.Value & Chr(34) & ", " & Chr(34) & Forms!Insert_Data![Department].Value & Chr(34) & "," & Chr(34) & Forms!Insert_Data!Supplier.Value & Chr(34) & "," & Chr(34) & Forms!Insert_Data![Requestor].Value & Chr(34) & "," & Forms!Insert_Data![quantity ordered].Value & "," & Chr(34) & Forms!Insert_Data![part number].Value & Chr(34) & "," & Chr(34) & Forms!Insert_Data!Description.Value & Chr(34) & ", " & _
" " & Chr(34) & Forms!Insert_Data![privincial tracking number].Value & Chr(34) & "," & Chr(34) & Forms!Insert_Data![po number].Value & Chr(34) & "," & Chr(34) & Forms!Insert_Data![Status].Value & Chr(34) & "," & Chr(34) & Forms!Insert_Data![billing code].Value & Chr(34) & "," & Chr(34) & Forms!Insert_Data![itscc code].Value & Chr(34) & "," & Chr(34) & Forms!Insert_Data![itscc task number].Value & Chr(34) & ")"

End If



Thanks you very much

 

[MarkK]

What doesn't make sense to me is having a table named 'Laptops'. Wouldn't it make more sense to have a table named 'Product' with a column called 'ProductType' that might have a value of 'Laptop'? What do you do if you need to start dealing with Desktops and PDAs, create a new table for each of these?

 

[hardik_088]

sorry friend but i have used many places so i cant change
and i also thought about that after created tables but i have created many thing so i did noot change.
and for desktop and other peripherals i have different table

thanks

 

[MarkK]

My 2c is that it will always be easier to correct a table design problem than to work around it.
Cheers,

 

[hardik_088]

Thanks ,
I am trying to change table design but i want to solve that error (to check all the fields with DCount function or any other ways)

 

[MarkK]

I would not keep a running sum of the quantity ordered, rather I would calculate that value from the raw data as needed. A bank account balance, for instance, is not stored as a number somewhere. It is a calculation of all deposits minus all withdrawals.
Likewise in an inventory system, the sum of QuantityOrdered should not be stored somewhere as a number. You should calculate it from the raw data when you need it. This makes your system much more simple to maintain because you don't have to do complex calculations--what you are doing here--when you store data; you simply store it. Do your calculations when you retrieve your data.
But again, I believe you have a table design problem because QuantityOrdered should be a field in a table called Orders, or OrderDetails. A table called Laptop might have a fields called Brand, Model, Weight, Processor, ScreenSize and so on, but the QuantityOrdered is not a property of a laptop, it is a propery of an order.
Cheers,
Mark

 

[hardik_088]

Thanks
I know that i have used wrong name for fields and i will change that later on. and can you please tell me waht i have done wrong in query for now.

 

[hardik_088]

hi guys,

I am puting code here but here my problem is that i have used Dcount function and description field is working good but when i use both [part number] and [department] fields then not working and if i use just one from [part number] and [department] field then its working what is the problem i am not getting

If (Forms!Insert_data![Type].Value = "Laptops") Then

If DCount("Description", "Laptops", "Description = " & Chr(34) & Forms!Insert_data.Description & Chr(34)) > 0 Then
If DCount("[part number]", "Laptops", "[Part number] = " & Chr(34) & Forms!Insert_data.[part number] & Chr(34)) > 0 Then
If DCount("department", "Laptops", "department = " & Chr(34) & Forms!Insert_data.[Department] & Chr(34)) > 0 Then
DoCmd.RunSQL "UPDATE Laptops SET Laptops.[quantity ordered] = Laptops.[quantity ordered] + " & Forms!Insert_data![quantity ordered] & " WHERE Laptops.Description = " & Chr(34) & Forms!Insert_data.Description & Chr(34)
DoCmd.Close acForm, "Insert_Data"

Else

DoCmd.RunSQL "INSERT INTO Laptops ([requsition type],[type],[purchase requisition],Location,department,requestor,[suggested supplier],[quantity ordered],[part number] ,Description,[privincial tracking number],[po number],[status],[billing code],[itscc code],[itscc task number]) " & _
" VALUES (" & Chr(34) & Forms!Insert_data![requsition type].Value & Chr(34) & "," & Chr(34) & Forms!Insert_data![Type].Value & Chr(34) & "," & Chr(34) & Forms!Insert_data![purchase requisition].Value & Chr(34) & "," & Chr(34) & Forms!Insert_data!Location.Value & Chr(34) & ", " & Chr(34) & Forms!Insert_data![Department].Value & Chr(34) & "," & Chr(34) & Forms!Insert_data!Supplier.Value & Chr(34) & "," & Chr(34) & Forms!Insert_data![Requestor].Value & Chr(34) & "," & Forms!Insert_data![quantity ordered].Value & "," & Chr(34) & Forms!Insert_data![part number].Value & Chr(34) & "," & Chr(34) & Forms!Insert_data!Description.Value & Chr(34) & ", " & _
" " & Chr(34) & Forms!Insert_data![privincial tracking number].Value & Chr(34) & "," & Chr(34) & Forms!Insert_data![po number].Value & Chr(34) & "," & Chr(34) & Forms!Insert_data![Status].Value & Chr(34) & "," & Chr(34) & Forms!Insert_data![billing code].Value & Chr(34) & "," & Chr(34) & Forms!Insert_data![itscc code].Value & Chr(34) & "," & Chr(34) & Forms!Insert_data![itscc task number].Value & Chr(34) & ")"
End If
End If
End If

Thanks

 

[boblarson]

Is Department and Part number both text?

Also, perhaps if you posted a copy of the database so we can actually see what is happening, that might help. I might not be able to do anything right away though as I am pretty busy at work and I also only have Access 2003 here.

 

[hardik_088]

hi bob,
here is my database you just open "Insert_data" form and insert data if you add same data that already exist in database then it will update but if you insert different data then it is not inserting values in table.

For example you can try this data
Type :- Laptop
Supplier:- PC Corp
Description:- Toshiba Tecra M10
Department: IT
Part Number TMB3C-0L06F

then it will work but if change value in department or part num,ber it should insert data but it is not.