Hi,
I would like to delete a record based on a user entered string. Here
are the
details. I have a txtFind textbox. A string is entered by the user.
Based on
that string value it displays that one record in the fields in the
frmDeleteCompon ent form. There is also a lstDelete listBox which
displays all
the record. But for some reason it does not delete that particular
record as
requested by the user. I have written the sequence I am following and
I am
hoping that someon is able to answer my question. I need help.
Thanks,
SA
'============== =============== ===
My code in frmDeleteCompon ent:
'============== =============== ===
Private Sub cmdDelete_Click ()
On Error GoTo Err_cmdDelete_C lick
Dim stDocName As String
Dim Msg, Style, Title, response
Msg = "Do you wish to Delete This Component?"
Style = vbYesNo + vbExclamation
Title = "Delete Component Confiramtion"
response = MsgBox(Msg, Style, Title)
If response = vbYes Then
stDocName = "macroDelet e"
DoCmd.RunMacro stDocName
End If
Exit_cmdDelete_ Click:
Exit Sub
Err_cmdDelete_C lick:
MsgBox Err.Description
Resume Exit_cmdDelete_ Click
End Sub
'************** *************** *********
Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Cli ck
DoCmd.GoToContr ol txtPartNumber.N ame
DoCmd.FindRecor d Me.txtFind.Valu e, acEntire, False, acSearchAll,
True,
acCurrent, True
Exit_cmdFind_Cl ick:
Exit Sub
Err_cmdFind_Cli ck:
MsgBox Err.Description
Resume Exit_cmdFind_Cl ick
End Sub
'============== =============== =======
Details of macroDelete:
Action:
1 SetWarnings
2 OpenQuery
3 RunMacro
4 Close
5 OpenForm
Details
----------
1 SetWarning: is set to No
2 OpenQuery: calls queryDelete ; View: datasheet; dataMode: Edit
Here's the sql statement:
DELETE tblParts.*, "PartNumber " AS Expr1
FROM tblParts
WHERE ((("PartNumber" )="txtFind")) ;
3 runMacro: runs another macro called MacroDeleteDisp lay with
repeatCount
set to
Action: GoToControl: lstDelete
RunCommand: Refresh
4 Close: Actually closes the frmDeleteCompon ent (I have included the
code
from that form above)
5 OpenForm: frmDeleteCompon ent; View:Form; Window Mode: Dialog 5 1922
Try something like this in a command button:
Private Sub Command1_Click( )
DoCmd.SetWarnin gs False
If MsgBox("Do you want to delete this record?",vbYesN o, "Delete Record
Yes/No") = vbYes Then
DoCmd.RunSql "Delete * From tblPars Where PartNumber = '" & txtFind &
"'"
End If
DoCmd.SetWarnin gs True
End If
Note: to make the code more readable - you may want to rename txtFind as
txtPartNumber. Also, when using parameters in a query as above - you
have to delimit the parameter with single quotes ' ' if it is a string.
In Jet sql -- data parameters are delimted with the # symbol. Number do
not require delimiting. I also temporarily disabled the default
warnings message and then re-enable it at the end of the procedure.
Otherwise you will get the default message everytime you delete
something:
1 Record is about to be deleted. Continue Yes/No?
You already have a custom message with the If MsgBox(...) statement.
Rich
*** Sent via Developersdex http://www.developersdex.com ***
Correction: Date Parameters #3/25/2007# require the # symbol as the
delimeter in Jet Sql. Most sql languages use the single quotes ' ' for
delimiting dates the same as strings. That I know, only Jet sql (Access
Jet sql) uses the # symbol for delimiting dates.
Rich
*** Sent via Developersdex http://www.developersdex.com ***
On May 19, 12:17*pm, Rich P <rpng...@aol.co mwrote:
Try something like this in a command button:
Private Sub Command1_Click( )
DoCmd.SetWarnin gs False
If MsgBox("Do you want to delete this record?",vbYesN o, "Delete Record
Yes/No") = vbYes Then
* DoCmd.RunSql "Delete * From tblPars Where PartNumber = '" & txtFind &
"'"
End If
DoCmd.SetWarnin gs True
End If
Note: to make the code more readable - you may want to rename txtFind as
txtPartNumber. *Also, when using parameters in a query as above - you
have to delimit the parameter with single quotes ' ' if it is a string.
In Jet sql -- data parameters are delimted with the # symbol. *Number do
not require delimiting. *I also temporarily disabled the default
warnings message and then re-enable it at the end of the procedure.
Otherwise you will get the default message everytime you delete
something:
1 Record is about to be deleted. *Continue Yes/No?
You already have a custom message with the If MsgBox(...) statement.
Rich
*** Sent via Developersdexht tp://www.developersd ex.com***
Well, I had one more question ---
how would I show all the records minus the deleted record in the
lstBox. I would like to refresh the lstBox with the right information.
Currently it is showing all the records.
Also, I would like to know what is wrong with the way I have set the
macro originally.
Thanks,
Sangeeta.
For general purpose operations - the built in macros are fairly ideal
because they don't require any maintenance. But if you need to
customize some operation - the macro is not the most ideal way to go
because they are not easy to customize. Thus, you would use VBA to
write custom procedures/functions.
As for "refreshing " your listbox with the current rows in your table -
just add this to the sample procedure:
Private Sub Command1_Click( )
DoCmd.SetWarnin gs False
If MsgBox("Do you want to delete this record?",vbYesN o, "Delete Record
Yes/No") = vbYes Then
DoCmd.RunSql "Delete * From tblPars Where PartNumber = '" & txtFind &
"'"
End If
listbox1.Rowsou rce = "Select fldx From tblParts"
listbox1.Requer y
Me.Requery
DoCmd.SetWarnin gs True
End If
Rich
*** Sent via Developersdex http://www.developersdex.com ***
On May 19, 2:15*pm, Rich P <rpng...@aol.co mwrote:
For general purpose operations - the built in macros are fairly ideal
because they don't require any maintenance. *But if you need to
customize some operation - the macro is not the most ideal way to go
because they are not easy to customize. *Thus, you would use VBA to
write custom procedures/functions. *
As for "refreshing " your listbox with the current rows in your table -
just add this to the sample procedure:
Private Sub Command1_Click( )
*DoCmd.SetWarni ngs False
*If MsgBox("Do you want to delete this record?",vbYesN o, "Delete Record
*Yes/No") = vbYes Then
* *DoCmd.RunSql "Delete * From tblPars Where PartNumber = '" & txtFind &
*"'"
*End If
*listbox1.Rowso urce = "Select fldx From tblParts"
*listbox1.Reque ry
*Me.Requery
*DoCmd.SetWarni ngs True
End If
Rich
*** Sent via Developersdexht tp://www.developersd ex.com***
Thank you for all your help. It works like magic.
SA This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then...
|
by: Sue |
last post by:
As soon as a character is entered in the first field of a new record, the number
of records shown in the navigation buttons increases by 1 and the new record
button becomes enabled. In the BeforeUpdate event of the first field, I check to
see if that value has been previously entered. If it has, I do a Cancel = True
and a Me.Undo. The number...
|
by: Megan |
last post by:
Hi everybody-
I inherited a database that somehow uses a bound combo box as a record
selector. Let me give you some background. The form is based on data
from 2 tables. The first table, Person, records info about a person.
The second table, Case, records information about a person's case,
almost like a human resources database.
The...
|
by: (PeteCresswell) |
last post by:
I've got apps where you *really* wouldn't want to delete certain items by
accident, but the users just have to have a "Delete" button.
My current strategies:
Plan A:
------------------------------------------------------------------------
1) Make the cmd button black and do not give it an accelerator key.
2) Issue two levels of...
|
by: Richard |
last post by:
A shipment of material is received.
The shipment contains several items.
Each item is assigned an internal tracking number for auditing
purposes and further processed.
The tracking number is mandatory, unique and non-zero, but _cannot_ be
obtained or assigned automatically by Access (due to business rules
).
Table A is the receive table...
| |
by: bluez |
last post by:
I want to design a webpage where user can search the data from the database and list out the related records. Each of the record got a delete button which allow user to delete the record.
Filename : search_student.php
<?php
include("../user_access/user_access_control.php");
include("../Database/database.php");
$searchStudentControl =...
|
by: Bobby |
last post by:
Hi
I am using Access 2003 mdb as a front end to an application which uses
SQL Server 2000 as the backend. The two are connected using ODBC.
On one particular table (the Stock table), I have a simple form which
updates stock. When the user presses the delete key, he gets the usual
warning about not being able to undo this command, and the...
|
by: jmarcrum |
last post by:
Hello all!
i have a “monitor-type” program, where my program recognizes my defined "commands”, and executes the given command, or produces an error message that says “unrecognized command”. After execution or error message, the program puts the cursor on the next line, and waits for the next command.
i have two commands that I CANNOT SEEM...
|
by: lhsiber |
last post by:
I am new to access and am having a problem with filtering. Here is a little bit of my setup: I have a main form that has a listbox so that users can choose one or many groups in which to display information about. The control name of the listbox is "grupos". The name of the field it is sorting is "Grupo_Nombre". From the user´s selection, they...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |