How to Allow tables to be filtered in Runtime

[SteveStew44]

Hello AW,

I can open up my tables using the code below in a distributable runtime version, but I cannot filter and sort in the table. Right clicking does nothing.

Dim strTableName
strTableName = Nz(Me.cboTableNames, "")
DoCmd.OpenTable TableName:=strTableName, View:=acViewNormal, datamode:=acEdit

I know with a form we can select to allow filters in the form properties, but when the OpenTable command is used, how can we allow for filtering?
Is it possible to open the tables in a generic form that allows for filtering?

Thanks in advance for any guidance.

Steve

 

[CJ_London]

for runtime, you need to write all your own shortcut menus. I don't know about whether this is possible with tables but it is a very bad idea to allow users direct access to a table - use a form instead.

As to how to write them, google it - it will depend on whether you are using mdb or accdb

 

[SteveStew44]

Thanks for the reply CJ!

Yes, I agree, this is in the admin only section of the db (.mde.), where Users cannot access the tables. I need to pop in and manipulate data once in a while.

So, I gather from what you are saying, you recommend to design a form where the tables can be opened? With respect to shortcut menus, I don't quite understand what you mean since the tables are opening in datasheet view.

Cheers!
Steve

 

[CJ_London]

shortcut menus are the menus displayed when you right click on a column to sort, filter, hide etc. You can program them to do more, such as print or export as well

 

[SteveStew44]

OK, Thanks CJ!

Steve

 

[gemma-the-husky]

why open the table? why not use a query? That's much easier.

 

[SteveStew44]

Hi gemma and others,

The DB is a distributed runtime version and I need to be able to remote in and change table data through an Admin Only section.

I would like to select a table in a combo box. Click on a button and the selected table opens in a form. I need the form so I can allow the data to be filtered/sorted.

After working on this problem, from what I can gather, setting the forms' RecordSource to the selected table is not enough, the form has to have the all the fields dropped in the form. Do you know how through code I can drop all the Field List into the form so I can open it in datasheet?

Thanks,
Steve

 

[gemma-the-husky]

I still think

docmd.openquery "select * from " & tablename

might give you more flexibility

I am not sure what facilities you get in runtime though.

Why not get the client to install one full version you can use for development.

 

[CJ_London]

still think

docmd.openquery "select * from " & tablename

might give you more flexibility

I am not sure what facilities you get in runtime though.

you don't get anything, specifically in this case the options to sort and filter (you also don't get things like ribbon, navigation pane etc). So you need to use a form where you can specify a custom built shortcut menu with this features

setting the forms' RecordSource to the selected table is not enough

No it's not, you need to build a form for each table (which can be done very quickly). I've never tried it in runtime, but you might be able to write some code which opens a table in design view, creates the required bound controls and assigns the required control sources and shortcut menus (filtering numbers has different options to filtering text for example). Then switch the view to datasheet. But creating the forms will be a much easier option.

 

[gemma-the-husky]

just to repeat, then.

for the cost of a few quid, it is a bit daft for any client company not to have at least one full version of access available. It saves reinventing the wheel.

 

[SteveStew44]

Hi Gang,

Thanks for the replies!

Here's a little update.

I created some code that populates a table with the Table name, Field name, and Field Type.

Private Sub btnOpenTable_Click()
Dim strTableName
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim Rst As Recordset
Dim f As Field
Set db = CurrentDb

strSQL = "DELETE [tblTableFieldNames].* FROM [tblTableFieldNames] "
DoCmd.SetWarnings False
db.Execute strSQL
Me.Requery
Me.Refresh

strSQL = "SELECT * FROM [tblTableFieldNames]"
Set rs = CurrentDb.OpenRecordset(strSQL)

strTableName = Nz(Me.cboTableNames, "")
Set Rst = CurrentDb.OpenRecordset(strTableName)
For Each f In Rst.Fields
rs.AddNew
rs("TableFieldName") = (f.Name)
rs("TableName") = strTableName
rs("FieldType") = (f.Type)
rs.Update
Next

DoCmd.SetWarnings True
Rst.Close
rs.Close
db.Close
Set db = Nothing

DoCmd.OpenForm "frmAdminTableEdit", acDesign, "", "", , acNormal

End Sub

My idea was to make some code that would run in the new form On Open Event and create the controls for the table fields in the new form.
However, when the new form opens in Design mode, the code doesn't run in the form On Open Event. I tried to Open in Normal mode then Open the from in Design mode in On Open Event and it it errors.

Any guidance on either:
a) How to open the new form in Normal mode and then open the new form in Design mode and have the code run?
b) How to open the new form in Deign mode and have the code run?

Cheers,
Steve

 

[CJ_London]

when the new form opens in Design mode, the code doesn't run

it won't - just as it won't in design view when you have full access

tried to Open in Normal mode then Open the from in Design mode

it will error

It would be much simpler if you just created a form for each table in you're development db before moving to runtime

My idea was to make some code that would run in the new form On Open Event and create the controls for the table fields in the new form.

If you really want to go this route, have a 'form creator' form. it would have a dropdown for you to select a table, and then the code would run to create the form and save it and then you could click a button or whatever to open the newly saved form.

 

[SteveStew44]

Hi CJ,

Thanks for the info!

Yes, I agree, I used to have a form for each table as you mentioned, but with over 100 tables, it became nuisance to manage because every time a new field was added in the table, the form did not have the new fields automatically.

I like your idea of a form creator. I will explore that and get back with you.

Cheers,
Steve

 

[CJ_London]

all this so you can filter a table. Would be a lot cheaper to do as Dave suggests, buy a copy of Access as a present to your client

 

[SteveStew44]

Hi CJ,

Is there a way though code to drop the Field List of a table into a form?

I could just open the form in Datasheet and that would be it. If only it were that simple, lol.

Cheers,
Steve

 

[spikepl]

It seems to me that you are making something exceedingly complex.

You can abuse the system a bit - you'd have to test whether it works in Runtime. The subform control normally houses a form, but you can in fact assign a table to it.

Try it. Make a form, drag one of the tables onto it. Access will make a subform control and a subform. In the Source Object property of the subform control now select the table rather than the subform . Try the same in VBA, and then in Runtime. Also I'm not sure what facilites you lose by not having a subform - therer is no free lunch.

 

[gemma-the-husky]

all this so you can filter a table. Would be a lot cheaper to do as Dave suggests, buy a copy of Access as a present to your client

I didn't mean give them a development copy. I meant trying to "power-use" access without a full access environment is like trying to run windows without an operating system.

Runtime is generally OK, but a client company ought to buy one or two full copies available, to do things like C&R.

Anyway I just tried opening a query in run-time, and again, there are no right-click options.

 

[CJ_London]

Is there a way though code to drop the Field List of a table into a form?

No, but you could open the tabledef and loop through the fields collection adding controls to a form based on field name and type - and at the same time allocate the appropriate shortcut menu

 

[SteveStew44]

Lol CJ!

Yes, I saw what Dave said, and it seems that it would be cheaper to just buy my clients a copy of Access, lol, but I have over a thousand clients using it.

I am making some progress, though, I'll update shortly...

Thanks All!

Cheers,
Steve

I

 

[SteveStew44]

Hello Gang!

Spikepl, you were on to it! The solution was really quite simple and I was complicating it.

A powerhouse programmer solved it for me with 1 simple line of code, lol! He did exactly what Spikepl was eluding to: Create an unbound Subform and assign a table to it by selecting a table from a combo box.

Private Sub cboTables_AfterUpdate()
Me.subform.SourceObject = "Table." & Me.cboTables
End Sub

Crazy, but so simple and efficient! I am just amazed at how little I know about programming when I come across powerhouse programmers.

Thank you so much Everyone for your input. I hope someone else can benefit from this little trick.

Cheers!
Steve