Any Method to Hide Records from a Table by DoCmd Method (1 Viewer)

[JithuAccess]

Hello guys,

Is there any method to hide the Records in a Table on a Button click Event?

When a user click on a command button a Table will open and user would be copying data from an Excel spreadsheet to Access Table. This is my Code:

DoCmd.OpenTable "tblTest", acViewNormal, acEdit
DoCmd.GoToRecord acDataTable, "tblTest", acNewRec

In this Table there are more than 11,000 Records. I want to hide all these Records from this table when a user click on the command button. So that users won't overwrite in an Existing Record (By mistake). Can we do this?

Thanks

 

[Isaac]

Never open a table for a user to interact with. Use forms (edit/enter/key) and code (batch/import/miscellaneous)

 

[JithuAccess]

Never open a table for a user to interact with. Use forms (edit/enter/key) and code (batch/import/miscellaneous)

Thanks a lot. I completely understand the Risk Factor. But there are more than 100 Entries per day and it may take 1 Full day to do this job when we are using Form

Thanks

 

[plog]

So you need something users can paste records into? Just create a query based on the table with criteria that no current records meet.

 

[Isaac]

There is no situation where it is appropriate to open a table for a user. That I can think of.

 

[JithuAccess]

So you need something users can paste records into? Just create a query based on the table with criteria that no current records meet.

Thanks a lot. This works Perfectly

 

[JithuAccess]

There is no situation where it is appropriate to open a table for a user. That I can think of.

Thank You. As plog suggested, I have created a query based on tblTest and then in the criteria of the all fields I put "Is Null". So the query will return just the blank fields. Users will copy and paste data from Excel in to this Query and this data will reflect in the tblTest Table.

Thanks a lot

 

[Gasman]

I would be importing the Excel workbooks directly.? Not asking users to copy and paste.
I've worked in an environment where I had to do that all day every day, and wondered about the quality of management.

Also less prones to errors. My colleagues were copying pand pasting postcodes into surnames, first names into surnames, you name it, if the DB allowed it (and it did) we had all manner of mistakes, and this was for producing cheques.!!!!

 

[JithuAccess]

I would be importing the Excel workbooks directly.? Not asking users to copy and paste.
I've worked in an environment where I had to do that all day every day, and wondered about the quality of management.

Also less prones to errors. My colleagues were copying pand pasting postcodes into surnames, first names into surnames, you name it, if the DB allowed it (and it did) we had all manner of mistakes, and this was for producing cheques.!!!!

Thanks a lot for your reply.

I tried DoCmd.TransferSpreadhseet method. But unfortunately it was not working.

Thank You

 

[Isaac]

Thank You. As plog suggested, I have created a query based on tblTest and then in the criteria of the all fields I put "Is Null". So the query will return just the blank fields. Users will copy and paste data from Excel in to this Query and this data will reflect in the tblTest Table.

Thanks a lot

Good job in not opening the table.
And, as post 8 and 2 state, you probably would want to do this in code, not make a user copy/paste anything.

 

[JithuAccess]

Good job in not opening the table.
And, as post 8 and 2 state, you probably would want to do this in code, not make a user copy/paste anything.

I guess there is some bug in my Database

I tried TransferSpreadsheet method and it was not working in my current Database (I have more than 44 Tables in this Database). But when I create a New Database and use DoCmd.Spreadsheet Method it worked.

Copying and pasting data from Excel to Access is my only option now.

Thank You