adodb, adodc and msaccess

[auwi987]

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

[abelingaw]

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

[auwi987]

thnx dude for your help

[ChrisPadgham]

you can remove line8 by formating your date in line 7

IDS = "APCSO" & format$(now,"ddmmmyyyy") & idhand