Hello;
I am attempting to use a command button "Delete Current Assignment Record" on a form to delete a user-selected record from a table entitled "Registrati on" with Key field "Registrati on ID".
I am using a combo box named "Select Task" that displays info from three tables to enable the user to select a record. The form's data source is the table Registration. column 7 within the combo box = the Registration ID.
the on click event for the command button is:
Private Sub Delete_Current_ Assignment_Reco rd_Click()
Delete [Registration].* from Registration where [Registration].[Registration ID] = [select task].Column(7)
End Sub
I have also tried using the acCmdDelete run command, but am unsure how to make sure the table Registration is set prior to execution.
MS ACCESS 2003;
OS Windows 2000 svc pack 4
suggestions on how to make this work properly would be appreciated.
6 4922
Hi. To execute an SQL statement from VBA code you need to use either the DoCmd.RunSQL method, or the Execute method of the current database object. Trying to run the SQL the way you have done at present is certain to lead to the VBA compiler informing you there is a syntax error.
The normal way to approach this task is to build a SQL statement as a text string, then use RunSQL or Execute to execute the statement. Building it as a string is not difficult, but there are slight differences in approach if the WHERE clause is referring to a numeric value or a string, as string literals have to be enclosed in single quotes. Both versions are shown below, but make sure you use the correct one and delete the other in your final version: - Private Sub Delete_Current_Assignment_Record_Click()
-
Dim strSQL as String
-
strSQL = "Delete * from Registration where [Registration ID] = '" & Me![select task].Column(7) & "'" ' this one is the string version
-
strSQL = "Delete * from Registration where [Registration ID] = " & Me![select task].Column(7) ' this one is the numeric version
-
CurrentDb.Execute strSQL
-
End Sub
The Execute method does not generate any user warnings; RunSQL does. To use RunSQL instead of Execute the warnings have to be turned off then on again after execution as shown below: - DoCmd.SetWarnings False
-
DoCmd.RunSQL strSQL
-
DoCmd.SetWarnings True
Welcome to Bytes!
-Stewart
Thank You...I am trying the CurrentDb.Execu te version...but get an error
"too few parameters. expected 1." at the
CurrentDb.Execu te strSQL statement.
This caused a prompt for me to enter a registration ID, and upon entering deleted all the information in the table, vice the record that had the same registration ID...it does reflect the right registration ID in debug, before running the do command, but prompts for entry of the Registration ID and then deletes all info in the table... - Private Sub Delete_Current_Assignment_Record_Click()
-
Dim strSQL As String
-
strSQL = "Delete * from Registration where [Registration ID] = " & Me![select task].Column(7)
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL strSQL
-
DoCmd.SetWarnings True
I'm guessing that for some reason, when the "delete from" runs that the table Registration referenced in the code is not being addressed (current focus?) in any case, getting a prompt to key in the Registration ID when I run it."
any further suggestions are appreciated. - Private Sub Delete_Current_Assignment_Record_Click()
-
Dim strSQL As String
-
strSQL = "Delete * from Registration where [Registration ID] = " & Me![select task].Column(7)
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL strSQL
-
DoCmd.SetWarnings True
Hi again. When you use a RunSQL statement it 'does what it says on the tin'; there is no possibility of your SQL string not addressing table Registration - the SQL statement explicitly names that table and no other.
The fact that you are being asked for a value for Registration ID suggests that the underlying field in that table which you are wishing to match is not actually called 'Registration ID' but something else (e.g. RegistrationID) . Check the actual name of the field in table Registration, because it must be spelled exactly as you see it in table design view. Otherwise, the SQL will be referring to a non-existent field and the delete will not function at all (since no existing row will match the SQL Where clause if it is referring to a non-existent field).
-Stewart
At this point, I would use the tools Access supplies to check the SQL statement.
1. Stop the code at line 5, the DoCmd.RunSQL strSQL
2. In immediate window, display the contents of strSQL with ?strSQL - then copy entire string to clipboard
3. From Database window, select "Create query in Design view"
4. Close the ShowTable window without selecting anything
5. Click "SQL" on left end of query design toolbar (if not SQL, click downarrow next to designview icon
6. Clear "SELECT;"
7. Paste from clipboard your SQL statement.
8. Change DELETE to SELECT and click tableview icon on toolbar
9. Use designview to modify query until you get correct single record to display when you run query.
10. Switch to SQLview, and use this to compare with your string, still in immediate window. Replace "SELECT" with "DELETE"
OldBirdman
Sign in to post your reply or Sign up for a free account.
Similar topics |
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 primary key of the table, person, is his/ her social security
number. The primary key of the other...
|
by: Zlatko Matić |
last post by:
There is a form (single form) and a combobox. I want that current record of
the form is adjusted according to selected value in the combobox. Cuurrent
record should be the same as the value in the combobox.
What is the solution?
Thank you in advance.
|
by: ricky.agrawal |
last post by:
I'm really not sure how to go about this in Access. What I've created
is a table for each location. Those tables are identical in format but
different in information.
The tables are named after key points such as the store number and the
store ID. The fields of those tables are generic fields such as sales
per day, bank deposit and what not. The first field for each store
table is the date and I've set that as the primary key as one...
|
by: M.L. Abram |
last post by:
Hello all,
I do not know if this question is regarding table design, queries, or
programming. Below, I have given a table design using Access 2003.
Fields 'Product' and 'Color' are primary keys and 'Active'
is a Boolean data type. What I am trying to accomplish is having only
one record selected active out of the 'Product' field for each
type. If possible, have the user be able to do this task while viewing
the data within a...
|
by: AA Arens |
last post by:
Hi,
I have a database with 2 main forms. Contacts and companies. I share
the base with two others via LAN. On the companies form I have buttons
to navigate throught the records
(>400). We are mostly handling let say 5 companies. Every time I have
to navigate or choose the find record button to get the right company.
I am looking fo a feature to have listed in a combo list the last 5
visited records ("recently visited records").
| |
by: JHite |
last post by:
I am using Access 2003. My Form has 4 fields:
MailingListID, auto generated – primary key
LastName
FirstName
NickName
I used the Combo box wizard to set up look-up box on the form. I answered the wizards question by saying “find a record in the form”, I choose the first 3 fields for the combo box and made field 1 width = 0. The wizard then created the unbound control box and generated the code shown below
Private Sub...
|
by: anco85 |
last post by:
Hi. Im a total access and VB noob and require your much appreciated help.
I have a table that list all the vehicles in our company.
I created a form to view this information much easier with a combo box to select the record you are looking for. All this works wonderfull.
What I want to do now is create a command to delete a selected record. This aswell is easy enough.
What I need though, is some form of password protection to prevent...
|
by: Ricardonz |
last post by:
Hi,
I am using access 2007 but saving as 2003 file.
I have a form with 2 layers of sub-dataheets. When I delete a record by right clicking and selecting delete from the innermost datasheet the record disappear as if deleted, but when I open the form again the record reappears.
I'm sure that I am overlooking something fairly simple, but can't figure it out,
any help would be greatly appreciated
|
by: Kyosuke18 |
last post by:
Hi everyone,
I have a problem in deleting a data that is connected on the database.. I tried this code but it shows me an error: Run-time error '-2147217900(80040e14)': Syntax error in string in query expression 'ID=". Here is the code that i did:
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim ab As String
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My...
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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 captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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
| | |