change value to table if record exist (1 Viewer)

[hardik_088]

Hi ,

I have Form OrderLaptops and table laptops. I have fields in Laptops table like Description,[quantity ordered],Location,Department,Part number etc....

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

i made button in the OrderLaptops form and in click event i write update query.
button click event Code is here
DoCmd.RunSQL "UPDATE Laptops SET Laptops.[quantity ordered] = Laptops.[quantity ordered] + Forms!OrderLaptops![Quantity Ordered] WHERE Laptops.Description = Forms!OrderLaptops.Description"

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 Ordered), 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.


So how can i do in access 2007?

Thank you very very much

 

[VilaRestal]

OrderLaptops form AfterUpdate event (could alternatively be put in its Quantity Ordered AfterUpdate event).

Before I start, how will you know whether the addition has already been made? For example, if someone makes an order and it updates the table - the laptop was already there so it adds the number to its existing one - what happens if they then go back in to the same order and change the amount? How will it know that it's already added one figure from that order and needs to subtract it before adding the new one? Would you stop users from doing that (by not allowing editing of an order once it's been made)? Or some other method?

Anyway, that issue aside, the code would need to be something like this (in the OrderLaptops' module):

Private Sub Form_AfterUpdate()
    'Form should already have verified that Description and [Quantity Ordered] are not null (should be required fields in an order)
    Dim rsLaptops As DAO.Recordset
    Set rsLaptops = CurrentDB.OpenRecordset("SELECT * FROM Laptops WHERE Description = " & Chr(34) & Me!Description & Chr(34), dbOpenDynaset)
    With rsLaptops
        If .RecordCount = 0 Then
            'Add laptop:
            .AddNew
            .Fields("Description") = Me!Description
            .Fields("[Quantity Ordered]") = Me![Quantity Ordered]
        Else
            'Update laptop:
            .MoveFirst 'Primary key so can only be one record
            .Edit
            .Fields("[Quantity Ordered]") = Nz(.Fields("[Quantity Ordered]"),0) + Me![Quantity Ordered]
        End If
        .Update
        .Close
    End With
End Sub

With regards to the issue I mentioned: Perhaps a better way would be to just add the laptop if its not already there and then calculate a laptop's quantity ordered as a Sum of LaptopOrders.[Quantity Ordered] for that laptop minus some system of noting when orders get delivered (Sum of LaptopsDelivered.[Quantity Delivered] for that Laptop perhaps).

 

[hardik_088]

Thanks dear,
I made buttion in OrderLaptop and i write code like

Private Sub Command0_Click()
DoCmd.RunSQL "UPDATE Laptops SET Laptops.[quantity ordered] = Laptops.[quantity ordered] + Forms!OrderLaptop![Quantity Ordered] WHERE Laptops.Description = Forms!OrderLaptop.Description"
DoCmd.Close acForm, "OrderLaptop"

End Sub

but it is just updating tabel but i have problem is that if record not exist it shoud create a new record. so what i should do.

 

[hardik_088]

hi i tried to insert data but it get error in query so can you tell me

DoCmd.RunSQL "INSERT INTO Laptop (Location, Description, [quantity ordered]) VALUES (Forms!OrderLaptops!Location.value, Forms!OrderLaptops!Description.value, Forms!OrderLaptops![quantity ordered].value)"

 

[VilaRestal]

It will be looking for fields called Forms!OrderLaptops!Location.value and there's no such field. It needs to have the values inserted into the string. It would need to be this:


DoCmd.RunSQL "INSERT INTO Laptop (Location, Description, [quantity ordered]) VALUES (" & Chr(34) & Forms!OrderLaptops!Location.value & Chr(34) & ", " & Chr(34) & Forms!OrderLaptops!Description.value & Chr(34) & ", " & Forms!OrderLaptops![quantity ordered].value & ")"

Likewise the update would need to be this:

DoCmd.RunSQL "UPDATE Laptops SET Laptops.[quantity ordered] = Laptops.[quantity ordered] + " & Forms!OrderLaptop![Quantity Ordered] & " WHERE Laptops.Description = " & Chr(34) & Forms!OrderLaptop.Description & Chr(34)

 

[boblarson]

It will be looking for fields called Forms!OrderLaptops!Location.value and there's no such field. It needs to have the values inserted into the string. It would need to be this:


DoCmd.RunSQL "INSERT INTO Laptop (Location, Description, [quantity ordered]) VALUES (" & Chr(34) & Forms!OrderLaptops!Location.value & Chr(34) & ", " & Chr(34) & Forms!OrderLaptops!Description.value & Chr(34) & ", " & Chr(34) & Forms!OrderLaptops![quantity ordered].value & Chr(34) & ")"

Did you mean to include the Chr(34)'s with quantity ordered? Wouldn't that be a numeric field (I would hope) and therefore wouldn't use quotes?

 

[VilaRestal]

Did you mean to include the Chr(34)'s with quantity ordered? Wouldn't that be a numeric field (I would hope) and therefore wouldn't use quotes?

Yeah I noticed and edited it Well spotted

 

[VilaRestal]

It will be looking for fields called Forms!OrderLaptops!Location.value and there's no such field. It needs to have the values inserted into the string. It would need to be this:


DoCmd.RunSQL "INSERT INTO Laptop (Location, Description, [quantity ordered]) VALUES (" & Chr(34) & Forms!OrderLaptops!Location.value & Chr(34) & ", " & Chr(34) & Forms!OrderLaptops!Description.value & Chr(34) & ", " & Forms!OrderLaptops![quantity ordered].value & ")"

Likewise the update would need to be this:

DoCmd.RunSQL "UPDATE Laptops SET Laptops.[quantity ordered] = Laptops.[quantity ordered] + " & Forms!OrderLaptop![Quantity Ordered] & " WHERE Laptops.Description = " & Chr(34) & Forms!OrderLaptop.Description & Chr(34)

But I notice in one the form is called OrderLaptops and the other its called OrderLaptop. Some mistake surely

 

[hardik_088]

thanks
ya its work perfectly but now if i enter data ,i want to check record exist or not how can i ?

 

[VilaRestal]

Various ways

Easiest might be:

If DCount("Description","Laptops","Description = " & Chr(34) & Forms!OrderLaptop.Description & Chr(34)) > 0 Then
    'Laptop is in table already
Else
    'Laptop is not in table
End If

 

[hardik_088]

Thanks you very very much its work nicely but when i click on button it showing pop up that you append or update record if i don't want that pop up or if i want my message what i should do?

 

[VilaRestal]

Put

Application.DisplayAlerts = False

at the start and

Application.DisplayAlerts = True

at the end

 

[boblarson]

Put

Application.DisplayAlerts = False

at the start and

Application.DisplayAlerts = True

at the end

Oops, got the programs mixed up. That is for Excel. Access is

DoCmd.SetWarnings False

and

DoCmd.SetWarnings True


but if you use those make sure to have an error handler and an exit point in your procedure, so it can reset if there is an error, otherwise it becomes a real pain for other things.

 

[hardik_088]

Cheers Man It works perfectly that I want

Thank you very very very very much

 

[VilaRestal]

Oops indeed (I've been doing Excel VBA recently and it's starting to affect me )

Thanks for that Bob

 

[hardik_088]

All subform records

Hi ,
I have form "SearchLaptops" and that has one text box(txtKeyword) ,one combobox (txtSearchBy)and one button(btnSearch) and also sub form "SubLaptop" . when I click on "btnSearch" I will get data in subform that I want to find. And when I click on record selector, another form "Laptops" will open with that record where i clicked in subform.

but it is just showing that record but i want to see all record that i find in subform and it should open with that record where i clicked.

example .. I find 10 records in subform. if i click on record 5 ,the form laptops should open with all records that i find in subform and display record number 5.

And Laptops form is bound with Laptops table
How can I do.? I tried this code in Subform.

Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_Form_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Laptops"
stLinkCriteria = "[serial number]=" & Me![serial number]
DoCmd.OpenForm stDocName, , , stLinkCriteria



Exit_Form_Click:
Exit Sub

Err_Form_Click:
MsgBox Err.Description
Resume Exit_Form_Click


End Sub


Thanks a lot

 

[hardik_088]

insert value in table

hi ,
Can you tell me where I am wrong here.

Code

DoCmd.RunSQL "INSERT INTO Laptops (Date,[requsition type],type,[purchase requisition],Location,department,requestor,[suggested supplier],[quantity ordered],[part number] ,Description) VALUES (" & Forms!OrderLaptops![Date].Value & "," & Chr(34) & Forms!OrderLaptops![requsition type].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![Type].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![purchase requisition].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops!Location.Value & Chr(34) & ", " & Chr(34) & Forms!OrderLaptops![Department].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![suggested supplier].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![Requestor].Value & Chr(34) & "," & Forms!OrderLaptops![quantity ordered].Value & "," & Chr(34) & Forms!OrderLaptops![part number].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops!Description.Value & Chr(34) & ")"

if I try just for field then its working

code

DoCmd.RunSQL "INSERT INTO Laptops (Location, Description, [quantity ordered]) VALUES (" & Chr(34) & Forms!OrderLaptops!Location.value & Chr(34) & ", " & Chr(34) & Forms!OrderLaptops!Description.value & Chr(34) & ", " & Forms!OrderLaptops![quantity ordered].value & ")"

Thanks a lot

 

[VilaRestal]

The problem is with the date field:

Similarly to how values for text fields must be enclosed in quotation marks:
Chr(34) & ValueForTextField & Chr(34)
date fields must be enclosed in hash signs:
"#" & ValueForDateField & "#"
also I think they need to be formatted in USA date format (I'm not certain for an SQL insert statement):
"#" & Format(ValueForDateField,"mm/dd/yyyy") & "#"

So, assuming all the other field types are correct and there's no other mistakes (I can't see any), the code should be this (I've used a With statement to make it easier (if ever there was a time to use it this is it ) and removed the .Value bits - not needed):

With Forms!OrderLaptops
    DoCmd.RunSQL "INSERT INTO Laptops " _
            & "(Date,[requisition type],type,[purchase requisition],Location,department,requestor,[suggested supplier],[quantity ordered],[part number] ,Description) VALUES (" _
            & "#" & Format(![Date],"mm/dd/yyyy") & "#," _
            & Chr(34) & ![requisition type] & Chr(34) & "," _
            & Chr(34) & ![Type] & Chr(34) & "," _
            & Chr(34) & ![purchase requisition] & Chr(34) & "," _
            & Chr(34) & !Location & Chr(34) & ", " _
            & Chr(34) & ![Department] & Chr(34) & "," _
            & Chr(34) & ![suggested supplier] & Chr(34) & "," _
            & Chr(34) & ![Requestor] & Chr(34) & "," _
            & ![quantity ordered] & "," _
            & Chr(34) & ![part number] & Chr(34) & "," _
            & Chr(34) & !Description & Chr(34) & ")"
End With

And I corrected the spelling of field [requisition type], that needs checking

 

[boblarson]

And the field names DATE and type must be put in square brackets or else it will fail. Another good reason to NOT use that word as a field or object name as they are Access Reserved Words.

    DoCmd.RunSQL "INSERT INTO Laptops " _
            & "([B][COLOR=red][Date][/COLOR][/B],[requisition type],[B][COLOR=red][type][/COLOR][/B],[purchase requisition],Location,department,requestor,[suggested supplier],[quantity ordered],[part number] ,Description) VALUES (" _

 

[hardik_088]

hi,
yes i tried that code and its work but without date if i add date then not

can you check just (date )where i am wrong ?

code
DoCmd.RunSQL "INSERT INTO Laptops (Date,[requsition type],type,[purchase requisition],Location,department,requestor,[suggested supplier],[quantity ordered],[part number] ,Description,[privincial tracking number],[po number],[status],[received],[billing code],[itscc code],[itscc task number]) " & _
" VALUES ( " & "#" & Format(Forms!OrderLaptops![Date].Value, "mm/dd/yyyy") & "#" & "," & Chr(34) & Forms!OrderLaptops![requsition type].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![Type].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![purchase requisition].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops!Location.Value & Chr(34) & ", " & Chr(34) & Forms!OrderLaptops![Department].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![suggested supplier].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![Requestor].Value & Chr(34) & "," & Forms!OrderLaptops![quantity ordered].Value & "," & Chr(34) & Forms!OrderLaptops![part number].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops!Description.Value & Chr(34) & ", " & _
" " & Chr(34) & Forms!OrderLaptops![privincial tracking number].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![po number].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![Status].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![Received].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![billing code].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![itscc code].Value & Chr(34) & "," & Chr(34) & Forms!OrderLaptops![itscc task number].Value & Chr(34) & ")"


Thanks