Hi,
Basically, I am trying to extract part of an account code to provide the
users with the part of the account code that they need to see, while leaving
the entire account code in the table. I am new to VB, can someone tell me
what is wrong with this code?
Public Function Extract(Account_Code As String) As String
'Setup the connection
Dim MyConnection As ADODB.Connection
Set MyConnection = CurrentProject.Connection
'Define a new recordset and pre-define optional arguments
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = MyConnection
myRecordSet.CursorType = adOpenDynamic
myRecordSet.LockType = adLockOptimistic
'Store SQL statement in a variable
Dim mySQL As String
mySQL = "SELECT MASTER.Invoice_Number, MASTER.Line, MASTER.Queue, "
mySQL = mySQL + "MASTER.Description, MASTER.Units, MASTER.Cost,"
mySQL = mySQL + "MASTER.Category, MASTER.Supplier_Name,"
mySQL = mySQL + "MASTER.Supplier_Number, MASTER.PO_Number, "
mySQL = mySQL + "MASTER.Create_Batch, MASTER.Create_Date, "
mySQL = mySQL + "MASTER.Invoice_Date, Clearing_Account"
mySQL = mySQL + "AS Account_Code"
mySQL = mySQL + "FROM MASTER ORDER BY Clearing_Account,"
mySQL = mySQL + "MASTER.Invoice_Number, MASTER.Description"
'Fill recordset with data from Master table
myRecordSet.Open mySQL
For Each AccessObject In myRecordSet.Fields(14)
Dim Tokens() As String
Tokens = Split(myRecordSet.Fields(14).Value, ".", -1)
Extract = Tokens(3)
myRecordSet.MoveNext
Next
myRecordSet.Close
Set myRecordSet = Nothing
Set MyConnection = Nothing
End Function
Help would be greatly appreciated.
Thanks,
David
davidg2356 said:
I am trying to learn VB, so I am trying to understand.
(Please, bear with me.)
[First of all, I am guessing that by recordset you mean the result of a
query.]
Are you trying to have me set it up so that the code calls the SQL
statement, because if so, that is not really what I want to do. The reason
being is that I want to be able to call the function and apply it to any
query (or object for that matter).
From what I have read, I thought a "For" loop would be the most practical.
What is eof and bof? (end of file?)
I don't know, throw me a line... drowning...
Thanks,
David
Dave F said:
David -
I'm guessing you're accessing your records through a recordset of some
sort.. if this is the case, you can do the following:
dim rs as recordset
set rs = currentdb.openrecordset (sqlStmt)
if not (rs.eof or rs.bof) then ' make sure you have some data
while not rs.eof
code = extract (rs("field_name").value)
'most likely do something else with the code
rs.movenext ' don't forget this line or you end
up in an infinite loop
wend
end if
rs.close
set rs = nothing
That type of loop has worked for me.
Hope this helps -
Dave
davidg2356 said:
Hello,
I have the following code:
Public Function Extract(Account_Code As String) As String
Dim Tokens() As String
Tokens = Split(Account_Code, ".", -1)
Extract = Tokens(3)
End Function
I am guessing that the problem I am experiencing is due to the fact that I
need to loop this function so that it hits every record in the table. (I
am
new to VB [and apparently very dense.])
So how would I loop this, since I won't know how many rows are going to be
in the table at any given time?
i As Integer
i=0
For i=0 To ????
Code
Next
Or Could I say that "While Account_Code <> NULL -> Code->Wend"
Thanks,
David
