If Exists

[AssetManagement]

Hello, I am new to Access and I am trying to put a condition on a form button using VB.

What I am trying to is to take a field on a form (HistoryEntry.Barcode) and have it compared to the Asset table to verify that the barcode exist (Barcode is the column name).

If it exists, it will insert all the information on the form into the History table. If it does not it will return a message box.

I know that in SQL I could use the
IF exists (select * from Assets where barcode = HistoryEntry.Barcode) then
insert into History (columnnames) values (columnvalues).

Currently my VB does (This is the standard button macro in Access):

Private Sub Add_Asset_History_Click()
On Error GoTo Add_Asset_History_Click_Err

On Error Resume Next
DoCmd.GoToRecord , "", acNewRec
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If

Add_Asset_History_Click_Exit:
Exit Sub

Add_Asset_History_Click_Err:
MsgBox Error$
Resume Add_Asset_History_Click_Exit

End Sub

 

[pr2-eugin]

Try a DCount and if the Count <> 0 then there exists a Barcode in the History table..

Also you are better of in VBA that Macros..

 

[denomc100]

Hi,

Try this

Sub test2()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from BarCode where ref_no = 10000000 ")
If rs.EOF And rs.BOF Then
MsgBox "Not On Table"
Else
MsgBox "Record on Table"
End If

rs.Close


End Sub

 

[AssetManagement]

I guess the next question i have is how do you call a field from a form?

 

[JackKaptijn]

I find the function Dlookup very helpfull.
You should try Dlookup("Barcode","Assets","Barcode = " & Me.History.Barcode)

When the barcode does not exist you will get Null as return.

 

[pr2-eugin]

@JackKaptijn > Not to put your solution of DLookUp down, though it somehow does the same thing, in my point is not recommended.. Just because of the fact you have to add another set of code to check if it is a Null value, as you cannot compare Null with any string values..

[COLOR=Green]' If Barcode is Numeric type[/COLOR]
DCount("*","Assets","Barcode = " & Me.History.Barcode)
[COLOR=Green]' If Barcode is Text type[/COLOR]
DCount("*","Assets","Barcode = '" & Me.History.Barcode & "'")

As shown above you have to concatenate the field values by using " and &