Modules & VBA :: Deleting Record From Table Or Query

Feb 20, 2014

I am trying to delete a record from a table and when I pass the variable as a text value it works but when I pass as a number I am getting a mismatch error.

I have to use it as a number as I am doing other update code in my database and it is a number.

Code:
DoCmd.RunSQL "DELETE * FROM TblIssueData Where tblIssueData.SerNum = ' & Me.txtserNum & ';"

When I am using TblIssueData SerNum as a text variable in table it works but when I specify SerNum as a number in the table it gives me data mismatch error. I have to leave it as a number for other VBA code in my database. I believe it is just a syntax error but not sure where to go with it.

View Replies


ADVERTISEMENT

Modules & VBA :: Event Information - Deleting Record Creates A Blank Record

Sep 8, 2013

I have a form set to the table Client Information with a subform set to the table Event Information. Client Information has a one to many relationship to Event Information.

There is a button that deletes the current record in Client Information--also deleting the related records in Event Information--then closes the form. The code works fine but a blank record in Event Information is apparently being created before the form closes.

Here's the VBA that I'm using:

Private Sub CmdDelReturn_Click()
Dim CmdDelReturnMsg As String
CmdDelReturnMsg = MsgBox("Delete event & client then return to front?", vbYesNo + vbDefaultButton1, "Delete and Return?")

[Code] ....

It's not a big deal because the button won't be used often and I can manually go into the table to delete the blank record. But if there's a simple solution to prevent this that would be nice.

View 3 Replies View Related

Modules & VBA :: Deleting Only One Record?

Apr 3, 2014

How do I delete only one record in a table that has duplicate records using SQL?

Code:
DELETE tblTemp.Fruit, tblTemp.[Country of Origin], tblTemp.Qty, tblTemp.Date, tblTemp.Currency
FROM tblTemp
WHERE (((tblTemp.Fruit)='Fruit') AND ((tblTemp.[Country of Origin])='Country of Origin') AND ((tblTemp.Qty)='Qty') AND ((tblTemp.Date)='Date') AND ((tblTemp.Currency)='Currency'));

View 10 Replies View Related

Modules & VBA :: Reset Autonumber And Keep Sequence After Deleting Record

Feb 28, 2014

I have a Microsoft Access database with SQL Server backend with 10000 records in my table, I have a autonumber field and in my database the purpose of the autonumber field is just to keep the record sequence (not to treat as a unique identifier) and I don't want to use a number field to manually enter the sequence. Whenever the record is deleted or the user won't save the record, it put gaps in the record sequence, Is there any way to refresh the autonumbers automatically "After delete confirm" event or "After Insert/update" even.

View 13 Replies View Related

Modules & VBA :: Deleting Record And Then Delete Listed File?

Jan 7, 2015

What is the correct syntax that would delete a file when the record is deleted. The file's path is listed in a record field, MailLocation. Every time I try this code, I receive an error!

I've tried the below, and number of iterations, including calling the killfile differently (me![MailLocation], me.MailLocation).

Code:
Private Sub Form_AfterDelConfirm(Status As Integer)
Dim KillFile As String
KillFile = me!MailLocation
Kill KillFile
End Sub

View 6 Replies View Related

Error Deleting Record In Table

Mar 16, 2005

I have a corrupt record in a table that I am trying to delete. I keep getting the following error when I try and delete the record: "The search key was not found in any record".

Can anyone help me with this. All I am trying to do is delete this one record

Thanks!

View 1 Replies View Related

Deleting Child Table Record?

Jul 7, 2015

I have two tables in my access database with Parent Child relationship.Then i have below query based on which i have a datasheet form.

SELECT MOC_EMERGENCY.[MOC NUMBER], MOC_EMERGENCY.PERMANENT_MOC, MOC_DATA.TITLE
FROM MOC_DATA INNER JOIN MOC_EMERGENCY
ON MOC_DATA.[MOC NUMBER] = MOC_EMERGENCY.[MOC NUMBER]

When i delete a record in this form, my parent table record (in MOC_DATA table) is automatically deleted.

View 2 Replies View Related

Deleting A Specific Record From A Table Using A Value From A Form

Feb 23, 2014

I have a form where a user enter an ID and some details. This is saved in Table A.The ID is also in Table B - What I want is, once the user saves it in Table A-I want it to be deleted from Table B. Ultimately, Table A has all information and Table B will only have those IDs that haven't been entered in Table A. what is entered in Table A shouldn't be there in Table B.Is there a way to do this using a macro?I did find a VBA code online -

Private Sub Submit_Enter()
DoCmd.RunSQL "DELETE * FROM TBL_ClaimsToBeAssigned"
where [Claim ID] = Forms![FRM_PendsAssign]![Claim ID]
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM TBL_ClaimsToBeAssigned"
DoCmd.SetWarnings True
End Sub

View 2 Replies View Related

Modules & VBA :: Deleting Values From A Table Selected From A List Box

Mar 19, 2014

Im trying to delete values from a table based on the selected values of a listbox. The listbox values have a hidden column which related to the ID on the table to which I am trying to delete from (if that makes sense).

If I use the following code:

Code:
Set ctl = Me.Results_listbox
For Each varItem In ctl.ItemSelected
CurrentDb.Execute "Delete * FROM Table1 WHERE Table1.ID = " & ctl.ItemData(varItem)
Next varItem

I get an error stating Object doesn't support this property or method.

View 7 Replies View Related

Modules & VBA :: Format Spreadsheet To Import Into Table - Deleting Rows In Excel

Jun 25, 2014

I am trying to format a spreadsheet to import into a Table. To do this I need to delete the top 8 rows and then the 4 rows below the data I need, both areas contain header data. The 4 rows below the data I need are blank but formatted oddly and it is causing issues during import.

Code:
Sub ExcelFormat()
Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.worbooks.Open ("Z:DataTest.xlsx")
End Sub

View 11 Replies View Related

Deleting All Related Sub Records When Deleting The "main" Record.

Apr 12, 2008

I have two tables.

1. Persons (list of persons)
2. Job history (list of jobs)

each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.

how can i do this? vba or simple properties options?

- Roy

View 1 Replies View Related

Deleting Query Table With An Array

Apr 11, 2008

Hi, I have a problem deleting a specific row and field that my query have.

What I want to do is mainly delete a specific rows in the my Query table that has : Field 1, Field 2, Field 3, Field4

what i tried to do was doing is create and array that loop through the Query table and check in field 1 for a certain value and if that certain value appears I want to delete that row: for example

Dim IntRow As Integer
Dim IntCol As Integer
Dim varData As Variant


For IntCol = 0 To numFields Step 1
For IntRow = 0 To numRows Step 1
If (rst.EOF = False) Then
'Check Field 1 for value 47-72 to delete
Else
If (varData(0,IntRow) > 47 And varData(0,IntRow) < 72) Then
varData(IntCol, IntRow).Delete
End If
End If
Next IntRow
Next IntCol



In the example Above my syntax is not correct for :
If (varData(0,IntRow) > 47 And varData(0,IntRow) < 72)
and for varData(IntCol, IntRow).Delete.

can anyone help me to find the exact syntax i need to use to execute the program above?

THANK YOU!

View 2 Replies View Related

Modules & VBA :: Updating Record In Table / Query

Mar 4, 2014

I am trying to edit an existing record in my database.It goes to the correct record to update but will not update if "RecordUpdate" is specified as auto numbered.If the "RecordUpdate" is just specified as a number in the query it is fine.

CurrentDb.Execute "UPDATE QryIssueData " & _
" SET RecordUpdate =" & Me.txtRecordNum & _
", IssueDate ='" & Me.txtIssueDate & "'" & _
", Equipment ='" & Me.cboEquipment & "'" & _
", IssueDocNumber ='" & Me.txtIssue & "'" & _

[code]....

View 3 Replies View Related

Modules & VBA :: Query A Table To See If Record Exists With Particular Field Blank

Mar 14, 2014

I am trying to achieve the following - I want to query a table to see if a record exists with a particular field blank. If so, I would like to prompt the user for data.

In real world terms, when assigning an item to a user I would like to first make sure that the item is not already assigned to somebody else. I have 4 fields, UserName, Item, IssueFrom, IssueTo. So when an item is assigned to a user, the first 3 fields are populated and the IssueTo remains blank, until that item is assigned to somebody else.

At the minute I have nothing in place to prevent a user from assigning the same item to multiple users and having multiple records for the same item in the table.

View 7 Replies View Related

Modules & VBA :: Moving Record From One Table To Another - Incomplete Query Error

Sep 25, 2014

The below SQL is basically trying move a record from one table to another, the table a copies of one another and the AlphaName variable is a string.

I keep getting the incomplete query error, somethings missing but what.

Code:

strSQL = "INSERT INTO [Holding Table].* SELECT ([Import Table].* FROM [Import Table]
WHERE ([Import Table].[Alpha Name] = '" & AlphaName & "'));"

View 3 Replies View Related

Modules & VBA :: Automatically Repeat Last Record Under Certain Conditions In A Table / Query

Jul 15, 2014

I’d like to repeat the last not = 0 record under certain conditions, in a table or in a query.What I have:

Calendar_All Dates
Calendar dateMyValue
7/6/20140.00
7/7/2014108.94
7/8/2014107.71

[code]...

Basically, if Calendar date > Date(), if Calendar Date not Saturday or Sunday, weekday(Calendar date<>1 and <>7), AND Calendar Date not in (Holidays table).Then repeat the last not = 0 value of MyValue. I thought of changing the default value but the value is already 0, while default is null + I need to set the default value under certain conditions.

View 14 Replies View Related

Deleting Record Using Main/Subform While Maintaining Record Position

Jul 6, 2006

Hello All,

I have an ordering db that utilyzes the typical Form / Subform layout. I
would like to be able to delete a record from the Main form and maintain the
same postion relative to the other records. For example, if I am scrolling
through the records and want to delete record #45, After I delete it I would
like to be able to have record #44 visible to the user.

I am currently using (2) SQL statements to delete the records from the main
Order table and the Order Details table which are linked without Cascade
Delete Related Records being on.

I had set-up a recordset procedure to try and find the bookmark set before
deletion but the only way I can get the sub-form to not show a blank form (no
controls visible for the record just deleted and the #deleted in controls on
the main form) is to requery after the delete, which loses the bookmark. If
i place the ReQuery at the end it displays the records properly, (without the
blank record) but returns the record to the first record. I have tried
turning off any sorting references to OrderBy, etc. but it still returns to
the first record.

I have also tried using "DoCmd.RunCommand acCmdDeleteRecord" but it doesn't delete the record from both tables.



Dim rst As Recordset
Dim strSearchName As String

Me.AllowDeletions = True
Me.sfmOrderDetails.Form.AllowDeletions = True

Set rst = Me.RecordsetClone
strSearchName = Str(Me.sfmOrderDetails.Form.txt_Order_No.Value)
rst.FindFirst "Order_No = " & strSearchName

CurrentDb.Execute "DELETE Order_ID FROM tblOrderDetails WHERE Order_ID = " &
Order_ID,dbFailOnError
CurrentDb.Execute "DELETE Order_ID FROM tblOrders WHERE Order_ID = " &
Order_ID, dbFailOnError

Me.Requery
Me.sfmOrderDetails.Form.Requery

Me.Bookmark = rst.Bookmark
Set rst = Nothing

Thanks!

View 6 Replies View Related

Deleting A Record With VB

Jun 15, 2005

Hi

I have a form with a "Clear Form" button on to allow the user to delete the record they are working on without updating the database. A question window pops up asking if they are sure they want to delete the record, if they answer yes the form will clear and re-load without updating the database.

The OnClick Event Procedure will not allow me to delete the data, it always saves. I've tried
DoCmd.SetWarnings False
If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo, "LogBook 2002") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Else
DoCmd.SetWarnings True
End If
but I get the error "2046 - The command or action DeleteRecord isn't available now." Is there another way, a better way?

View 9 Replies View Related

Deleting A Record

Nov 22, 2006

Hi All,

I have a very simple name and address DB. What I would like to know is "What is the easiest way of finding a record using the switchboard and then deleting that record from the DB?"
I am an Access novice so nice easy replies would be appreciated.

Many Thanks
Keith:o

View 4 Replies View Related

Deleting Record After Use

May 6, 2007

Hi All

I have 2 tables "Customers" and "Work in Progress (WIP)"

The "WIP" table is result of the form "WIP" where I input all my data for each job completed. I use a lookup control "JobNo" to input the customer details from the table "Customers".

The objective is to call up the customer details into "WIP" add the job details, then on save, delete the record in the "Customers" table.

If this is possible, could you give me some ideas how.

Thanks in Advance.

Geoffk

View 5 Replies View Related

Deleting A Record

Nov 9, 2005

If you delete a record, the auto number is deleted but the following number remains unchanged e.g. 1 2 4 5 6 (Record 3 deleted). How can you rebuild a table so the auto number remain sequential. Using Access 2003

Dave

View 4 Replies View Related

Deleting A Record

Oct 12, 2006

I have a form that is normally populated with records from a table based on a query. I have a new requirement that would allow the form to be a "New Record" that they could enter information and save to the table that is mentioned above. My problem is unless they finish entering data, I would like this record to NOT be saved to the table. If they enter no data, the record is currently not saved, which is good. If they enter any data but do not finish, the table is saved, which is not good.
Is there a delete last record, or is there a better way to do this. The table is replicated so the ID's are
random.
Thanks

View 3 Replies View Related

Deleting Or Undoing A Record

Feb 11, 2008

Hi,

how do you cancel,undo a record made from a form? my scenario is this.
i open a form, a value is passed to the form automatically and a new record is created. i can then enter my info on the subform as needed. sometimes i dont have the right info or need the right to cancel. i have tried the following:

If (me.Dirty = True) Then
Me.undo
End if
DoCmd.Close acForm, me.Name

along with

CancelEvent
doCmd.close

both do not undo the record created in the main form which is what i need to do. i also thought of taking the record number via code and finding and delting it but that doesn't work either.

is there way to do this? im sure there is.

many thanks,

Nigel

View 1 Replies View Related

Deleting The Last Record Where Key Is Autonumber

Nov 3, 2004

I have a situation where I need to delete the last record I entered.

This is the sequence

1. The user selects that they want to add a new record (customer order)
2. I (using a macro) append a new record with some information filled out. This is shown in an input form (with lots of other info displayed) and I save the autonumber key to an invisible text field.
3. The user can then either SAVE or CANCEL. In the cancel I need to delete the record that was just added (see step 1.)

The problem is I need to close the form before I delete the record (thus removing the invisible text field containing the key to the newly created record). Once I close the form I don't know how to determine which record to delete (since I no longer know the Key) and I can't delete the record while the form is open since it is locked.

I am probably not doing this incorrectly but my issue is:

How do I delete (using SQL) the latest record an individual has created (which will always be the highest autonumber Key with the userID equal to this users's ID)?

Any ideas????

Thanks

View 1 Replies View Related

Refresh Subform After Deleting Record

Jan 10, 2006

Ok heres the situation, I have one form(frmBikes) that i use to filter the results in a subform(frmSubBikes). From the main form i have a button which opens another form(frmSell) which allows me to enter the sold price and when "cmdSell" is pressed many delete and append queries are run on the data stored in (frmSubBikes). When this button is pressd the deleted record has #deleted in each field where as it should be gone and i get this error message.
(The expression you entered refers to an object that is closed or does not exist)
What do i need to change? my minds been off this project for ages now ive got a brain block so help would be appreciated.

this is the code for "cmdSell"

If MsgBox("You are about to complte selling transaction: " & r & ". " & Chr(13) & " Is that correct ? ", vbQuestion + vbYesNo, " User Accounts") = vbYes Then

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


DoCmd.SetWarnings False
DoCmd.OpenQuery "appSoldBikes", acNormal, acEdit
DoCmd.OpenQuery "UpdSoldBikes", acNormal, acEdit
DoCmd.OpenQuery "DelHires", acNormal, acEdit
DoCmd.OpenQuery "DelRepairs", acNormal, acEdit
DoCmd.OpenQuery "DelSoldBikes", acNormal, acEdit
DoCmd.Close
Me![frmSubBikes].Form.Filter = Searchstr
Me![frmSubBikes].Form.FilterOn = True
End If
Exit_cmdSave_Click:
Exit Sub

Hope you can help,
Thanks Sci

View 4 Replies View Related

Deleting A Record Through A Button In A Form?

Mar 15, 2006

Help needed! (Apologies if this is a stupid question !)

I'm making a database for my OCR A2 coursework.

I have a table set up for tenants, and have created a form through which the user can delete a record from that table.

I have a look up combo box, to select the record (tenant) for deletion, and a command button - using the wizard that came up I have selected "delete record" - but it doesnt work! It doesn't delete the record i have selected.

Why Is this not happening ? It also doesn't bring up a warning box saying "you are about to delete one record" or something similar?

Thanks xzxx

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved