Variable length array function-Query help needed

[vbJupiter]

I have a sub routine that add news records to an AssignedBatches table based on criteria in a function. The function returns a variable length array of batch numbers that are to used in assigning batches. I've got this part working well. Now I need to show the user the added records. My understanding is that Access SQL does not allow variables in teh criteria, but does accept functions.

I have the batch numbers stored in a Public Function called GetBatchNumbers() within the current form. I created a query detailing all the fields the user needs to see with the criteria: Where MasterBatch.BatchNum = GetBatchNumbers()

In a sub routine within the current form, I am opening the query using:

docmd.OpenQuery "qryAssignedBatches", acviewnormal, acReadonly

I am getting a runtime error (3085): undefined function 'GetBatchNumbers' in
expression.

I don't think it's the SQL syntax that's causing the error. Is my
problem that I am opening the query from within a subroutine & it can't see
the function data? Or do I need to have the function in it's own module for
this work?

Perhaps a temporary table to store the batch numbers is the way to go?

I could use some help!
Thanks,
AA

 

[pbaldy]

Try putting the function in a standard module rather than a form module.

 

[vbJupiter]

Thank you. I'm trying to get er going in the module. I haven't used modules before, can you please tell me how to call a form sub routine from within the module & pass the module's function values to it?

ie. when I had the function in the form, after I assigned the variables, I called the sub routine by:

AssignBatch strBatchNumbers()


I am struggling to figure this out in the module....

 

[vbJupiter]

I'm sorry, after rereading that, I think I could have worded it much better.

I would like to know how to pass an array (loaded in a standard module function) as an argument to a form procedure.

Hope that clarifies!

 

[gemma-the-husky]

how many batch numbers have you got - why do you need an array, and how many numbers are there in the array.

its just that your criteria expression is confusing. If you only need one batchnum, what function type is getbatchnumbers defined as.

MasterBatch.BatchNum = GetBatchNumbers()

 

[vbJupiter]

how many batch numbers have you got - why do you need an array, and how many numbers are there in the array.

its just that your criteria expression is confusing. If you only need one batchnum, what function type is getbatchnumbers defined as.

MasterBatch.BatchNum = GetBatchNumbers()

The MasterBatch table contains thousands of different batch numbers. There are 16 criteria rules as to what batch number gets assigned depending upon what type of functionality is needed. I use case statements within the function to return the correct batch number(s) associated with the type of batch. The function also returns a variable length array because the user can select multiple quantities.

I hope that helps. TIA!

 

[gemma-the-husky]

I think your function has to be declared in a module not in a form, although I could be wrong, but its straightforward

go to the modules tab, and open a new module - its exactly like a form code, exact that the data is live throughout the project. dims and subs and functions can be private or public - public can be used by any form in your dbs, private only within the module itself.

i think if you put your batch number array, and the public function getbatchnumbers in the module instead of in the form, you should be ok, except that I think the statement

Where MasterBatch.BatchNum = GetBatchNumbers()

is not correct. I think you are trying to return the rows where the batchnum is included in your array - is that correct?

If so, I think it should be

where getbatchnumbers([masterbatch].[batchnum]) = true

getbatchnumbers needs to be declared as a boolean function, and returns true if the batchnum is in your array, and all the logic for this has to be handled within the function

Hope this makes sense - let us know if you still have errors

 

[vbJupiter]

I think your function has to be declared in a module not in a form, although I could be wrong, but its straightforward

go to the modules tab, and open a new module - its exactly like a form code, exact that the data is live throughout the project. dims and subs and functions can be private or public - public can be used by any form in your dbs, private only within the module itself.

This makes sense. I've got the function in a module and it is working.

i think if you put your batch number array, and the public function getbatchnumbers in the module instead of in the form, you should be ok,

The public function getbatchnumbers is what gives me the batch number array. They aren't two seperate procedures. So, I can't do the following suggestion:

except that I think the statement

Where MasterBatch.BatchNum = GetBatchNumbers()

is not correct. I think you are trying to return the rows where the batchnum is included in your array - is that correct?

If so, I think it should be

where getbatchnumbers([masterbatch].[batchnum]) = true

getbatchnumbers needs to be declared as a boolean function, and returns true if the batchnum is in your array, and all the logic for this has to be handled within the function

Hope this makes sense - let us know if you still have errors

See, if I convert it to a boolean function, I lose the actual batch number results, which are used in a form sub routine to add the records to the assigned batch table. My function in the module is working fine, there just seems to be a disconnect between sub routine and the function. Is there a way to pass the array from the module to the subroutine on the form as an argument?

When I had the function in the form module with the sub routine, to pass the array to the sub, I simply had:

AssignBatch GetBatchNumbers()

where AssignBatch is the sub routine & GetBatchNumbers the array


Thanks for your help!