VBA HELP

[Shailesh]

Dear Friends

I have written the following codes to update the table Cntry.

Private Sub Form_AfterUpdate()
Dim conDatabase As ADODB.Connection

Dim strSQL As String
Dim CNTR As Integer
Dim CNTRSQL As String


Set conDatabase = CurrentProject.Connection
'Set conDatabase = CurrentDb
strSQL = "insert into cntryview values(" & Chr(34) & Me.Country_O &
Chr(34) & ")"

CNTRSQL = "SELECT count(COUNTRY) as num FROM CNTRY WHERE COUNTRY =" &
Chr(34) & Me.Country_O & Chr(34) & " "
conDatabase.Execute CNTRSQL


'Set rstEmployees = New Recordset
'rstEmployees.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic
conDatabase.Execute strSQL
MsgBox "data inserted"

conDatabase.Close
Set conDatabase = Nothing
End Sub


Can you please suggest how can i modify the above code so that it would
check for the value in the table and if the same value is not there then
only it would update the table.

YOur guidance will help me to solve the problem.

Thanks and best regards
Shailesh

 

[Dirk Goldgar]

Dear Friends

I have written the following codes to update the table Cntry.

Private Sub Form_AfterUpdate()
Dim conDatabase As ADODB.Connection

Dim strSQL As String
Dim CNTR As Integer
Dim CNTRSQL As String


Set conDatabase = CurrentProject.Connection
'Set conDatabase = CurrentDb
strSQL = "insert into cntryview values(" & Chr(34) & Me.Country_O
& Chr(34) & ")"

CNTRSQL = "SELECT count(COUNTRY) as num FROM CNTRY WHERE COUNTRY
=" & Chr(34) & Me.Country_O & Chr(34) & " "
conDatabase.Execute CNTRSQL


'Set rstEmployees = New Recordset
'rstEmployees.Open strSQL, conDatabase, adOpenDynamic,
adLockOptimistic conDatabase.Execute strSQL
MsgBox "data inserted"

conDatabase.Close
Set conDatabase = Nothing
End Sub


Can you please suggest how can i modify the above code so that it
would check for the value in the table and if the same value is not
there then only it would update the table.

YOur guidance will help me to solve the problem.

Thanks and best regards
Shailesh

There could be a couple of ways to do this, but first I'd like to point
out that, if the table "CNTRY" has field COUNTRY as its primary key (as
seems plausible to me), or has a unique index on COUNTRY, you don't need
to to check first whether a country name you want to add is already
there. You can just execute an INSERT statement to add it, and ignore
the error that will be reaised if the country is already there. Code
for that would look like one of the following two blocks, depending on
whether you want to use ADO or DAO to do it:

strSQL = "INSERT INTO CNTRY (COUNTRY) VALUES(" & _
Chr(34) & Me.Country_O & Chr(34) & ")"

'----- ADO version -----
On Error Resume Next
CurrentProject.Connection.Execute strSQL
Select Case Err.Number
Case 0: MsgBox "data inserted"
Case &H80004005: MsgBox "not inserted; already exists."
Case Else
MsgBox "not inserted -- " & Err.Description, vbExclamation,
"Error " & Err.Number
End Select
'----- end ADO version -----

'----- DAO version -----
On Error Resume Next
With CurrentDb
.Execute strSQL, 128
Select Case Err.Number
Case 0: MsgBox "data inserted"
Case 3022: MsgBox "not inserted; already exists."
Case Else
MsgBox "not inserted -- " & Err.Description,
vbExclamation, "Error " & Err.Number
End Select
End With
'----- end DAO version -----

If you really want to check for the value in the table first, you may as
well use the same recordset to both do the checking and the adding:

'----- ADO existence-checking version -----
Dim rs As ADODB.Recordset
Dim strSQL As String

strSQL = _
"SELECT COUNTRY FROM CNTRY WHERE COUNTRY =" & _
Chr(34) & Me.Country_O & Chr(34)

Set rs = New ADODB.Recordset
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
With rs
If .BOF Then
.AddNew
![COUNTRY] = Me.Country_O
.Update
MsgBox "data inserted"
Else
MsgBox "not inserted; already exists."
End If
.Close
End With

Set rs = Nothing
'----- end ADO existence-checking version -----