Data-Access with Vbasic

[elanch]

hi friends!
I'm new to both vb and dataAccess. Now i have created a form in data-access. I need the datas entered in the form to be appended in the access-Table when i click the save button.

For eg:
Roll NO : Txt box
Name : Txt box
Sex : Txt box
Class : Txt box

New_Button Save_button

Here whenever i cllick the new_button all tha above fields to be re-set except the roll No. It has to display the next Roll No.(For eg. The previous roll No saved is 02ME11, Now it has to display 02ME12).
Then when i click the save_button, the datas has to be saved in access table.

please send me the code for this

elanch

[manoshailu]

hi,

Use the below codings.

' To create an object for connection and recordset

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

'To Create a New Id

Private Sub cmbnew_Click()
rs.Open "SELECT Mid([rollno],5,6) + 1 FROM Table1", cn, adOpenDynamic, adLockOptimistic
Text1.Text = "02ME" + Trim(Str(rs(0)))
rs.Close
End Sub

'To Add records

Private Sub cmdadd_Click()
rs.Open "select * from table1", cn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs(0) = Text1.Text
rs(1) = Text2.Text
rs(2) = Text3.Text
rs(3) = Text4.Text
rs.Update
rs.Close
End Sub

'To Set connections.

Private Sub Form_Load()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "dani", False, False
End Sub

Regards
Shailu:)

[QVeen72]

HI Mona,

I think we should use Max():

rs.Open "SELECT Max(Mid([rollno],5,6)) + 1 FROM Table1", cn, adOpenDynamic, adLockOptimistic

And we may get Error if there are no recs in the Table, check for Null there

Regards
Veena

[manoshailu]

hi veena,

U r right veena i forget to include that max.

regards
shailu.

[dedort]

hi friends!
I'm new to vb and access. i would like to ask favor from you on how to connect my database access to vb? cause i have difficulties in coding my connection from access to vb.
thnaks!

[manoshailu]

hi,

use below code.

'Create an object for connection and recordset
'set reference
'Project -> Reference - > Microsoft Activex Data Objects 2.6 Library
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Private Sub Form_Load()

'Set Connections and recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

'Create DSN by
'In Run Command type "odbcad32" and press enter
'Then in userDSN tab click ADD button, select "Microsoft Access Driver(*.mdb), type any name in Data Source Name, click the Database button,Select access Database where u stored.
use that DSN name in connection open. Here i used DSN name "dani"

cn.Open "dani", False, False
End Sub

regards.
shailu.:)