Urgently need help in getting Autonumbering ID. Thanks Alot.
 

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.

Re: Urgently need help in getting Autonumbering ID. Thanks Alot.
 

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

Re: Urgently need help in getting Autonumbering ID. Thanks Alot.
 

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

Re: Urgently need help in getting Autonumbering ID. Thanks Alot.
 

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.

Re: Urgently need help in getting Autonumbering ID. Thanks Alot.
 

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"