Visual Basic 6 and Max record in sql server

[B1naryPro]

I have vb6 and i am connected to a sql server 7 database through the data environment. What i want to do is when I add a new record in VB I want to have a function which goes out to the sql server and checks a certain field (say an ID field) and gets the max record of that field and adds an increment of 1 to the text box. I.e so if there is 7 in the id field i want the text box to populate with 8. Thanks

JPBinary
MCSE, MCSA

 

[SkipVought]

Hi pro,

You know that there's only 10 kinds of people in the world.

Since you have a function that returns the max rows in a table, wouldn't your answer be...

TextBox1.Text = MaxRows(MyTable) + 1

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[B1naryPro]

I don't have a function for this yet.

JPBinary
MCSE, MCSA

 

[SkipVought]

Costello: "I want to meet my uncle in Alaska."
Abbott: "Well, when you get to Alaska, you'll get to Nome."
Costello: "I already know him! He's my uncle!"

I almost responded by saying,

"When you get you function, you'll have your answer!"


Are you asking how to write that function?


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[B1naryPro]

yes i'm asking help in writing the function for the maxrecords.
thanks

JPBinary
MCSE, MCSA

 

[SkipVought]

Here's a sample that you can start from...

Function MaxRows(sTable As String)
    Dim dbsMAP As DAO.Database, wksMAP As DAO.Workspace, rstMAP As DAO.Recordset
    
    Set wksMAP = CreateWorkspace("ReadMAP", "ADMIN", "", dbUseJet)
    
    Set dbsMAP = wksMAP.OpenDatabase("C:\Documents and Settings\metzgerc\My Documents\ExcludeList.mdb", dbDriverNoPrompt, False, "")
    
    Set rstMAP = dbsMAP.OpenRecordset(sTable, dbOpenTable)
    With rstMAP
        .MoveLast
        .MoveFirst
        MaxRows = .RecordCount
    End With
    
    Set rstMAP = Nothing
    Set dbsMAP = Nothing
    Set wksMAP = Nothing
End Function

Hope this helps :-0


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[B1naryPro]

Ok i have the function now how do i incorporate in the text box. No how do i populate the textbox with the field

textbox1.text = MaxRows ????

Here is my function:

Function MaxRows(sTable As String)
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
con.Open strCon
sSql = "Select Phonelogid From tblMain"
rs.Open sSql, con
With rs
.MoveLast
.MoveFirst
MaxRows = .RecordCount
End With
'close connection and recordset
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
End Function

JPBinary
MCSE, MCSA

 

[B1naryPro]

Ok i have the function now how do i incorporate in the text box. how do i populate the textbox with the field?

textbox1.text = MaxRows ????

Here is my function:

Function MaxRows(sTable As String)
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
con.Open strCon
sSql = "Select Phonelogid From tblMain"
rs.Open sSql, con
With rs
.MoveLast
.MoveFirst
MaxRows = .RecordCount
End With
'close connection and recordset
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
End Function

JPBinary
MCSE, MCSA

 

[SkipVought]

textbox1.text = MaxRows(MyTableName)

assuming that the database is fixed. Otherwise you will alos have to pass the database patn/name.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[B1naryPro]

When i do that i get an error of

ByRef argument type mismatch

JPBinary
MCSE, MCSA

 

[SkipVought]

the variable(s) that you pass has/have to be declared as the same type as the function's argument(s)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com