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!!!!
After a hiatus of a few years I find myself back writing Access applications, so I need to get caught up a bit.
First of all help me out with the whole ADO, DAO RunSQL thing. In the past whenever I needed to do any database operations I almost always used straight SQL with DoCmd.RunSQL, e.g.:
lsSQL = "INSERT INTO tblUsers CenterID, WorkerID..." DoCmd.RunSQL (lsSQL)
Most other coders seem to use some recordset approach, e.g:
The only time I ever used recordsets was when I needed to loop through each record and apply some logic that was too convoluted for SQL or at least too convoluted for me to write in SQL.
So, what's the advantage of using recordsets - whether ADO or DAO - over RunSQL?
I have a table with one entry- just a date that holds the last day of the month for a function that reminds the user to do something. I have some code that's supposed to change this entry via an update query- but it's not working- literally, no errors, just nothing. I tried running it in an actual query- but still nothing. The query should update the table endCurrMonth to the last day of the month. I've tried hardcoding various dates- still nothing DoCmd.RunSQL ("UPDATE tableMetrics SET tableMetrics.endCurrMonth = DateSerial(Year(Date()), Month(Date()) + 1, 0)") anyone know what I'm doing wrong? thanx
im creating a small database and am extremely new to vba, ive got stuck on running a select query from within the vba code itself, the book im currently reading while learning about this stuff suggests my code whould work but after looking into it i believe its wrong as ive read various threads saying you cannot use docmd.runsql with a select query, just wondering if anyone could help and throw some light on how to get this little bit of code working. below is the part of my code that falls over,
basicaly it should lookup the weight based on what the parcel type is and find the price, the parcel type is worked out earlier in my code and is held in strParcelType, theirs probably an easier way to do this as well but have'nt got that far in my book :)
intWeight = Me.txtWeight
Select Case intWeight Case 0 To 100 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[0-100g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 101 To 250 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[101-250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 251 To 500 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[251-500g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 501 To 750 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[501-750g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 751 To 1000 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[751-1000g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" Case 1001 To 1250 intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[1001-1250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))" End Select
Me.txtEstimate = strParcelType & intCost
many thanks to any gurus who can point me in the right direction or show me a easier way to do this
I have a VBA application i am writing. I am trying to use an sql statement with docmd.runsql to insert a value into my table that matches a particular record. (which will be the one open). For testing purposed i have stripped down my code.
When running the code it works, however it puts chinese symbols in all the other fields of the record and dose not insert the record into the CapExFileName Field. Then when you try and delete the record it comes up with no search index found.
Any idea on why it would be doing this ?
here is the code.
.......
Dim SQLstring As String
SQLstring = "UPDATE Assets SET Assets.CapExFileName = 'Test' WHERE Assets.Barcode = 'Testies1234'"
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.
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
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.
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?
I 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.
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.
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
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.
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.
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?
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.
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)
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.
I 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.
