Requery Combo Box

[Guest]

I have read every message I could find on this subject but I must be missing
something. I am having trouble requerying data to a combo box. I have it
set up as a value list but keep getting an error.

Here is my statement for adding to the combo box

If MsgBox("Do you wish to add this employee as a Trainer?", vbYesNo) =
vbYes Then
Response = acDataErrAdded 'combo automatically requeried
Else
Response = acDataErrContinue
Me.Undo
End If

I get the message box "Do you wish to add this employee...." and when I
click Yes I get "The text you entered is not on the list..." message box.

I have my "Limit to List" set to "yes".

Also, do I have to requery somewhere in my after update event? Thanks.

 

[Marshall Barton]

I have read every message I could find on this subject but I must be missing
something. I am having trouble requerying data to a combo box. I have it
set up as a value list but keep getting an error.

Here is my statement for adding to the combo box

If MsgBox("Do you wish to add this employee as a Trainer?", vbYesNo) =
vbYes Then
Response = acDataErrAdded 'combo automatically requeried
Else
Response = acDataErrContinue
Me.Undo
End If

I get the message box "Do you wish to add this employee...." and when I
click Yes I get "The text you entered is not on the list..." message box.

I have my "Limit to List" set to "yes".

Also, do I have to requery somewhere in my after update event? Thanks.

You need to use some code that adds the new item to the
list. If you are using a Value List, check AddItem in VBA
Help. OTOH, the new item will not persist after the form is
closed.

If you are using a table or query, then you need to add a
new record with the new item to the table (this will
persist).

 

[Guest]

I changed my Row Source Type to a table/query and my Row Source to a table
called "Trainer Names". The information received by combo box will go into a
field called "Trainer Name". However, now I get an error on my "INSERT INTO"
statement.

Dim db As Database
Set db = CurrentDb

If MsgBox("Do you wish to add this employee as a Trainer?", vbYesNo) = vbYes
Then
db.Execute "INSERT INTO [Trainer Names](Trainer Name) VALUES (""" & NewData
& """)", dbFailOnError

Response = acDataErrAdded

Else

Me.Trainer.Undo
Response = acDataErrContinue
End If

 

[Marshall Barton]

I changed my Row Source Type to a table/query and my Row Source to a table
called "Trainer Names". The information received by combo box will go into a
field called "Trainer Name". However, now I get an error on my "INSERT INTO"
statement.

Dim db As Database
Set db = CurrentDb

If MsgBox("Do you wish to add this employee as a Trainer?", vbYesNo) = vbYes
Then
db.Execute "INSERT INTO [Trainer Names](Trainer Name) VALUES (""" & NewData
& """)", dbFailOnError

Response = acDataErrAdded

Else

Me.Trainer.Undo
Response = acDataErrContinue
End If

I think the problem is because your field name contains a
space, which requires [ ] around it:

"INSERT INTO [Trainer Names] ([Trainer Name]) VALUES ...

 

[Guest]

It worked! Thanks again!

I changed my Row Source Type to a table/query and my Row Source to a table
called "Trainer Names". The information received by combo box will go into a
field called "Trainer Name". However, now I get an error on my "INSERT INTO"
statement.

Dim db As Database
Set db = CurrentDb

If MsgBox("Do you wish to add this employee as a Trainer?", vbYesNo) = vbYes
Then
db.Execute "INSERT INTO [Trainer Names](Trainer Name) VALUES (""" & NewData
& """)", dbFailOnError

Response = acDataErrAdded

Else

Me.Trainer.Undo
Response = acDataErrContinue
End If

I think the problem is because your field name contains a
space, which requires [ ] around it:

"INSERT INTO [Trainer Names] ([Trainer Name]) VALUES ...

 

[Guest]

Marsh, I hate to get greedy, but now that the code is working I would like to
expand on it. Right now the user can enter a new "Trainer Name" when a
certain criteria is met. I would actually like the "Trainer Name" added
automatically when a certain criteria is met.

When an employee reaches 100% (which is noted in the "Flexibility %" text
box) is there a way their name can be added automatically to the "Trainer
Name" combo box. My code right now is...


Private Sub Trainer_NotInList(NewData As String, Response As Integer)


Dim db As Database
Set db = CurrentDb

If MsgBox("Do you wish to add this employee as a Trainer?", vbYesNo) = vbYes
Then
db.Execute "INSERT INTO [Trainer Names]([Trainer Name]) VALUES (""" &
NewData & """)", dbFailOnError

Response = acDataErrAdded

Else

Me.Trainer.Undo
Response = acDataErrContinue
End If


End Sub

and it works great, asking the user if they would like to add the employee.

It worked! Thanks again!

I changed my Row Source Type to a table/query and my Row Source to a table
called "Trainer Names". The information received by combo box will go into a
field called "Trainer Name". However, now I get an error on my "INSERT INTO"
statement.

Dim db As Database
Set db = CurrentDb

If MsgBox("Do you wish to add this employee as a Trainer?", vbYesNo) = vbYes
Then
db.Execute "INSERT INTO [Trainer Names](Trainer Name) VALUES (""" & NewData
& """)", dbFailOnError

Response = acDataErrAdded

Else

Me.Trainer.Undo
Response = acDataErrContinue
End If

I think the problem is because your field name contains a
space, which requires [ ] around it:

"INSERT INTO [Trainer Names] ([Trainer Name]) VALUES ...

 

[Marshall Barton]

Marsh, I hate to get greedy, but now that the code is working I would like to
expand on it. Right now the user can enter a new "Trainer Name" when a
certain criteria is met. I would actually like the "Trainer Name" added
automatically when a certain criteria is met.

When an employee reaches 100% (which is noted in the "Flexibility %" text
box) is there a way their name can be added automatically to the "Trainer
Name" combo box. My code right now is...


Private Sub Trainer_NotInList(NewData As String, Response As Integer)


Dim db As Database
Set db = CurrentDb

If MsgBox("Do you wish to add this employee as a Trainer?", vbYesNo) = vbYes
Then
db.Execute "INSERT INTO [Trainer Names]([Trainer Name]) VALUES (""" &
NewData & """)", dbFailOnError

Response = acDataErrAdded

Else

Me.Trainer.Undo
Response = acDataErrContinue
End If

End Sub

and it works great, asking the user if they would like to add the employee.

I don't understand. If you want to add the 100% names to
the combo box list, why not just include those names in the
combo box's RowSource query?

I'm probably missing something important there. Here's
another thought, how about adding the name to the trainers
table in the form's AfterUpdate event?

If the 100% criteria is required and the only field in the
trainers table is the trainer name field, then I don't see a
need for the trainer names table.

It seems that I need a lot more detailed information before
I can make a meaningful suggestion about this aspect of your
situation.

 

[Guest]

I have designed this database for other users to fill in. If they check all
four squares, the employee is 100% and that is when I wanted the employee
name filled in as a trainer. The code I included does work, but the user has
to fill in the employee name in the "Trainer" combo box instead of having
Access automatically add the name without asking "Do you want to add this
employee as a trainer". Hope that makes more sense?

Marsh, I hate to get greedy, but now that the code is working I would like to
expand on it. Right now the user can enter a new "Trainer Name" when a
certain criteria is met. I would actually like the "Trainer Name" added
automatically when a certain criteria is met.

When an employee reaches 100% (which is noted in the "Flexibility %" text
box) is there a way their name can be added automatically to the "Trainer
Name" combo box. My code right now is...


Private Sub Trainer_NotInList(NewData As String, Response As Integer)


Dim db As Database
Set db = CurrentDb

If MsgBox("Do you wish to add this employee as a Trainer?", vbYesNo) = vbYes
Then
db.Execute "INSERT INTO [Trainer Names]([Trainer Name]) VALUES (""" &
NewData & """)", dbFailOnError

Response = acDataErrAdded

Else

Me.Trainer.Undo
Response = acDataErrContinue
End If

End Sub

and it works great, asking the user if they would like to add the employee.

I don't understand. If you want to add the 100% names to
the combo box list, why not just include those names in the
combo box's RowSource query?

I'm probably missing something important there. Here's
another thought, how about adding the name to the trainers
table in the form's AfterUpdate event?

If the 100% criteria is required and the only field in the
trainers table is the trainer name field, then I don't see a
need for the trainer names table.

It seems that I need a lot more detailed information before
I can make a meaningful suggestion about this aspect of your
situation.

 

[Marshall Barton]

Well, it makes sense, but I was hoping for more details
about the situation you are trying to deal with.

What does it take to be a trainer?

What fields are in the Trainers table?

Somehow I have the impression that the Trainers table only
has one field for the trainer's name. ** IF ** a person
becomes a trainer if, and only if, all four yes/no fields
are true, then you can just set the combo box's RowSource to
a query something like:

SELECT [employee name field]
FROM [employees table]
WHERE [check1 field] And [check2 field]
And [check3 field] And [check4 field]
ORDER BY [employee name field]

Then the list will be up to date every time the form is
opened, without any code.

If my guesses are incorrect, please explain what the real
situation is all about (instead of telling me what your form
is, or is not, doing).
--
Marsh
MVP [MS Access]

I have designed this database for other users to fill in. If they check all
four squares, the employee is 100% and that is when I wanted the employee
name filled in as a trainer. The code I included does work, but the user has
to fill in the employee name in the "Trainer" combo box instead of having
Access automatically add the name without asking "Do you want to add this
employee as a trainer". Hope that makes more sense?

Marsh, I hate to get greedy, but now that the code is working I would like to
expand on it. Right now the user can enter a new "Trainer Name" when a
certain criteria is met. I would actually like the "Trainer Name" added
automatically when a certain criteria is met.

When an employee reaches 100% (which is noted in the "Flexibility %" text
box) is there a way their name can be added automatically to the "Trainer
Name" combo box. My code right now is...


Private Sub Trainer_NotInList(NewData As String, Response As Integer)


Dim db As Database
Set db = CurrentDb

If MsgBox("Do you wish to add this employee as a Trainer?", vbYesNo) = vbYes
Then
db.Execute "INSERT INTO [Trainer Names]([Trainer Name]) VALUES (""" &
NewData & """)", dbFailOnError

Response = acDataErrAdded

Else

Me.Trainer.Undo
Response = acDataErrContinue
End If

End Sub

and it works great, asking the user if they would like to add the employee.

I don't understand. If you want to add the 100% names to
the combo box list, why not just include those names in the
combo box's RowSource query?

I'm probably missing something important there. Here's
another thought, how about adding the name to the trainers
table in the form's AfterUpdate event?

If the 100% criteria is required and the only field in the
trainers table is the trainer name field, then I don't see a
need for the trainer names table.

It seems that I need a lot more detailed information before
I can make a meaningful suggestion about this aspect of your
situation.

 

[Guest]

To become a trainer an employee must complete four areas (which are detailed
in the "training squares"). The person who will be maintaining the database
will get their information from the supervisor. Once all four areas are
complete, the employee is 100% and will be a trainer on that line

I wanted code that when the "Flexibility text box" reaches 100% the employee
would be added as a trainer. Right now my code states "an employee is 100%
trained, would you like to add them as a trainer" and that is prompted by the
"Square 4 check box". The user will click "yes" and then add the employe
name to the trainer combo box. I'd like to skip this step and have the
employee name automatically added in the trainer combo box.

My code looks like this right now. (If an employee has "Square #4" checked
they are 100% and get this prompt)

Private Sub Square_4_AfterUpdate()

If Square_4 = True Then

MsgBox "This employee is now 100%, would you like to add them as a Trainer?"
End If

End Sub

Thes following code is in my "Trainer Not in List" procedure

Private Sub Trainer_NotInList(NewData As String, Response As Integer)


Dim db As Database
Set db = CurrentDb

If MsgBox("Do you wish to add this employee as a Trainer?", vbYesNo) = vbYes
Then
db.Execute "INSERT INTO [Trainer Names]([Trainer Name]) VALUES (""" &
NewData & """)", dbFailOnError

Response = acDataErrAdded

Else

Me.Trainer.Undo
Response = acDataErrContinue
End If

End Sub

I'm thinking I may have duplicate code but this is all new to me. The fact
I can get anything to work at this point makes my day! Thanks again.

Well, it makes sense, but I was hoping for more details
about the situation you are trying to deal with.

What does it take to be a trainer?

What fields are in the Trainers table?

Somehow I have the impression that the Trainers table only
has one field for the trainer's name. ** IF ** a person
becomes a trainer if, and only if, all four yes/no fields
are true, then you can just set the combo box's RowSource to
a query something like:

SELECT [employee name field]
FROM [employees table]
WHERE [check1 field] And [check2 field]
And [check3 field] And [check4 field]
ORDER BY [employee name field]

Then the list will be up to date every time the form is
opened, without any code.

If my guesses are incorrect, please explain what the real
situation is all about (instead of telling me what your form
is, or is not, doing).
--
Marsh
MVP [MS Access]

I have designed this database for other users to fill in. If they check all
four squares, the employee is 100% and that is when I wanted the employee
name filled in as a trainer. The code I included does work, but the user has
to fill in the employee name in the "Trainer" combo box instead of having
Access automatically add the name without asking "Do you want to add this
employee as a trainer". Hope that makes more sense?

Michelle wrote:

Marsh, I hate to get greedy, but now that the code is working I would like to
expand on it. Right now the user can enter a new "Trainer Name" when a
certain criteria is met. I would actually like the "Trainer Name" added
automatically when a certain criteria is met.

When an employee reaches 100% (which is noted in the "Flexibility %" text
box) is there a way their name can be added automatically to the "Trainer
Name" combo box. My code right now is...


Private Sub Trainer_NotInList(NewData As String, Response As Integer)


Dim db As Database
Set db = CurrentDb

If MsgBox("Do you wish to add this employee as a Trainer?", vbYesNo) = vbYes
Then
db.Execute "INSERT INTO [Trainer Names]([Trainer Name]) VALUES (""" &
NewData & """)", dbFailOnError

Response = acDataErrAdded

Else

Me.Trainer.Undo
Response = acDataErrContinue
End If

End Sub

and it works great, asking the user if they would like to add the employee.


I don't understand. If you want to add the 100% names to
the combo box list, why not just include those names in the
combo box's RowSource query?

I'm probably missing something important there. Here's
another thought, how about adding the name to the trainers
table in the form's AfterUpdate event?

If the 100% criteria is required and the only field in the
trainers table is the trainer name field, then I don't see a
need for the trainer names table.

It seems that I need a lot more detailed information before
I can make a meaningful suggestion about this aspect of your
situation.

 

[Marshall Barton]

To become a trainer an employee must complete four areas (which are detailed
in the "training squares"). The person who will be maintaining the database
will get their information from the supervisor. Once all four areas are
complete, the employee is 100% and will be a trainer on that line

I wanted code that when the "Flexibility text box" reaches 100% the employee
would be added as a trainer. Right now my code states "an employee is 100%
trained, would you like to add them as a trainer" and that is prompted by the
"Square 4 check box". The user will click "yes" and then add the employe
name to the trainer combo box. I'd like to skip this step and have the
employee name automatically added in the trainer combo box.

[snip the code]


Let's try an experiment. Make a copy of the form using
Copy/Paste in the database window.

Using the copy, get rid of all that not in list code. Then
set the combo box's RowSource to a query like:
SELECT [emplyee name field]
FROM [the employees table]
WHERE [the check box 4 field]
ORDER BY [emplyee name field]

Then try the form and check the check boxes for an employee.
Close and reopen the form and look to see if that employee
is now in the trainers combo box list.

Post back with what you like or don't like about how that
works.

 

[Guest]

It worked!

One more question, would there be a way to clear out the Trainer Name if
"Square 4" is accidentally checked and then unchecked? Meaning the employee
would only be at 75% and not a trainer.

Thanks again for all your help.

To become a trainer an employee must complete four areas (which are detailed
in the "training squares"). The person who will be maintaining the database
will get their information from the supervisor. Once all four areas are
complete, the employee is 100% and will be a trainer on that line

I wanted code that when the "Flexibility text box" reaches 100% the employee
would be added as a trainer. Right now my code states "an employee is 100%
trained, would you like to add them as a trainer" and that is prompted by the
"Square 4 check box". The user will click "yes" and then add the employe
name to the trainer combo box. I'd like to skip this step and have the
employee name automatically added in the trainer combo box.

[snip the code]


Let's try an experiment. Make a copy of the form using
Copy/Paste in the database window.

Using the copy, get rid of all that not in list code. Then
set the combo box's RowSource to a query like:
SELECT [emplyee name field]
FROM [the employees table]
WHERE [the check box 4 field]
ORDER BY [emplyee name field]

Then try the form and check the check boxes for an employee.
Close and reopen the form and look to see if that employee
is now in the trainers combo box list.

Post back with what you like or don't like about how that
works.

 

[Marshall Barton]

It would automatically clear the next time the form is
opened.

If you want the combo box to update immediately when the
fourth check box is checked or unchecked, then add a little
code to check box 4's AfterUpdate event procedure:
Me.Dirty = False 'save the changed data to the table
Me.[the combo box].Requery
--
Marsh
MVP [MS Access]

It worked!

One more question, would there be a way to clear out the Trainer Name if
"Square 4" is accidentally checked and then unchecked? Meaning the employee
would only be at 75% and not a trainer.

To become a trainer an employee must complete four areas (which are detailed
in the "training squares"). The person who will be maintaining the database
will get their information from the supervisor. Once all four areas are
complete, the employee is 100% and will be a trainer on that line

I wanted code that when the "Flexibility text box" reaches 100% the employee
would be added as a trainer. Right now my code states "an employee is 100%
trained, would you like to add them as a trainer" and that is prompted by the
"Square 4 check box". The user will click "yes" and then add the employe
name to the trainer combo box. I'd like to skip this step and have the
employee name automatically added in the trainer combo box.

[snip the code]


Let's try an experiment. Make a copy of the form using
Copy/Paste in the database window.

Using the copy, get rid of all that not in list code. Then
set the combo box's RowSource to a query like:
SELECT [emplyee name field]
FROM [the employees table]
WHERE [the check box 4 field]
ORDER BY [emplyee name field]

Then try the form and check the check boxes for an employee.
Close and reopen the form and look to see if that employee
is now in the trainers combo box list.

Post back with what you like or don't like about how that
works.

 

[Guest]

Hi Marshall, not sure what I did wrong, but my "Trainer" combo box did not
update when I unchecked Square 4. I put

Me.Dirty = False 'save the changed data to the table

Me.[Trainer].Requery

if the "after update" event of Square 4. Maybe I've looked at it too long,
but thought I did what you told me. I even tried closing and reopening the
form but the trainer name stayed in. Thanks for your help.

It would automatically clear the next time the form is
opened.

If you want the combo box to update immediately when the
fourth check box is checked or unchecked, then add a little
code to check box 4's AfterUpdate event procedure:
Me.Dirty = False 'save the changed data to the table
Me.[the combo box].Requery
--
Marsh
MVP [MS Access]

It worked!

One more question, would there be a way to clear out the Trainer Name if
"Square 4" is accidentally checked and then unchecked? Meaning the employee
would only be at 75% and not a trainer.

Michelle wrote:

To become a trainer an employee must complete four areas (which are detailed
in the "training squares"). The person who will be maintaining the database
will get their information from the supervisor. Once all four areas are
complete, the employee is 100% and will be a trainer on that line

I wanted code that when the "Flexibility text box" reaches 100% the employee
would be added as a trainer. Right now my code states "an employee is 100%
trained, would you like to add them as a trainer" and that is prompted by the
"Square 4 check box". The user will click "yes" and then add the employe
name to the trainer combo box. I'd like to skip this step and have the
employee name automatically added in the trainer combo box.
[snip the code]


Let's try an experiment. Make a copy of the form using
Copy/Paste in the database window.

Using the copy, get rid of all that not in list code. Then
set the combo box's RowSource to a query like:
SELECT [emplyee name field]
FROM [the employees table]
WHERE [the check box 4 field]
ORDER BY [emplyee name field]

Then try the form and check the check boxes for an employee.
Close and reopen the form and look to see if that employee
is now in the trainers combo box list.

Post back with what you like or don't like about how that
works.

 

[Marshall Barton]

It would seem that somehow the combo box is no longer
refering to the table with the four check box fields.
Please post a Copy/Paste of the combo box's row source
query's SQL.

Either that or check box 4's AfterUpdate event procedure is
doing something to prevent the unchecked value from being
saved. Maybe I should also see the code in the AfterUpdate
event procedure.
--
Marsh
MVP [MS Access]

Hi Marshall, not sure what I did wrong, but my "Trainer" combo box did not
update when I unchecked Square 4. I put

Me.Dirty = False 'save the changed data to the table

Me.[Trainer].Requery

if the "after update" event of Square 4. Maybe I've looked at it too long,
but thought I did what you told me. I even tried closing and reopening the
form but the trainer name stayed in.

It would automatically clear the next time the form is
opened.

If you want the combo box to update immediately when the
fourth check box is checked or unchecked, then add a little
code to check box 4's AfterUpdate event procedure:
Me.Dirty = False 'save the changed data to the table
Me.[the combo box].Requery

It worked!

One more question, would there be a way to clear out the Trainer Name if
"Square 4" is accidentally checked and then unchecked? Meaning the employee
would only be at 75% and not a trainer.


:

Michelle wrote:

To become a trainer an employee must complete four areas (which are detailed
in the "training squares"). The person who will be maintaining the database
will get their information from the supervisor. Once all four areas are
complete, the employee is 100% and will be a trainer on that line

I wanted code that when the "Flexibility text box" reaches 100% the employee
would be added as a trainer. Right now my code states "an employee is 100%
trained, would you like to add them as a trainer" and that is prompted by the
"Square 4 check box". The user will click "yes" and then add the employe
name to the trainer combo box. I'd like to skip this step and have the
employee name automatically added in the trainer combo box.
[snip the code]


Let's try an experiment. Make a copy of the form using
Copy/Paste in the database window.

Using the copy, get rid of all that not in list code. Then
set the combo box's RowSource to a query like:
SELECT [emplyee name field]
FROM [the employees table]
WHERE [the check box 4 field]
ORDER BY [emplyee name field]

Then try the form and check the check boxes for an employee.
Close and reopen the form and look to see if that employee
is now in the trainers combo box list.

Post back with what you like or don't like about how that
works.

 

[Guest]

My row source is:

SELECT [Employee Names].[Employee Name] FROM [Employee Names] ORDER BY
[Employee Names].[Employee Name];

My after update on square 4 is:

Me.Dirty = False 'save the changed data to the table
Me.[Trainer].Requery

Thanks!

It would seem that somehow the combo box is no longer
refering to the table with the four check box fields.
Please post a Copy/Paste of the combo box's row source
query's SQL.

Either that or check box 4's AfterUpdate event procedure is
doing something to prevent the unchecked value from being
saved. Maybe I should also see the code in the AfterUpdate
event procedure.
--
Marsh
MVP [MS Access]

Hi Marshall, not sure what I did wrong, but my "Trainer" combo box did not
update when I unchecked Square 4. I put

Me.Dirty = False 'save the changed data to the table

Me.[Trainer].Requery

if the "after update" event of Square 4. Maybe I've looked at it too long,
but thought I did what you told me. I even tried closing and reopening the
form but the trainer name stayed in.

It would automatically clear the next time the form is
opened.

If you want the combo box to update immediately when the
fourth check box is checked or unchecked, then add a little
code to check box 4's AfterUpdate event procedure:
Me.Dirty = False 'save the changed data to the table
Me.[the combo box].Requery


Michelle wrote:
It worked!

One more question, would there be a way to clear out the Trainer Name if
"Square 4" is accidentally checked and then unchecked? Meaning the employee
would only be at 75% and not a trainer.


:

Michelle wrote:

To become a trainer an employee must complete four areas (which are detailed
in the "training squares"). The person who will be maintaining the database
will get their information from the supervisor. Once all four areas are
complete, the employee is 100% and will be a trainer on that line

I wanted code that when the "Flexibility text box" reaches 100% the employee
would be added as a trainer. Right now my code states "an employee is 100%
trained, would you like to add them as a trainer" and that is prompted by the
"Square 4 check box". The user will click "yes" and then add the employe
name to the trainer combo box. I'd like to skip this step and have the
employee name automatically added in the trainer combo box.
[snip the code]


Let's try an experiment. Make a copy of the form using
Copy/Paste in the database window.

Using the copy, get rid of all that not in list code. Then
set the combo box's RowSource to a query like:
SELECT [emplyee name field]
FROM [the employees table]
WHERE [the check box 4 field]
ORDER BY [emplyee name field]

Then try the form and check the check boxes for an employee.
Close and reopen the form and look to see if that employee
is now in the trainers combo box list.

Post back with what you like or don't like about how that
works.

 

[Marshall Barton]

My row source is:

SELECT [Employee Names].[Employee Name] FROM [Employee Names] ORDER BY
[Employee Names].[Employee Name];

What happened to the query's WHERE clause? The query used
to be like:

SELECT [Employee Name]
FROM [Employee Names]
WHERE [the check box 4 field]
ORDER BY [Employee Name]

 

[Guest]

When I use the "where" clause it takes all my names out of the trainer box.

SELECT [Employee Names].[Employee Name] FROM [Employee Names] WHERE [SQUARE
4] ORDER BY [Employee Names].[Employee Name];

My row source is:

SELECT [Employee Names].[Employee Name] FROM [Employee Names] ORDER BY
[Employee Names].[Employee Name];

What happened to the query's WHERE clause? The query used
to be like:

SELECT [Employee Name]
FROM [Employee Names]
WHERE [the check box 4 field]
ORDER BY [Employee Name]

 

[Marshall Barton]

Then we have to cross check the query to make sure it agrees
with what is in the Employee Names table. Are you sure that
the field in the table is really named SQUARE 4 and that it
is a Yes/No type field.

What else has changed since the query worked?
--
Marsh
MVP [MS Access]

When I use the "where" clause it takes all my names out of the trainer box.

SELECT [Employee Names].[Employee Name] FROM [Employee Names] WHERE [SQUARE
4] ORDER BY [Employee Names].[Employee Name];

My row source is:

SELECT [Employee Names].[Employee Name] FROM [Employee Names] ORDER BY
[Employee Names].[Employee Name];

What happened to the query's WHERE clause? The query used
to be like:

SELECT [Employee Name]
FROM [Employee Names]
WHERE [the check box 4 field]
ORDER BY [Employee Name]

 

[Guest]

I think I found my problem (one of many I guess). The code below will
automatically add all “Employee Names†to my Trainer box.

SELECT [Employee Names].[Employee Name] FROM [Employee Names] ORDER BY
[Employee Names].[Employee Name];

When I tried adding WHERE [Square 4] it removed all names. You asked me to
look at my query and you were right, I did not have “Square 4†in the
Employee Names table. I have a subform that contains “Square 4â€. The name
of my subform table is “Employee Info†and the Employee Name is “Employeeâ€.
I think this is where my troubles begin. Square 4 is called “Square 4†in
the Employee Info table.

I tried adding this code but it still clears out all the names.

SELECT [Employee Names].[Employee Name], [Employee Info].[Square 4] FROM
[Employee Names] INNER JOIN [Employee Info] ON ([Employee Names].[Employee
ID] = [Employee Info].[Employee ID]) AND ([Employee Names].[Employee Name] =
[Employee Info].Employee);

UGH, I’m ready to burn this database and start all over. Thanks for any help.

Then we have to cross check the query to make sure it agrees
with what is in the Employee Names table. Are you sure that
the field in the table is really named SQUARE 4 and that it
is a Yes/No type field.

What else has changed since the query worked?
--
Marsh
MVP [MS Access]

When I use the "where" clause it takes all my names out of the trainer box.

SELECT [Employee Names].[Employee Name] FROM [Employee Names] WHERE [SQUARE
4] ORDER BY [Employee Names].[Employee Name];

Michelle wrote:

My row source is:

SELECT [Employee Names].[Employee Name] FROM [Employee Names] ORDER BY
[Employee Names].[Employee Name];


What happened to the query's WHERE clause? The query used
to be like:

SELECT [Employee Name]
FROM [Employee Names]
WHERE [the check box 4 field]
ORDER BY [Employee Name]