Working with a Double Query on a a Form

[Lou_Reed]

In the db that is in the file attached to Post # 74, I have made some combo boxes so that the user cannot choose a blank. This was done by using the validation rule in (is not null) in the properties for data to that combo. There is still a blank among the choices in the combo box, but it simply cannot b chosen. A good example is the add meeting combo box in frmAttendance.



I know there is a better way, I just do not know how to apply it. In some of the combo boxes in Post # 74 db,there is not even a choice for a blank space. In other words no blink space is shown, so it obviously cannot be chosen. A good example of this is the Organization combo box in frmPersonnel.

How was this done? Any help appreciated.

Thanks in advance.

Respectfully,

Lou Reed

[June7]

I looked at the comboboxes on frmEmail, frmAttendance, frmPersonnel. None of them have blank row in list. And I also do not see anything in ValidationRule. Options to prevent blank rows are:

1. set field as Required in table

2. use Not Is Null criteria in the RowSource SQL


Cannot change the value in meeting box on frmEmail because textbox is bound to ysnMeetingID primary key autonumber field of tblMeetings. Why would you want to change this value? Why do you even show any of the PK/FK values?

If purpose of frmEmail is to select an employee to send email to, then the design of this form is all wrong. The only fields that should be editable are Meeting_Invite_Sent and Notes. Controls used to enter selection criteria should be UNBOUND, otherwise you will change the data in the record.

Doesn't matter if combobox is created before or after records are created. Cannot select an employee in the combobox because it is bound to a fieldname that is not in the form RecordSource. Again, binding to field will change the value in record. I don't think you want to do that.

[Lou_Reed]

Options to prevent blank rows are:

 1. set field as Required in table

 2. use Not Is Null criteria in the RowSource SQL

The option that I used was to put in the validation rule : "Is Not Null"

It worked, you still got a blank choice, but you could not select it. It was there, however, it was un-selectable.

I put it in Validation Rule because RowSource had SQL code in it. I was thinking that it is not good to add something
additional, so I put it in Validation Rule. Should I still make it required?

Why would you want to change this value? Why do you even show any of the PK/FK values?

It was there primarily for the purpose of debugging. Please do not forget it is possible to have more than one meeting on a specific date, but each meeting
has a unique MeetingID. So that is how I distinguish it. The meeting date helps a human understand what the meeting is, but the MeetingID (not user friendly at all) keeps each
meeting (even ones on the same date) unique.

I am still baffled as to why I cannot select an employee from the combo box. The combo box gives me a choice. But when I select it, it refused to let me put it in as the choice.

There is just a chime and then nothing.

How to fix.

Respectfully,


Lou Reed

[Lou_Reed]

In the attached file is a db that has a malfunctioning from. frmEmail. I try to select employee and it will not let me put the name in the box. It simply will not.
How can I fix this?

The email address below the employee name is also not working although it should when I choose the employee name give the employee's
email address. It does not.

Anyway that is my question. that is my problem.

Any help appreciated. thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I have corrected the issues of incomplete names in the employee combo box in frmEmail. The correction was to remove a criteria fro9m the rowsource SQL code. It worked.
All employees are listed now. However, if I select a employee other than Boris Karloff, it will not let me put it in the combo box selection.

That is my question. Why can't I put a new name in the employees combo box in the frmEmail form?

The attached file contains the corrected db.

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[Lou_Reed]

I guess that I just need an answer to the Employees Combobox in frmEmail. It only lets me select Boris Karloff - no one else.

As you can see there are many more selections. The combobox disallows all but Boris Karloff.

Why is that?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[June7]

Using Validation Rule "Is Not Null" does not prevent display of blank row, just won't allow it to selected. If you don't want the blank row to show then use one of the suggested options.

I suggest a text field in tblMeetings for a meeting Description. As you said, meeting ID is not user-friendly but descriptive text is.

The combobox is bound to a field that is not editable because the field is a calculation that concatenates name parts. Do not bind the control.

Change the combobox properties:
RowSource: SELECT [tblPersonnel].[PersonnelID], [FirstName] & " " & [LastName] AS FullName, emailaddress FROM tblPersonnel ORDER BY [FirstName] & " " & [LastName];
ColumnCount: 3
ColumnWidths: 0";2";0"

Change the email address textbox ControlSource to:
=[cboEmployeeName].[Column](2)

What do you want to happen after name selected in combobox?

The combobox allows selection of name that is not associated with meeting displayed on form. Why do you want to do that?

[Lou_Reed]

I assume thta this answers the thread shown here:

https://www.accessforums.net/showthread.php?t=66423

In that thread, I only used three columns in my combo box for employees; the first was first name, the second was last name and the third was the email address -a unique specifier. I did not use autonumber at all. I did not have to, I had email address and that was unique.

Still with that I can only place the first name in the selection, not first and last name. I think it is because the combo allows only one selection. So I am stuck.

If I use a calculated full name it cannot be selected because it is calculated. So what do I do?

I would like the complete name (first and last name) to be selected. How do I do it.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

In the db which is in the attached file, I created the combobox properties as you said for EmployeeName. It works, but t will not let me enter a name after I select it. It just chimes and then nothing.

Also, the email address text-box just shows : #Name?, which I believe is related to the Employee combo box above it.

I am not sure what to do next. Please tell me what is wrong.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[June7]

I was answering questions in posts 78 - 81 of this thread. I have not looked at the other one.

Again, do not bind the combobox - do not put anything in the ControlSource property.

[Lou_Reed]

Okay, I will give it a shot. Can binding the combo box, disallow any name selection?

Respectfully,

Lou Reed

[Lou_Reed]

I made the changes that you suggested and it did take care of the combo box for Employee name, but it did not change anything n the textbox right below it for
email_address. It still stays blank.

I have attached the file to the post so you can see the changes that I made. It just does not
create an email address whenever I change the employee name. That is simply forbidden. In fact the screen
in the textbox is blank. It says blank throughout the whole process of moving through the names of the employees.

What is causing this?

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[June7]

You did not change the combobox RowSource sql as suggested in post 82.

[Lou_Reed]

I thought that I did. Now please remember that we are talking about the email address textbox.

I will check this out.\

Respectfully,


Lou Reed

[Lou_Reed]

below is my Rowsource SQL code

SELECT [tblPersonnel].[PersonnelID], [FirstName] & " " & [LastName] AS FullName FROM tblPersonnel ORDER BY [FirstName] & " " & [LastName];

It is as you suggested, it was matter of copy and paste. The problem is with the email address text box, not the combo box or so I thought.

Or is the problem with the combo box?

I must do some more checking.

Thanks for your help and I will be back.

Respectfully,


Lou Reed