prohibit save records before click save button in multiSubforms in one form (1 Viewer)

ahmedjamalaboelez

Ahmed J. Aboelez
Local time
Today, 12:02
Joined
Feb 25, 2015
Messages
79
good day every one , :coffee:
1st , i really like the new cool design of the forums,and i really appreciate your help in the following discussionšŸ„°
subject :
- for each data table in my database i use 2 tables to prohibit automatic save , Ex. one is (users) and the second is (users-mod).
- both of them has the same structure ( Userid-Name-Password) ,without any assigned primary key or mandatory fields, so i guarantee no save errors messages appear except what i set with VBA Criteria.
- the first table is set for data only and has no connection with any other database object ,and the second table is just temporary table attached to the form , and i use SQL statements to select , update , insert between the two tables , and of course some VBA codes to set entry criteria in the form.
- this way take too much time and code write , but of course not increase database object and avoid automatically save in the multiSubforms in the same form.
- so i create a table which contain all SQL statement of ( select , update , pending , delete ) queries and i call it by D lookup,

Here is the question ::)
- is using this way will reduce the efficiency of database data processing and speed?
- is there is any way to automate the process of select , update , pending and delete statements specially the two tables has the same structure , and i have many tables in the database , which impossible to make all queries for all tables in the database, i need the idea of module which can create those SQL Statements with just the name of table and the name of process ( select, update, delete) ?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:02
Joined
Feb 19, 2013
Messages
16,553
is using this way will reduce the efficiency of database data processing and speed?
probably not anything really noticeable on form updates
is there is any way to automate the process
use a public function in a general module and pass as parameters the values required - suggest destination table and form name or object and the action required. You should then be able to dynamically create a sql statement to execute.

if the module knows the form, you can get the form recordset which you can loop through the fields to determine each field name to build your sql

You do leave yourself open to other issues - no primary key, multiple users changing records at the same time, etc. Creating temporary tables will lead to bloat as they are created/deleted

You might want to consider using an ADO disconnected recordset to populate your forms rather than a temporary table.

I've not tried it but another alternative is to use transactions. here is a link for how to use with forms https://stackoverflow.com/questions/1117155/can-i-wrap-an-access-form-with-a-transaction
 

Users who are viewing this thread

Top Bottom