Creating a public Function and passing the recordset.

[ssalem]

Hi,


Here I am again with another problem. I have a bunch of repetitive code that I want to create a function that I can call. Not sure if I can do this a better way so needing some direction. In Foxpro I use to create a sub routine within that program or just call another program and return. In VBA not so easy. This is what I have and what I want to accomplish. I am importing a text file that has a similar format as this:


b1
b2
b3
b4
b5
b6
x
x
x
No. of Pieces
qty1
qty2
qty3
qty4
qty5
qty6
x
x
x
Amount of Postage
amount1
amount2
amount3
amount4


amount5
amount6

What I am trying to do is import this into a table with b1,qty1,amount1 and loading them into the correct fields. I want to only code the movement through the file once. This is my subroutine I want to create.

Public Function Pieces1()
Mcount2 = Mcount2 + 1
Do Until rs10!fld1 = "No. of Pieces"
rs10.MoveNext
mcount = mcount + 1
Loop
rs10.Move Mcount2
mcount = mcount + Mcount2

End Function

I have defined mcount,mcount2 and mcount3 as public variables but the problem I am having is passing the recordset. I want this to move through the recordset from the record location in my main sub and I need to return the values of all my counters. Mcount2 is my counter for movement between b1,b2,b3 so I know if I am on the 1,2,3,4,5 or 6 record of the set of records. mcount is my counter how many lines I move to obtain the qty. The code I wrote works exactly how I want it to....I just don't want to code is 100 times over and over. Hope I explained this clear enough. I guess my question is: Is a public function the way I need to do this or is there an easier way. If it is how do I pass the recordset so I can maintain my movement through the recordset.

Any direction would be helpfull.

I was able to declare my rs10 as public but when I try to set the recordset publically it won't allow me to. This is a DAO recordset.

[rzw0wr]

Public Function Pieces1()
Mcount2 = Mcount2 + 1
Do Until rs10!fld1 = "No. of Pieces"
rs10.MoveNext
mcount = mcount + 1
Loop
rs10.Move Mcount2
mcount = mcount + Mcount2

End Function

Unless I am missing something important or there is more code somewhere, I don't think this is a DAO recordset.

To make a DAO recordset;

Dim st10 as DAO.RecordSet
Dim db as currentdb

set st10 = db.DAO.OpenRecordset(query or table name as string)

st10.Close
Set st10 = Nothing

Nothing has to be a public variable.

Dale

[ssalem]

Yes there is alot more code to this. This is the function I am calling from my sub where the recordset is defined. If I set my recordset here it will not be at the same location as in my Sub. So I have a sub with somewhat this code.


Dim db as dao.database
dim rs10 as dao.recordset

set db = currentdb()
set rs10 = db.openrecordset("Import")


Select Case Mid(rs10!fld1, 1, 3)
Case Is = "B1"
Call Pieces1
b1pi = rs10!fld1
Call Postage1
b1po = rs10!fld1
mcount4 = mcount3 + mcount
rs10.Move Val("-" & mcount4)
mcount4 = 0
mcount = 0
mcount3 = 0
Case = "B2"
etc etc over 100 different possiblities. Not all starting with B.


I want to do functions for both the Pieces and Postage since it is the same code over and over. I actually could create a function for skipping backwards and zeroing out my counts in a function also so I dont have to repeat that code. But I would like to figure this out first. Mainly the function is to move through the recordset and keep track of its location.

[rzw0wr]

I guess I am still confused.
Why the Public variables if this is a DAO recordset?
Why do you dimension your recordset in a sub?
I would think that the dimensions would not be in the same scope as the function.

Dale

[ssalem]

ok lets make this very basic.

I have a sub that is converting a text file to a table.

from that sub I want to create a function to do repetitive code. Not sure how I should go about it. I am very much a novice at VBA. Old foxpro user. So don't totally understand how passing variables between subs and functions totally work. But I think this is very basic as far as a programming aspect. I have a bunch of code I want to repeat over and over. I need to know how with going to a function I can move through records in a recordset and return my record counters. Maybe I don't need to declare them as public variables. I am trying anything to get it to work. My function bombs at the reference to my recordset saying my object is not defined. I made my counts public but if I don't need to then I just need to pass them back.

[rzw0wr]

I too am fairly new to VBA.

The attached link may explain more than I can.

http://msdn.microsoft.com/en-us/library/dn125824.aspx

I never define a cursor type.

Dale

[DepricatedZero]

ssalem, what you want to do is define your function. It's currently just a fake sub.

Subs execute, functions return.

So you could do something like this to make sure your object is passed and returned:'

Private Function Pieces1(rs as DAO.Recordset) as DAO.Recordset

The first section, "(rs as DAO.Recordset)" defines what the function receives. This can be done with subs as well. The second part is unique to functions - it defines what the function is, what it returns. It's how the compiler knows that when integer "X = CalculateIntegerFromCoffee(coffee)" that CalculateIntegerFromCoffee will return an Integer Type object. It also turns the function into a variable within itself.

For example, a very simple setter/getter from a data structure:

Public Sub SetName(strName as String)
   name = strName
End Sub

Public Function GetName() as String
   GetName = name
End Function

so what you might want to do is something like this

Private Sub Pieces1(rs as DAO.Recordset)
Mcount2 = Mcount2 + 1
Do Until rs10!fld1 = "No. of Pieces"
rs10.MoveNext
mcount = mcount + 1
Loop
rs10.Move Mcount2
mcount = mcount + Mcount2

End Sub

I can't tell exactly what you're doing with mcount and mcount2 though.

On the topic of public/private function/sub

Most things should be private. Only make it public if you would want something other than itself to be able to access it. For instance the setter/getter concept is a result of the idea that classes should know about themselves, and ask about eachother. You shouldn't call the properties of another class directly, but by asking the class for it - same with changing it . This ensures that the data is received as intended.

Subs are used when nothing is returned, or expected returned.

so whereever this is being called you would do something like "Pieces1 rs" to call it and pass it the recordset (assuming your recordset exists before the call and is called rs, and no return data is expected)

[ssalem]

If I am starting at B1 and moving forward 9 records to get to "No of pieces" mcount is keeping track of how many records it is moving forward so you can return back to B1. Mcount2 is keeping track of which record in the set it is and adding it to mcount. mcount is then used to do rs.move "-" & mcount. Then I am moving forward 1 record to pick up B2. mcount and mcount2 are just counters to keep track of movement. It all works correctly i just want to reduce coding for something repetitive.

Thank you for explaining in basic terms of exactly what and why you do something. Sometimes examples explain things much easier so that you can relate to it. Sometimes I read this stuff and without having the basic knowledge of how things work you can get confused. I like to understand what I am doing and understand the concept so I can apply it with knowledge in the future.

Thank you for sharing your wisdom and understanding. It is forums like this, when you are teaching yourself, that help with the struggles. I read some of this Microsoft explainations but they don't give clear enough examples of what you are truly looking for. Plus the fact you maybe not understanding the basics before attacking a project. But you have to get it done anyways.

I will let you know how it all shakes out. I am also dealling with my procedure being too long. So understanding subs and functions more will help in breaking it into sections and reducing code.

[ssalem]

Quick question if I need to return the counters back to the original sub do I need to define and pass those as well? So I think my understanding is as follows:

I need to pass the recordset as in your original statement. So I can still keep track of where the record pointer is. But I also need to return the values of mcount and mcount2.

Private Function Pieces1(rs as DAO.Recordset) as DAO.Recordset

Would I then need to do this?

Private Function Pieces1(mcount as integer, mcount2 as integer, rs as DAO.Recordset) as DAO.Recordset

But I am not quite sure how I return. I am passing mcount and mcount2 but also want to return them. I will be playing with this to see what works.

Thanks again.

[DepricatedZero]

Glad I'm able to help self teaching can be a pain. Functions were one of the biggest hurdles I had. Not the concept of them, but the way they work.

In VBA, Functions can return only a single element. Now, that element CAN be an Array, but that's probably beyond what you're trying to do.

One simple rule I follow is that "functions should be short. they should be shorter than that."

I would say at the point you need multiple data types returned is where you should start splitting your functions. I'm going to throw together a bit of code for you - I don't promise it will work or be what you want, and I'm going to rename your vars a little:

Private Sub Pieces(rs as DAO.Recordset)
' You could use "rs.MoveFirst" here to always go to the first record
   Dim currentIndex as Integer 'this was mcount2
   Dim arrPieces(1 to rs.RecordCount) as Integer
   currentIndex = rs.AbsolutePosition

   Do Until rs.EOF 
      arrPieces(currentIndex) = GetPieces(rs)
      currentIndex = currentIndex + 1 
      rs.Move = currentIndex
   Loop
End Sub  

Private Function GetPieces(rs as DAO.Recordset) as Integer
   Do Until rs!fld1 = "No. of Pieces"
      rs.MoveNext
   Loop

   GetPieces = rs.AbsolutePosition
End Function

Untested and half-cocked but it demonstrates the method I think. What this should do, if I'm understanding what you're trying to do correctly, is this:

Create an array to store numbers of pieces in. Each entry in the recordset has a number of pieces. Create a variable to track your absolute position in the recordset (you may or may not want to move to the first record). Now in a loop through each record in the recordset:

Assign to the index in the array that correlates to the index of the recordset (1 is 1, 2 is 2), the number of pieces by calling GetPieces.

GetPieces just iterates through the recordset until it finds "No. of Pieces" and then returns the AbsolutePosition - the record number, which is then set as arrayPieces(currentIndex).

Then add 1 to your current index and move to that record, and loop.

Not sure if I got what you were going for but that should at least give you something to go on

Functions return values when the function is exited, by what was last assigned to the function. Note how just before "End Function" I have "GetPieces = rs.AbsolutePosition" - that's what sets the returned value

[ssalem]

Thanks again, I did solve it before you replied and everything is working fine and breaking things into other functions reducing code I fixed the procedure to large problem I was having also. I had to break some of my select statements into functions and return the variable I was looking for but that cleared it up. By using my original code and just having my function as (rs10 as dao.recordset) after my function name and having mcount and mcount2 as public variables everything works fine. I will set error trapping to reset mcount and mcount2 if errors happen.

Thanks again for your help it is much appreciated.

[DepricatedZero]

Absolutely! Glad to hear you got it working as you wanted.