Solved: on table close event.

[Trevor]

is there a way through vba to order a column of a table either assending or decending.
I am using a form to open a table(doesn't close the table.), but when the user clickes the "x" to close the table or uses file> close, I would like the table to automtically sort a colum either assending or decending ie
Table = table and column = colume
so table.column
Thanks for help

[Carl A]

I'm not sure that can be done.

One suggestion would be to create a form in datasheet view and apply a filter using advanced filter. Then in the forms properties set the Filter On load to yes Here you can set you Order By criteria and Order by on Load to Yes. By using a form it will give you more control over your users.


HTH

[Trevor]

you mean a form to open the table?, if yes I've done that?, the only reason I am trying to do it on close is so that everything stays in alphabetical order after they edit the table or is my best solution to tell the users when done editing before closing to right click column 'x' and order assending/decending?

[Carl A]

you mean a form to open the table?, if yes I've done that?,
No use your existing form to open another form that has been designed in Datasheet view it will resemble the table and the user will most likely be none the wiser. I have included an example. By default Access displays the records in the order they have been entered. Refer to my previous post on the forms properties. By using a form you can hide your tables and the users will not be allowed to modify them. (of course they can always undhide them).

HTH

[Trevor]

Is it possable to prevent the users from re-nameing colum(s) useing a form to present a table,.
FYI: I Don't think I can actualy hide my tables , i think it will cause an error, "error can't find table"

[Carl A]

Is it possable to prevent the users from re-nameing colum(s) useing a form to present a table,.
FYI: I Don't think I can actualy hide my tables , i think it will cause an error, "error can't find table"
You can make your database application a MDE. If that is not an option a crude one would be:

Private Sub Form_Unload(Cancel As Integer)
'of course isAdmin would probably be Public in a Module
Dim isAdmin As Boolean
If isAdmin = False Then
MsgBox "Design Changes are not allowed"
DoCmd.Save
DoCmd.CloseDatabase
Else

End If
End Sub
Set your flag with a login form. That way when they try to go into design mode it closes the database if they don't have admin rights of course if they have access to the code they can change it.

Yes you can hide tables.

[Trevor]

I can't make an mde, becaue I get an error, then researching the error in the help files it come out to be that because the DB is more then like 5 mb in size it errors out
and since im using a databasaform, I think I can order a table comble assending with the form onclose event but not sure how to do it because every time I try i get sql error Ie: select distict from table tech where tech.emp order assending:
error when run, sql syntax

and because it it a database view they can't change column lables.

[Trevor]

your mde alternitive looks like it would work, but how does the code then know who admin is and isn't?
wouldn't it just be easyer to use the security wizard for that?
becase in the grand sceem of things I have lookup tables that I don't wan't anybody to be able to edit directly but they may edit them only though a form, and I have the backend tables witch I want password proected so that only the person with the password can edit them

[Trevor]

On close Even of my form in datasheet view of table I have :
[Code]
Select * From CallLU.CallType Order By Asc
[Code]
To order the table in assending order and I get an error on my syntax, the table and colume are named correctly, their posision may be wrong in the statement.

[Carl A]

your mde alternitive looks like it would work, but how does the code then know who admin is and isn't?


Just use a login form and assign the value that is entered to the variable.
Of course you should have a password table with all users.

But with a MDE the users will not be allowed access to the design of the Form.

[Carl A]

On close Even of my form in datasheet view of table I have :

Select * From CallLU.CallType Order By Asc

Select * From CallLU.CallType Order By Field1 Asc;
Substitute Field1 with the field you want sorted

[Trevor]

thanx, did i forget to mark as solved?, I managed to under properties for table .orderby, [Tablename].[Fieldname] automaticly , will order the table by the selected field, since the user will be editing table from datasheet, this way will order the table by the set value even if in datasheet the values are out of order