I have developed a database in Access which has user permissions implemented. These are set so that only Full Data Users have permission to delete information.
However, it has come to my attention that users can get around this by creating a query and then deleting records displayed in its results. I can't understand why this is possible, because the permissions should prevent this.
Can anyone help?
Thanks,
Gary
I created a form for project tracking. The form has a project name field in it that is a required field. The manager fills that out first, but may go back later to fill in additional data. I created a button to allow them to search for a project name, but they keep opening the form and typing in the project name they want to search for in the project name field of the first record that appears. This changes or deletes the project name for the record they typed in. Is there a way to prevent them from typing in that one field after a project name is initially entered?
View 5 Replies View RelatedIn C# this combo box is calling "DropDownList".
How to make in in Access?
Hello All,
I'm trying to limit the data entered into a specifc field, but also the data must be unique with respect to other fields.
i.e.
Two fields: System A & System B.
Data entered into System A, can not be entered into System B.
Anyway to prevent this from recurring?
Would I use a validation rule?
Thanks in advance
Hello again,
I have three questions this time 'round:
1. I'm in need of some way of preventing a user from opening form B unless he has entered information in form A.
Right now it is possible to open form B without entering data in form A (form A being the date/time info and form B being the case technical info) due to which a record is created for clientnr 0 (which should be a non existing client). Querying for clientnr 0 from time to time and deleting those records is easy enough, but who knows what kind of relevant data could be stored accidentally in a record that doesn't belong there.
This happens by the way, even though referential integrity is enforced throughout the dbase. Maybe I should say, because of that. I want to keep it that way of course to prevent orphans roaming around. But having orphans for clientnr. 0 is also not a good idea.
2. What is the wiser thing to do: keep all information concerning one case for one client (date/time info per case, techinfo per case, maybe even products sold info per case, payment info per case) in one table and have the data needed for each thing entered in seperate forms, or have all data split up into seperate tables like I have now. One for clients, one for dates and times, one for the technical info for that case etc.... ?
It is starting to seem a bit ehrm... useless to have all that data for one case floating around seperate tables. I don't know... it seems so much more complex (having to create multiple relations, multiple PK's per table etc.).
Where do I draw the line? For example the image attached (relationship2.jpg): now I have one PK in the table holding the clients (clientnr), two PK's in the table holding the case date/time info (clientnr and casenumberdatetimeinfo), three PK's in the table holding the techinfo (clientnr, casenumberdatetimeinfo and casetechinfonr). And what's next? Four PK's in the table holding the sold productsinfo? Five PK's in the table holding the invoice info? And what about the relations between those tables? Right now I can still comprehend.... but when I start thinking ahead, I'm starting to get dizzy. So some advice on this would really be highly appreciated.
3. Which build up of the relations between the tables in the dbase is better? The one in relationship.jpg or the one in relationship2.jpg?
I'm using the following code to ask the user whether to save changes to the data, don't save changes or Cancel the close and continue editing. I'm closing the form using the 'X' only.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim UserResp As Integer
UserResp = MsgBox("Record(s) have been added or changed." & vbCrLf & "Save the changes?", vbYesNoCancel)
Select Case UserResp
[Code] ....
The problem I'm having is when I press the 'Cancel' button in the message box. When I press 'Cancel' the form remains open (bPreventClose=True) which is what I want. But the changes to the data get undone even though I don't have Me.Undo in that case.
First, what's causing the undo?
Second, how do I prevent the data from undoing when I press 'Cancel.'
I went to add a record earlier and selected 'Cancel' because I wanted to change one small item. I lost all the data I had put in and had to start over.
I have some queries that pull data from one table and other queries that pull data from two related tables. In both cases, how do I prevent the data from being changed after the queries results are displayed? I know I could make a form and lock each field so it couldn't be changed, but I was hoping to avoid having to do that.
View 6 Replies View RelatedA form displays information on a construction site in various text boxes. I want to enable the user to change this information but not until a save button is pressed.
Now I have the problem that as soon as I change the value of a text box, the data in the database is updated.
Is there a way to prevent these updates but still get the text fields to be linked to the attributes of a table?
I have been consistently getting an error almost every month when I try to load some data that has been input into access, then I have it linked to an excel sheet so that whenever I refresh, the access data imports into excel and updates my pivots, charts, etc.
When my data entry ppl enter data, sometime they forget to enter a code, or something, and when they forget to enter that, it creates some kind of error in the query. The query still runs in access, but shows something similar to " #ERROR#" in the field IF I ever do find it in the access query. The issue is that I cant filter to find that error. I literally have to scan and scroll through thousands of lines of data to try and find this error. When I try to refresh the data in excel, the following error message pops up;
"Data could not be retrieved from the database. Check the database server or contact your database administrator. Make Sure the external database is available, and then try the operation again."
In past months I can usually find the #ERROR# by scrolling through access and finding it. Some months I have EXTREME trouble finding the error. It can take hours out of my work day. Is there any way to more easily identify which line these errors are in rather than scrolling through thousands of lines of data? Is there a way to still export the data to excel with the errors still in them?
In my Membership Database (Access 2010), I have a Table entitled [Foreigners] in which are stored the names of prospective members of the organisation and the name and ID of the Interest Group they are proposing to join.
When a formal Application to join is received their personal details are recorded using an Entry Form related to the main [Mail List] Table.
The current procedure then is to manually delete the entry from the [Foreigner] Table and finally to record their Interest Group data using another Entry Form entitled [GroupMembers]. This relies on an operator remembering to do the necessary.
It occurs to me that this process could be automated. I wondered whether it could be entirely automated or would be best effected with a button on the main Entry Form, such that on completion of data entry of personal details the button would be activated to do the necessary deletion and addition.
The [Foreigner] Table is an entirely stand-alone table, having no relationship with the other concerned tables. The only common factor will be the Member Name, which in the main table is a concatenation of First Name & Surname..
Hello Gurus,
I need to perform a massive deletion within Access and I want to find out if there is a simple piece of SQL that can be written to complete this task.
Example:
I have a table called GAB - one of the columns is called email addresses
Currently all the email addresses look like this: m14.dpitts@****.com
I need them to look like this: dpitts@****.com, so all I need to do is remove the 'm14.'
Is there something within Access that will accomplish this?? Kinda like substr in Oracle?
Any help would be great!!! :)
Can I add a delete button at the bottom of my form to allow me to delete a record or do i have to go to that table and delete it from there. if so how do i proceed with this.
thanks
I have a form with a sub-form and a sub-sub-form :p
If I try to delete the record being viewed in the sub-form using
With DoCmd
.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End With
Then I here a ding (like you do when you try to delete a record) but I see no record delete confirmation box come up, if I hit enter, it deletes. So its there, but I can't see it, it's not behind the form either.
Any ideas?
Cheers
can some one help.
i have two tables table1,table2 each with one same field Country.
In my RunSQl query, i want to delete all records in table1 and table2 simultaneously from one button with a certain country.
The problem is how can i do this from may be one RunSQL statement
i tried to use two deletion runSQl commands, but only one is executed. Actually the one that comes second.
If i put doevents in the middle of these statements, only the first is executed.
Here is my code:
Private Sub RemoveCtry_Click()
docmd.setwarnings false
Docmd.RunSQL "DELETE * FROM table1 WHERE (Country='" & USA & "')"
Doevents
Docmd.RunSQL "DELETE * FROM table2 WHERE (Country='" & USA & "')"
docmd.setwarnings true
End Sub
if I use One Statement:
Docmd.RunSQL "DELETE table1.Country,table2.Country FROM table1,table2 WHERE ((table1.Country='" & USA & "' ) AND (table2.Country=' " & USA & " '))"
I get an error that i have to specify the table to delete from!!!!
I have two tables which are connected with one to many relation, here are these tables: tblCars PK. intCarID txtModelName intManufacturerID intModelPrice tblManufacturerPK. intManufacturerID txtName txtAdress txtTelephoneNow, how do I achieve that when I delete a Car from the database, the Manufacturer is not deleted, because as you know one Manufacturer can create several model of cars, but when I delete Manufacturer all the cars from that Manufacturer are deleted? I know I need to enable cascade Delete but when I do that in each case when I delete a record from one database its relational record from the other table is deleted. How do I acomplish this?
View 3 Replies View RelatedI have a database with several forms, tables, queries etc. and it works fine. One of the users of the database accidentally deleted a record which was displayed by a query. I have selected AllowDelections = No in all object properties. Despite this, the record got deleted. Is there a way to prevent such deletions? Kindly help.
View 1 Replies View Relatedhiya
i have a subform which won't allow me to delete a record unless i'm in the datasheet view. is there anyway to change this. the properties have been set to allow deletions.
thanks
trish
Hey all-
I'm trying to create a simple 1 field temp table to populate a combo box with the name of the current user and the word "Company." However, after the user closes the form (or as soon as the Temp table is no longer necessary) I would like to delete the table. I can create the table, the fields, add the data, and populate the combo box just fine, but I'm having problems deleting the table after I'm done. I keep getting the error:
Run-Time Error 3211: The database engine could not lock table 'Temp' because it is already in use by another person or process.
here's my code:
Code:Option Compare DatabaseDim dbRoofing As DAO.Database Private Sub Form_Close()dbRoofing.TableDefs.Delete "Temp" 'where i get caught when i close the formEnd Sub Private Sub Form_Open(Cancel As Integer)Set dbRoofing = CurrentDb Dim tblTemp As TableDefDim rcdTemp As DAO.Recordset Set tblTemp = dbRoofing.CreateTableDef("Temp")tblTemp.Fields.Append tblTemp.CreateField("Owner", dbText)dbRoofing.TableDefs.Append tblTemp Set rcdTemp = dbRoofing.OpenRecordset("Temp", dbOpenDynaset)With rcdTemp.AddNew!Owner = CurrentUser.Update.AddNew!Owner = "Company".Update.CloseEnd WithOwner.RowSource = "SELECT Temp.Owner FROM Temp"End Sub
thanks guys
Hi,
The user wants all of the records in the database to have continuous numbering. I have used AutoNumber in the past but if you delete a record, it produces gaps in the numbering.
I was wondering if there is a way to number the records so they are always numbered continuously (1,2,3,etc.) even if some record is deleted.
I have tried using the following:
Set db = CurrentDb()
Set recordlist = db.Openrecordset("SELECT Max(tblContactHistory.ItemID) AS MaxID FROM tblContactHistory")
ItemID = (recordlist!MaxID + 1)
recordlist.Close
Set recordlist = Nothing
but I still cannot get the records to have continuous numbering after a record is deleted.
Any helped would be appreciated.
Thanks!
I want to create a custom sequence when deleting a record but I'm having a problem.
What I want to do is prompt the user for the reason he/she is deleting the record and then archive this so it can be viewed in a log at a later time. This extra step is not as easy as I thought it would be.
What I did was put some coding into the On Delete event of the form. The code opens a form with a text box. The user can enter the reason for the delete in the text box and click OK or click cancel to abort the deletion.
My problem here is opening another form from the On Delete event. As soon as the form is open, the code just passes to the next step of the sequence and deletes the record.
There are 2 approaches that I've thought of but I'm not happy with either.
Approach 1:
In the On Delete event, set Cancel to True. That way the deletion is not handled by the delete sequence but rather by the new form that opens. The problem is that deleting a record from another form is not that simple. What I've tried is to execute a SQL statement that grabs the ID of the record from the open form and delete it. The problem is that although the record is deleted, all the fields on the form say #Deleted in them which is not that pretty. (Maybe there's some other way to do the deletion without this problem that I'm not aware of...?)
Approach 2:
Use an Inputbox. Unlike a custom form, the Inputbox delays execution of the code so the code can determine from the results (i.e. should it cancel the delete if the user clicked Cancel). The problem with Inputboxes is that they are not very flexible in the layout, they look pretty "packaged" (i.e. they look to me like the programmer didn't bother creating a means to enter a value). There's no way to place the buttons where you want or have it do better validation (e.g. I want the FORM to check that there's at least a few words describing why the user is deleting the invoice rather than have the form with the record get the input message passed back and then give an error and then pop open the Inputbox again)
I think that especially when you're creating an application with anything financial, you'll want to log a deleted invoice including the date, amount, possibly the client's name, invoice number, reason for deletion and the login name of the person doing the deletion. All of these are easy to record other than the reason.
Help would be much appreciated.
SHADOW
I have a form and a subform. There are a number of buttons on the form, allowing me to add, update, delete, etc. records from the subform. The process is that I select a record on the subform, its details are displayed on the form, then clicking on <Delete>, for example, removes it.
Each of these processes work fine on their own and also if carried out one after the other, for the most part. The one problem I'm finding is when I delete a record.
Firstly, the subform appears to refresh and the record is no longer shown. However, if I then try to select another row from the subform, I get a message saying 'record is deleted'. I click on <OK> to remove this message and I can then select the same row or another row without any problems.
Secondly, if I now try to add a new record (the code behind the button is just 'DoCmd.GoToRecord , , acNewRec'), I get a message saying that I can't go to the selected record.
If I close and reopen the form between each activity, all works fine, suggesting that something isn't refreshing properly. I've tried putting 'Me.refresh' and 'Me.requery' immediately after running the deletion, but to no effect.
Any suggestions?
I'm having trouble executing a SQL command in VB... I want it to find the the value of the input box in TBL-Purchases and Delete all related values. Here's my code.... I get an error on the line I've highlighted in green...
Private Sub Command31_Click()
Dim Message, Title, Default, MyValue1, MyValue2
Title = "Sell Stocks"
Default = ""
MyValue1 = InputBox("Which stock ticker name would you like to sell?")
MyValue1 = UCase(MyValue1)
[Code] .....
I'm using this stardard piece of code on a button that deletes a record from a simple continuous contact form.
Code:
If MsgBox("Do You Want To Delete This Record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
End If
If the form is called "CONTACTS" with a field called "Contact_Name", what code do I use to have the relevant Contact_Name appear in the MsgBox to be sure that I'm deleting the correct record ...... something like this .....
Do You Want To Delete The Record Called Paul Quinn ?
I have a table whose key is two fields. I am having no luck in selecting a given record from a form and deleting it. No problem in a table with a single field key.
View 14 Replies View RelatedI created an option group with 4 options when designing a form. Now I need to add 3 more options in same group, but I can not find any way of doing so. Similaralily I need to delete one option in another form.
Please help
We need to reset the autonumber after the last record is deleted.
For example:
2006
2007
2008
if 2008 gets deleted, we want the autonumber to reset back to 2008 rather than move to 2009.
Is this possible? This is the code we are using but cannot get the string to recognize the variable. If the variable is replaced with a number, it works, but it defeats the purpose of adding the plus 1 to the last autonumber.
Code:
Private Sub Command0_Click()
Dim RLMax As Integer
Dim Statement As String
RLMax = DMax("[id]", "Table1")
RLMax = RLMax + 1
strSQL = "Alter table table1 Alter Column Id Autoincrement(RLMax,1)"
DoCmd.RunSQL strSQL
End Sub
I realize we don't want to depend on the autonumber for anything other than a row identifier, but the table is setup that it is important for the rows to be sequential if the last record is deleted. Only if the last record is deleted.