Urgently need help in getting Autonumbering ID. Thanks Alot.

[Dr3am3rz]

Hi people, I need an urgent help in this. I have a form where I have created a textbox(disabled) in order to show the Number ID from the access database Auto ID but have a problem here. The newly created table in the database doesn't show any number example like it doesn't show No.1 in the 1st row of recordset if there is no data, it will only show the No.1 if there is data entered. I have a situation here where I need to open a new form to enter some data, when I open the form, it will be able to show No. 1 in the textbox and when I close the form, it will be able to enter the data in the table. I don't know whether am I clear enough.

The table in the database looks like this when there is no data entered:

AutoID Subject Date
(New ID) (Blank) (Blank)

If I manually entered some data in the 1st row, it will become like this:

AutoID Subject Date
1 Testing 26/5/07
(New ID) (Blank) (Blank)

So no matter what it just doesn't show the no. of the next recordset. Hope it's clear enough.

Here is my code:

Function Item_Open()
Dim ADOConn
Dim RS
Dim strSQL
Dim GetID
Set ADOConn = CreateObject("ADODB.Connection")
ADOConn.Provider = "Microsoft.JET.OLEDB.4.0"
ADOConn.Open = "C:\Users\interm\Documents\Outlook\Database\Task Data.mdb"
Set RS = ADOConn.Execute("SELECT [ID] FROM Data;")
Set GetID = RS.Fields("ID")
If Item.Size = 0 Then
Item.UserProperties("Task ID") = GetID
End If
End Function
 
Function Item_Write()
Dim ADOConn
Dim RS
Dim strSQL
Dim MySubject
Dim MyProblem_Type
Dim MyPriority
Dim MyStatus
Dim MyAssignTo
Dim MyAssignBy
Dim MyCustomer_Name
Dim MyCustomer_Email
Dim MyCustomer_Contact
Dim MyCustomer_Company
Dim MyCustomer_Category
Dim MyStart_Date
Dim MyDue_Date
Dim MyDate_Completed
Dim MyReminder_Time
Dim MyDisplay
Dim MyProblem
Dim MySolution
Set MySubject = Item.UserProperties("Subject")
Set MyProblem_Type = Item.UserProperties("Problem Type")
Set MyPriority = Item.UserProperties("Priority Field")
Set MyStatus = Item.UserProperties("Status Field")
Set MyAssignTo = Item.UserProperties("Assigned To")
Set MyAssignBy = Item.UserProperties("Assigned By")
Set MyCustomer_Name = Item.UserProperties("Customer Name")
Set MyCustomer_Email = Item.UserProperties("Email")
Set MyCustomer_Contact = Item.UserProperties("Customer Contact")
Set MyCustomer_Company = Item.UserProperties("Company")
Set MyCustomer_Category = Item.UserProperties("Customer Category")
Set MyStart_Date = Item.UserProperties("Start Date")
Set MyDue_Date = Item.UserProperties("Due Date")
Set MyDate_Completed = Item.UserProperties("Date Completed")
Set MyDisplay = Item.UserProperties("Display")
Set MyProblem = Item.UserProperties("Problem Notes")
Set MySolution = Item.UserProperties("Solution Notes")
 
 
Set ADOConn = CreateObject("ADODB.Connection")
ADOConn.Provider = "Microsoft.JET.OLEDB.4.0"
ADOConn.Open = "C:\Users\interm\Documents\Database\Task Data.mdb"
 
Set RS = CreateObject("ADODB.Recordset")

strSQL = "INSERT INTO Data (Subject, Problem_Type, Priority, Status, Assign_To, Assign_By, Customer_Name, " &_
"Customer_Email, Customer_Contact, Customer_Company, Customer_Category, " &_
"Start_Date, Due_Date, Date_Completed, Display, Problem, Solution)" &_
"VALUES ('"&MySubject&"', '"&MyProblem_Type&"', '"&MyPriority&"', '"&MyStatus&"', " &_
"'"&MyAssignTo&"', '"&MyAssignBy&"', '"&MyCustomer_Name&"', '"&MyCustomer_Email&"', "&_
"'"&MyCustomer_Contact&"', '"&MyCustomer_Company&"', '"&MyCustomer_Category&"', "&_
"'"&MyStart_Date&"', '"&MyDue_Date&"', '"&MyDate_Completed&"', '"&MyDisplay&"', "&_
"'"&MyProblem&"', '"&MySolution&"')"
ADOConn.Execute(strSQL)
End Function

Thanks alot if there's any help provided.

[Dr3am3rz]

I don't know how to use the code wrap, please forgive me.

[QVeen72]

Hi,

If u r using VB6,

Change this Line :

Set RS = ADOConn.Execute("SELECT [ID] FROM Data;")

To
Set RS =Nothing
RS.Open "SELECT Max(ID) FROM Data;",ADOConn

If Not RS.EOF Then
If Not Is Null(RS(0)) Then
GetData =RS(0)+1
Else
GetData=1
End If
End If

I hope It is Clear.
If u r using VB6.. Why r u Late Binding the Objects? Directly use :
Dim AdoConn As New ADODB.Connection
Dim RS As New ADODB.RecordSet

Regards
Veena

[Dr3am3rz]

Hmm.. I'm not sure whether I'm using vb6 because I never do any vb programming before and I'm using for the outlook programming. What you mean by late binding? I try your codings out and post again if it'snot working. Thanks for your reply.

[Dr3am3rz]

Ok, your code has problem. 1st, RS required, 2nd, Syntax error "If Not Is Null(RS(0)) Then".

I have to Set RS = ........... if not I will get the error msg "RS required"