guys hello i have database with table name tblAgent with a column agent_id and a vb6.0 form add-agent i want to generate id everytime i open form and everytime i added new data it refreshes the ado recordsource and fetch the max(agent_id) and +1 to increment the agent_id

now i can generate id everytime i open add-agent

the problem is icant regenerate an incremented id everytime i added a new 1

here's my code to open form

sub openid()

adoAgent.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & (App.Path & "\Database\AutomationDB.mdb") & ";Persist Security Info=False;Jet OLEDB:Database Password="

adoAgent.RecordSource = "SELECT MID(AGENT_ID,12,1) AS ID FROM tblAgent WHERE AGENT_ID=(SELECT LAST(AGENT_ID) FROM tblAgent)"

 adoAgent.Refresh 

End Sub

button Save "the add button"

Sub genid()
   adoAgent.RecordSource = "SELECT MID(AGENT_ID,12,1) AS ID FROM tblAgent WHERE AGENT_ID=(SELECT LAST(AGENT_ID) FROM tblAgent)"
   adoAgent.Refresh
   
    
   idhand = CInt(txtHideID.Text) + 1
   IDS = "APCSO" & DateValue(Now) & idhand
   txtAgentID.Text = Replace$(IDS, "/", "")
End Sub

as you can see i have txtHideID i manually set tru txtHideID IDE properties not programmatically, it fetch the data from the query set from adoAgent

and txtAgentID.Text display the fully generated ID

sorry guys for poor grammar, i hope i explained it clearly thanks in advance

Here's to give you an idea.

Dim xIdNo As Integer
Dim xEmpID As Integer
 
       xEmpID = rsX!EmpID        'xEmoID var contains the value of your ID column
       xEmpID = xEmpID + 1       'Adds 1 to the original value of your xEmpID var
       txtIDNo.Text = xEmpID     'Displays it in a textbox

       rsX.Close                 'Closes recordset
   
    Exit Sub

Much better if you create a Sub for this then call it on your Add button code.
And don't set the textbox to invisible so you can see the effect.

Goodluck

thnx dude for your help

you can remove line8 by formating your date in line 7

IDS = "APCSO" & format$(now,"ddmmmyyyy") & idhand
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.