I'm back with Access again for a short time (hurray) where I'm picking up a problem for one of Col's dbs. And I'm really stuck! :(
It's a car parking db and you go into the record from a form with a list box on.
There is a delete button, which has the usual wizard code behind it:
On Error GoTo Err_Delete_Click
Dim rsp As Variant
rsp = MsgBox("You will delete the entire record and associated permit and car details. Are you sure you want to do this?", vbQuestion + vbYesNo, "Warning!!!")
Basically all this did was bring up the msgbox, bleep and not do anything else. When I debugged, it appeared to go through the code no problem.
I looked at an older db of mine and changed a few bits:
added docmd.setwarnings false and true before and after code
changed the Variant to Integer
Added docmd.close and requery of the form that the listbox is on, after the DoCmd. .... ... acVer70.
This closes the form, but doesn't appear to delete or requery, and when you click on the list box, there's now an error message, "No Current Record".
Anyone know what I'm doing wrong please.
Many thanks - Ally
I'm going now, but will be back on Monday.
Why does this sql not work? It brings up the records which satisfy the criteria if one clicks on the view button. Yet does not delete the records! No warning signs appear at all
DELETE pupils.*, pupils.[Pupil ID] FROM pupils WHERE (((pupils.[Pupil ID])=[Forms]![Parents]![cmboPupilsDelete])); Any ideas? Thanks
I have a problem with a delete query i am trying to set up and hopefully one of you experts can help me out.
I have a table called [PMI] and a table called [Referrals]
The tables have a one to many relationship and are linked by the primary key field Patient_No.
I want to delete all records in [PMI] that have no records in [Referrals] and this is what i'm having problems with. I have created a select query where i can return the records i want to delete but its the removing of them from PMI that i cannot figure out.
Hope that makes sense, all help is greatly appreciated.
when I try and run the following query it says "please specify the table", and for some reason in the clause I cannot change the expression WHERE to FROM. Any ideas?
This is a copy of the SQL statement used to update records from the tblContentImport Table to the tblContent Table.
UPDATE tblContent INNER JOIN tblConentImport ON (tblContent.Code = tblConentImport.Code) AND (tblContent.Type = tblConentImport.Type) AND (tblContent.Title = tblConentImport.Title) AND (tblContent.LoginName = tblConentImport.LoginName) SET tblContent.[Date Assigned] = [tblconentimport].[Date Assigned], tblContent.[Date Started] = [tblconentimport].[Date Started], tblContent.[Last Accessed] = [tblconentimport].[Last Accessed], tblContent.[Date Completed] = [tblconentimport].[Date Completed], tblContent.[Time Spent (min)] = [tblconentimport].[Time Spent (min)], tblContent.Score = [tblconentimport].[Score], tblContent.Result = [tblconentimport].[Result] WHERE (((tblContent.LoginName)=[tblconentimport].[Loginname]) AND ((tblContent.Title)=[tblconentimport].[Title]) AND ((tblContent.Type)=[tblconentimport].[Type]) AND ((tblContent.Code)=[tblconentimport].[Code]));
Now I want to Delete these same records from the ContentImport Table. I change this statement to start with the word DELETE and Access want me to identify which table to delete the records from. What is wrong with my statement. Please help?
I've been creating a simple macro which will append data and then delete data from a table.The criteria for the queries is found in a form, and this is used as a paramter.However, when i run this all as a macro...the append and delete queries somehow can't read the criteria in the form. Rather...i have to type in the criteria again...and then it works.I have attached a copy of my DB.
1. Go to frmArchiveStock 2. Select a stock from the combo box (this the criteria for the append and delete queries) 3. Click the button to run macro mcrStockArchive.
How can i get the queries to pick up the value entered in the form to act as a criteria when they run?
All I am trying to do is insert to have a form with a "Delete Record" button on it. The problem is I don't want anyone to be able to delete a record, I would like someone to have to insert a password to confirm the delete.
I have a query based on two tables, joined on one field (this field is the primary key in Table A. Table B doesn't have a primary key). When working inside this query, it gives me the option to delete records. However, when I try, the row disappears from the query but then if I save and close and either re-open table A or the resulting query, that record is back. It doesn't actually delete. If I go to table A directly and delete it, it's gone for good.
How can I make it so that when I delete the record inside the query it actually deletes? And if that's not an option, can I make it so that it doesn't seem like I can delete records inside the query so that people don't make the mistake?
I have two simple tables. I want to delete the records from Table1 that are on Table2. I've created a select query that gives me what I want but when I change to a delete query, I get this message: "Unable to delete from specified tables"
I think my problem has something to do with security but I can't figure out what to change.
I have a program that runs under access 2007 that I use at my work. We will soon be updating to MS office 2010 and the program will not work now because a calender file .ocx was removed from access 2010. Is there a way to get the 2007 .ocx file to work in access 2010?The program I am using is a relatively simple stand-alone and unsupported app that we use to request patient arrival and departure from various radiology tests inside a hospital. No reports are made from the app other than the number of patient transports for the day.
The app is placed on a common drive accessed from any pc in the hospital. No special permissions are required. But our app does use the calendar, time and date functions in access 2007. When I tried the app on a pc with access 2010, it basically says it (access) cannot open the app because a .ocx file is not present.Is there a way to make the access 2010 calendar file work in access 2007?
I have an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are Organizations Entry Form and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization.On my Organizations EntryForm, I created a command button to duplicate a record using the wizard. It works fine.
I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?
I have 2 computers (computer 1 with Office XP and computer 2 with Office XP SP1). An MDE file created from an MDB file on computer 1 will not work on computer 2 (run time error), but an MDE file created on computer 2 also does not work on computer 1 (error message was "database corrupted").
Does anyone know why and what I can do to make the MDE work on both machines?
Hi. I have got a small database, I have split it into front and back end. I then try to make an MDE out of the front end and after going to tools>db utilities>make MDE file, i got the save as box open up and i pressd save to my desktop,the box disappears and the screen is just blank(like there is no database file has been open,at the bottom of Access it says Make MDE/ADE. And it just crashes.. it doesnt come up with any errors. If anyone has any information pleeeeeaaaseee help.......
My Combo Box works and displays new entries in text boxes in my form. The selection appears in every record. I would like the selection to be stored only in the displayed record and not for each and every record. Any ideas?
My combo box works on an existing query( Qry1). I would like the data from my query (Qry1) to be displayed in the record (in Form1) which I am viewing. When I go onto a new record (IN Form1) I would like to make a new selection from my query (Qry1) and then store these details in the new record in Form1.
This seems so simple, but, being a novice, I'm scratching my head.
I'm using an unbound text box in the form footer to sum the value of a column in the form ( =Sum([LineTotal]) ). [LineTotal] evaluates correctly, yet the sum is always #ERROR. Is there something I am missing, or am I correct in thinking that this should work?
I open a form that is bound to a linked SQL table. On that form a have a list box that shows a series of records based on a query. The listbox DOES contain the PK field "Record_date" in its query.
I am expecting that if I click on a given record in the list box the form would change to that matching record. What am I doing wrong ?
List box code:
Private Sub List212_AfterUpdate()
' Find the record that matches the control. Dim rst As Object Set rst = Me.Recordset.Clone rst.FindFirst "[Record_date] = #" & Format(Me![List212], "mm/dd/yyyy") & "#" If Not rst.EOF Then Me.Bookmark = rst.Bookmark
End Sub
If I change the form record and click the list box, its always takes me back to the 1st record - not the matching record selected in the form.
Hy,i've got three tables: products: product_id,product_name months: month_id,name_of_month calculations: calculations_id,month_id,amount
Also have this query:
SELECT DISTINCTROW products.product_name, Sum(calculations.amount) AS [Sum Of amount] FROM products INNER JOIN calculations ON products.product_id=calculations.product_id WHERE calculations.month_id in (forms!frmMyForm!txtMyTextbox.value) GROUP BY products.product_name;
My problem lies in this part of query : WHERE calculations.month_id in (forms!frmMyForm!txtMyTextbox.value).
I've got problem when i want to get values from my textbox on my form. If i instead (forms!frmMyForm!txtMyTextbox.value) put (1,2) my query works fine,but when i wanna get this same values from textbox on my form it returns me an error. I have also noticed one more thing:If i put in my textbox just value 1 it works fine,but if i use more values separeted by comma instead of result it returns me an error. Why is this,and how to avoide this problem? Thanks!
If [Forms]![UpdatedFullInventory]![PAM_ID] = [Tables]![UpdatedPropPassInventory]![PAM_ID] Then MsgBox "A property pass for PAM ID " & PAM_ID & " has already been created. Please search the property pass database for more information!", vbOKOnly, "Error!" End If
My debug message says
Microsoft can not find the field 'l' referred to in your expression.
I have an issue where I need all but 2 users out of my DB on Wednesday morning for billing reasons.
I thought of writting a batch file that would delete the frontend DB from all computers from 8:00 - 10:00 every wed morning. However if a user comes in before 8:00 and have the DB open then it will not delete the front end from their desktop.
Can anyone think of a way to keep users out of a system from 8-10am every Wed?
have searched forum for this but not getting what i want (or maybe i just don't understand very well) but here's my question anyway:
the table is related to several other tables and the cascade delete property is on.
on a master form (with subfrm for related tbls) i want cmdbutton to do this:
when the user DELETE a record, the deleted record (and! it's subdata) must be removed from the current table(s) but copied(?) to another table(s) which won't be accessed by the user, so that i can keep track of deleted records.
do i have to setup DUPLICATE tables (and relate them again) to capture deleted records ? and then have a qry that will cut(?) paste records that gets deleted?
i think i need only one qry that will do all this, but not sure how ?
I have a table with several fields and I am trying to delete one of the fields. It tells me the field in related to one or more tables and to use Database Relationships to delelet the relation. I have taken every table out of the Relation Database, Deleted every query, form that ever related to the table and still can't delete the field from the table. I have done Database repair, delete the entire table and rebuilt it and I still have the problem. Any help out there? Thanks.
ok ive made myself a DELETE query, that should Delete all records in a table that are 'TRUE'.... however i cant add it to a command button in my form...it wont appear in my 'command button wizard' as part of my queries, its not there...
I have two tables. table A and table B. Both tables have a field called CASE. What I want to do is delete from table A the CASE where table A and table B CASE is the same. example:
Table A Table B Case Case 1 2 2 4 3 4 5 I want the results of the query to delete CASE 2 & 4 from Table A
