Hello, I'm using Access 2013
I have an unbound sub form that I'm populating with a SQL query in VBA by creating a querydef and passing it to the sub form source control. The sub form is shown in datasheet view. I had a user accidentally delete a record through the sub form, and I want to make sure that doesn't happen again. I tried setting the allow deletions property to "no" but that property doesn't show up in the sub form properties. It does show up in the main form but if I set that to "no" they can still delete a record through the sub form even though the main form is locked. I don't want to lock the sub form because people still need to edit values in the sub form, but I want to eliminate the chance of deleting any records with it.
Is there something I'm overlooking? I've tried searching the internet and have not had any luck finding anything.
Any help would be appreciated.
Thanks,
Rodney
You could try this for Line 30: - Me.Item_Draw_subform.Form.AllowDeletions = False
It sets the AllowDeletions on the SubForm instead of the Main Form.
10 7225 NeoPa 32,556
Expert Mod 16PB
Each form has an AllowDeletions property. If you set that to True for the form that you're using as a Sub-form then it should disallow deletions while allowing edits.
Certain properties get reset by other actions, like setting the RecordSource property and by opening the form with particular parameters, so check the value of the property once the form is running. If it gets itself reset then add some code to set it to True when it's run.
NeoPa thank you for the quick response.
Shouldn't I set AllowDeletions to False if I don't want them to delete fields? Also, I have a combo box selector that changes the RecordSource from table to table, I added a chunk of code to set the Allow Deletions property to false every time the RecordSourse changes, but to no avail if I select a record in the subform and press delete it get's deleted.
I've tried setting the AllowDeletions in the form to true, and false, but it didn't make a difference.
Not sure if this makes any difference but as for properties this is what I've got on the main form, and none of these properties show up on the sub form.
Data Entry - set to yes (I only want them do add new data in the main form.)
Allow Additions - set to yes
Allow Deletions - set to no
Allow Edits - set to yes
Allow Filters - set to no
Thanks,
Rodney
zmbd 5,501
Expert Mod 4TB
This is part of my code used to setup forms.
In this case, a supervisor for a related table to allow editing of a record; however, I don't want additions nor deletions directly to the child records as these need to be recorded/validated for auditing reasons: - Case "zusermodesupervisor"
-
With Me
-
.AllowAdditions = False
-
.AllowDeletions = False -
.AllowEdits = True
-
.Tag = 1
-
.z_ctrl_lbl_usermessage.Caption = "To add records use the controls below. You may Edit current records."
-
End With
ok, maybe I just need to show what I've got code wise -
Private Sub cboGetTables_AfterUpdate()
-
-
Dim strQuery As String
-
Dim qry As QueryDef
-
-
'check if MasterQ exist if it does delete it
-
For Each qry In CurrentDb.QueryDefs
-
If qry.Name = "MasterQ" Then CurrentDb.QueryDefs.Delete "MasterQ"
-
Next
-
-
'choose query depending on user perfered sort order
-
If Me.chbSortDesc Then
-
strQuery = "SELECT T.PartNum, T.PrevPartNum, T.Description, T.Initials, T.DateIssued, T.DescLen, T.ECN, T.EngPrefVend, T.EngPrefVendNum, T.EngPrefMfg, T.EngPrefMfgNum FROM [" & Me.cboGetTables.Column(0) & "] as T WHERE (((t.PartNum) Like IIf([Forms]![Item Draw]![txtfPartNum] <> """", [Forms]![Item Draw]![txtfPartNum], ""*"")) And ((t.Description) Like IIf([Forms]![Item Draw]![fDesc] <> """", [Forms]![Item Draw]![fDesc], ""*"")) And ((t.Initials) Like IIf([Forms]![Item Draw]![txtInitials] <> """", [Forms]![Item Draw]![txtInitials], ""*"")) And ((t.DateIssued) >= IIf([Forms]![Item Draw]![DateIssued1] <> """", [Forms]![Item Draw]![DateIssued1], #1/1/1980#))) ORDER BY T.[" & Left(Me.cboGetTables.Column(0), 2) & "PartID]DESC;"
-
Else
-
strQuery = "SELECT T.PartNum, T.PrevPartNum, T.Description, T.Initials, T.DateIssued, T.DescLen, T.ECN, T.EngPrefVend, T.EngPrefVendNum, T.EngPrefMfg, T.EngPrefMfgNum FROM [" & Me.cboGetTables.Column(0) & "] as T WHERE (((t.PartNum) Like IIf([Forms]![Item Draw]![txtfPartNum] <> """", [Forms]![Item Draw]![txtfPartNum], ""*"")) And ((t.Description) Like IIf([Forms]![Item Draw]![fDesc] <> """", [Forms]![Item Draw]![fDesc], ""*"")) And ((t.Initials) Like IIf([Forms]![Item Draw]![txtInitials] <> """", [Forms]![Item Draw]![txtInitials], ""*"")) And ((t.DateIssued) >= IIf([Forms]![Item Draw]![DateIssued1] <> """", [Forms]![Item Draw]![DateIssued1], #1/1/1980#))) ORDER BY T.[" & Left(Me.cboGetTables.Column(0), 2) & "PartID]Asc;"
-
End If
-
-
CurrentDb.CreateQueryDef "MasterQ", strQuery
-
-
'chage form record source to match what was selected in the combo box
-
On Error Resume Next
-
Forms![Item Draw].RecordSource = Me.cboGetTables.Column(0)
-
If Err Then
-
MsgBox "Sorry, that table doesn't exist in the database yet.", vbOKOnly, "Error, Table not found"
-
Exit Sub
-
End If
-
-
'set new query as source object in Subform, and prevend deletion of records
-
Me.Item_Draw_subform.SourceObject = "Query.MasterQ"
-
Me.AllowDeletions = False
-
-
End Sub
-
This is what I've got coded when the query changes in the sub form. Even though I've got the allow deletions set to false I can still delete records in the sub form if I select the left side and press delete.
Thanks, guys for you help.
zmbd 5,501
Expert Mod 4TB
it appears that this code is on the parent form?
As NeoPa said in his post, this appears to be an issue with the subform itself.
+ Open your subform directly in design view
+ What is the property in the subform set to when you do this?
You could try this for Line 30: - Me.Item_Draw_subform.Form.AllowDeletions = False
It sets the AllowDeletions on the SubForm instead of the Main Form.
Sweet, jforbes that worked!!!! :-)
Thanks NeoPa and zmbd I did open the subform in design view and made sure that the allow deletion property was set to "no", but I was still able to delete a record.
NeoPa 32,556
Expert Mod 16PB
Hi Rodney.
You're quite right. I should have said to set it to False rather than True. That said, it is the form you use as a subform that you want to set it for. Not the main form nor the SubForm object itself.
I suspect that the fix to your code would be to replace lines #28 through #30 with : - 'set new query as source object in Subform, and prevent deletion of records
-
With Me.Item_Draw_subform
-
.SourceObject = "Query.MasterQ"
-
.AllowDeletions = False
-
End With
That helps, I was just confused because the subform object didn't have the property. so I didn't think to physically change the subform property because I didn't think it existed.
Thanks again all,
zmbd 5,501
Expert Mod 4TB
Instead of setting that in the Parent form I set the values directly in the form I am using as the subform. Typically in the on_open event; thus, the "me" holds true whenever the form is opened - ether directly or as subform.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: bher2 |
last post by:
please help
i was trying to delete a specific record on a table but it still shows
the same record that should be deleted. i dont know how.
|
by: Alex |
last post by:
Hi,
I have tables that have default records that must not be deleted or
modified.
Is there an easy way to do this. Like setting a trigger on the Primary
key value ?
Alex
|
by: Hi5 |
last post by:
Hi,
I am designing a database for a client in which It has a client table
including the followings:
1-Table Client
2-Table lookupcategory
3-Table Ctegory
|
by: ricky.agrawal |
last post by:
I'm really not sure how to go about this in Access. What I've created
is a table for each location. Those tables are identical in format but
different in information.
The tables are named after...
|
by: dk4300 |
last post by:
Hi!!
I have a subform with a comments (memo) field, linked to a form.
On the Memo field:
Properties-Other-Enter Key Behavior = New Line in Field
On the subform:
Properties- Other- Cycle =...
|
by: Kalaine |
last post by:
MSAccess 2003:
Users tab through the data entry fields on a form, and accidently tab to the next record thereby erroneously inserting a record. What can I code in the Form_BeforeInsert to prevent...
|
by: pamela fluente |
last post by:
Is there an option to avoid the deletion of the entire site when doing
the deployment (Publish feature) ?
Why on the heart it has to erase *everything* by default? This seems
absurd!
If am I...
|
by: Jerry Maiapu |
last post by:
Can please someone help me out. I am a beginner (of VB)
I have a main form with a sub form created based on two tables having one to many relationships.
i want to prompt the users a message...
|
by: BobS |
last post by:
I have written a form whose function is to delete the record I have entered under the search criteria. The user enters the asset tag in the text box and presses the search cmd box . The record is...
|
by: zmbd |
last post by:
The question is based on post #3 in the following"
http://bytes.com/topic/access/answers/940234-discussion-advantages-subform-vs-filtering
In the filtred form users simply enter either the...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |