Problem Inserting A Record After A Deletion
Apr 25, 2006
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?
View Replies
ADVERTISEMENT
Apr 14, 2006
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
View 2 Replies
View Related
Jul 20, 2005
hiya
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
View 7 Replies
View Related
Apr 1, 2007
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!
View 1 Replies
View Related
Aug 7, 2007
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
View 5 Replies
View Related
Aug 19, 2013
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 ?
View 2 Replies
View Related
Oct 21, 2015
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 Related
Jul 16, 2015
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.
View 12 Replies
View Related
Mar 6, 2013
I have 2 tables.
1- customers table with 2 fields : customername,customerno
2-conversationstable with 4 fields: date,customername,customerno,details
The conversations table is for keeping memo of telephone conversations with the customers.
I built a simple form deriving from the conversations table. And added to it a combobox with 2 columns from customers table to select the customername an customerno for the form.
While deleting the record in the conversations table,through the form, I saw that the customername and customerno in the customers table of that particular customer record are deleted also. I made no links between both tables.
I wonder why that happened.
View 1 Replies
View Related
May 29, 2013
What I would like to do it take all 'RoleTitle' from RolesList, all 'CourseID' from courselist and show them as a pivot table/matrix with a checkbox that creates or deletes entries from the RoleRequirements table. So, if it exists in the RoleRequirements table then it should appear a checked, unchecking it would delete it from the table.
I am using access 2010 with a SQL server 2005 backend.
View 3 Replies
View Related
Aug 19, 2013
I have two forms both with separate tables
(1) Register and
(2) Payments.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database
Option Explicit
Private Sub AttachPaymentDetails()
Call PerformInsert("tblFinancialBudget", "frmFinancialBudget")
End Sub
[code]....
View 5 Replies
View Related
Nov 11, 2004
Hi,
I am working with a simple MS Access database. I am trying to add a new user with the following SQL statement:
Set connection = Server.CreateObject("ADODB.Connection")
connection.Open(strCon)
strSQL = "INSERT INTO UserTable (UserID, FirstName, LastName, Password) values ('" & userName & "', '" & userFName & "', '" & userLName & "', '" & userPWD & "')"
connection.execute(strSQL)
However when trying to run the code, IE gives me the following error:
Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/dvdcollection/registration.asp, line 60
Line 60 is the connection.execute(strSQL) line.
I have double check the statement but cannot see anything wrong with it.
I would appreciate it if someone can help me out on this one.
Thanks.
View 2 Replies
View Related
Oct 13, 2005
I have a field and in front of all records in this feild I would like to add a fixed text. e.g. if the record is XYZ i want to add abc in front to make it abcXYZ. Sorry newbie here.
ThaNKS
View 7 Replies
View Related
Mar 14, 2005
hi
i have attached a document with print screen of the form and combo box properties i am working on
the way it works is, when the user selects a code, the appropriate desciption, uoi and price is displayed. but for some reason the price combo value is not being written to the price field in the table. all the data on the form except for item desc and uoi is written to tbl_waste. the item info for the combo boxes comes from qry_items.
please help..i have been banging my head for days now...thanks
View 2 Replies
View Related
May 14, 2005
I really hope I can explain this right :o
When a customer makes a purchase I INSERT the transaction into the 'transactions' table, which I have no problems with. My problem stems from that I can't seem to get the 'OrderNumber' back from the 'transaction' table (OrderNumber is an AutoNumber by the way).
I have tried the following code -
TempOrder = DLookup("OrderNumber", "transactions", "customerID = '" & Forms![frmOrder]!TempID & "'")
"Forms![frmOrder]!TempID" is the value I use from another form (to see who the customer is). I want "TempOrder" to be the OrderNumber that is created in the transactions table.
Regards, James
View 6 Replies
View Related
Apr 9, 2013
I have a query with records,
I want a field named RECORD NUMBER beside it I want a consecutive number increment EG
RecordNumber - RecordDecription - RecordTitle
------------------------------------------
1 - TITLE001 - TITLE001
2 - TITLE002 - TITLE002
3 - TITLE003 - TITLE003
is there a formula i can use in my query to create this?
View 5 Replies
View Related
Feb 2, 2005
Hi,
I'm trying to insert a record into an Access database from some ASP Code.
I have:
...
strSQL = "INSERT INTO tblUser (UserID, Date, Comments) VALUES " & _
(" & nID & ", #" & Now() & "#, " & "''" & strComments & "');"
conn.Execute(strSQL)
...
I'd have thought this should work but it doesn't.I just get a "Syntax error in INSERT INTO statement".
My date field, called "Date" (just in case that's a problem!) in my database has no input mask defined, it's just a basic date/time field. I've tried replacing the '#' symbols with single quotes, and doesn't work either. I haven't had any luck finding a definitive example of how to do this, even though it's trivial surely. All the similar examples I've found talk about how to update a record set with a date, or how to set the system locale, etc.
Any ideas as to what I might be doing wrong?
View 1 Replies
View Related
Oct 14, 2005
Hi,
With a table in open view can a record be moved to a different position, i'm using access 2003. Also is there any way to sort besides ascending or descending?
I've got a lot of tables to make and if I forget a single column/field and have to add it later (like I have done all ready numerious times) I'm almost having to recreate the table again in order to have the field fit in the correct spot in the table. These tables are bound to combo boxes so its important every thing is grouped for easy choosing.
Thanks--All help will be very appreciated
View 6 Replies
View Related
Jul 2, 2014
The problem I have is, that I need to insert an apointment into de database but first I need to verify if there is a record or an apointment in that room that day the same hour, if that is so send a msgbox saying an apointment already exist in that room this day at this hour.
i tried using dlookup but it only works in one record using just one criteria
View 10 Replies
View Related
Dec 14, 2014
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
************************************************** ********
Public Function GetTargetType() As Variant
GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]")
End Function
************************************************** ********
Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.
************************************************** ********
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!target_group = GetTargetType()
'Forms!frmNmsConsumptionEntry!target_group = GetTargetType()
'[tblNmsConsumption.target_group] = GetTargetType()
End Sub
************************************************** ********
making sure I can insert this value once retrieved.
View 7 Replies
View Related
Jan 24, 2012
I have a main form where information for a particular part number or job is entered. Should the part number not yet exist, Access will let you know. I have a button within the main form that opens up the Form_EnterPartNumber form which allows you to enter the new part number.
What I would hope to be able to do would be to refresh (requery?) the main form after creating a new part number and closing the Form_EnterPartNumber form..........but, keep the main form on the current record instead of resetting back to record number 1.
Is this possible?
View 1 Replies
View Related
Aug 4, 2005
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!!! :)
View 3 Replies
View Related
Jan 17, 2005
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
View 2 Replies
View Related
May 18, 2005
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!!!!
View 14 Replies
View Related
Dec 5, 2007
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
View 1 Replies
View Related
Mar 22, 2008
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 Related
