LookUp a value in a table

  • Thread starter Thread starter Dimitris Nikolakakis
  • Start date Start date
D

Dimitris Nikolakakis

I have a table Orders (OrderID, Date, ... ... ...)

I have a form FOrderInsert and I have a TextBox and the user gives an
OrderID.

I have put a button on the form that inserts a record in Orders table with
the value of TextBox and Date=Now().
My problem is if the record exists (TextBox exists in Orders.OrderID).

My thought is to write code......

IF Me.TEXTBOX EXISTS IN ORDERS TABLE THEN
MSGBOX
Me.ActiveControl.Undo
ELSE
INSERT RECORD IN ORDERS

but I do not know the exact way.


thanks
 
The fastest method is to build a recordset and count the number of records
that match the ID. If you have more than a few thousand records, that would
be the preferrable method. The easiest way is probably to use DLookup or
DCount to get a count of records matching the criteria:

Dim x As Integer
x = DLookup("OrderID","Orders","OrderID=" & Me.TxetBoxName)

If x = 1 Then Me.Undo

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
The line below was meant to use DCount and was also mistyped:

x = DCount("OrderID","Orders","OrderID=" & Me.TextBoxName)

The recordset replacement for DLookup is built like:

Function QuickLookup(strField As String, strTable As String, strWhere As
String) As Variant
On Error GoTo Error_handler

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

strSQL = "SELECT " & strTable & "." & strField & " FROM " & strTable & "
WHERE " & strWhere & ";"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.RecordCount <> 0 Then
QuickLookup = rs(strField)
Else
QuickLookup = Null
End If

Exit_Here:
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_here

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top