Visual Basic 4 / 5 / 6 RSS Visual Basic 4 / 5 / 6 RSS

Urgently need help in getting Autonumbering ID. Thanks Alot.

Reply

•
•
Join Date: May 2007
Posts: 4
Reputation: Dr3am3rz is an unknown quantity at this point 
Solved Threads: 0
Dr3am3rz Dr3am3rz is offline Offline
Newbie Poster

Urgently need help in getting Autonumbering ID. Thanks Alot.

 
0
  #1
May 28th, 2007
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:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1.  
  2. Function Item_Open()
  3. Dim ADOConn
  4. Dim RS
  5. Dim strSQL
  6. Dim GetID
  7. Set ADOConn = CreateObject("ADODB.Connection")
  8. ADOConn.Provider = "Microsoft.JET.OLEDB.4.0"
  9. ADOConn.Open = "C:\Users\interm\Documents\Outlook\Database\Task Data.mdb"
  10. Set RS = ADOConn.Execute("SELECT [ID] FROM Data;")
  11. Set GetID = RS.Fields("ID")
  12. If Item.Size = 0 Then
  13. Item.UserProperties("Task ID") = GetID
  14. End If
  15. End Function
  16.  
  17. Function Item_Write()
  18. Dim ADOConn
  19. Dim RS
  20. Dim strSQL
  21. Dim MySubject
  22. Dim MyProblem_Type
  23. Dim MyPriority
  24. Dim MyStatus
  25. Dim MyAssignTo
  26. Dim MyAssignBy
  27. Dim MyCustomer_Name
  28. Dim MyCustomer_Email
  29. Dim MyCustomer_Contact
  30. Dim MyCustomer_Company
  31. Dim MyCustomer_Category
  32. Dim MyStart_Date
  33. Dim MyDue_Date
  34. Dim MyDate_Completed
  35. Dim MyReminder_Time
  36. Dim MyDisplay
  37. Dim MyProblem
  38. Dim MySolution
  39. Set MySubject = Item.UserProperties("Subject")
  40. Set MyProblem_Type = Item.UserProperties("Problem Type")
  41. Set MyPriority = Item.UserProperties("Priority Field")
  42. Set MyStatus = Item.UserProperties("Status Field")
  43. Set MyAssignTo = Item.UserProperties("Assigned To")
  44. Set MyAssignBy = Item.UserProperties("Assigned By")
  45. Set MyCustomer_Name = Item.UserProperties("Customer Name")
  46. Set MyCustomer_Email = Item.UserProperties("Email")
  47. Set MyCustomer_Contact = Item.UserProperties("Customer Contact")
  48. Set MyCustomer_Company = Item.UserProperties("Company")
  49. Set MyCustomer_Category = Item.UserProperties("Customer Category")
  50. Set MyStart_Date = Item.UserProperties("Start Date")
  51. Set MyDue_Date = Item.UserProperties("Due Date")
  52. Set MyDate_Completed = Item.UserProperties("Date Completed")
  53. Set MyDisplay = Item.UserProperties("Display")
  54. Set MyProblem = Item.UserProperties("Problem Notes")
  55. Set MySolution = Item.UserProperties("Solution Notes")
  56.  
  57.  
  58. Set ADOConn = CreateObject("ADODB.Connection")
  59. ADOConn.Provider = "Microsoft.JET.OLEDB.4.0"
  60. ADOConn.Open = "C:\Users\interm\Documents\Database\Task Data.mdb"
  61.  
  62. Set RS = CreateObject("ADODB.Recordset")
  63.  
  64. strSQL = "INSERT INTO Data (Subject, Problem_Type, Priority, Status, Assign_To, Assign_By, Customer_Name, " &_
  65. "Customer_Email, Customer_Contact, Customer_Company, Customer_Category, " &_
  66. "Start_Date, Due_Date, Date_Completed, Display, Problem, Solution)" &_
  67. "VALUES ('"&MySubject&"', '"&MyProblem_Type&"', '"&MyPriority&"', '"&MyStatus&"', " &_
  68. "'"&MyAssignTo&"', '"&MyAssignBy&"', '"&MyCustomer_Name&"', '"&MyCustomer_Email&"', "&_
  69. "'"&MyCustomer_Contact&"', '"&MyCustomer_Company&"', '"&MyCustomer_Category&"', "&_
  70. "'"&MyStart_Date&"', '"&MyDue_Date&"', '"&MyDate_Completed&"', '"&MyDisplay&"', "&_
  71. "'"&MyProblem&"', '"&MySolution&"')"
  72. ADOConn.Execute(strSQL)
  73. End Function

Thanks alot if there's any help provided.
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2007
Posts: 4
Reputation: Dr3am3rz is an unknown quantity at this point 
Solved Threads: 0
Dr3am3rz Dr3am3rz is offline Offline
Newbie Poster

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

 
0
  #2
May 28th, 2007
I don't know how to use the code wrap, please forgive me.
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2006
Posts: 848
Reputation: QVeen72 is on a distinguished road 
Solved Threads: 120
QVeen72's Avatar
QVeen72 QVeen72 is offline Offline
Practically a Posting Shark

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

 
0
  #3
May 29th, 2007
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
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2007
Posts: 4
Reputation: Dr3am3rz is an unknown quantity at this point 
Solved Threads: 0
Dr3am3rz Dr3am3rz is offline Offline
Newbie Poster

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

 
0
  #4
May 30th, 2007
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2007
Posts: 4
Reputation: Dr3am3rz is an unknown quantity at this point 
Solved Threads: 0
Dr3am3rz Dr3am3rz is offline Offline
Newbie Poster

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

 
0
  #5
May 30th, 2007
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"
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the Visual Basic 4 / 5 / 6 Forum
Thread Tools Search this Thread



* 6 429 2007 access activex add age application basic beginner birth bmp calculator cd cells.find click client code college component connection connectionproblemusingvb6usingoledb copy creat ctrl+f data database datareport date delete dissertations dissertationthesis dissertationtopic edit error excel excelmacro file filename form hardware header iamthwee image inboxinvb internetfiledownload keypress label listbox listview liveperson login looping machine microsoft movingranges number objectinsert open oracle password prime program prompt range-objects readfile reading record refresh remotesqlserverdatabase report save search sendbyte sites sort sql sql2008 sqlserver subroutine tags textbox time urldownloadtofile vb vb6 vb6.0 vba visual visualbasic visualbasic6 web window windows
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC