How to Allow tables to be filtered in Runtime (1 Viewer)

SteveStew44 · Feb 17, 2015

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 · Feb 17, 2015

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 · Feb 17, 2015

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 · Feb 17, 2015

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 · Feb 17, 2015

OK, Thanks CJ!

Steve

gemma-the-husky · Feb 17, 2015

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

SteveStew44 · Feb 18, 2015

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 · Feb 18, 2015

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 · Feb 18, 2015

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 · Feb 18, 2015

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 · Feb 19, 2015

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 · Feb 19, 2015

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 · Feb 19, 2015

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 · Feb 19, 2015

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 · Feb 19, 2015

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 · Feb 19, 2015

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 · Feb 19, 2015

CJ_London said:
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 · Feb 19, 2015

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 · Feb 20, 2015

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 · Feb 20, 2015

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

How to Allow tables to be filtered in Runtime (1 Viewer)

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Super Moderator

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Eledittingent Beliped

Super Moderator

Super Moderator

Registered User.

Registered User.

Similar threads

Users who are viewing this thread