Vba Dcount please help :) (1 Viewer)

[dale_j1992]

Hello all, i relatively new and have not used access in many months, i have slight issue any help would be greatly appreciated.

I currently have a textbox which i enter a barcode using a scanner, this triggers the following code.

Private Sub TxtBarInp_AfterUpdate()
If DCount("barcode", "tbllog", "[barcode]=" & "'" & TxtBarInp & "'") > 0 Then
overwrite_resp = MsgBox("Barcode Number " _
& TxtBarInp & " has already been entered." _
& vbCr & vbCr & "Do you want to overwrite the record?", vbYesNo + vbDefaultButton, "Duplicated Barcode")
If overwrite_resp = vbYes Then
MsgBox ("You will now been taken to the record.")
Call overwriteyes
DoCmd.Requery
DoCmd.OpenForm "Frmlog"
Else
End If
Else
Call overwriteno
DoCmd.Requery
DoCmd.OpenForm "Frmlog"

End If
End Sub


This works at the first station as it tells me if the barcode has been already inputted before however now there is a station required after the barcode will already in the system. Is there a way of modifying this code too look in the “tblLog” table and for the barcode, but only triggering the overwrite if the fields the second station fill in are blank, these :banghead:ufields are ‘Grindingstate’ Grindinglogdate’ & ‘grindinglogby’.

Any help is greatly appreciated.

 

[arnelgp]

what do you mean by station and what is the name of the second station?

 

[dale_j1992]

Thanks for your reply.

Basically i have a computer set up on the line (station 1), the operator scans a barcode on the part, the code i posted above currently searches my table to see if the barcode has already been scanned/used, if it has it prompts you if you wish to overwrite, if the barcode is not in use it opens as a new record ready to add it. When the form opens the operator enters part okay, scrap or rework, then the date, operator name are saved automatically. This was all working fine, until there is now another station after this, so the barcode is already in the table, so when scanned it asks to overwrite, i need to find a way to look in table for the barcode then, then check if the fields in the table that the operator at station2 completes are blank/null. I am not very savvy with access this may ve simple for you.
My other method of thinking was to use a different table for each station and combine the data later on when required for viewing.
Many thanks for your time.

 

[arnelgp]

try adding a field also on your table (MachineName) that saves the name of the user, therefore you only need to check this field if it were scanned on station 2 or on this stattion (station 1).

you can use Environ("UserName") to save it to the table:

add MachineName field in your pop up form, make it'ts property Locked=True.
then on BeforeUpdate Event of the form fill this field:

Private sub form_BeforeUpdate(Cancel As Integer)
Me.MachineName = Environ("userName")
End Sub

and your other code would be:

Private Sub TxtBarInp_AfterUpdate()
If DCount("*", "tbllog", "[barcode]=" & "'" & TxtBarInp & "' And MachineName = Environ('UserName')") > 0 Then
    overwrite_resp = MsgBox("Barcode Number " _
                & TxtBarInp & " has already been entered." _
            & vbCr & vbCr & "Do you want to overwrite the record?", vbYesNo + vbDefaultButton, "Duplicated Barcode")
        If overwrite_resp = vbYes Then
            MsgBox ("You will now been taken to the record.")
            Call overwriteyes
            DoCmd.Requery
            DoCmd.OpenForm "Frmlog"
        Else
        End If
Else
    Call overwriteno
    DoCmd.Requery
    DoCmd.OpenForm "Frmlog"

End If
End Sub

 

[Mark_]

To elaborate on Arnel's response, think this through from the end users perspective.

You are describing a situation where your user interface needs to match each position. You should have one form for the first "Station" and that is the only form a user assigned to the first "Station" sees. You need a separate form for the second "Station" that only deals with those fields the second "station" is responsible for.

It is fine to have separate forms that each work on a different part of a record. You are seeing one of those situations. It does mean you will want to break out your "Edited by" information into a child table so that you know who changed what, when. This method will help greatly when you discover you have another "station" or multiple additional stations that you need to add.