Query Help

[Guest]

I have a button on a form that has a macro attached to its ON CLICK property.
The macro opens/runs a query to delete an employee the user indicates. The
user is prompted to enter the employee’s last name, first name
(concatentated). Then takes the user to the first record. And lastly it
displays an informational message indicating to the user the record has been
deleted.

* * *
I want an error message to appear if the user makes a mistake and enters an
employee name that is not in the database. How would I go about
accomplishing this task?

 

[Guest]

What happen if you have two employee's with the same name?
You should select a unique value for each employee that you can delete the
record by.

On the form you can create a Combo, so the user can select the employee to
delete, that way he can't select an employee that doesn't exist, it is a much
safer way to delete important information, as employee.
===================================
To check if employee exist before deleting, instead of running macro on the
OnClick event of the button, run this code

Dim EmployeeName as String
EmployeeName = InputBox("Please enter name to delete")
If EmployeeName <> "" Then
If Dcount("*","[TableName]","[EmployeeName]='" & EmployeeName & "'") = 0
Then
MsgBox "Employee name doesn't exist"
Else
Docmd.RunSql "Delete * From TableName Where "[EmployeeName]='" &
EmployeeName & "'"
MsgBox "Employee " & EmployeeName & " was deleted"
End if
Else
Msgbox "No name entered"
End If

Note: I didn't try this code, but I hope it will give you an idea

 

[Guest]

One problem with macros is the lack of error handling; however, this really
wouldn't be a database error anyway. You could do it in code with some fancy
EOF checks, but that could be a pain also and doesn't address the real
problem of allowing the users to type in something that isn't in the database.

Consider this: Add an unbound combo box with a row source of the employees
last and first names merged together. Call it cboEmployees. The SQL would
look something like below.

SELECT DISTINCT [LastName] & [FirstName] AS [EmpNames]
FROM Employees
ORDER BY [LastName] & [FirstName];

Set Limit To List to Yes on the combo box.

In your query put something like below in the criteria:

[Forms]![frmTheFormName]![cboEmployees].[value]

Now they can not make a mistake as only employees in the database will show
up.

WARNING: If you have a John A. Smith and a John B. Smith as employees, both
my suggestion and your current way of doing things could delete more records
than you bargained for.

 

[Guest]

Thank you. Your solution was very helpful.

What happen if you have two employee's with the same name?
You should select a unique value for each employee that you can delete the
record by.

On the form you can create a Combo, so the user can select the employee to
delete, that way he can't select an employee that doesn't exist, it is a much
safer way to delete important information, as employee.
===================================
To check if employee exist before deleting, instead of running macro on the
OnClick event of the button, run this code

Dim EmployeeName as String
EmployeeName = InputBox("Please enter name to delete")
If EmployeeName <> "" Then
If Dcount("*","[TableName]","[EmployeeName]='" & EmployeeName & "'") = 0
Then
MsgBox "Employee name doesn't exist"
Else
Docmd.RunSql "Delete * From TableName Where "[EmployeeName]='" &
EmployeeName & "'"
MsgBox "Employee " & EmployeeName & " was deleted"
End if
Else
Msgbox "No name entered"
End If

Note: I didn't try this code, but I hope it will give you an idea
--
Good Luck
BS"D

I have a button on a form that has a macro attached to its ON CLICK property.
The macro opens/runs a query to delete an employee the user indicates. The
user is prompted to enter the employee’s last name, first name
(concatentated). Then takes the user to the first record. And lastly it
displays an informational message indicating to the user the record has been
deleted.

* * *
I want an error message to appear if the user makes a mistake and enters an
employee name that is not in the database. How would I go about
accomplishing this task?

 

[Guest]

Thank you. Your solution was very helpful.

What happen if you have two employee's with the same name?
You should select a unique value for each employee that you can delete the
record by.

On the form you can create a Combo, so the user can select the employee to
delete, that way he can't select an employee that doesn't exist, it is a much
safer way to delete important information, as employee.
===================================
To check if employee exist before deleting, instead of running macro on the
OnClick event of the button, run this code

Dim EmployeeName as String
EmployeeName = InputBox("Please enter name to delete")
If EmployeeName <> "" Then
If Dcount("*","[TableName]","[EmployeeName]='" & EmployeeName & "'") = 0
Then
MsgBox "Employee name doesn't exist"
Else
Docmd.RunSql "Delete * From TableName Where "[EmployeeName]='" &
EmployeeName & "'"
MsgBox "Employee " & EmployeeName & " was deleted"
End if
Else
Msgbox "No name entered"
End If

Note: I didn't try this code, but I hope it will give you an idea
--
Good Luck
BS"D

I have a button on a form that has a macro attached to its ON CLICK property.
The macro opens/runs a query to delete an employee the user indicates. The
user is prompted to enter the employee’s last name, first name
(concatentated). Then takes the user to the first record. And lastly it
displays an informational message indicating to the user the record has been
deleted.

* * *
I want an error message to appear if the user makes a mistake and enters an
employee name that is not in the database. How would I go about
accomplishing this task?

 

[Guest]

First, thanks for the information.

I have a couple of questions. I created the combo box (based on the
Employee table) with the row source based on the merged first and last name .

SELECT DISTINCT [Employee Table].Lastname & ", " & [Employee
Table].Firstname AS EmployeeNames FROM [Employee Table];

At this point, when user selects an employee from the combo box, the
employee record will appear on screen.

After entering the criteria in the query, the combo box no longer displays
the list of employees.

Forms![Employee/Event Entry Form]![cboEmployees].[value]

Even after the combo box is functional, how do I connect, how do I perform
the deletion of the selected employee?

Thanks in advance.

One problem with macros is the lack of error handling; however, this really
wouldn't be a database error anyway. You could do it in code with some fancy
EOF checks, but that could be a pain also and doesn't address the real
problem of allowing the users to type in something that isn't in the database.

Consider this: Add an unbound combo box with a row source of the employees
last and first names merged together. Call it cboEmployees. The SQL would
look something like below.

SELECT DISTINCT [LastName] & [FirstName] AS [EmpNames]
FROM Employees
ORDER BY [LastName] & [FirstName];

Set Limit To List to Yes on the combo box.

In your query put something like below in the criteria:

[Forms]![frmTheFormName]![cboEmployees].[value]

Now they can not make a mistake as only employees in the database will show
up.

WARNING: If you have a John A. Smith and a John B. Smith as employees, both
my suggestion and your current way of doing things could delete more records
than you bargained for.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a button on a form that has a macro attached to its ON CLICK property.
The macro opens/runs a query to delete an employee the user indicates. The
user is prompted to enter the employee’s last name, first name
(concatentated). Then takes the user to the first record. And lastly it
displays an informational message indicating to the user the record has been
deleted.

* * *
I want an error message to appear if the user makes a mistake and enters an
employee name that is not in the database. How would I go about
accomplishing this task?