I need help with my CODE!!!!!!!

[scollins]

I am trying to find out if the customer id is already in the table:

Set dbs = CurrentDb()
str = "SELECT [CUSTOMER DATA].* FROM [CUSTOMER DATA];"
Set rst2 = dbs.OpenRecordset(str)
If Me![CustAcctID] = rst2!CustAcctID Then
MsgBox "This Customer already exists in the database", vbCritical

Can someone help me with my syntax?

 

[jtmach]

Try this

Set dbs = CurrentDb()
str = "SELECT * FROM [CUSTOMER DATA] WHERE CustAcctID = " & Me![CustAcctID]
Set rst2 = dbs.OpenRecordset(str)
if rst2.eof = false Then
        MsgBox "This Customer already exists in the database", vbCritical
end if

Basically this will open a recordset where all the records must have a CustAcctID equal to the one in your form. If the recordset comes back at the end if the file (EOF) then the CustAcctID is not in the form. If it is not at the end of the file the customer exists
The hardest questions always have the easiest answers.

 

[jebry]

Hi!

Try this:

Dim rst2 As DAO.Recordset
Dim str As String
str = "SELECT CustAcctID FROM [CUSTOMER DATA] Where CustAcctID = '" & Me.!YourTextBox & "'"
Set rst2 = CurrentDB.OpenRecordset(str)
If rst2.EOF = False OR rst2.BOF = False Then
MsgBox "This Customer already exists in the database", vbCritical)
End If

hth
Jeff Bridgham
bridgham@purdue.edu

 

[JoaoTL]

Set dbs = CurrentDb()
str = "SELECT [CUSTOMER DATA].* FROM [CUSTOMER DATA];"
Set rst2 = dbs.OpenRecordset(str)


rst2.MoveFirst
rst2.FindFirst("[CustAcctID] =" Me.CustAcctID)
If .NoMatch Then
'Do Nothing
else
MsgBox "This Customer already exists in the database", vbCritical
End If

Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt

 

[esakal]

hello,

why don't u use the dlookup function ?

' use this code if the ID field is a string type
if dlookup("CustAcctID","customer data", "CustAcctID = '" & Me![CustAcctID] & "'&quot then msgbox "the Customer ID exist"

' use this code if the ID field is integer type
if dlookup("CustAcctID","customer data", "CustAcctID = " & Me![CustAcctID]) then msgbox "the Customer ID exist"

yours,
esakal