Check if record exists, if no then append record

[Steph007]

Hello All!
I'm new using Access 2007 (& VBA) and i am having trouble with the following:

What I'm trying to do is this:

I have a form (frmOpportunityDetails) to view and change the details of an opportunity from the table tblProspect.
I have made a button on this form called "cmdConvertToCustomer".

When the user clicks this button, Access should:

- first check if the company name (field "CompanyName") already exists in the tblCustomer. Because it is possible to have the same company is the table tblProspect and tblCustomer. (We could do business with someone in the region "TC 1" and have a opportunity to do business with this someone in the region "TC 2")

- When the name does not already exists, an append query will transfer the data from the table tblProspect to tblCustomer.

- When the name does exists, a message box should appear saying "This customer already exists, change info manually".

- Then the form "frmCustomerDetails" should open so the user can update the info AND the opportunity should be deleted from the tblProspect.

I am having trouble to make the vba code work...
Maybe someone could have a look at it and give me some advise?

Thanks in advance!

Private Sub cmdConvertToCustomer_Click()
Dim intAnswer As Integer
Dim str As String
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim strSQL As String
Dim rstProspect As Recordset
Set ws = DBEngine(0)
Set db = ws(0)
Set rstProspect = Me.Recordset
If DCount("*", "[tblCustomer]", "[CompanyName] = " & CompanyName) > 0 Then
str = "Open details form + delete the opportunity"
intAnswer = MsgBox(str, vbInformation + vbOKCancel, "Info")
End If
If intAnswer = vbOK Then
DoCmd.OpenForm "frmActiveCustomers"
DoCmd.OpenQuery "qryDeleteProspect"
CurrentDb.Execute "DELETE * FROM tblCompany WHERE CompanyID = " & Me.CompanyID
Else
str = "This will convert the opportunity to a customer"
intAnswer = MsgBox(str, vbInformation + vbOKCancel, "Info")
End If
If intAnswer = vbOK Then
strSQL = "INSERT INTO tblCustomer ( CompanyID ) VALUES (" & rstProspect("CompanyID") & ")"
    db.Execute strSQL, dbFailOnError
CurrentDb.Execute "DELETE * FROM tblProspect WHERE CompanyID = " & Me.CompanyID
    DoCmd.Close
Else
Exit Sub
End If
End Sub

 

[jdraw]

When you say , -

I am having trouble to make the vba code work..

, do you get an error message; do you get any results??
I see some terms that throw up a bit of a flag wondering what your tables and relationships look like.

Customer, Company, Prospect

Can you make a jpg of your tables and relationships and post it in a zip file?

You've told us how you want to do something with a form, but we know so little about the purpose of your database or your environment or skill level.

 

[Steph007]

When I click the command button, I'll get the runtime error 2471 The expression you entered as a query parameter produced this error: VDK (or any other company name)

When I put the next line in the immediate window, I'll get 'Compile error: Expected: expression)

If DCount("*", "[tblCustomer]", "[CompanyName] = " & CompanyName) > 0 Then

I've had some lessons about database design (using access) at school, but that's been over more than 8 years ago. So I'm a bit rusty, to say the least.. I've read a lot about ms access and vba, but sometimes it's hard to see the trees for the forest.

This purpose of this database is to make a sales pipeline for our sales department at my company. To this day they are using excel, but because of the long lists of data it's become a bit messy.
So they asked me to make a database. Why me? Altough I do not have a lot of experience, I still know more about it then my colleagues.

Hope to have answered your questions, but feel free to ask more off course!

 

[boblarson]

quick question - why is Sales ID in both the tblCompany and tblCalls? When you find yourself with links that basically create a circle, something usually isn't right.

Second, your DCount needs quotes if CompanyName is text

"[CompanyName]=" & Chr(34) & Me.CompanyName & Chr(34)

 

[Steph007]

I thought about it and I don't really have an answer! The table tblCalls is linked with the table tblCompany, which is linked with the table tblSales... So I don't really need to link the table tblCalls with the table tblSales, right?
Are there any consequences when I delete this relationship?

I have also put the quotes in the line of code, but something is still not right I think. I do no longer get the error message, but when I click the command button I automatically get the MsgBox 'Open Details form + delete the opportunity" followed by the MsgBox 'This will convert the opportunity to a customer' - regardless if the Company Name already exists or not...

Do you maybe have some advise for me?

 

[boblarson]

Can you maybe upload a copy of the database here? If you run Compact and Repair first and then ZIP the file by right clicking on the file and select SEND TO > COMPRESSED FOLDER, as long as it is below 2 MB at that point, you can upload it here. You can do so even if you have less than 10 posts for zipped files.

See here for more on how to do the upload:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[Steph007]

Here you find a copy of the database with some fictional data.

Thank you in advance for your time and help!

I hope the db is not to horrible... And if you have questions, off course shoot!

 

[Steph007]

Anyone have some advise for me?
Still hoping that someone can help me out!

 

[psprakki]

hi to all

I am new to VBA. I have a recovery table - tblRecovery, and I open it by using a form - frmRecovery, when ever I open this form and click 'Run Recovery"command in that form, then the out standing amount should be decreased by an EMI amount and Remaining months should be decreased by 1 .
kindly help me to write this in VBA code