How to populate last record in Textbox from db .

[zebnoon]

Hye, I have problem with Auto Generate Slip No from Database.I wrote following code to get last record but it does not give me last record. Please help me?

Private Sub Command1_Click()
Module1.Connect

Dim rs As New ADODB.Recordset
Dim str As String

str = "SELECT Item_ID FROM Entry_Tab"
rs.CursorLocation = adUseClient
rs.Open str, conn, adOpenDynamic, adLockOptimistic

rs.MoveLast
Text1.Text = rs("Item_Id")

rs.Close
Module1.Disconnect
End Sub

[debasisdas]

change line # 9 to following and try.

rs.Open str, conn, adOpenStatic, adLockOptimistic

[zebnoon]

All is going well . without any problem but when i open MS Access Database it stop to get last data from db.even i tried your method i got same problem code is there.

Module1.Connect

Dim rs As New ADODB.Recordset
Dim str As String

str = "Entry_Tab"
rs.CursorLocation = adUseClient
rs.Open str, conn, adOpenStatic, adLockOptimistic


rs.MoveLast
Text1.Text = rs("Item_Id")
txtID.Text = Text1.Text + 1
rs.MoveNext


rs.Close
Module1.Disconnect

[BitBlt]

Where is the object "conn" set? Are you supposed to be using Module1 as your ADODB connection? If so, then you need to specify that in your recordset open statement.

[zebnoon]

here is my connection Module

Public Sub Connect()
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = E:\Projects\TS\database\MyDb.mdb"
conn.Open
End Sub

[zebnoon]

and please give me what is difference between

rs.Open str, conn, [B]adOpenStatic[/B], adLockOptimistic

and

rs.Open str, conn, [B]adOpenDynamic[/B], adLockOptimistic

?

[debasisdas]

A cursor controls the navigation of records in a recordset and how the records will be updated

adOpenForwardOnly - this is the default cursor if no other is specified. This cursor allows only forward movement through a recordset

adOpenKeyset - this cursor supports forwards as well as backwards navigation. It also allows you to update a recordset and all changes will be reflected in other users recordsets. The cursor also supports bookmarking

adOpenDynamic - this cursor supports forward and backward navigation but bookmarks may not be supported (ie Access). Any changes made to data are immediately visible with no need to resynchronise the cursor with the database

adOpenStatic - this cursor uses a static copy of data from the database and therefore no changes to the data are visible and supports forward and backward navigation

[BitBlt]

Where is the variable "conn" declared? Is it a global? Is it shared at the module level? Where is your "Connect()" function declared? What is the actual error you receive? Have you stepped through execution of your code? Have you put any of your variables in the "Watch" window? You aren't giving enough detail to diagnose.

[zebnoon]

please pay tension !listen. I am developing a software ware Auto ID have to generate For example I have (billing Receipt first Primary key) SlipID is 1 it is working almost well but when i open Actual database file to see records .after it when i open application of VB6 to add more entries it does not give me Previous SlipID when i receive error about duplicate primary key . Made a module to connect database Module1.Connect where conn is conn object.

Public Sub Connect()
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\WINDOWS\system32\Mydb.mdb"
conn.Open
End Sub

i think it will help to diagnose problem.

[BitBlt]

I don't understand what you are after. Your narrative is disjointed and refers to things that didn't appear anywhere else in the thread. You didn't answer a single one of the questions I asked, and posted the same code snippet from earlier in the thread.

Sorry I wasn't able to help. Perhaps someone else can do better.

[debasisdas]

@zebnoon

can you please rephrase your last question.

[AndreRet]

How ar you zeb? I am only back in a blib of a second, lol

Your answer is quite easy, if all the popsters read it properly -

You need to create a "next" record from the last record as per your question - Auto Generate Slip No?

All you need to do is to count the record count and add 1 to create the next record....

str = "SELECT Item_ID FROM Entry_Tab"
rs.CursorLocation = adUseClient
rs.Open str, conn, adOpenDynamic, adLockOptimistic
 
rs.MoveLast
Text1.Text = rs("Item_Id")
 
rs.Close

becomes something like ----

Dim xCount As Integer

str = "SELECT Item_ID FROM Entry_Tab"
rs.Open str, conn, adOpenStatic, adLockOptimistic
xCount = rs.RecordCount 
Text1.Text = rs + 1
'All code you need to add the next record here.....
 
rs.Close

[zebnoon]

its ok any way i little bit solved my problem. anyway thank u.

[zebnoon]

anyways thank you BitBlt.i think i solve this problem .

[rasheedraj]

Function Cant()

   Dim cnview As New ADODB.connection
   Dim rsview As New ADODB.Recordset

Call connection(cnview, App.Path & "\Commissions.mdb", "endromida")
Call Recordset(rsview, cnview, "SELECT * FROM Commi_Cus")

   If txtShaha = ("Commi_Cus" ,"Sha_no") Then
      txtCou = Sha_no + 1
    End If


End Function