If exist update else enter

[mkling]

I have a blank table called 'Event'. I have a form(unbound) called event with two combo boxes one 'qevent name combo' the other 'qevent date combo' along with other text fields. These combo boxes get their data from a linked excel table called 'Event Calendar'.



What I would like to do is Insert into the Event table the Event name from the calendar an event name and event date if it does not exist or populate the form's fields with the existing data from the event table so it can be updated. Here is the code I wrote:

IF EXISTS (SELECT [EVENT].[EVENT NAME], EVENT.[EVENT DATE] WHERE ([EVENT].[EVENT NAME = [FORMS]![EVENT].[QEVENT NAME COMBO] AND [EVENT].[EVENT DATE] = [FORMS]![EVENT].[QEVENT DATE COMBO])
    UPDATE Event SET Event.[EVENT NAME] = [Forms]![Event].[QEVENT NAME COMBO], EVENT.[EVENT DATE] = [FORMS]![EVENT].[QEVENT DATE COMBO]
Else
INSERT INTO Event ( [Event Name], [Event Date] )
SELECT [forms]![event].[qevent name combo] AS ename, [forms]![event].[qevent date combo] AS edate;

I was trying to do this with a query but that didn't work either. I got an example of SQL code and tried to modify it with no luck. Thanks in advance for your help.

[orange]

You haven't told us what happens when you try to run the code. Did you get an error?

You say a query didn't work but again you didn't show us the query or tell us WHAT didn't work.

You said you got some SQL, but had no luck with it.

This may not be your day unless you start to share some of the info you want help with. We can not guess what exactly you have done, nor what messages may have resulted. If you want help from the forum, get specific; describe your situation; tell us what you'd like to achieve in plain English, post your code and messages....and perhaps someone will has some suggestions, advice or recommendations.

[mkling]

You haven't told us what happens when you try to run the code. Did you get an error?

You say a query didn't work but again you didn't show us the query or tell us WHAT didn't work.

You said you got some SQL, but had no luck with it.

This may not be your day unless you start to share some of the info you want help with. We can not guess what exactly you have done, nor what messages may have resulted. If you want help from the forum, get specific; describe your situation; tell us what you'd like to achieve in plain English, post your code and messages....and perhaps someone will has some suggestions, advice or recommendations.

I appologize. Here is the article where I got the code from which I tried to use as the basis for my code.

This is a pretty common situation that comes up when performing database operations. A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not. If we refer to the Books Online documentation, it gives examples that are similar to:
IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
This approach does work, however it might not always be the best approach. This will do a table/index scan for both the SELECT statement and the UPDATE statement. In most standard approaches, the following statement will likely provide better performance. It will only perform one table/index scan instead of the two that are performed in the previous approach.

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
INSERT INTO Table1 VALUES (...)
The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.
Just remember, the examples in the MSDN documentation are usually the easiest way to implement something, not necessarily the best way. Also (as I re-learned recently), with any database operation, it is good to performance test the different approaches that you take. Sometimes the method that you think would be the worst might actually outperform the way that you think would be the better way.

The error I am getting is my code didn't compile. There are syntax errors in mine. Being very new to all this I get a lot of that. I ususally search the web, find something similar then through trial and error stumble into the solution.

[orange]

Well, stumbled upon may work, but there are several concepts underlying good database solutions.

If you have a database, and if you can tell me what exactly you are trying to do, I will look at your database if you want to post it.

Suggest you zip a copy and post it.

What is this data base for - not homework I hope. We don't do homework.

[mkling]

Here is the database in question. I added two records.

Sorry for the confusion. No I am not doing this for homework. lol. I should take several classes though. I really appreciate your time.

[orange]

Your zip file contains accdb (which means it is saved in 2007 or 2010.)
I can not open accdb file.

Your profile says you have 2002?????

[mkling]

sorry. I tried to save as 2002-2003 but it wouldnt let me. I have 2010.

[June7]

1. Event.[Event Type] field is in the combobox RowSource twice, means 8 columns, not 9

2. combobox column indexing starts with 0 so your references are off by 1

3. You want to check if ID/Date pair does not exist in Event and if true, create new record with just the selected ID/Date values?

4. If ID/Date pair already in Event then go to that record?

5. Why not use a bound form with:

Private Sub QEvent_Name_Combo_AfterUpdate()
Me.QEvent_Date_Combo = Null
End Sub

Private Sub QEvent_Date_Combo_AfterUpdate()
Dim intID As Integer
intID = Nz(DLookup("[Event ID]", "Event", "[EVENT NAME]='" & Me.QEvent_Name_Combo & "' AND [EVENT DATE]=#" & Me.QEvent_Date_Combo & "#"), 0)
If intID = 0 Then
    DoCmd.GoToRecord acActiveDataObject, "Event", acNewRec
    Me.QEventName = Me.QEvent_Name_Combo.Column(0)
    Me.QEventDate = Me.QEvent_Name_Combo.Column(1)
Else
    Me.RecordsetClone.FindFirst "[Event ID]=" & intID
    Me.Bookmark = Me.RecordsetClone.Bookmark
End If
Me.QEvent_Name_Combo = Null
Me.QEvent_Date_Combo = Null
End Sub

Private Sub Update_Event_Click()
DoCmd.RunCommand acCmdSaveRecord
End Sub

6. If you do 5. then move the comboboxes into form header and create textboxes for the name and date fields.

[mkling]

Thank you for replying.

This is what it looks like now:

Private Sub QEvent_Date_Combo_AfterUpdate()
'DoCmd.SetWarnings False
'DoCmd.OpenQuery "Enter Name and Date to Event From Calendar Query", acViewNormal
'DoCmd.SetWarnings True
Dim intID As Integer
intID = Nz(DLookup("[Event ID]", "Event", "[EVENT NAME]='" & Me.QEvent_Name_Combo & "' AND [EVENT DATE]=#" & Me.QEvent_Date_Combo & "#"), 0)
If intID = 0 Then
DoCmd.GoToRecord acActiveDataObject, "Event", acNewRec
[event].[Event_Name] = Me.QEvent_Name_Combo.Column(0)
[event].[Event_Date] = Me.QEvent_Name_Combo.Column(1)
Else
Me.RecordsetClone.FindFirst "[Event ID]=" & intID
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

I changed the me. qeventname and me.qeventdate.


It quit on "Me.RecordsetClone.FindFirst "[Event ID]=" & intID"
with the error: You entered an expression that has an invalid reference to the Recordsetclone property

[mkling]

Is there a way to do a query to check for the exist condition? I can create a query to update the table and I can create a query to insert data. I could create the query to see if the record exists do the update query. if it does not do the insert query.

[June7]

Don't know why the RecordsetClone is not working. It did for me.

You can modify the code I provided. The DLookup already does the check for existing. So instead of the Goto and the RecordsetClone actions, do the appropriate action query, like:
DoCmd.SetWarnings = False
If intID Then
'code to run INSERT query
Else
'code to run UPDATE query
End If
DoCmd.SetWarnings = True

[mkling]

June7,
Your code:

DoCmd.GoToRecord acActiveDataObject, "Event", acNewRec
    Me.QEventName = Me.QEvent_Name_Combo.Column(0)
    Me.QEventDate = Me.QEvent_Name_Combo.Column(1

kicks back an error "Microsoft Access can't find the field 'l1' referred to in your expression". It stops on the first line.
It did the same thing when I changed your code to:
[event].[Event_Name] = Me.QEvent_Name_Combo.Column(0)
[event].[Event_Date] = Me.QEvent_Name_Combo.Column(1)

I was thinking the table was not being referenced.
Any suggestions?

[June7]

It breaks on the DoCmd line? Try simplifying it. I use just:

DoCmd.GoToRecord , , acNewRec

Why would table be referenced? This is code behind a form, right?

[mkling]

It breaks on the DoCmd line? Try simplifying it. I use just:

DoCmd.GoToRecord , , acNewRec

Why would table be referenced? This is code behind a form, right?

Is that not what this is doing?

Me.QEventName = Me.QEvent_Name_Combo.Column(0)
    Me.QEventDate = Me.QEvent_Name_Combo.Column(1)

I thought this was inserting the event name and event date into the table. If so, the table is event.[event name] and event.[event date], right? There is nothing in the form called 'me.qeventname' or 'me.qeventdate'.

I also tried me.QEvent_Name_Combo and me.QEvent_Date_Combo believeing that is what you meant but where I got no error, nothing was entered into the Event table nor displayed in the Event form fields.

Another stupid question. Is this dependant upon the fields in the form being bound to the event table?

[June7]

The suggested code is intended to populated fields of form's RecordSet, if you bind the form as suggested in post 8. I probably should have showed ! instead of dot for the fieldname reference.

Me!QEventName = Me.QEvent_Name_Combo.Column(0)
Me!QEventDate = Me.QEvent_Name_Combo.Column(1)