Trouble with check if Exists before Edit or Add Record

[mrfixit1170]

Hello All,
I am hoping to get some help from anyone with more expertise, I have a form to edit a record in a table,
I have most of the script done but I am having a little trouble with my strSQL statement.

Set MyDB = CurrentDb
strSQL = "Select * From dbo_tbl_List_SiteVisit2 WHERE [IETM_ID] = " & Me!lboAVUMTaskMappingUpdate

I would like to have the Select statement to check for more than one column in the table. These columns [IETM_ID], [Maint_Funct],[MOS_ID],[Quantity],[Time] are what I would like to check and then

The same goes with the check for the records in the database, I would like to add columns to my check. If records exist then edit the record with information, if the record doesn't exist then create the record

'See if the Data has already been Captured, if not, Add, not Edit the Record
If DCount("*", "tbl_List_SiteVisit2", "[IETM_ID] = " & Me![lboAVUMTaskMappingUpdate]) = 0 Then

Here is the whole script, I poached it from another user and tried to modify it


but my learning curve keeps getting in the way.

Private Sub btnAVUMTMUDelete_Click()
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
 
Set MyDB = CurrentDb
strSQL = "Select * From dbo_tbl_List_SiteVisit2 WHERE [IETM_ID] = " & Me!lboAVUMTaskMappingUpdate

'See if the Data has already been Captured, if not, Add, not Edit the Record
If DCount("*", "tbl_List_SiteVisit2", "[IETM_ID] = " & Me![lboAVUMTaskMappingUpdate]) = 0 Then

'NOT Captured/ADD
  Set rst = MyDB.OpenRecordset("dbo_tbl_List_SiteVisit2", dbOpenDynaset, dbSeeChanges)
    With rst
      .AddNew
        !IETM_ID = ctl
        !Maint_Funct = Forms!frm_AVUM_TaskMapping_UpDate!cboTMMFo1
        !MOS_ID = Forms!frm_AVUM_TaskMapping_UpDate!cboTMMOSo1
        !Quantity = Forms!frm_AVUM_TaskMapping_UpDate!txboTMQtyo1
        !Time = Forms!frm_AVUM_TaskMapping_UpDate!txboTMTimeo1
      .Update
      .Bookmark = .LastModified
  End With

'Data Captured, so Edit the Recordset
Else
  Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    With rst
      .Edit
         !IETM_ID = ctl
        !Maint_Funct = Forms!frm_AVUM_TaskMapping_UpDate!cboTMMFo1
        !MOS_ID = Forms!frm_AVUM_TaskMapping_UpDate!cboTMMOSo1
        !Quantity = Forms!frm_AVUM_TaskMapping_UpDate!txboTMQtyo1
        !Time = Forms!frm_AVUM_TaskMapping_UpDate!txboTMTimeo1
      .Update
    End With
End If
 
rst.Close
strSQL = Nothing
Set rst = Nothing
    
End Sub

Any help would be much appreciated thanks

[pbaldy]

I would skip the DCount and do it all with the recordset. This would become:

strSQL = "Select * From dbo_tbl_List_SiteVisit2 WHERE [IETM_ID] = " & Me!lboAVUMTaskMappingUpdate & " AND [Maint_Funct] = " & Me.Whatever & " AND..."

keeping in mind that text field values need to be surrounded by ' and date/time field values by #. Then open a recordset on that SQL. Then your code becomes:

If rst.EOF Then 'no existing record
  'your code to add new
Else
  'your code to edit
End If

If you really want to be slick, within the If/Then all you have is .AddNew or .Edit, then the rest of the lines follow. That way you only have them once. By the way, this line isn't necessary:

strSQL = Nothing

It's a variable, not an object.

[mrfixit1170]

Thanks pbaldy for your input, I have been working at this all day trying to get this to work, I just wanted to reply and let you know that I am trying your suggestion. I am having trouble getting the strSQL to format right with my form....I will post something in a few maybe you all can see what I am doing wrong? Does it matter how I reference a listbox control in the SQL statement.

[pbaldy]

Certainly post back with your failing effort if you get stuck. The only quirks with referencing a listbox are if you want other than the bound column or if the listbox is multiselect. This may help debug the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

[mrfixit1170]

Looks like the request for this options was reviewed and removed....thanks for the info on this one everyone.