Little database help? why isnt this working? 3

[unborn]

Im trying to get a 2 digit number from my database. Database is Clients then Table is Contracts. Im new to db and ive compiled this code with other parts of code that works in my program but isnt working now.

Public Function RevServiceDay(sdCustomer)
Dim mCn As ADODB.Connection
Dim mRs As ADODB.Recordset
Dim Rs As ADODB.Recordset
Dim sSql As String

Set mCn = New ADODB.Connection
Set mRs = New ADODB.Recordset
mCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\my work\Programs\clients.mdb"
mRs.Open "Contracts", mCn, adOpenKeyset, adLockOptimistic, adCmdTable

sSql = "SELECT ServiceDay FROM Contracts WHERE CustomerID LIKE '%" & Format(sdCustomer, "00000000") & "%'"

Set Rs = New ADODB.Recordset
Rs.Open sSql, mCn, adOpenForwardOnly, adLockReadOnly

While Not Rs.EOF And Not Rs.BOF
 testStr = GetString(Rs.Fields("ServiceDay").Value)
 Rs.MoveNext
Wend

RevServiceDay = testStr

Rs.Close
mRs.Close
mCn.Close
End Function

Basically i put label1 = RevServiceDay(txtAcctID) it it pulls the account number from a text box and needs to look up the service date for this client. When i check it the recordcount shows as "-1" so im assuming there is no records found. But i am looking in my database and it does have information in it. Ive altered the code many times trying to get it to work and i feel this is the closest way but i just cant seem to get it. Any help would be appreciated!! Thanks!

Running in circles is what I do best!

 

[gmmastros]

Your getting close.

To use the GetInfo subroutine that I mentioned before...

Suppose you have a form. On this form you have a couple captions, one for Shoe Size and the other for Eye Color.

You could have a subroutine called, DisplayData like so...

Private Sub DisplayData(ByVal CustomerId As Long)
  Dim strEyeColor As String
  Dim dShoeSize As Double

  Call GetInfo(CustomerId, dShoeSize, strEyeColor)

  lblShoeSize.Caption = dShoeSize
  lblEyeColor.Caption = strEyeColor
End Sub

When this subroutine is first called, you will know what the CustomerId is because it is being passed in. The purpose of the subroutine is to display information on the screen. In order to do this, we need to get the information from the database, that's where GetInfo comes in. You call the GetInfo subroutine, passing in the CustomerId (that we already know). This subroutine has 2 byref parameters so you can think of it as "this subroutine passes back 2 values". Since the parameters are byref in the GetInfo subroutine, you change the values. The values appear changed outside the GetInfo subroutine (in this case, in the DisplayData subroutine). Then, you set the caption properties to the values "passed back" from the subroutine.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[unborn]

Ok i think you just helped me in making a sub for easy access/adding of info into my database let me verify!!

Private Sub DisplayData(ByVal CustomerId As Long)
  Dim strEyeColor As String
  Dim dShoeSize As Double

  Call GetInfo(CustomerId, dShoeSize, strEyeColor)

  lblShoeSize.Caption = dShoeSize
  lblEyeColor.Caption = strEyeColor
End Sub

So in "getInfo" i would be supplying the code to draw the informaion of the chosen items out. Now because they are set as ByRef it allows what ever the data it gets to be pulled out and changed in the sub. Now if i have this a Global that would change it globally correct? Or is that awhole other mess :x

Now lets say i didnt want to pull all the information. I could probably code something that if i leave it blank or put 0 in it it wont draw that information.. aww man im so excited hahaha ive been trying to figure out how to code my database access so i dont have to type the same information 50 million times.

So i could actually use this in a control action like.

Dim dbFName as String
Dim dbLName as String

GetName (000001023, dbFName, dbLName)
txtLName = dbFName
txtLName = dbLName

so the acctID would actually have a value and then i would just type in the variables above or can i leave them blank since they will be set anyways. I noticed nothing is passed to them so they are entered blank and after the sub is called it assigns the information to them for me to distribute as i need. Correct?

-Hits vb to test some situations out.

Man i dont know how ive gone with out this knowledge all these years haha i feel lame but this is valueable!! thanks again!

Running in circles is what I do best!

 

[unborn]

Ok reading through a tutorial site it said i could do the same thing with functions -shrug is there a down fall to this?

Private Sub Command1_Click()
Dim FName As String
Dim LName As String

FName = "Mary"
LName = " Bennett"

MsgBox FName & LName & " Before Funct"

MsgBox FuncCheck(FName, LName)
MsgBox FName & LName & " After Funct"


End Sub

Public Function FuncCheck(ByRef FName As String, ByVal LName As String) As String
FName = "Mark"
LName = " Jones"
MsgBox FName & LName & " In Function"
FuncCheck = "True"
End Function

It allows me to still grab the items out of it and then return a value i guess i could use to determine if it successfully changed?

So it would then depend if i need the actuall call to return something if not i would assume a sub would be best because it would be like defining a variable and not using it. Correct?

Running in circles is what I do best!

 

[BobRodes]

<Strings are a really bad example to choose ...

The main reason is that strings' behavior gets a bit counterintuitive when passed to API calls.

In code not involving API calls, the behavior is as expected (although a good deal more is going on internally than appears on the surface):

Private Sub ChangeString(ByRef a As String, ByVal b As String)
a = "new string"
b = "new string"
End Sub

Private Sub Command1_Click()
Dim a As String, b As String
a = "old string"
b = "old string"
ChangeString a, b
Debug.Print "a = " & a
Debug.Print "b = " & b
End Sub

In the above, the debug window shows
a = new string
b = old string

Now, looking at a simple API call (well, about as simple as they get anyway):

Private Declare Function GetWindowsDirectory Lib "kernel32" Alias "GetWindowsDirectoryA" _
(ByVal lpBuffer As String, ByVal nSize As Long) As Long

Private Function WinDir() As String
Dim s As String * 255 'fixed length string is not really necessary
Dim i As Integer
i = GetWindowsDirectory(s, 255) 'i will hold the number of characters changed from null
WinDir = Left$(s, i)
End Function

In this example, s will contain the Windows directory. In other words, the lpBuffer argument, which is byVal, alters the string s that was passed to it. This seems to contradict the rules.

A clue to this behavior is in the name of the argument itself. What we're actually passing to lpBuffer is a long pointer to a buffer, while nSize is the size of the buffer. In other words, we're actually passing the value of a memory address. The API function populates as many bytes of the memory address as needed to return the windows directory, and leaves the rest of the buffer as null characters. Its return value is the number of characters it used up in the buffer. So, we can take the return value and use it to strip off excess null characters in the string.