Help to create a table with query and then run table through a public function

[brittaff]

I am writing code in VBA in Access.
I need to filtered a data set. I am brand new to this so I apologize in advance if this isn't clear.

qryFilter is the query that creates a table "Filter" that only has the filtered data.
BuildExhibits is the public function that I need to run with the new filtered table.

Public Function InuputData()
 
  DoCmd.OpenQuery (qryFilter)
  Dim objRecordset As ADODB.Recordset
  Set objRecordset = New ADODB.Recordset
  objRecordset.ActiveConnection = CurrentProject.Connection 
  objRecordset.Open ("Filter")
  DoCmd.BuildExhibits (Filter)
End Function

[NeoPa]

Hi Britaff.

Perhaps you need to look at walking before you try running. This is way beyond "Not asked very well". There is no question to start with. Just a statement of extent that doesn't make any real sense and some code which is built on what seems to be a complete lack of understanding of what you're trying to do.

Why is that important? So I can slag you off and pretend to be clever at your expense? Not really. Most people just think I'm the dufus here. It's important because we are actually quite good at working out what people mean in spite of their being pretty bad at expressing themselves clearly. This leaves us with nothing to work from. Like there's no defining thought behind it at all. In such a situation we don't even have clues as to what you might be asking for help on. Believe me, when there is a new thread with 50+ views and no answers yet then something is very wrong.

It may help for you to learn about some of the terminology. Mostly we can guess what someone means when they use the wrong words. Here there are so few clues, and what there are contradict each other. Misuse of terminology just makes it even harder to decrypt.

Why don't you start with an explanation, in basic English, of the situation you understand to be required. This should include the data you have available at the start as well as the expected data required from the process. Where data is stored and where it's used in a transient way is also important to indicate clearly.

[brittaff]

I'm sorry, I don't even truly know how to ask my question. This was assigned to me and is obviously beyond my capabilities.

I am using Microsoft Access. I was given an assignment to run a pre-written VBA module that is inside a large VBA project. The module is supposed to be ran on a table created in Access through a query.
I have the query working; it takes a very large data set and creates a table that only includes items from the original data set that have a checkbox checked. The purpose is to only run the pre-written VBA code on selected data instead of all 5 million+ entries.

So, I currently have the filtered data saved in a table called "Filter."
I want this table to be ran through the pre-written module called BuildExhibit.
This module will then create a more detailed table from the "Filter" table.

My issue now lies in creating code in VBA that will use my new table (Filter) and run the BuildExhibit module on the filtered data.
I don't know how if I first have to create the table in VBA for the "Filter" or if there is a function that I could use to call to the already created table.
Then, that table has to be the input data for the BuildExhibit module in the project.

I hope this explains my problem more, if not, I'm at a point where I don't even know how to ask for help.
I'm sorry if this doesn't help. I appreciate any push in the right direction.

[NeoPa]

Hi Brittaff.

I recognise and appreciate the effort you're putting into this so that we can share your understanding of the problem. There are still bits that confuse me but we should be able to make some progress (In fact I found some of my earlier confusions explained - a little out of order but the answers were found which is good).

Brittaff:
So, I currently have the filtered data saved in a table called "Filter."
I want this table to be ran through the pre-written module called BuildExhibit.

To be able to advise on the usage of code - in this case a procedure - we would need to know :

1. What the procedure does.
2. What the calling code passes as parameters.

Asking how to interface to a piece of code we know nothing more about is somewhat akin to asking a member of the audience exactly what they're thinking. There literally are many millions of possibilities and, without clues, we have no idea how to proceed.

Brittaff:
This module will then create a more detailed table from the "Filter" table.

What's the name of the table it's expected to create?

Brittaff:
My issue now lies in creating code in VBA that will use my new table (Filter) and run the BuildExhibit module on the filtered data.
I don't know how if I first have to create the table in VBA for the "Filter" or if there is a function that I could use to call to the already created table.

This seems to say the opposite of what you said before. If you already have the filtered data saved in a table called "Filter." as specified earlier in your post then why do you need to ask about how to create the table? Both can't be true. One must be inaccurate. This is very confusing for people trying to translate your request into something meaningful.

Brittaff:
Then, that table has to be the input data for the BuildExhibit module in the project.

This takes us back to your BuildExhibit procedure. You describe it as a Module but they aren't directly callable so I'm hoping it's a specific Procedure within a Module. Without knowing what it expects as input and what parameters it expects to be passed we're working with blindfolds on. How is the code in the Procedure even written? Does it expect the name of a table in its parameters when called? Or does it expect to find a table, with a particular name AND design to be available already?

Although there is certainly more clarity now than earlier we are still at the point where we're being asked how long the piece of string is.

Hopefully I've provided some insight as to why we need the clarity and also where we most need the information that so far is missing.

You should understand also that this work, if passed directly to any of our interested experts, would be fairly trivial as we'd have the direct access to allow us to answer most of these questions. Much of the difficulty is not in the work itself but in the ability to communicate issues when that access is unavailable. You are by no means the only member who struggles with that aspect.

[brittaff]

The BuildExhibit module is defined as a public function in VBA. The end result is that it aggregates records into a sorted table. These records are coming from an Access table called InputData. The InputData table is created through a query.

The BuildExhibits module in VBA defines the public function BuildExhibits() and ends with creating a table called tblExhibits. This function doesn't have any defined parameters in the function.

This is the beginning of the code in the BuildExhibits module in VBA:

Public Function BuildExhibits()
 
    'Aggregate Records into a sorted table containing an autonumber field.
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * from tblExhibitsTemplate;"
    DoCmd.SetWarnings True
    Dim strSQL As String
 

From here, I believe the tblExhibits table is created.

Instead of data coming from the InputData table, I want it to come from the table "Filter" because it doesn't have all of the inputs, only the ones I want.
I don't know how to run the new table "Filter" through the public function BuildExhibits() (if it is even possible).
I am unsure whether VBA code or a Macro would be the best way to execute this.

I hope this helps explain what I am looking for.. and I will apologize again if it is still unclear. Trying to verbalize something I already don't understand has proven to be difficult.

[isladogs]

I also appreciate your efforts to explain but there are many things that remain unclear.

I still have no idea why you want to create a new table of filtered records in order to update them.
You should never duplicate data in Access databases.
Why not just update those records selected by the filter from the original table?

In your latest post, you say that you have a module called BuildExhibits with a function also called BuildExhibits.
That isn't a good idea and Access will often object if you do so. Always use a different name for the module e.g. modBuildExhibits.

You also state that the BuildExhibits function creates a new table tblExhibits.
However the code snippet shown does no such thing.
Instead it empties all records from an existing table tblExhibitsTemplate
Perhaps the rest of the code that isn't shown does what you said?

I could go on...but hopefully you get the point that your attempts at explanation are mostly adding to the confusion.
It may be best to upload a cut down version of your database after removing all but a few records and altering any confidential data

[NeoPa]

Hi again Brittaff.

I'm guessing the BuildExhibits() function is so long and complicated you want to avoid posting it in full. Understandable. However, at this point that may be the most reliable way of sharing what it does in a way we can understand. Describing such things clearly in English is never easy - and is particularly complex without a clear understanding of it yourself.

Although I see no direct answers to any of my direct questions I do see some stuff which I can determine some of the answers from. That isn't an ideal way to approach direct questions. Direct answers is what you should really be looking for. It saves enormous amounts of time being wasted.

Nevertheless, from the very limited snippet of code it seems clear that the procedure has no parameters and is designed to work directly on a table with a specific name as input data and another for output. We don't have the relevant code to know how that's handled, or even which table that may be, but it's certainly not designed to be flexible.

Brittaff:
Instead of data coming from the InputData table, I want it to come from the table "Filter" because it doesn't have all of the inputs, only the ones I want.

For this you will need to make changes to the BuildExhibits() procedure. Is that possible? If not then whoever gave this to you must have a very limited understanding of what's going on too.

The table references within BuildExhibits() (though we haven't seen any yet) are almost certainly hard-coded to "InputData" as they are not passed as a parameter. Thus that procedure is limited to working on that table exclusively as it stands.

Brittaff:
I don't know how to run the new table "Filter" through the public function BuildExhibits() (if it is even possible).

As it stands, no - it isn't possible.

That said, it would be fairly trivial work to update that procedure to take a table name as a parameter and use that within it instead of the string "InputData". I could explain but it may be easier, and more reliable, if you posted the existing code of that procedure & I could post back an updated version that allowed you to change the input table if desired by including it as a parameter.

Brittaff:
I am unsure whether VBA code or a Macro would be the best way to execute this.

A basic question at last. VBA. Simples!

PS. Adding parameters to allow you to use names for other parts of the process (EG. Output table name.) which may default to one value but allow overriding, is pretty straightforward. If you need that too then let us know.

PPS. IslaDogs also mentioned the idea of of using the output of a SELECT query instead of an actual table, and this would definitely be a better way to approach this. However, I suggest we learn to walk first and maybe look at that idea later on when some of the basics have been dealt with. It will all make much more sense to you once you've built up a little experience.

[brittaff]

I'll attempt to take this one step at a time.
First, yes..the BuildExhibits() code is very long. And as IslaDogs said, the moduke is called modBuildExhibit and the function in the module is BuildExhibits(). I cannot make changes to the function, it is also used by other people. If necessary, I'd have to ask if I can even post it because it is for my internship. I don't believe that the person who assigned this job to me knows much about the code; they just know what they want the code to do.

Here is the code for the InputDataQuery:

SELECT DataInput.Area, DataInput.Code, DataInput.Amount INTO InputData
FROM DataInput LEFT JOIN Areas ON DataInput.[Area] = Areas.[Area]
WHERE (((Areas.Include)=Yes));
 

The "Filter" table has the same fields: Area, Code, and Amount.

Here is the code for the AreasQuery:

SELECT Areas.Include, Areas.Area, Areas.[Status] AS Expr1, Areas.Source
FROM Areas;
 

The Areas referenced above is where the information is hard-coded (I think).

I'd definitely benefit from what you said: "PS. Adding parameters to allow you to use names for other parts of the process (EG. Output table name.) which may default to one value but allow overriding, is pretty straightforward. If you need that too then let us know."

I will also look into the SELECT query and see if that can move me forward at all.

Thank you.

[brittaff]

I found the problem, some misnamed items. Another intern has a similar process working and I am getting help from them.

Thanks for all of your help, sorry for all of the confusion!

[NeoPa]

Ah. That sounds like you've found an easier way to approach this. I'm happy for you :-)

A bit of a shame as things are just starting to become workable here but ultimately we're here to help so knowing you have local help is good too.

One thing I will offer, if I may, is an illustration of my earlier comment about the simplicity of amending a procedure to handle optionally setting a particular value but defaulting to the original when this option isn't used. For this I'll use a Subroutine (Sub) Procedure rather than a Function Procedure as your code does as there is no requirement to return a value - and that's what differentiates the two types.

So, assume the code below does something with a table called [tblOriginal] (For this illustration what it does hardly matters.) :

Public Sub MySub()
    'Some illustrative code simply selects the table in the Navigation Pane.
    Call DoCmd.SelectObject(ObjectType:=acTable, _
                            ObjectName:="tblOriginal", _
                            InDatabaseWindow:=True)
End Sub

Now I want to be able to use it to do the same fundamental work, but on a different table - but here's the kicker - this must be done in such a way as to ensure all the others who use this procedure are not affected by my changes. In that case I'd do it like this :

Public Sub MySub(Optional ByVal strTable As String = "tblOriginal")
    'Some illustrative code simply selects the table in the Navigation Pane.
    Call DoCmd.SelectObject(ObjectType:=acTable, _
                            ObjectName:=strTable, _
                            InDatabaseWindow:=True)
End Sub

You should notice that the only changes are on lines #1 & #4. There are no additional lines, nor any removed. Just a couple of simple changes that allow me to specify my own value if I want to, without upsetting anyone else who already uses this code.