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