Save record in Database

[smugcool]

HI,

I have created a form with various text boxes around.... i want as soon as sombody will click on the save button the data will be saved in a database called mydb.mdb......i m a very newbie to this vb....kindly help.....i have written code as below.....but geting an error of "c:\incidentrep ort\incidentrep ortmydb.mdb not found"

Whereas i am keeping the database in the same path as the error is coming.

Private Sub cmdAdd_Click()
Dim conec As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
conec.Connectio nString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source ='" & App.Path & "MyDB.mdb'"
conec.CursorLoc ation = adUseClient
conec.Open
rs.Open "select * from clients where Client_Id='" & txtUserName.Tex t & "'", conec, adOpenKeyset, adLockOptimisti c
If txtUserName.Tex t = "" Then
MsgBox "Please fill the Client Id Number", vbCritical
Else
With rs
' Search for identical/matching STUDENTCODE

If rs.RecordCount = 0 Then '---> If there's no identical/matching Clientid, then save as new data
.AddNew
End If
!clientid = txtUserName.Tex t
!clientname = txtName_First.T ext

!clientADDRESS = txtName_Last.Te xt
.Update
End With
End If

End Sub


kindly help me in geting this minor problem resolved.

when i debug error gets highlighted in cn.open

[smugcool]

Private Sub cmdAdd_Click()
Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim strSQL As String
cn.ConnectionSt ring = "Provider=Micro soft.Jet.OLEDB. 4.0;" & "Data Source=" & strMyDB & ";"
cn.CursorLocati on = adUseClient
cn.Open
strSQL = "SELECT * FROM clients" & " WHERE client_id=" & FixApostrophies (txtClient_Id.T ext)
rs.Open strSQL, cn, adOpenForwardOn ly, adLockOptimisti c
If txtName_First.T ext = "" Then
MsgBox "Please fill the Student Code Number", vbCritical
Else
With rs
' Search for identical/matching Clientid

If rs.RecordCount = 0 Then '---> If there's no identical/matching STUDENTCODE, then save as new data
.AddNew
End If
!clientCODE = txtUsername.Tex t
!clientNAME = txtName_First.T ext

!clientADDRESS = txtName_Last.Te xt
.Update
End With
End If

End Sub

i have resolved the earilier problem now i got stuck with some other problem..

and i am geting syntex error missing some query expression in 'client_Id='

I am not geting any idea of how to deal with it....please somebody help me.

below is the code what i did

Private Sub cmdAdd_Click()
Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim strSQL As String
cn.ConnectionSt ring = "Provider=Micro soft.Jet.OLEDB. 4.0;" & "Data Source=" & strMyDB & ";"
cn.CursorLocati on = adUseClient
cn.Open
strSQL = "SELECT * FROM clients" & " WHERE client_id=" & FixApostrophies (txtClient_Id.T ext)
rs.Open strSQL, cn, adOpenForwardOn ly, adLockOptimisti c
If txtName_First.T ext = "" Then
MsgBox "Please fill the Student Code Number", vbCritical
Else
With rs
' Search for identical/matching Clientid

If rs.RecordCount = 0 Then '---> If there's no identical/matching STUDENTCODE, then save as new data
.AddNew
End If
!clientCODE = txtUsername.Tex t
!clientNAME = txtName_First.T ext

!clientADDRESS = txtName_Last.Te xt
.Update
End With
End If

End Sub

[hariharanmca]

[CODE=vb]conec.Connectio nString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source ='" & App.Path & "MyDB.mdb'"[/CODE]

You have to check here
App.Path & "MyDB.mdb'"
that should be
App.Path & "\MyDB.mdb' "

like

[CODE=vb]conec.Connectio nString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source ='" & App.Path & "\MyDB.mdb' "[/CODE]

[smugcool]

Thanx hariharan

i got rid of this problem..

Now the problem is somthing different.

Now i am geting syntex error missing some query expression in 'client_Id='

I am not geting any idea of how to deal with it....please somebody help me.
See here the code.

Private Sub cmdAdd_Click()
Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim strSQL As String
cn.ConnectionSt ring = "Provider=Micro soft.Jet.OLEDB. 4.0;" & "Data Source=" & strMyDB & ";"
cn.CursorLocati on = adUseClient
cn.Open
strSQL = "SELECT * FROM clients" & "WHERE client_id="" "
rs.Open strSQL, cn, adOpenDynamic, adLockOptimisti c
If txtName_First.T ext = "" Then
MsgBox "Please fill the Student Code Number", vbCritical
Else
With rs
' Search for identical/matching Clientid

If rs.RecordCount = 0 Then '---> If there's no identical/matching STUDENTCODE, then save as new data
.AddNew
End If
!clientCODE = txtUsername.Tex t
!clientNAME = txtName_First.T ext

!clientADDRESS = txtName_Last.Te xt
.Update
End With
End If

End Sub

[hariharanmca]

[CODE=vb]strSQL = "SELECT * FROM clients" & "WHERE client_id="" "[/CODE]

that also simple.
just check this line, you gave 3 double quotes there is no value to select in where clause.
[CODE=vb]
strSQL = "SELECT * FROM clients WHERE client_id=" & valNumericToSel ect & ""[/CODE]this will better

[QVeen72]

Thanx hariharan

i got rid of this problem..

Now the problem is somthing different.

Now i am geting syntex error missing some query expression in 'client_Id='

I am not geting any idea of how to deal with it....please somebody help me.
See here the code.

Private Sub cmdAdd_Click()
Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim strSQL As String
cn.ConnectionSt ring = "Provider=Micro soft.Jet.OLEDB. 4.0;" & "Data Source=" & strMyDB & ";"
cn.CursorLocati on = adUseClient
cn.Open
strSQL = "SELECT * FROM clients" & "WHERE client_id="" "
rs.Open strSQL, cn, adOpenDynamic, adLockOptimisti c
If txtName_First.T ext = "" Then
MsgBox "Please fill the Student Code Number", vbCritical
Else
With rs
' Search for identical/matching Clientid

If rs.RecordCount = 0 Then '---> If there's no identical/matching STUDENTCODE, then save as new data
.AddNew
End If
!clientCODE = txtUsername.Tex t
!clientNAME = txtName_First.T ext

!clientADDRESS = txtName_Last.Te xt
.Update
End With
End If

End Sub

Hi,

Not very Sure, What is the field name of ID? coz, in SQL Statement u search for "client_id" and in ur RS AddNew, u refer to as "ClientCode ".
may be the table does not have client_id as field name...?

REgards
Veena