Windows 7 32bit 
Access 2007
I am not real familiar with VBA but am with programming. I have a function that I am calling from a MACRO to Format data that was imported from a text file and write it to a new file. The problem I am dealing with is that I want to call a subroutine of code that is called numerous times from the function. I seem to be just having problems with what I shoud use. I tried to create Public Functions and using a call command to call it. One of my subroutines is to loop through the file locating a paticular record and then returning to the original function. The problem I have is when I go to the called Function it does not recognize my recordset. It says I have no Object Defined. Here is just a snip it from my code. I tried to handle this like an error code by just putting routines at the end, using a goto and then exiting the function after my loop was complete but I got an error that there was no "Error". Please point me in the right direction of what I should be using here. I simply just want to call a subroutine so I don't have to write the code over and over. This is just a small snipit of my code...but gets the logic across I am trying to accomplish.
Public Function ImportFormat()
Dim db as dao.database
dim rs1 as dao.recordset
set db as currentdb()
set rs1 as db.openrecordset("Import")
Do until rs1.eof
Select Case rs1!fld1
Case Is = "B1"
mcount2 = mcount2 + 1
Call Pieces1
b3pi = rs1!fld1
Call Postage1
b3po = rs1!fld1
Case Is = "B2"
mcount2 = mcount2 + 1
Call Pieces1
b4pi = rs1!fld1
Call Postage1
b4po = rs1!fld1
Case Is = "B3 "
mcount2 = mcount2 + 1
Call Pieces1
b7pi = rs1!fld1
Call Postage1
b7po = rs1!fld1
End Select
Loop
End Function
Public Function Pieces1()
Do Until rs1!fld1 = "No. of Pieces"
rs1.MoveNext
mcount = mcount + 1
Loop
Return
End Function
Public Function Postage1()
Do Until rs1!fld1 = "Total Total Postage"
rs1.MoveNext
mcount3 = mcount3 + 1
Loop
Return
End Function
Reply With Quote
