Verify info

[Tom]

Hi all,

the user supposed to type in company name through a form into a table. when
he exits or still working on the form I would like the DB to check if that
company name is already in the list and if thats the case, give him a
massage that that company name is already in the system


Thanks,

Tom

 

[Arvin Meyer [MVP]]

Something like this ought to work in the control's BeforeUpdate event:

Private Sub txtCompanyName_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler

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

Set db = CurrentDb

strSQL = "SELECT [CompanyName] FROM tblCompany WHERE [CompanyName] = """ &
Me![txtCompanyName] & """"
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "This Company is already in the database.", vbOKOnly, "Duplicate"
End If

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

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

 

[Tom]

Thanks Arvin, it`s working great.
What do I need to add in order to clear the text box.
I tried:

Me.company=Null

That doesnt work.

Thanks for the help,
Tom

Something like this ought to work in the control's BeforeUpdate event:

Private Sub txtCompanyName_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler

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

Set db = CurrentDb

strSQL = "SELECT [CompanyName] FROM tblCompany WHERE [CompanyName] = """ &
Me![txtCompanyName] & """"
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "This Company is already in the database.", vbOKOnly,
"Duplicate"
End If

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

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

Hi all,

the user supposed to type in company name through a form into a table. when
he exits or still working on the form I would like the DB to check if that
company name is already in the list and if thats the case, give him a
massage that that company name is already in the system


Thanks,

Tom

 

[Arvin Meyer [MVP]]

Since you should still me in txtCompanyName, I'd simply use a

Me.txtCompanyName.Undo

right after the Cancel statement.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Thanks Arvin, it`s working great.
What do I need to add in order to clear the text box.
I tried:

Me.company=Null

That doesnt work.

Thanks for the help,
Tom

Something like this ought to work in the control's BeforeUpdate event:

Private Sub txtCompanyName_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler

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

Set db = CurrentDb

strSQL = "SELECT [CompanyName] FROM tblCompany WHERE [CompanyName] = """ &
Me![txtCompanyName] & """"
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "This Company is already in the database.", vbOKOnly,
"Duplicate"
End If

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

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

Hi all,

the user supposed to type in company name through a form into a table. when
he exits or still working on the form I would like the DB to check if that
company name is already in the list and if thats the case, give him a
massage that that company name is already in the system


Thanks,

Tom