SQL in vba. Find if record already exists...

[paulpan]

Hi

I want to find if a signoff already exists and if it does replace the record with new data. I am stuck on the first part of it. The procedure falls over on noDUP (I want it to return an integer, not a string). I was going to say that if noDUP =1 then refresh the recordset. If no then just addNew recordset set....

Please help...

Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim irow As Integer
Dim sreqd As String
Dim objConnection As Object
Dim objRecordSet As Object

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")


objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Documents and Settings\Desktop\Sign Off Database.mdb"

objRecordSet.Open "SELECT * FROM tblSignOffs", _
objConnection, adOpenStatic, adLockOptimistic

'***********************************
Dim noDUP As Integer

noDUP = "SELECT * FROM tblSignOffs GROUP BY tblSignOffs.[3_NAVDate], tblSignOffs.[1_FundName] HAVING (((tblSignOffs.[3_NAVDate])=#'6/25/2008'#) AND ((tblSignOffs.[1_FundName])='PP01'))"

 

[xenou]

I hope I'm not in over my head here, but you need to query the database and see if it returns a record. Here, you are simply trying to assign a string value to an integer variable which is doomed from the start.

Something like:

'-------------------
Dim strSQL as String
Dim blnRecordFound as Boolean
strSQL = "SELECT * FROM tblSignOffs" 'You would use the whole SQL text here
Call objRecordSet(strSQL, objConnection, adOpenStatic, adLockOptimistic)
If (objRecordSet.EOF and objRecordSet.BOF) Then
    blnRecordFound = False
Else
    blnRecordFound = True
End If

If objRecordSet.State And ObjectStateEnum.adStateOpen) Then objRecordset.Close 'Close recordset
Set objRecordSet = Nothing

If blnRecordFound Then
    '~~Do this
Else
    '~~Do this
End If
'-------------------

Hope this helps,
AB