Access MS Access: Avoiding duplicates from an append query using DLookup?

[abue42]

Hi Guys!

First post and in need of help

I have a form (frmAllCustomers) based on the table (tblAllCustomers) that displays all companies in the market with potential business for my company. When a new contract is undertaken i have a command button on frmAllCustomers that runs an append query to move the company's details into frmClients (based on tblClients).
I want to add to my code so that if the record trying to be appended from frmAllCustomers already exists in frmClients, a friendly message box appears such as "Company is already a customer!" instead of Access' Key Violation message. I've been told DLookup is the way to go. any ideas? I have something similar to this:


DLookup(["CustomerID"]["tblClient"] me.customerID)
If DLookup is = 0 Then
Run query
Else
MsgBox(Company already a customer!)

Many Thanx in advance

Andy

 

[cvo-aalst]

Hi Guys!

First post and in need of help

I have a form (frmAllCustomers) based on the table (tblAllCustomers) that displays all companies in the market with potential business for my company. When a new contract is undertaken i have a command button on frmAllCustomers that runs an append query to move the company's details into frmClients (based on tblClients).
I want to add to my code so that if the record trying to be appended from frmAllCustomers already exists in frmClients, a friendly message box appears such as "Company is already a customer!" instead of Access' Key Violation message. I've been told DLookup is the way to go. any ideas? I have something similar to this:


DLookup(["CustomerID"]["tblClient"] me.customerID)
If DLookup is = 0 Then
Run query
Else
MsgBox(Company already a customer!)

Many Thanx in advance

Andy

Hi Andy,

The DLookUp is a possibility.
But I prefer VBA

Example for Access 97 and UP. You must first make a reference tor the Microsoft DAO 3.6 Lib form within the VBA editor
Private Sub cmdDAO_Click()

Dim rsLookUp As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rsLookUp = db.OpenRecordset("SELECT * FROM tabel1 WHERE code = '" & Me.txtLookUp.Value & "'")

If Not rsLookUp.BOF And Not rsLookUp.EOF Then
MsgBox "Record gevonden: "
Else
MsgBox "Geen record gevonden"
End If

End Sub


Example for Access 2000, ....

Private Sub cmdADO_Click()
Dim rsLookUp As ADODB.Recordset
Dim cmdLookUp As ADODB.Command
Dim dbConn As ADODB.Connection

Set dbConn = New ADODB.Connection
dbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=pcreview_2002.mdb;User ID=Admin;Password=;"

dbConn.Open

Set cmdLookUp = New ADODB.Command

With cmdLookUp
.CommandText = "SELECT * FROM tabel1 WHERE code = '" & Me.txtLookUp.Value & "'"
.ActiveConnection = dbConn
End With

Set rsLookUp = cmdLookUp.Execute()


If Not rsLookUp.BOF And Not rsLookUp.EOF Then
MsgBox "Record gevonden: "
Else
MsgBox "Geen record gevonden"
End If

End Sub

 

[abue42]

Am i missing something?

sorry to bother you again
Hope u dont mind but im not that experienced in access and i think ive missed something out to add in your code

This is what im using:

Dim Response

Response = MsgBox("Are you sure you wish to add this company to your list of clients?", vbYesNo, "Confirm append company details")

If Response = vbYes Then

Dim rsLookUp As ADODB.Recordset
Dim cmdLookUp As ADODB.Command
Dim dbConn As ADODB.Connection

Set dbConn = CurrentProject.Connection

Set cmdLookUp = New ADODB.Command

With cmdLookUp
.CommandText = "SELECT * FROM tblClients WHERE Company_Name = '" & Me.Company_Name & "'"
.ActiveConnection = dbConn
End With

Set rsLookUp = cmdLookUp.Execute()


If Not rsLookUp.BOF And Not rsLookUp.EOF Then
MsgBox "Comapny is already a client!"
Else
Dim stDocName As String

stDocName = "AppQryNewClient"
DoCmd.OpenQuery stDocName, acNormal, acEdit

End If



Else
End If

Exit_Command54_Click:
Exit Sub


I'm getting the error message "No value given for one or more required parameters."

Should i have something in the line Set rsLookUp = cmdLookUp.Execute()
Or is my
.CommandText = "SELECT * FROM tblClients WHERE Company_Name = '" & Me.Company_Name & "'"
wrong

many thanks

Andy

 

[cvo-aalst]

sorry to bother you again
Hope u dont mind but im not that experienced in access and i think ive missed something out to add in your code

This is what im using:
.....

I'm getting the error message "No value given for one or more required parameters."

Should i have something in the line Set rsLookUp = cmdLookUp.Execute()
Or is my
.CommandText = "SELECT * FROM tblClients WHERE Company_Name = '" & Me.Company_Name & "'"
wrong

many thanks

Andy

Andy

.CommandText = "SELECT * FROM tblClients WHERE Company_Name = '" & Me.Company_Name & "'"
Is Me.Company_Name a textbox or a label??

When its a textbox you must change your code to
.CommandText = "SELECT * FROM tblClients WHERE Company_Name = '" & Me.Company_Name.Value & "'"

The best way is to always provide the propertie with the control