Autonumber from access field.

[singoi]

Hi Freinds,
a Complicated situation for me.

i Have a 2 tables in access with the same Field saying BANF_Nr which sees like " -BANF-0001"
here this 0001 is an Autonumber like 0001,0002,0003,0004 and so on

i have a textbox in Form txtbanf which should call automatically the next number from this field..suppose there is BANF-0028 the last ones..now when i go to this form the txtbanf.text should be automatically filled by BANF-0029.

this BANF-0029 should not get saved unless i click the button SAVE.

i tried to attach some jpeg files for your convinience but couldnt..

Please help me friends in getting this code..

Thx

[vb5prgrmr]

To get last on use a select top 1 field from table order by unique ID (auto number field)
Break it apart with split on the - character
Use the upper bound of array (1) with the val function to put value into a variable.
variable + variable + 1
StringVariable = Format(variable, "0000")
stringvariable = LowerBound of array (0) & "-" & stringvariable
'...

Good Luck

[vb5prgrmr]

Ahh... that should be...

To get last on use a select top 1 field from table order by unique ID (auto number field) DESC

Good Luck

[dhavalcoholic]

Can you attach a picture of Database Table??

Also try getting Max(Banf) in a variable.

Then txtBanf = variable + 1

(I m not sure if I understood your question properly)

[singoi]

Hi Freinds,
I feel secured seeing some responses for my problem.

i am attaching few more pics.

1st Pic is BANF_Nr table where only BANF_Nr is recorded.
2nd Pic is another table with described BANF_Nr where each BANF consists of number of position numbers.

3rd Pic shows the two Tables in database

4rd Pic which is a Form with Listview (lst1) top one and Listview (lst2) bottom one. when i click this Open Button these two Listviews get enabled and lst1 shold show only BANF_Nr from Table BANF_Nr

and lst2 should get enabled and show the detailed BANF_Nr from second Table.

5th Pic shows the Formfor Neu BANF Commandbutton.. this should get activated with continued BANF_Nr like BANF-0005 becoz there already exists BANF-0004. and then lst2 should be enabled with empty records for this BANF-0005. when i press this Button Einfügen then the records for BANF-0005 will be recorded in database and be shown here in this lst2

i hope u got an idea ..

now the code i have written

in Module:
Public Conn As New ADODB.Connection
Public Connection As New ADODB.Connection
Public rs As New ADODB.Recordset
Public recordset1 As New ADODB.Recordset
Public recordset2 As New ADODB.Recordset
Public command As New ADODB.command
Public Gender As String

Public Function ConnectADO()

Set Conn = New ADODB.Connection
Set rs = New ADODB.Recordset

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= " & App.Path & "\Bestellanforderung.mdb"

End Function

'This procedure closes the connection to the database
Public Sub CloseConnection()
Connection.Close
End Sub

Public Sub OpenConnection()
With Connection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open App.Path & "\Bestellanforderung.mdb"
End With
End Sub
--------------------------------------------------------
code in form

Private Sub ShowBanf()
Dim i As Integer, j As Integer
Dim SQL As String
OpenConnection

SQL = "SELECT * FROM BANF_Nr"

With command
.ActiveConnection = Connection
.CommandType = adCmdText
.CommandText = SQL
Set recordset1 = .Execute
End With
cmdClearText_Click

With recordset1
If Not .BOF Then
lst1.ListItems.Clear
While Not .EOF
j = j + 1
lst1.ListItems.Add j, "K" & ![BANF_Nr]

.MoveNext
Wend
'lblFriends.Caption = Trim(Str(j)) & " Friends in List"
End If
End With
CloseConnection
End Sub

Private Sub cmdopbanf_Click()

ShowBanf

End Sub

it dooesnt show the records in lst2

Thx

[dhavalcoholic]

I dont know the way you are storing Banf_Nr in Database.

I mean.... if you are storing it as "Banf-0001".. i.e Text format, then still you can use "Max(Banf_Nr)" in SQL to get the highest Banf_Nr till now. But you CANT increment it simply by adding 1 .

But if you are storing it in Database as simply "0001" i.e Numeric format, then you can easily get the maximum value!!

Use the following SQL command....

"Select Max(Banf_Nr) from Table"

Then store it in some variable....... add 1 to it..... and then display it in the text box you want.

[vb5prgrmr]

see post #2 above, it gives step by step

Good Luck