Open Forms in Datasheet View (All Records) (1 Viewer)

billgyrotech

Banned
Local time
Yesterday, 19:52
Joined
Apr 18, 2013
Messages
258
Hello,


On my Main Form I have command buttons for Customers, Descriptions, and Part Numbers. I would like these to open to datasheet view for all records from the Main Form. I have these password protected ("admin") for the Administrator to edit if needed.


Is there a way to do this? I don't want to affect the Clerk form (password "clerk") for entering new Customers, new Descriptions, or new Part Numbers if needed on there. The 3 forms are set to data entry.


I am not sure if this can be done separately or not.



I appreciate any help,
Bill
 
Last edited:

Ranman256

Well-known member
Local time
Yesterday, 20:52
Joined
Apr 9, 2015
Messages
4,339
i have various forms. admin has all rights,
mgr has some rights (edit/add)
user has little rights: view only, no add,delete, etc

i capture user ID when the main form opens (users dont see it)
then look up his rights in the tUsers table.

Code:
Private Sub Form_Load()
txtUser = getUserID()
txtRights = dlookup("[rights]","tUsers","[userid]='" & txtUser & "'")
End Sub

Public Function getUserID() As String
getUserID = Environ("Username")
End Function

then the user clicks button on the menu, the logic opens THAT form.
Code:
sub btnVwCustomers()
   select case me.txtRights
       case "U"
         docmd.openform "fCustomersU"
       case "M"
         docmd.openform "fCustomersM"
       case "A"
         docmd.openform "fCustomersA"
   end select
end sub

or you could just set the restrictions on a single form, depending on the user rights.
 

billgyrotech

Banned
Local time
Yesterday, 19:52
Joined
Apr 18, 2013
Messages
258
Thanks for the reply but I was maybe not clear. From the main form I want those to open to datasheet view please showing all customers for instance.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:52
Joined
Feb 28, 2001
Messages
27,001
The issue of opening in datasheet view is possible. Are you looking at opening a query in datasheet view?

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openquery

This would do it and you can tell Access whether the view is read-only or not based on the third parameter of the .OpenQuery method. Click on the blue text for the 2nd and 3rd parameters in that article to see the options for each parameter. Note that doing this would open the datasheet according to the sizing at which that query was last saved in datasheet mode. So if you last opened the query in full-screen size, this will open in full-screen size.
 

billgyrotech

Banned
Local time
Yesterday, 19:52
Joined
Apr 18, 2013
Messages
258
Oh so I should create a query and then open that?


I hope that is correct. :confused:
 

billgyrotech

Banned
Local time
Yesterday, 19:52
Joined
Apr 18, 2013
Messages
258
Thanks Doc that works using the command buttons to open the queries I really appreciate the help.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:52
Joined
Feb 28, 2001
Messages
27,001
Billy, my correct answer to #5's question is "No, you shouldn't create a query or a form. You should create whatever works correctly for you." But I gave you the easiest answer so you could get things up and running faster.

Please note that there can be reasons why you would not want to use a query. For one, it is harder to filter the recordset if it ever becomes necessary to hide certain records from certain people based on their role. That becomes harder when opening a stored query using this method, and .OpenQuery requires a query name, not a query (SQL) string. For another, there is no easy error or range checking on queries opened in datasheet mode.

Down the road, if you need to consider filtering what everyone sees, you might need to consider opening a form in continuous mode (which looks similar to a datasheet but isn't one). Forms can have an extant .Recordsource query but can have filters added after the fact by diddling with the form's .Filter and .FilterOn properties. Forms can also do "sanity" checking to verify that a given input makes sense in context.

Therefore, if a query in datasheet mode is all that you need and you don't care much about error issues, you should be OK. If you want finer control later, you probably want some kind of form.
 

Users who are viewing this thread

Top Bottom