[RESOLVED] error 3265

[kirara22]

how to generate automatic IDnumber of customer?


i try the following code:

Const DBPATH = "dataBASE.mdb"
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH

SQL = "SELECT MAX(IDNUMBER) FROM table1"

newID_Number.caption = rs.Fields("LAST_ID_nuMBER") + 1

End Sub

i've got he following error mesage.
item cannot be found in the collection corresponding to the requested name or ordinal.

sorry im really new vb

tnx

[Hack]

That is not a VB error. That is an SQL error. It means that the field you are referencing doesn't exist.

[kirara22]

i c.. hehhehe..

can u help me out how would i generate automatic id number for customer?

tnx

[sparbag]

Hello,

If the database uses an autonumber field for the table primary key then you do not need to create the key field value yourself, the database will do it automatically.

Kind regards

Steve

[kirara22]

but how would it apper during loadform in my IDNUMBER.caption.

tnx

[sparbag]

Hello,

From post #2 Hack identified that you were using an incorrect field name for the primary key field. If that is the case you will need to correctly identify the name of the primary key field and then use its value for the label caption.

You will need to use the recordset object:

With rs
     .ActiveConnection = cn
     .Source = SQL
     .Open
End With
newID_Number.caption = rs(0).Value

When you need to create a new record the rs.AddNew will automatically cause a new identity key field value to be created.
rs.Update will then write the new values to the database.

I will add that the user doesn't need the key field value and doesn't even need to know it exists.

P.S. This code is written off the top of my head so may contain errors.

Kind regards

Steve