Combine record with Same name and add quantity

[theinviter]

Dears;
good afternoon;

i would like to seek your support. as i have created a form to track medication taken from storage.
so i want when i scan the item name twice, the quantity changes from default 1 to 2.
Form name: Data entry
as the user will just scan the barcode to add the item.
how can i do this, please advise.

file attached.

 

[RogerCooper]

That seems like a dangerous thing to do, as the user could scan twice by mistake. Especially for tracking medication, the user should be forced to enter the quantity. I wouldn't even use a default, what if you need to audit your inventory for controlled substances.

 

[jdraw]

Good point Roger. In any hospital situation that would be a med-error with serious repercussion possible-especially to the patient.

 

[theinviter]

That seems like a dangerous thing to do, as the user could scan twice by mistake. Especially for tracking medication, the user should be forced to enter the quantity. I wouldn't even use a default, what if you need to audit your inventory for controlled substances.

actually ,we are designing this for tracking the number of medication taken from cabinet for each patient. just for number tracking.
can you please advise

 

[jdraw]

Suggest for thoroughness you consider:

Medication name (DIN)
Medication strength (if applicable)
Quantity
Person name/id doing this transaction
Patient receiving the medication
Date/timestamp
Directed by (Doctor)

 

[theinviter]

I TRIED BELOW, BUT NEED SOME CORRECTION.
Private Sub Scan_BeforeUpdate(Cancel As Integer)
Dim blnFound As Boolean
Dim s1 As String
Dim S2 As String

On Error GoTo errhandler:


' check if the Item already exists on the table
' if it exists, go to the record instead

s1 = Scan

With Me.RecordsetClone

.FindFirst "

 = '" & [Scan] & "'"
    blnFound = Not (.NoMatch)
    
    If blnFound Then
        ' goto that record on the form
         
        Me.Bookmark = .Bookmark
        Me.Qnty = Me.Qnty.Value + 1
        '
        
    End If
End With

' if there is no duplicate add new record
If Not blnFound Then
    DoCmd.GoToRecord , , acNewRec
    If Nz(DLookup("[Item]", "[Medication]", "[Code] ='" & [Scan] & "'"), 0) = 0 Then
Me.Code = DLookup("[Item]", "[Medication]", "[Other Code] ='" & [Scan] & "'")
Me.Med_Name = DLookup("[Item]", "[Medication]", "[Other Code] ='" & [Scan] & "'")

Else
Me.Med_Name = DLookup("[Item]", "[Medication]", "[Code] ='" & [Scan] & "'")
Me.Code = Scan
End If

'DoCmd.GoToRecord , , acNewRec

        
End If

Exit Sub
errhandler:
    'error handling code
   Resume Next