Docmd.findrecord If record is not found then ... (1 Viewer)

[Dranoweb]

Hi,
I'm currently designing an inventory system for my workplace using a barcode scanner (software keyboard wedge type).

At present I have it use the prefix CTRL + B

On my form (access 2007) I have the keypress event monitor for the acsii code for CTRL + B (Ascii 2) as this appears to do nothing to the form.

When Ascii code 2 is detected, opens an input box for the barcode.

Then when barcode is entered and carriage return is hit (Barcode suffix) a search is triggered:

DoCmd.FindRecord strName, acAnywhere, False, acSearchAll, False, acAll, True

Now where I need help, is working out how to trigger an event if nothing is found. In this case I plan to have it open a new record, and drop the contents of my string into the barcode box.

'DoCmd.GoToRecord , , acNewRec
'barcode.Value = strName

My current sub below:

Private Sub Form_KeyPress(KeyAscii As Integer)
Dim strName As String  'Barcode string

If KeyAscii = 2 Then
Beep
barcode.SetFocus

'Open an input box for barcode 
 strName = InputBox(Prompt:="PLEASE ENTER BARCODE:", _
          Title:="ENTER BARCODE!", Default:="9315626027678")

        If strName = "ALPACA" Or _
           strName = vbNullString Then

           Exit Sub

        Else
'search for barcode
DoCmd.FindRecord strName, acAnywhere, False, acSearchAll, False, acAll, True

'<<<< Start Code to be run if nothing found
'DoCmd.GoToRecord , , acNewRec
'barcode.Value = strName
'<<<< End Code to be run if nothing found
        End If
stock.SetFocus

'<<< Redundant function
'Command17_Click
'<<< Redundant function

End If
End Sub

 

[John Big Booty]

You could use the Dcount() function to first check if the barcode already exists (i.e.. Dcount() > 0) , if it does then run your existing code otherwise add a new record.

 

[Dranoweb]

Thanks for the input, it has led me to a new way of doing things, but I dont seem to be getting my syntax right. I have tried a few ways, and have settled on the last one being the correct.

stock is the table in question.
ID2 is my primary key.
barcode is the column I wish to search
strName is the variable (string) that contains the barcode to search for.

Dim FoundOrNot As Long
    'FoundOrNot = DCount("ID2", "STOCK", "[barcode] = strName")

    'FoundOrNot = DCount(ID2, "stock", "[" & barcode & "] = " & strName)

    FoundOrNot = DCount("ID2", "stock", "[barcode] = " & strName)

 

[John Big Booty]

Try;

FoundOrNot = DCount("ID2", "stock", "[barcode] = " & Chr(34) & strName & CHR(34))

As strName is a string variable it must be enclosed in quotes CHR(34). You might also see;

FoundOrNot = DCount("ID2", "stock", "[barcode] = '" & strName & "'")

Which is essentially the same just not as clear.

 

[Dranoweb]

First answer worked.

I used msgbox foundornot as a temporary output, and it shows 0 for not found and 1 for found.

Thankyou very much - been at this for two days, with mountains of stock piling up. I can at last get on with the work.

I owe you one.

 

[Dranoweb]

Just thought I would post my working code for those trawling the net in search of answers like I do.

I added some check boxes to allow for automatic adding and subtracting as I scan objects.

I also fixed my routine that will add a new record if an unknown barcode is scanned, and set it's stock value to 1.

Private Sub Form_KeyPress(KeyAscii As Integer)
Dim strName As String

If KeyAscii = 2 Then

RunCommand acCmdSaveRecord

Beep
barcode.SetFocus
 
 strName = InputBox(Prompt:="PLEASE ENTER BARCODE:", _
          Title:="ENTER BARCODE!", Default:="9315626027678")

        If strName = "ALPACA" Or _
           strName = vbNullString Then

           Exit Sub

        Else
        
        Dim FoundOrNot As Long

    FoundOrNot = DCount("ID2", "stock", "[barcode] = " & Chr(34) & strName & Chr(34))
   
If FoundOrNot = 0 Then
DoCmd.GoToRecord , , acNewRec
barcode.Value = strName
stock.Value = 1
Else
DoCmd.FindRecord strName, acAnywhere, False, acSearchAll, False, acAll, True

If Check24.Value = -1 Then
'ADD
Command21_Click
End If
If Check34.Value = -1 Then
'SUBTRACT
Command22_Click
End If




End If

End If

stock.SetFocus

'<<< Redundant function
'Command17_Click
'<<< Redundant function

End If
End Sub

 

[John Big Booty]

Good work, glad you got it sorted