redneckfiveo
New member
- Local time
- Yesterday, 19:14
- Joined
- Mar 12, 2014
- Messages
- 7
Hello. First off, wow! Thank you all for everything on Access World Forums. I have built an entire db based off your knowledge.
Issue: I have several update queries that run from an admin form to make changes to my main tables. I want to implement a dbdenyread (or something to that effect) so users cannot make changes to tables until my code is finished.
This is a multi-user split db, normalized (as best as I could), relational db.
Database:
TCS
Tables in question:
tblComplaint (Main Table)
tblAreaStreet (reference table)
Forms:
frmAdminMain (Where the code resides)
Listbox:
lstStreet (based on tblAreaStreet)
"frmAdminMain" has a textbox ("txtStreetChange") that allows the admin to make changes to the selected data on listbox "lstStreet". The admin must click save, which then runs 3 queries. The first two change all records in tblComplaint which contain the old data to match the new data. The third query changes the tblAreaStreet to match the new data in the textbox.
The code I have below works great to make the needed changes.
I am pretty sure I need to restrict access to the tables while the changes are being made. In the off chance someone were to access a record while the queries ran I think it would cause errors.
I searched and searched for the format to implement dbdenyread and now I'm asking for help. Thanks in advance!!!
Issue: I have several update queries that run from an admin form to make changes to my main tables. I want to implement a dbdenyread (or something to that effect) so users cannot make changes to tables until my code is finished.
This is a multi-user split db, normalized (as best as I could), relational db.
Database:
TCS
Tables in question:
tblComplaint (Main Table)
tblAreaStreet (reference table)
Forms:
frmAdminMain (Where the code resides)
Listbox:
lstStreet (based on tblAreaStreet)
"frmAdminMain" has a textbox ("txtStreetChange") that allows the admin to make changes to the selected data on listbox "lstStreet". The admin must click save, which then runs 3 queries. The first two change all records in tblComplaint which contain the old data to match the new data. The third query changes the tblAreaStreet to match the new data in the textbox.
The code I have below works great to make the needed changes.
I am pretty sure I need to restrict access to the tables while the changes are being made. In the off chance someone were to access a record while the queries ran I think it would cause errors.
I searched and searched for the format to implement dbdenyread and now I'm asking for help. Thanks in advance!!!
Code:
Private Sub btnSaveEdit_Click()
On Error GoTo btnSaveEdit_Click_Err
Dim strsqlMain As String
Dim strsqlSecond As String
Dim strsql As String
DoCmd.SetWarnings False
strsqlMain = ("UPDATE tblComplaint SET PrimaryStreet = '" & Me.txtStreetChange.Value & "'WHERE (PrimaryStreet = '" & Me.lstStreet & "');")
strsqlSecond = ("UPDATE tblComplaint SET CrossStreet = '" & Me.txtStreetChange.Value & "'WHERE (CrossStreet = '" & Me.lstStreet & "');")
strsql = ("UPDATE tblAreaStreet SET Street = '" & Me.txtStreetChange.Value & "'WHERE (Street = '" & Me.lstStreet & "');")
DoCmd.RunSQL strsqlMain
DoCmd.RunSQL strsqlSecond
DoCmd.RunSQL strsql
Me.lstStreet.Requery
btnSaveEdit_Click_Exit:
On Error Resume Next
DoCmd.SetWarnings True
Exit Sub
btnSaveEdit_Click_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume btnSaveEdit_Click_Exit
End Sub
Last edited: