Member Avatar for 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

  • 4 Contributors
  • 5 Replies
  • 224 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by manoshailu

Dani AI

Generated

A few practical fixes and a safer pattern to make the form reliable in multi‑user use.

As requested, you want the form to clear fields, generate the next roll value, then save into Access. ’s recordset idea and ’s note about checking for Null are useful starting points. The approach below avoids fragile string parsing and common race conditions.

Keep the roll prefix and its numeric sequence as separate columns (for example prefix TEXT and numPart INTEGER), or let Access generate a unique AutoNumber and combine it with a stored prefix for display. Storing the numeric part as a number makes finding the next value, formatting (leading zeros), and enforcing uniqueness far cleaner than extracting substrings from a text roll number.

Example workflow (VB6 + ADO, simplified):

  • Open a DSN‑less connection to the MDB/ACCDB file.
  • In a transaction: read the next numeric part with a parameterized query, build the display Roll, then insert with parameters, finally CommitTrans.
  • Use parameterized commands (not string concatenation) to avoid quoting bugs.

Sample snippets (illustrative — adapt paths/names):

' DSN-less open (ACE for .accdb, Jet for .mdb)
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Students.accdb;"

' Get next number for a given prefix (sPrefix)
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "SELECT Nz(MAX(numPart),0)+1 AS NextNum FROM Students WHERE prefix=?"
cmd.Parameters.Append cmd.CreateParameter("", 200, 1, 10, sPrefix)
Set rs = cmd.Execute
nNext = rs("NextNum")
rs.Close

sRoll = sPrefix & Format$(nNext, "00")

Use a parameterized INSERT for saving, and wrap the read+insert in conn.BeginTrans / conn.CommitTrans to reduce duplicate-number races. Always close recordsets/commands and handle errors with a rollback on failure. If strict sequential numbers are critical under concurrent use, prefer an AutoNumber-backed design or implement a small “sequence” table that you update inside the same transaction to reserve the next number.

Recommended Answers

All 5 Replies

Member Avatar for 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:)

Member Avatar for 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

Member Avatar for manoshailu

hi veena,


U r right veena i forget to include that max.


regards
shailu.

Member Avatar for 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!

Member Avatar for 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.:)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.