Combo Box question

Okay I only have 2 buildings so I created a combo box and put a value
list with these 2 values then I created a combo box for parking type
and I put in its row source this query SELECT DISTINCT ParkingType FROM
SpaceNum WHERE (((SpaceNum.Building)=Forms![FOB2 New Parking Allocation
Form]!Building)) UNION select distinct null FROM SpaceNum; Then in the
spacenum combo box I put the following query in the row source, SELECT
DISTINCT SpaceNum.SpaceNum FROM SpaceNUM WHERE
(((SpaceNum.ParkingType)=Forms![FOB2 New Parking Allocation
Form]![Parking Type])) UNION select distinct null FROM SpaceNum;
This all works to perfection. I really need to know how to make the
assigned check box populate 2 tables my main parking table and my
spacenum table. The code you sent me did not work. It did not like the
second line. Thanx for all your help
Klatuu wrote:
Here are some ideas that may help.
I see you have Parking Type and building. It would make life easy for the
users to find an appropriate space if you use what is known as Cascading
Combos. That is, filtering a combo based on another combo. For example,
The first thing a user would want to choose would be the building. Then the
would want to know what parking type to assign, and last, choose a space from
that list. So here is how you do that. You have 3 combos. The first
(cboBldg) would have a row source of the buildings in your table:
SELECT Building FROM tlbSpaceNum;
The next wouldl select a type (cboParkType) available at that building
SELECT [Parking Type] FROM tblSpaceNum WHERE Building = Me.cboBldg AND
NOT Assigned
Then, to choose a space (cboSpaceNum)
SELECT SpaceNum FROM tblSpaceNum WHERE Building = Me.cboBldg AND
[Parking Type] = Me.cboParkType AND NOT Assigned

These combos should all be unbound controls. An unbound control is one that
does not have a field from the form's recordsource as its Control Souce
property. That is how Access knows what values to display in which controls
and what field to update with the value.

Now, how to deal with keeping the Assigned field up to date in the SpaceNum
table.

First, we have to get the value from the combo into the control that has the
spacenum in the Main Parking Table (txtSpaceNum). This you do in the After
Upate event of cboSpaceNum. There are three things to do. First, put the
value in the control, update the Assigned field in the SpaceNum table, and be
sure the assigned check box (chkAssigned) is checked

Me.txtSpaceNUm = Me.cboSpaceNum
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = True & ";",
dbFailOnError
Me.chkAssigned = True

Now to the original question on unchecking the Assigned check box. We do
exactly the reverse. Use the After Update event of the check box.

If Me.chkAssigned = False Then
Me.txtSpaceNum = Null
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = False & ";",
dbFailOnError
End If

This is all air code, so it may take some tweeking or debugging. Let me
know how it works out.


"Fred" wrote:

Right now that spacenum combo is pulling all the spaces from the
spacenum table based on the parking type(handicap, Lot A, Government,
Executive).
In the Spacenum table I have these fieldsSpacenum, Parking Type,
Building, Assigned)
Yes in the spacenum table I have a yes/no field for assigned.
I have a field in the main parking table called spacenum
Yes I would like it to do that so I can report on who has what spot
I dont think it does at this point nothing updated from combo box to
spacenum table
I am not sure what you mean by bound control?

Thanx for all your help it is much appreciated. Fred


Klatuu wrote:
I don't know what you mean by the spacenum comb alread in use. Isn't this
the combo you use to show unassigned spaces from the table that has the space
numbers?

We can make it work. Tell me a little about your tables.
What fields are in the spacenum table?

Do you have a field in the spacenum table that tells whether the number is
assigned or not?

What field in the main parking table is used to store the assigned space
number?

Do you want the combo box to populate the spacenum field in the main parking
table when you choose a space?

Does it also need to update anything in the spacenum table?

Is the Comb Box a bound control?

If you can answer these questions, I can make some recommendations on how it
will work.

Also, there appears to be a syntax error in the query you posted.

(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);

The way it is written, it is looking for a field named [Main Parking Talbe]
in a tabled named Assigned.
"Fred" wrote:

A couple of problems with this, One my spacenum combo box is already in
use plus I have my space numbers in a different table. I cant get the
assigned check box to populate both tables. I am trying to use a query
like the following:

SELECT SpaceNum
FROM Spacenum
Where SpaceNum Not IN
(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);
This does not work Thanx for the help
Klatuu wrote:
Okay, great. That makes it really easy. Hopefully, you have a field in your
parking space table that tells whether or not it is assigned. If you don't,
you need one. For this, I would suggest a Boolean (yes/no) Field. For
example purposes, lets call it [SPACE_AVAILABLE] and True means it is not
assigned.

Make the Row Source for the combo a query filtered on that field:
SELECT [SPACE_ID] FROM tblParkingSpaces WHERE [SPACE_AVAILABLE]

This will cause only unassigned spaces to be presented in the combo.

What I would is write a Sub in the form module and call it from the After
Update events of the Combo Box and the Check Box.

Private Sub UpdateSpace()
Dim blnAssign As Boolean

blnAssign = Screen.ActiveControl.Name = "chkUnAssign"
Me.chkUnAssign = blnAssign
CurrentDb.Execute "UPDATE tblParkingSpaces SET [SPACE_AVAILABLE] = " &
blnAssign & ";", dbFailOnError
Me.cboSpaceAvailable.Requery
End Function.

Then in the After Update of the Combo Box and the Check Box:
Call UpdateSpace

"Fred" wrote:

I am assigning parking spaces and one is assigned I want that one to be
pulled from the drop down not from the field. When the person leaves I
unassign the parking spot and I need it to reappear in the drop down.
Thanx Fred
Klatuu wrote:
This number you assign - When you check the box to un assign the number, is
the number removed from the record or do you have a field in the record that
tells you whether the number is assigned?

If you un assign a number, then later deside to reassign a number to the
record at a later date, does in get a new number or is it reassigned the
original number?

"Fred" wrote:

Thanx Klatuu
I also need to know the code to remove the numbers and put the numbers
back in. Sorry for the confusion. Fred
Klatuu wrote:
You will need to requery the combo in two places.
1. In the After Update event of the control where you assign the number.
2. In the After Update event of the check box.

"Fred" wrote:

Hi Folks,
I have created a combo box full of numbers. I also have a check box
called assigned. When I create a record with a number that is assigned
I would it to be taken out of the combo box list and then when I
uncheck the box I would like it to reappear in the list. Where is the
best place to put my requery statement? Thanx for all your help Fred

The Main Parking table will be updated by having the value from the combo
(cblSpaceNum) put into the control txtSpaceNum if txtSpaceNum is bould to the
SpaceNum field in the Main Parking Table. The SpaceNum table is updated by
the CurrentDb.Execute statement.

Can you post back which line is not working? I am not sure what you mean by
second line.

Also, One thing I left out. To get the combos to show the correct list, you
have to requery cboParkingType and cboSpaceNumber in the After Update event
of cboBuilding and requery cboSpaceNumber in the After Update event of
cboParkingType. You do it like this:

for cboBuilding:
Me.cboParkingType.Requery
Me.cboSpaceNum.Requery
for cboParkingType
Me.cboSpaceNum.Requery

Pardon the omission.


"Fred" wrote:

Okay I only have 2 buildings so I created a combo box and put a value
list with these 2 values then I created a combo box for parking type
and I put in its row source this query SELECT DISTINCT ParkingType FROM
SpaceNum WHERE (((SpaceNum.Building)=Forms![FOB2 New Parking Allocation
Form]!Building)) UNION select distinct null FROM SpaceNum; Then in the
spacenum combo box I put the following query in the row source, SELECT
DISTINCT SpaceNum.SpaceNum FROM SpaceNUM WHERE
(((SpaceNum.ParkingType)=Forms![FOB2 New Parking Allocation
Form]![Parking Type])) UNION select distinct null FROM SpaceNum;
This all works to perfection. I really need to know how to make the
assigned check box populate 2 tables my main parking table and my
spacenum table. The code you sent me did not work. It did not like the
second line. Thanx for all your help
Klatuu wrote:
Here are some ideas that may help.
I see you have Parking Type and building. It would make life easy for the
users to find an appropriate space if you use what is known as Cascading
Combos. That is, filtering a combo based on another combo. For example,
The first thing a user would want to choose would be the building. Then the
would want to know what parking type to assign, and last, choose a space from
that list. So here is how you do that. You have 3 combos. The first
(cboBldg) would have a row source of the buildings in your table:
SELECT Building FROM tlbSpaceNum;
The next wouldl select a type (cboParkType) available at that building
SELECT [Parking Type] FROM tblSpaceNum WHERE Building = Me.cboBldg AND
NOT Assigned
Then, to choose a space (cboSpaceNum)
SELECT SpaceNum FROM tblSpaceNum WHERE Building = Me.cboBldg AND
[Parking Type] = Me.cboParkType AND NOT Assigned

These combos should all be unbound controls. An unbound control is one that
does not have a field from the form's recordsource as its Control Souce
property. That is how Access knows what values to display in which controls
and what field to update with the value.

Now, how to deal with keeping the Assigned field up to date in the SpaceNum
table.

First, we have to get the value from the combo into the control that has the
spacenum in the Main Parking Table (txtSpaceNum). This you do in the After
Upate event of cboSpaceNum. There are three things to do. First, put the
value in the control, update the Assigned field in the SpaceNum table, and be
sure the assigned check box (chkAssigned) is checked

Me.txtSpaceNUm = Me.cboSpaceNum
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = True & ";",
dbFailOnError
Me.chkAssigned = True

Now to the original question on unchecking the Assigned check box. We do
exactly the reverse. Use the After Update event of the check box.

If Me.chkAssigned = False Then
Me.txtSpaceNum = Null
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = False & ";",
dbFailOnError
End If

This is all air code, so it may take some tweeking or debugging. Let me
know how it works out.


"Fred" wrote:

Right now that spacenum combo is pulling all the spaces from the
spacenum table based on the parking type(handicap, Lot A, Government,
Executive).
In the Spacenum table I have these fieldsSpacenum, Parking Type,
Building, Assigned)
Yes in the spacenum table I have a yes/no field for assigned.
I have a field in the main parking table called spacenum
Yes I would like it to do that so I can report on who has what spot
I dont think it does at this point nothing updated from combo box to
spacenum table
I am not sure what you mean by bound control?

Thanx for all your help it is much appreciated. Fred


Klatuu wrote:
I don't know what you mean by the spacenum comb alread in use. Isn't this
the combo you use to show unassigned spaces from the table that has the space
numbers?

We can make it work. Tell me a little about your tables.
What fields are in the spacenum table?

Do you have a field in the spacenum table that tells whether the number is
assigned or not?

What field in the main parking table is used to store the assigned space
number?

Do you want the combo box to populate the spacenum field in the main parking
table when you choose a space?

Does it also need to update anything in the spacenum table?

Is the Comb Box a bound control?

If you can answer these questions, I can make some recommendations on how it
will work.

Also, there appears to be a syntax error in the query you posted.

(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);

The way it is written, it is looking for a field named [Main Parking Talbe]
in a tabled named Assigned.
"Fred" wrote:

A couple of problems with this, One my spacenum combo box is already in
use plus I have my space numbers in a different table. I cant get the
assigned check box to populate both tables. I am trying to use a query
like the following:

SELECT SpaceNum
FROM Spacenum
Where SpaceNum Not IN
(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);
This does not work Thanx for the help
Klatuu wrote:
Okay, great. That makes it really easy. Hopefully, you have a field in your
parking space table that tells whether or not it is assigned. If you don't,
you need one. For this, I would suggest a Boolean (yes/no) Field. For
example purposes, lets call it [SPACE_AVAILABLE] and True means it is not
assigned.

Make the Row Source for the combo a query filtered on that field:
SELECT [SPACE_ID] FROM tblParkingSpaces WHERE [SPACE_AVAILABLE]

This will cause only unassigned spaces to be presented in the combo.

What I would is write a Sub in the form module and call it from the After
Update events of the Combo Box and the Check Box.

Private Sub UpdateSpace()
Dim blnAssign As Boolean

blnAssign = Screen.ActiveControl.Name = "chkUnAssign"
Me.chkUnAssign = blnAssign
CurrentDb.Execute "UPDATE tblParkingSpaces SET [SPACE_AVAILABLE] = " &
blnAssign & ";", dbFailOnError
Me.cboSpaceAvailable.Requery
End Function.

Then in the After Update of the Combo Box and the Check Box:
Call UpdateSpace

"Fred" wrote:

I am assigning parking spaces and one is assigned I want that one to be
pulled from the drop down not from the field. When the person leaves I
unassign the parking spot and I need it to reappear in the drop down.
Thanx Fred
Klatuu wrote:
This number you assign - When you check the box to un assign the number, is
the number removed from the record or do you have a field in the record that
tells you whether the number is assigned?

If you un assign a number, then later deside to reassign a number to the
record at a later date, does in get a new number or is it reassigned the
original number?

"Fred" wrote:

Thanx Klatuu
I also need to know the code to remove the numbers and put the numbers
back in. Sorry for the confusion. Fred
Klatuu wrote:
You will need to requery the combo in two places.
1. In the After Update event of the control where you assign the number.
2. In the After Update event of the check box.

"Fred" wrote:

Hi Folks,
I have created a combo box full of numbers. I also have a check box
called assigned. When I create a record with a number that is assigned
I would it to be taken out of the combo box list and then when I
uncheck the box I would like it to reappear in the list. Where is the
best place to put my requery statement? Thanx for all your help Fred

since my form is based on the main parking table I just put as my
control source the spcenum field and that populates fine. The Current
DB.Execute statement is not working. Right now the database name is
DB.Eventually it will be called parking database Thanx Fred
Klatuu wrote:
The Main Parking table will be updated by having the value from the combo
(cblSpaceNum) put into the control txtSpaceNum if txtSpaceNum is bould to the
SpaceNum field in the Main Parking Table. The SpaceNum table is updated by
the CurrentDb.Execute statement.

Can you post back which line is not working? I am not sure what you mean by
second line.

Also, One thing I left out. To get the combos to show the correct list, you
have to requery cboParkingType and cboSpaceNumber in the After Update event
of cboBuilding and requery cboSpaceNumber in the After Update event of
cboParkingType. You do it like this:

for cboBuilding:
Me.cboParkingType.Requery
Me.cboSpaceNum.Requery
for cboParkingType
Me.cboSpaceNum.Requery

Pardon the omission.


"Fred" wrote:

Okay I only have 2 buildings so I created a combo box and put a value
list with these 2 values then I created a combo box for parking type
and I put in its row source this query SELECT DISTINCT ParkingType FROM
SpaceNum WHERE (((SpaceNum.Building)=Forms![FOB2 New Parking Allocation
Form]!Building)) UNION select distinct null FROM SpaceNum; Then in the
spacenum combo box I put the following query in the row source, SELECT
DISTINCT SpaceNum.SpaceNum FROM SpaceNUM WHERE
(((SpaceNum.ParkingType)=Forms![FOB2 New Parking Allocation
Form]![Parking Type])) UNION select distinct null FROM SpaceNum;
This all works to perfection. I really need to know how to make the
assigned check box populate 2 tables my main parking table and my
spacenum table. The code you sent me did not work. It did not like the
second line. Thanx for all your help
Klatuu wrote:
Here are some ideas that may help.
I see you have Parking Type and building. It would make life easy for the
users to find an appropriate space if you use what is known as Cascading
Combos. That is, filtering a combo based on another combo. For example,
The first thing a user would want to choose would be the building. Then the
would want to know what parking type to assign, and last, choose a space from
that list. So here is how you do that. You have 3 combos. The first
(cboBldg) would have a row source of the buildings in your table:
SELECT Building FROM tlbSpaceNum;
The next wouldl select a type (cboParkType) available at that building
SELECT [Parking Type] FROM tblSpaceNum WHERE Building = Me.cboBldg AND
NOT Assigned
Then, to choose a space (cboSpaceNum)
SELECT SpaceNum FROM tblSpaceNum WHERE Building = Me.cboBldg AND
[Parking Type] = Me.cboParkType AND NOT Assigned

These combos should all be unbound controls. An unbound control is one that
does not have a field from the form's recordsource as its Control Souce
property. That is how Access knows what values to display in which controls
and what field to update with the value.

Now, how to deal with keeping the Assigned field up to date in the SpaceNum
table.

First, we have to get the value from the combo into the control that has the
spacenum in the Main Parking Table (txtSpaceNum). This you do in the After
Upate event of cboSpaceNum. There are three things to do. First, put the
value in the control, update the Assigned field in the SpaceNum table, and be
sure the assigned check box (chkAssigned) is checked

Me.txtSpaceNUm = Me.cboSpaceNum
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = True & ";",
dbFailOnError
Me.chkAssigned = True

Now to the original question on unchecking the Assigned check box. We do
exactly the reverse. Use the After Update event of the check box.

If Me.chkAssigned = False Then
Me.txtSpaceNum = Null
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = False & ";",
dbFailOnError
End If

This is all air code, so it may take some tweeking or debugging. Let me
know how it works out.


"Fred" wrote:

Right now that spacenum combo is pulling all the spaces from the
spacenum table based on the parking type(handicap, Lot A, Government,
Executive).
In the Spacenum table I have these fieldsSpacenum, Parking Type,
Building, Assigned)
Yes in the spacenum table I have a yes/no field for assigned.
I have a field in the main parking table called spacenum
Yes I would like it to do that so I can report on who has what spot
I dont think it does at this point nothing updated from combo box to
spacenum table
I am not sure what you mean by bound control?

Thanx for all your help it is much appreciated. Fred


Klatuu wrote:
I don't know what you mean by the spacenum comb alread in use. Isn't this
the combo you use to show unassigned spaces from the table that has the space
numbers?

We can make it work. Tell me a little about your tables.
What fields are in the spacenum table?

Do you have a field in the spacenum table that tells whether the number is
assigned or not?

What field in the main parking table is used to store the assigned space
number?

Do you want the combo box to populate the spacenum field in the main parking
table when you choose a space?

Does it also need to update anything in the spacenum table?

Is the Comb Box a bound control?

If you can answer these questions, I can make some recommendations on how it
will work.

Also, there appears to be a syntax error in the query you posted.

(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);

The way it is written, it is looking for a field named [Main Parking Talbe]
in a tabled named Assigned.
"Fred" wrote:

A couple of problems with this, One my spacenum combo box is already in
use plus I have my space numbers in a different table. I cant get the
assigned check box to populate both tables. I am trying to use a query
like the following:

SELECT SpaceNum
FROM Spacenum
Where SpaceNum Not IN
(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);
This does not work Thanx for the help
Klatuu wrote:
Okay, great. That makes it really easy. Hopefully, you have a field in your
parking space table that tells whether or not it is assigned. If you don't,
you need one. For this, I would suggest a Boolean (yes/no) Field. For
example purposes, lets call it [SPACE_AVAILABLE] and True means it is not
assigned.

Make the Row Source for the combo a query filtered on that field:
SELECT [SPACE_ID] FROM tblParkingSpaces WHERE [SPACE_AVAILABLE]

This will cause only unassigned spaces to be presented in the combo.

What I would is write a Sub in the form module and call it from the After
Update events of the Combo Box and the Check Box.

Private Sub UpdateSpace()
Dim blnAssign As Boolean

blnAssign = Screen.ActiveControl.Name = "chkUnAssign"
Me.chkUnAssign = blnAssign
CurrentDb.Execute "UPDATE tblParkingSpaces SET [SPACE_AVAILABLE] = " &
blnAssign & ";", dbFailOnError
Me.cboSpaceAvailable.Requery
End Function.

Then in the After Update of the Combo Box and the Check Box:
Call UpdateSpace

"Fred" wrote:

I am assigning parking spaces and one is assigned I want that one to be
pulled from the drop down not from the field. When the person leaves I
unassign the parking spot and I need it to reappear in the drop down.
Thanx Fred
Klatuu wrote:
This number you assign - When you check the box to un assign the number, is
the number removed from the record or do you have a field in the record that
tells you whether the number is assigned?

If you un assign a number, then later deside to reassign a number to the
record at a later date, does in get a new number or is it reassigned the
original number?

"Fred" wrote:

Thanx Klatuu
I also need to know the code to remove the numbers and put the numbers
back in. Sorry for the confusion. Fred
Klatuu wrote:
You will need to requery the combo in two places.
1. In the After Update event of the control where you assign the number.
2. In the After Update event of the check box.

"Fred" wrote:

Hi Folks,
I have created a combo box full of numbers. I also have a check box
called assigned. When I create a record with a number that is assigned
I would it to be taken out of the combo box list and then when I
uncheck the box I would like it to reappear in the list. Where is the
best place to put my requery statement? Thanx for all your help Fred

It doesn't matter what the database is named. CurrentDb references the
database you are working in. The Execute method executes an SQL statement.
What error are you getting?

I think the problem is, I forgot to filter the spacenum table for the update
and it may have a syntax error. See if this helps:

CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
'" & Me.cboBuilding & "' AND [ParkingType] = '" & Me.cboParkingType & "' AND
[SpaceNum] = '" & Me.cboSpaceNum & "';"), dbFailOnError

Note the above assumes all fields exept Assigned are text fields. If any
are numeric fields, then take out the single quotes around them. For
example, if SpaceNum is numeric, it would be like this:

CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
'" & Me.cboBuilding & "' AND [ParkingType] = '" & Me.cboParkingType & "' AND
[SpaceNum] = " & Me.cboSpaceNum & ";"), dbFailOnError


"Fred" wrote:

since my form is based on the main parking table I just put as my
control source the spcenum field and that populates fine. The Current
DB.Execute statement is not working. Right now the database name is
DB.Eventually it will be called parking database Thanx Fred
Klatuu wrote:
The Main Parking table will be updated by having the value from the combo
(cblSpaceNum) put into the control txtSpaceNum if txtSpaceNum is bould to the
SpaceNum field in the Main Parking Table. The SpaceNum table is updated by
the CurrentDb.Execute statement.

Can you post back which line is not working? I am not sure what you mean by
second line.

Also, One thing I left out. To get the combos to show the correct list, you
have to requery cboParkingType and cboSpaceNumber in the After Update event
of cboBuilding and requery cboSpaceNumber in the After Update event of
cboParkingType. You do it like this:

for cboBuilding:
Me.cboParkingType.Requery
Me.cboSpaceNum.Requery
for cboParkingType
Me.cboSpaceNum.Requery

Pardon the omission.


"Fred" wrote:

Okay I only have 2 buildings so I created a combo box and put a value
list with these 2 values then I created a combo box for parking type
and I put in its row source this query SELECT DISTINCT ParkingType FROM
SpaceNum WHERE (((SpaceNum.Building)=Forms![FOB2 New Parking Allocation
Form]!Building)) UNION select distinct null FROM SpaceNum; Then in the
spacenum combo box I put the following query in the row source, SELECT
DISTINCT SpaceNum.SpaceNum FROM SpaceNUM WHERE
(((SpaceNum.ParkingType)=Forms![FOB2 New Parking Allocation
Form]![Parking Type])) UNION select distinct null FROM SpaceNum;
This all works to perfection. I really need to know how to make the
assigned check box populate 2 tables my main parking table and my
spacenum table. The code you sent me did not work. It did not like the
second line. Thanx for all your help
Klatuu wrote:
Here are some ideas that may help.
I see you have Parking Type and building. It would make life easy for the
users to find an appropriate space if you use what is known as Cascading
Combos. That is, filtering a combo based on another combo. For example,
The first thing a user would want to choose would be the building. Then the
would want to know what parking type to assign, and last, choose a space from
that list. So here is how you do that. You have 3 combos. The first
(cboBldg) would have a row source of the buildings in your table:
SELECT Building FROM tlbSpaceNum;
The next wouldl select a type (cboParkType) available at that building
SELECT [Parking Type] FROM tblSpaceNum WHERE Building = Me.cboBldg AND
NOT Assigned
Then, to choose a space (cboSpaceNum)
SELECT SpaceNum FROM tblSpaceNum WHERE Building = Me.cboBldg AND
[Parking Type] = Me.cboParkType AND NOT Assigned

These combos should all be unbound controls. An unbound control is one that
does not have a field from the form's recordsource as its Control Souce
property. That is how Access knows what values to display in which controls
and what field to update with the value.

Now, how to deal with keeping the Assigned field up to date in the SpaceNum
table.

First, we have to get the value from the combo into the control that has the
spacenum in the Main Parking Table (txtSpaceNum). This you do in the After
Upate event of cboSpaceNum. There are three things to do. First, put the
value in the control, update the Assigned field in the SpaceNum table, and be
sure the assigned check box (chkAssigned) is checked

Me.txtSpaceNUm = Me.cboSpaceNum
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = True & ";",
dbFailOnError
Me.chkAssigned = True

Now to the original question on unchecking the Assigned check box. We do
exactly the reverse. Use the After Update event of the check box.

If Me.chkAssigned = False Then
Me.txtSpaceNum = Null
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = False & ";",
dbFailOnError
End If

This is all air code, so it may take some tweeking or debugging. Let me
know how it works out.


"Fred" wrote:

Right now that spacenum combo is pulling all the spaces from the
spacenum table based on the parking type(handicap, Lot A, Government,
Executive).
In the Spacenum table I have these fieldsSpacenum, Parking Type,
Building, Assigned)
Yes in the spacenum table I have a yes/no field for assigned.
I have a field in the main parking table called spacenum
Yes I would like it to do that so I can report on who has what spot
I dont think it does at this point nothing updated from combo box to
spacenum table
I am not sure what you mean by bound control?

Thanx for all your help it is much appreciated. Fred


Klatuu wrote:
I don't know what you mean by the spacenum comb alread in use. Isn't this
the combo you use to show unassigned spaces from the table that has the space
numbers?

We can make it work. Tell me a little about your tables.
What fields are in the spacenum table?

Do you have a field in the spacenum table that tells whether the number is
assigned or not?

What field in the main parking table is used to store the assigned space
number?

Do you want the combo box to populate the spacenum field in the main parking
table when you choose a space?

Does it also need to update anything in the spacenum table?

Is the Comb Box a bound control?

If you can answer these questions, I can make some recommendations on how it
will work.

Also, there appears to be a syntax error in the query you posted.

(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);

The way it is written, it is looking for a field named [Main Parking Talbe]
in a tabled named Assigned.
"Fred" wrote:

A couple of problems with this, One my spacenum combo box is already in
use plus I have my space numbers in a different table. I cant get the
assigned check box to populate both tables. I am trying to use a query
like the following:

SELECT SpaceNum
FROM Spacenum
Where SpaceNum Not IN
(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);
This does not work Thanx for the help
Klatuu wrote:
Okay, great. That makes it really easy. Hopefully, you have a field in your
parking space table that tells whether or not it is assigned. If you don't,
you need one. For this, I would suggest a Boolean (yes/no) Field. For
example purposes, lets call it [SPACE_AVAILABLE] and True means it is not
assigned.

Make the Row Source for the combo a query filtered on that field:
SELECT [SPACE_ID] FROM tblParkingSpaces WHERE [SPACE_AVAILABLE]

This will cause only unassigned spaces to be presented in the combo.

What I would is write a Sub in the form module and call it from the After
Update events of the Combo Box and the Check Box.

Private Sub UpdateSpace()
Dim blnAssign As Boolean

blnAssign = Screen.ActiveControl.Name = "chkUnAssign"
Me.chkUnAssign = blnAssign
CurrentDb.Execute "UPDATE tblParkingSpaces SET [SPACE_AVAILABLE] = " &
blnAssign & ";", dbFailOnError
Me.cboSpaceAvailable.Requery
End Function.

Then in the After Update of the Combo Box and the Check Box:
Call UpdateSpace

"Fred" wrote:

I am assigning parking spaces and one is assigned I want that one to be
pulled from the drop down not from the field. When the person leaves I
unassign the parking spot and I need it to reappear in the drop down.
Thanx Fred
Klatuu wrote:
This number you assign - When you check the box to un assign the number, is
the number removed from the record or do you have a field in the record that
tells you whether the number is assigned?

If you un assign a number, then later deside to reassign a number to the
record at a later date, does in get a new number or is it reassigned the
original number?

"Fred" wrote:

Thanx Klatuu
I also need to know the code to remove the numbers and put the numbers
back in. Sorry for the confusion. Fred
Klatuu wrote:
You will need to requery the combo in two places.
1. In the After Update event of the control where you assign the number.
2. In the After Update event of the check box.

"Fred" wrote:

Hi Folks,
I have created a combo box full of numbers. I also have a check box
called assigned. When I create a record with a number that is assigned
I would it to be taken out of the combo box list and then when I
uncheck the box I would like it to reappear in the list. Where is the
best place to put my requery statement? Thanx for all your help Fred

I got a syntax error with the following line highlighted:
CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE
[Building] =
Klatuu wrote:
It doesn't matter what the database is named. CurrentDb references the
database you are working in. The Execute method executes an SQL statement.
What error are you getting?

I think the problem is, I forgot to filter the spacenum table for the update
and it may have a syntax error. See if this helps:

CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
'" & Me.cboBuilding & "' AND [ParkingType] = '" & Me.cboParkingType & "' AND
[SpaceNum] = '" & Me.cboSpaceNum & "';"), dbFailOnError

Note the above assumes all fields exept Assigned are text fields. If any
are numeric fields, then take out the single quotes around them. For
example, if SpaceNum is numeric, it would be like this:

CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
'" & Me.cboBuilding & "' AND [ParkingType] = '" & Me.cboParkingType & "' AND
[SpaceNum] = " & Me.cboSpaceNum & ";"), dbFailOnError


"Fred" wrote:

since my form is based on the main parking table I just put as my
control source the spcenum field and that populates fine. The Current
DB.Execute statement is not working. Right now the database name is
DB.Eventually it will be called parking database Thanx Fred
Klatuu wrote:
The Main Parking table will be updated by having the value from the combo
(cblSpaceNum) put into the control txtSpaceNum if txtSpaceNum is bould to the
SpaceNum field in the Main Parking Table. The SpaceNum table is updated by
the CurrentDb.Execute statement.

Can you post back which line is not working? I am not sure what you mean by
second line.

Also, One thing I left out. To get the combos to show the correct list, you
have to requery cboParkingType and cboSpaceNumber in the After Update event
of cboBuilding and requery cboSpaceNumber in the After Update event of
cboParkingType. You do it like this:

for cboBuilding:
Me.cboParkingType.Requery
Me.cboSpaceNum.Requery
for cboParkingType
Me.cboSpaceNum.Requery

Pardon the omission.


"Fred" wrote:

Okay I only have 2 buildings so I created a combo box and put a value
list with these 2 values then I created a combo box for parking type
and I put in its row source this query SELECT DISTINCT ParkingType FROM
SpaceNum WHERE (((SpaceNum.Building)=Forms![FOB2 New Parking Allocation
Form]!Building)) UNION select distinct null FROM SpaceNum; Then in the
spacenum combo box I put the following query in the row source, SELECT
DISTINCT SpaceNum.SpaceNum FROM SpaceNUM WHERE
(((SpaceNum.ParkingType)=Forms![FOB2 New Parking Allocation
Form]![Parking Type])) UNION select distinct null FROM SpaceNum;
This all works to perfection. I really need to know how to make the
assigned check box populate 2 tables my main parking table and my
spacenum table. The code you sent me did not work. It did not like the
second line. Thanx for all your help
Klatuu wrote:
Here are some ideas that may help.
I see you have Parking Type and building. It would make life easy for the
users to find an appropriate space if you use what is known as Cascading
Combos. That is, filtering a combo based on another combo. For example,
The first thing a user would want to choose would be the building. Then the
would want to know what parking type to assign, and last, choose a space from
that list. So here is how you do that. You have 3 combos. The first
(cboBldg) would have a row source of the buildings in your table:
SELECT Building FROM tlbSpaceNum;
The next wouldl select a type (cboParkType) available at that building
SELECT [Parking Type] FROM tblSpaceNum WHERE Building = Me.cboBldg AND
NOT Assigned
Then, to choose a space (cboSpaceNum)
SELECT SpaceNum FROM tblSpaceNum WHERE Building = Me.cboBldg AND
[Parking Type] = Me.cboParkType AND NOT Assigned

These combos should all be unbound controls. An unbound control is one that
does not have a field from the form's recordsource as its Control Souce
property. That is how Access knows what values to display in which controls
and what field to update with the value.

Now, how to deal with keeping the Assigned field up to date in the SpaceNum
table.

First, we have to get the value from the combo into the control that has the
spacenum in the Main Parking Table (txtSpaceNum). This you do in the After
Upate event of cboSpaceNum. There are three things to do. First, put the
value in the control, update the Assigned field in the SpaceNum table, and be
sure the assigned check box (chkAssigned) is checked

Me.txtSpaceNUm = Me.cboSpaceNum
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = True & ";",
dbFailOnError
Me.chkAssigned = True

Now to the original question on unchecking the Assigned check box. We do
exactly the reverse. Use the After Update event of the check box.

If Me.chkAssigned = False Then
Me.txtSpaceNum = Null
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = False & ";",
dbFailOnError
End If

This is all air code, so it may take some tweeking or debugging. Let me
know how it works out.


"Fred" wrote:

Right now that spacenum combo is pulling all the spaces from the
spacenum table based on the parking type(handicap, Lot A, Government,
Executive).
In the Spacenum table I have these fieldsSpacenum, Parking Type,
Building, Assigned)
Yes in the spacenum table I have a yes/no field for assigned.
I have a field in the main parking table called spacenum
Yes I would like it to do that so I can report on who has what spot
I dont think it does at this point nothing updated from combo box to
spacenum table
I am not sure what you mean by bound control?

Thanx for all your help it is much appreciated. Fred


Klatuu wrote:
I don't know what you mean by the spacenum comb alread in use. Isn't this
the combo you use to show unassigned spaces from the table that has the space
numbers?

We can make it work. Tell me a little about your tables.
What fields are in the spacenum table?

Do you have a field in the spacenum table that tells whether the number is
assigned or not?

What field in the main parking table is used to store the assigned space
number?

Do you want the combo box to populate the spacenum field in the main parking
table when you choose a space?

Does it also need to update anything in the spacenum table?

Is the Comb Box a bound control?

If you can answer these questions, I can make some recommendations on how it
will work.

Also, there appears to be a syntax error in the query you posted.

(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);

The way it is written, it is looking for a field named [Main Parking Talbe]
in a tabled named Assigned.
"Fred" wrote:

A couple of problems with this, One my spacenum combo box is already in
use plus I have my space numbers in a different table. I cant get the
assigned check box to populate both tables. I am trying to use a query
like the following:

SELECT SpaceNum
FROM Spacenum
Where SpaceNum Not IN
(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);
This does not work Thanx for the help
Klatuu wrote:
Okay, great. That makes it really easy. Hopefully, you have a field in your
parking space table that tells whether or not it is assigned. If you don't,
you need one. For this, I would suggest a Boolean (yes/no) Field. For
example purposes, lets call it [SPACE_AVAILABLE] and True means it is not
assigned.

Make the Row Source for the combo a query filtered on that field:
SELECT [SPACE_ID] FROM tblParkingSpaces WHERE [SPACE_AVAILABLE]

This will cause only unassigned spaces to be presented in the combo.

What I would is write a Sub in the form module and call it from the After
Update events of the Combo Box and the Check Box.

Private Sub UpdateSpace()
Dim blnAssign As Boolean

blnAssign = Screen.ActiveControl.Name = "chkUnAssign"
Me.chkUnAssign = blnAssign
CurrentDb.Execute "UPDATE tblParkingSpaces SET [SPACE_AVAILABLE] = " &
blnAssign & ";", dbFailOnError
Me.cboSpaceAvailable.Requery
End Function.

Then in the After Update of the Combo Box and the Check Box:
Call UpdateSpace

"Fred" wrote:

I am assigning parking spaces and one is assigned I want that one to be
pulled from the drop down not from the field. When the person leaves I
unassign the parking spot and I need it to reappear in the drop down.
Thanx Fred
Klatuu wrote:
This number you assign - When you check the box to un assign the number, is
the number removed from the record or do you have a field in the record that
tells you whether the number is assigned?

If you un assign a number, then later deside to reassign a number to the
record at a later date, does in get a new number or is it reassigned the
original number?

"Fred" wrote:

Thanx Klatuu
I also need to know the code to remove the numbers and put the numbers
back in. Sorry for the confusion. Fred
Klatuu wrote:
You will need to requery the combo in two places.
1. In the After Update event of the control where you assign the number.
2. In the After Update event of the check box.

"Fred" wrote:

Hi Folks,
I have created a combo box full of numbers. I also have a check box
called assigned. When I create a record with a number that is assigned
I would it to be taken out of the combo box list and then when I
uncheck the box I would like it to reappear in the list. Where is the
best place to put my requery statement? Thanx for all your help Fred

Hard to tell without the tables to test it with. It looks okay, but if you
are getting a syntax error, obviously it is not.
Are the table name and field names correct?

One thing we may try is changing it to a RunSQL and see that makes a
difference. The syntax is a little different and you don't have to have the
object references outside the qoutes

Docmd.RunSQL "UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
Me.cboBuilding AND [ParkingType] = Me.cboParkingType AND
[SpaceNum] = Me.cboSpaceNum"

"Fred" wrote:

I got a syntax error with the following line highlighted:
CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE
[Building] =
Klatuu wrote:
It doesn't matter what the database is named. CurrentDb references the
database you are working in. The Execute method executes an SQL statement.
What error are you getting?

I think the problem is, I forgot to filter the spacenum table for the update
and it may have a syntax error. See if this helps:

CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
'" & Me.cboBuilding & "' AND [ParkingType] = '" & Me.cboParkingType & "' AND
[SpaceNum] = '" & Me.cboSpaceNum & "';"), dbFailOnError

Note the above assumes all fields exept Assigned are text fields. If any
are numeric fields, then take out the single quotes around them. For
example, if SpaceNum is numeric, it would be like this:

CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
'" & Me.cboBuilding & "' AND [ParkingType] = '" & Me.cboParkingType & "' AND
[SpaceNum] = " & Me.cboSpaceNum & ";"), dbFailOnError


"Fred" wrote:

since my form is based on the main parking table I just put as my
control source the spcenum field and that populates fine. The Current
DB.Execute statement is not working. Right now the database name is
DB.Eventually it will be called parking database Thanx Fred
Klatuu wrote:
The Main Parking table will be updated by having the value from the combo
(cblSpaceNum) put into the control txtSpaceNum if txtSpaceNum is bould to the
SpaceNum field in the Main Parking Table. The SpaceNum table is updated by
the CurrentDb.Execute statement.

Can you post back which line is not working? I am not sure what you mean by
second line.

Also, One thing I left out. To get the combos to show the correct list, you
have to requery cboParkingType and cboSpaceNumber in the After Update event
of cboBuilding and requery cboSpaceNumber in the After Update event of
cboParkingType. You do it like this:

for cboBuilding:
Me.cboParkingType.Requery
Me.cboSpaceNum.Requery
for cboParkingType
Me.cboSpaceNum.Requery

Pardon the omission.


"Fred" wrote:

Okay I only have 2 buildings so I created a combo box and put a value
list with these 2 values then I created a combo box for parking type
and I put in its row source this query SELECT DISTINCT ParkingType FROM
SpaceNum WHERE (((SpaceNum.Building)=Forms![FOB2 New Parking Allocation
Form]!Building)) UNION select distinct null FROM SpaceNum; Then in the
spacenum combo box I put the following query in the row source, SELECT
DISTINCT SpaceNum.SpaceNum FROM SpaceNUM WHERE
(((SpaceNum.ParkingType)=Forms![FOB2 New Parking Allocation
Form]![Parking Type])) UNION select distinct null FROM SpaceNum;
This all works to perfection. I really need to know how to make the
assigned check box populate 2 tables my main parking table and my
spacenum table. The code you sent me did not work. It did not like the
second line. Thanx for all your help
Klatuu wrote:
Here are some ideas that may help.
I see you have Parking Type and building. It would make life easy for the
users to find an appropriate space if you use what is known as Cascading
Combos. That is, filtering a combo based on another combo. For example,
The first thing a user would want to choose would be the building. Then the
would want to know what parking type to assign, and last, choose a space from
that list. So here is how you do that. You have 3 combos. The first
(cboBldg) would have a row source of the buildings in your table:
SELECT Building FROM tlbSpaceNum;
The next wouldl select a type (cboParkType) available at that building
SELECT [Parking Type] FROM tblSpaceNum WHERE Building = Me.cboBldg AND
NOT Assigned
Then, to choose a space (cboSpaceNum)
SELECT SpaceNum FROM tblSpaceNum WHERE Building = Me.cboBldg AND
[Parking Type] = Me.cboParkType AND NOT Assigned

These combos should all be unbound controls. An unbound control is one that
does not have a field from the form's recordsource as its Control Souce
property. That is how Access knows what values to display in which controls
and what field to update with the value.

Now, how to deal with keeping the Assigned field up to date in the SpaceNum
table.

First, we have to get the value from the combo into the control that has the
spacenum in the Main Parking Table (txtSpaceNum). This you do in the After
Upate event of cboSpaceNum. There are three things to do. First, put the
value in the control, update the Assigned field in the SpaceNum table, and be
sure the assigned check box (chkAssigned) is checked

Me.txtSpaceNUm = Me.cboSpaceNum
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = True & ";",
dbFailOnError
Me.chkAssigned = True

Now to the original question on unchecking the Assigned check box. We do
exactly the reverse. Use the After Update event of the check box.

If Me.chkAssigned = False Then
Me.txtSpaceNum = Null
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = False & ";",
dbFailOnError
End If

This is all air code, so it may take some tweeking or debugging. Let me
know how it works out.


"Fred" wrote:

Right now that spacenum combo is pulling all the spaces from the
spacenum table based on the parking type(handicap, Lot A, Government,
Executive).
In the Spacenum table I have these fieldsSpacenum, Parking Type,
Building, Assigned)
Yes in the spacenum table I have a yes/no field for assigned.
I have a field in the main parking table called spacenum
Yes I would like it to do that so I can report on who has what spot
I dont think it does at this point nothing updated from combo box to
spacenum table
I am not sure what you mean by bound control?

Thanx for all your help it is much appreciated. Fred


Klatuu wrote:
I don't know what you mean by the spacenum comb alread in use. Isn't this
the combo you use to show unassigned spaces from the table that has the space
numbers?

We can make it work. Tell me a little about your tables.
What fields are in the spacenum table?

Do you have a field in the spacenum table that tells whether the number is
assigned or not?

What field in the main parking table is used to store the assigned space
number?

Do you want the combo box to populate the spacenum field in the main parking
table when you choose a space?

Does it also need to update anything in the spacenum table?

Is the Comb Box a bound control?

If you can answer these questions, I can make some recommendations on how it
will work.

Also, there appears to be a syntax error in the query you posted.

(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);

The way it is written, it is looking for a field named [Main Parking Talbe]
in a tabled named Assigned.
"Fred" wrote:

A couple of problems with this, One my spacenum combo box is already in
use plus I have my space numbers in a different table. I cant get the
assigned check box to populate both tables. I am trying to use a query
like the following:

SELECT SpaceNum
FROM Spacenum
Where SpaceNum Not IN
(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);
This does not work Thanx for the help
Klatuu wrote:
Okay, great. That makes it really easy. Hopefully, you have a field in your
parking space table that tells whether or not it is assigned. If you don't,
you need one. For this, I would suggest a Boolean (yes/no) Field. For
example purposes, lets call it [SPACE_AVAILABLE] and True means it is not
assigned.

Make the Row Source for the combo a query filtered on that field:
SELECT [SPACE_ID] FROM tblParkingSpaces WHERE [SPACE_AVAILABLE]

This will cause only unassigned spaces to be presented in the combo.

What I would is write a Sub in the form module and call it from the After
Update events of the Combo Box and the Check Box.

Private Sub UpdateSpace()
Dim blnAssign As Boolean

blnAssign = Screen.ActiveControl.Name = "chkUnAssign"
Me.chkUnAssign = blnAssign
CurrentDb.Execute "UPDATE tblParkingSpaces SET [SPACE_AVAILABLE] = " &
blnAssign & ";", dbFailOnError
Me.cboSpaceAvailable.Requery
End Function.

Then in the After Update of the Combo Box and the Check Box:
Call UpdateSpace

"Fred" wrote:

I am assigning parking spaces and one is assigned I want that one to be
pulled from the drop down not from the field. When the person leaves I
unassign the parking spot and I need it to reappear in the drop down.
Thanx Fred
Klatuu wrote:
This number you assign - When you check the box to un assign the number, is
the number removed from the record or do you have a field in the record that
tells you whether the number is assigned?

If you un assign a number, then later deside to reassign a number to the
record at a later date, does in get a new number or is it reassigned the
original number?

"Fred" wrote:

Thanx Klatuu
I also need to know the code to remove the numbers and put the numbers
back in. Sorry for the confusion. Fred
Klatuu wrote:
You will need to requery the combo in two places.
1. In the After Update event of the control where you assign the number.
2. In the After Update event of the check box.

"Fred" wrote:

Hi Folks,
I have created a combo box full of numbers. I also have a check box
called assigned. When I create a record with a number that is assigned
I would it to be taken out of the combo box list and then when I
uncheck the box I would like it to reappear in the list. Where is the
best place to put my requery statement? Thanx for all your help Fred

It gives me an end of statement error and syntax error on
(Me.cboBuilding AND [ParkingType] = Me.cboParkingType AND) this line.
Also dont we want to set assigned to 1
Klatuu wrote:
Hard to tell without the tables to test it with. It looks okay, but if you
are getting a syntax error, obviously it is not.
Are the table name and field names correct?

One thing we may try is changing it to a RunSQL and see that makes a
difference. The syntax is a little different and you don't have to have the
object references outside the qoutes

Docmd.RunSQL "UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
Me.cboBuilding AND [ParkingType] = Me.cboParkingType AND
[SpaceNum] = Me.cboSpaceNum"

"Fred" wrote:

I got a syntax error with the following line highlighted:
CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE
[Building] =
Klatuu wrote:
It doesn't matter what the database is named. CurrentDb references the
database you are working in. The Execute method executes an SQL statement.
What error are you getting?

I think the problem is, I forgot to filter the spacenum table for the update
and it may have a syntax error. See if this helps:

CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
'" & Me.cboBuilding & "' AND [ParkingType] = '" & Me.cboParkingType & "' AND
[SpaceNum] = '" & Me.cboSpaceNum & "';"), dbFailOnError

Note the above assumes all fields exept Assigned are text fields. If any
are numeric fields, then take out the single quotes around them. For
example, if SpaceNum is numeric, it would be like this:

CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
'" & Me.cboBuilding & "' AND [ParkingType] = '" & Me.cboParkingType & "' AND
[SpaceNum] = " & Me.cboSpaceNum & ";"), dbFailOnError


"Fred" wrote:

since my form is based on the main parking table I just put as my
control source the spcenum field and that populates fine. The Current
DB.Execute statement is not working. Right now the database name is
DB.Eventually it will be called parking database Thanx Fred
Klatuu wrote:
The Main Parking table will be updated by having the value from the combo
(cblSpaceNum) put into the control txtSpaceNum if txtSpaceNum is bould to the
SpaceNum field in the Main Parking Table. The SpaceNum table is updated by
the CurrentDb.Execute statement.

Can you post back which line is not working? I am not sure what you mean by
second line.

Also, One thing I left out. To get the combos to show the correct list, you
have to requery cboParkingType and cboSpaceNumber in the After Update event
of cboBuilding and requery cboSpaceNumber in the After Update event of
cboParkingType. You do it like this:

for cboBuilding:
Me.cboParkingType.Requery
Me.cboSpaceNum.Requery
for cboParkingType
Me.cboSpaceNum.Requery

Pardon the omission.


"Fred" wrote:

Okay I only have 2 buildings so I created a combo box and put a value
list with these 2 values then I created a combo box for parking type
and I put in its row source this query SELECT DISTINCT ParkingType FROM
SpaceNum WHERE (((SpaceNum.Building)=Forms![FOB2 New Parking Allocation
Form]!Building)) UNION select distinct null FROM SpaceNum; Then in the
spacenum combo box I put the following query in the row source, SELECT
DISTINCT SpaceNum.SpaceNum FROM SpaceNUM WHERE
(((SpaceNum.ParkingType)=Forms![FOB2 New Parking Allocation
Form]![Parking Type])) UNION select distinct null FROM SpaceNum;
This all works to perfection. I really need to know how to make the
assigned check box populate 2 tables my main parking table and my
spacenum table. The code you sent me did not work. It did not like the
second line. Thanx for all your help
Klatuu wrote:
Here are some ideas that may help.
I see you have Parking Type and building. It would make life easy for the
users to find an appropriate space if you use what is known as Cascading
Combos. That is, filtering a combo based on another combo. For example,
The first thing a user would want to choose would be the building. Then the
would want to know what parking type to assign, and last, choose a space from
that list. So here is how you do that. You have 3 combos. The first
(cboBldg) would have a row source of the buildings in your table:
SELECT Building FROM tlbSpaceNum;
The next wouldl select a type (cboParkType) available at that building
SELECT [Parking Type] FROM tblSpaceNum WHERE Building = Me.cboBldg AND
NOT Assigned
Then, to choose a space (cboSpaceNum)
SELECT SpaceNum FROM tblSpaceNum WHERE Building = Me.cboBldg AND
[Parking Type] = Me.cboParkType AND NOT Assigned

These combos should all be unbound controls. An unbound control is one that
does not have a field from the form's recordsource as its Control Souce
property. That is how Access knows what values to display in which controls
and what field to update with the value.

Now, how to deal with keeping the Assigned field up to date in the SpaceNum
table.

First, we have to get the value from the combo into the control that has the
spacenum in the Main Parking Table (txtSpaceNum). This you do in the After
Upate event of cboSpaceNum. There are three things to do. First, put the
value in the control, update the Assigned field in the SpaceNum table, and be
sure the assigned check box (chkAssigned) is checked

Me.txtSpaceNUm = Me.cboSpaceNum
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = True & ";",
dbFailOnError
Me.chkAssigned = True

Now to the original question on unchecking the Assigned check box. We do
exactly the reverse. Use the After Update event of the check box.

If Me.chkAssigned = False Then
Me.txtSpaceNum = Null
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = False & ";",
dbFailOnError
End If

This is all air code, so it may take some tweeking or debugging. Let me
know how it works out.


"Fred" wrote:

Right now that spacenum combo is pulling all the spaces from the
spacenum table based on the parking type(handicap, Lot A, Government,
Executive).
In the Spacenum table I have these fieldsSpacenum, Parking Type,
Building, Assigned)
Yes in the spacenum table I have a yes/no field for assigned.
I have a field in the main parking table called spacenum
Yes I would like it to do that so I can report on who has what spot
I dont think it does at this point nothing updated from combo box to
spacenum table
I am not sure what you mean by bound control?

Thanx for all your help it is much appreciated. Fred


Klatuu wrote:
I don't know what you mean by the spacenum comb alread in use. Isn't this
the combo you use to show unassigned spaces from the table that has the space
numbers?

We can make it work. Tell me a little about your tables.
What fields are in the spacenum table?

Do you have a field in the spacenum table that tells whether the number is
assigned or not?

What field in the main parking table is used to store the assigned space
number?

Do you want the combo box to populate the spacenum field in the main parking
table when you choose a space?

Does it also need to update anything in the spacenum table?

Is the Comb Box a bound control?

If you can answer these questions, I can make some recommendations on how it
will work.

Also, there appears to be a syntax error in the query you posted.

(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);

The way it is written, it is looking for a field named [Main Parking Talbe]
in a tabled named Assigned.
"Fred" wrote:

A couple of problems with this, One my spacenum combo box is already in
use plus I have my space numbers in a different table. I cant get the
assigned check box to populate both tables. I am trying to use a query
like the following:

SELECT SpaceNum
FROM Spacenum
Where SpaceNum Not IN
(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);
This does not work Thanx for the help
Klatuu wrote:
Okay, great. That makes it really easy. Hopefully, you have a field in your
parking space table that tells whether or not it is assigned. If you don't,
you need one. For this, I would suggest a Boolean (yes/no) Field. For
example purposes, lets call it [SPACE_AVAILABLE] and True means it is not
assigned.

Make the Row Source for the combo a query filtered on that field:
SELECT [SPACE_ID] FROM tblParkingSpaces WHERE [SPACE_AVAILABLE]

This will cause only unassigned spaces to be presented in the combo.

What I would is write a Sub in the form module and call it from the After
Update events of the Combo Box and the Check Box.

Private Sub UpdateSpace()
Dim blnAssign As Boolean

blnAssign = Screen.ActiveControl.Name = "chkUnAssign"
Me.chkUnAssign = blnAssign
CurrentDb.Execute "UPDATE tblParkingSpaces SET [SPACE_AVAILABLE] = " &
blnAssign & ";", dbFailOnError
Me.cboSpaceAvailable.Requery
End Function.

Then in the After Update of the Combo Box and the Check Box:
Call UpdateSpace

"Fred" wrote:

I am assigning parking spaces and one is assigned I want that one to be
pulled from the drop down not from the field. When the person leaves I
unassign the parking spot and I need it to reappear in the drop down.
Thanx Fred
Klatuu wrote:
This number you assign - When you check the box to un assign the number, is
the number removed from the record or do you have a field in the record that
tells you whether the number is assigned?

If you un assign a number, then later deside to reassign a number to the
record at a later date, does in get a new number or is it reassigned the
original number?

"Fred" wrote:

Thanx Klatuu
I also need to know the code to remove the numbers and put the numbers
back in. Sorry for the confusion. Fred
Klatuu wrote:
You will need to requery the combo in two places.
1. In the After Update event of the control where you assign the number.
2. In the After Update event of the check box.

"Fred" wrote:

Hi Folks,
I have created a combo box full of numbers. I also have a check box
called assigned. When I create a record with a number that is assigned
I would it to be taken out of the combo box list and then when I
uncheck the box I would like it to reappear in the list. Where is the
best place to put my requery statement? Thanx for all your help Fred

It depends on which place we want to execute the line. When we assign the
number in the After Update of the Combo, we want to send -1 (True) to show it
assigned. If it is in the After Update of the check box when we unassign, we
want to send 0 (FalseO
Try changing Me. to Forms!FormName!

"Fred" wrote:

It gives me an end of statement error and syntax error on
(Me.cboBuilding AND [ParkingType] = Me.cboParkingType AND) this line.
Also dont we want to set assigned to 1
Klatuu wrote:
Hard to tell without the tables to test it with. It looks okay, but if you
are getting a syntax error, obviously it is not.
Are the table name and field names correct?

One thing we may try is changing it to a RunSQL and see that makes a
difference. The syntax is a little different and you don't have to have the
object references outside the qoutes

Docmd.RunSQL "UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
Me.cboBuilding AND [ParkingType] = Me.cboParkingType AND
[SpaceNum] = Me.cboSpaceNum"

"Fred" wrote:

I got a syntax error with the following line highlighted:
CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE
[Building] =
Klatuu wrote:
It doesn't matter what the database is named. CurrentDb references the
database you are working in. The Execute method executes an SQL statement.
What error are you getting?

I think the problem is, I forgot to filter the spacenum table for the update
and it may have a syntax error. See if this helps:

CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
'" & Me.cboBuilding & "' AND [ParkingType] = '" & Me.cboParkingType & "' AND
[SpaceNum] = '" & Me.cboSpaceNum & "';"), dbFailOnError

Note the above assumes all fields exept Assigned are text fields. If any
are numeric fields, then take out the single quotes around them. For
example, if SpaceNum is numeric, it would be like this:

CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
'" & Me.cboBuilding & "' AND [ParkingType] = '" & Me.cboParkingType & "' AND
[SpaceNum] = " & Me.cboSpaceNum & ";"), dbFailOnError


"Fred" wrote:

since my form is based on the main parking table I just put as my
control source the spcenum field and that populates fine. The Current
DB.Execute statement is not working. Right now the database name is
DB.Eventually it will be called parking database Thanx Fred
Klatuu wrote:
The Main Parking table will be updated by having the value from the combo
(cblSpaceNum) put into the control txtSpaceNum if txtSpaceNum is bould to the
SpaceNum field in the Main Parking Table. The SpaceNum table is updated by
the CurrentDb.Execute statement.

Can you post back which line is not working? I am not sure what you mean by
second line.

Also, One thing I left out. To get the combos to show the correct list, you
have to requery cboParkingType and cboSpaceNumber in the After Update event
of cboBuilding and requery cboSpaceNumber in the After Update event of
cboParkingType. You do it like this:

for cboBuilding:
Me.cboParkingType.Requery
Me.cboSpaceNum.Requery
for cboParkingType
Me.cboSpaceNum.Requery

Pardon the omission.


"Fred" wrote:

Okay I only have 2 buildings so I created a combo box and put a value
list with these 2 values then I created a combo box for parking type
and I put in its row source this query SELECT DISTINCT ParkingType FROM
SpaceNum WHERE (((SpaceNum.Building)=Forms![FOB2 New Parking Allocation
Form]!Building)) UNION select distinct null FROM SpaceNum; Then in the
spacenum combo box I put the following query in the row source, SELECT
DISTINCT SpaceNum.SpaceNum FROM SpaceNUM WHERE
(((SpaceNum.ParkingType)=Forms![FOB2 New Parking Allocation
Form]![Parking Type])) UNION select distinct null FROM SpaceNum;
This all works to perfection. I really need to know how to make the
assigned check box populate 2 tables my main parking table and my
spacenum table. The code you sent me did not work. It did not like the
second line. Thanx for all your help
Klatuu wrote:
Here are some ideas that may help.
I see you have Parking Type and building. It would make life easy for the
users to find an appropriate space if you use what is known as Cascading
Combos. That is, filtering a combo based on another combo. For example,
The first thing a user would want to choose would be the building. Then the
would want to know what parking type to assign, and last, choose a space from
that list. So here is how you do that. You have 3 combos. The first
(cboBldg) would have a row source of the buildings in your table:
SELECT Building FROM tlbSpaceNum;
The next wouldl select a type (cboParkType) available at that building
SELECT [Parking Type] FROM tblSpaceNum WHERE Building = Me.cboBldg AND
NOT Assigned
Then, to choose a space (cboSpaceNum)
SELECT SpaceNum FROM tblSpaceNum WHERE Building = Me.cboBldg AND
[Parking Type] = Me.cboParkType AND NOT Assigned

These combos should all be unbound controls. An unbound control is one that
does not have a field from the form's recordsource as its Control Souce
property. That is how Access knows what values to display in which controls
and what field to update with the value.

Now, how to deal with keeping the Assigned field up to date in the SpaceNum
table.

First, we have to get the value from the combo into the control that has the
spacenum in the Main Parking Table (txtSpaceNum). This you do in the After
Upate event of cboSpaceNum. There are three things to do. First, put the
value in the control, update the Assigned field in the SpaceNum table, and be
sure the assigned check box (chkAssigned) is checked

Me.txtSpaceNUm = Me.cboSpaceNum
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = True & ";",
dbFailOnError
Me.chkAssigned = True

Now to the original question on unchecking the Assigned check box. We do
exactly the reverse. Use the After Update event of the check box.

If Me.chkAssigned = False Then
Me.txtSpaceNum = Null
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = False & ";",
dbFailOnError
End If

This is all air code, so it may take some tweeking or debugging. Let me
know how it works out.


"Fred" wrote:

Right now that spacenum combo is pulling all the spaces from the
spacenum table based on the parking type(handicap, Lot A, Government,
Executive).
In the Spacenum table I have these fieldsSpacenum, Parking Type,
Building, Assigned)
Yes in the spacenum table I have a yes/no field for assigned.
I have a field in the main parking table called spacenum
Yes I would like it to do that so I can report on who has what spot
I dont think it does at this point nothing updated from combo box to
spacenum table
I am not sure what you mean by bound control?

Thanx for all your help it is much appreciated. Fred


Klatuu wrote:
I don't know what you mean by the spacenum comb alread in use. Isn't this
the combo you use to show unassigned spaces from the table that has the space
numbers?

We can make it work. Tell me a little about your tables.
What fields are in the spacenum table?

Do you have a field in the spacenum table that tells whether the number is
assigned or not?

What field in the main parking table is used to store the assigned space
number?

Do you want the combo box to populate the spacenum field in the main parking
table when you choose a space?

Does it also need to update anything in the spacenum table?

Is the Comb Box a bound control?

If you can answer these questions, I can make some recommendations on how it
will work.

Also, there appears to be a syntax error in the query you posted.

(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);

The way it is written, it is looking for a field named [Main Parking Talbe]
in a tabled named Assigned.
"Fred" wrote:

A couple of problems with this, One my spacenum combo box is already in
use plus I have my space numbers in a different table. I cant get the
assigned check box to populate both tables. I am trying to use a query
like the following:

SELECT SpaceNum
FROM Spacenum
Where SpaceNum Not IN
(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);
This does not work Thanx for the help
Klatuu wrote:
Okay, great. That makes it really easy. Hopefully, you have a field in your
parking space table that tells whether or not it is assigned. If you don't,
you need one. For this, I would suggest a Boolean (yes/no) Field. For
example purposes, lets call it [SPACE_AVAILABLE] and True means it is not
assigned.

Make the Row Source for the combo a query filtered on that field:
SELECT [SPACE_ID] FROM tblParkingSpaces WHERE [SPACE_AVAILABLE]

This will cause only unassigned spaces to be presented in the combo.

What I would is write a Sub in the form module and call it from the After
Update events of the Combo Box and the Check Box.

Private Sub UpdateSpace()
Dim blnAssign As Boolean

blnAssign = Screen.ActiveControl.Name = "chkUnAssign"
Me.chkUnAssign = blnAssign
CurrentDb.Execute "UPDATE tblParkingSpaces SET [SPACE_AVAILABLE] = " &
blnAssign & ";", dbFailOnError
Me.cboSpaceAvailable.Requery
End Function.

Then in the After Update of the Combo Box and the Check Box:
Call UpdateSpace

"Fred" wrote:

I am assigning parking spaces and one is assigned I want that one to be
pulled from the drop down not from the field. When the person leaves I
unassign the parking spot and I need it to reappear in the drop down.
Thanx Fred
Klatuu wrote:
This number you assign - When you check the box to un assign the number, is
the number removed from the record or do you have a field in the record that
tells you whether the number is assigned?

If you un assign a number, then later deside to reassign a number to the
record at a later date, does in get a new number or is it reassigned the
original number?

"Fred" wrote:

Thanx Klatuu
I also need to know the code to remove the numbers and put the numbers
back in. Sorry for the confusion. Fred
Klatuu wrote:
You will need to requery the combo in two places.
1. In the After Update event of the control where you assign the number.
2. In the After Update event of the check box.

"Fred" wrote:

Hi Folks,
I have created a combo box full of numbers. I also have a check box
called assigned. When I create a record with a number that is assigned
I would it to be taken out of the combo box list and then when I
uncheck the box I would like it to reappear in the list. Where is the
best place to put my requery statement? Thanx for all your help Fred

It still doesnt like it and also at the end it is looking for a end of
expression
Klatuu wrote:
It depends on which place we want to execute the line. When we assign the
number in the After Update of the Combo, we want to send -1 (True) to show it
assigned. If it is in the After Update of the check box when we unassign, we
want to send 0 (FalseO
Try changing Me. to Forms!FormName!

"Fred" wrote:

It gives me an end of statement error and syntax error on
(Me.cboBuilding AND [ParkingType] = Me.cboParkingType AND) this line.
Also dont we want to set assigned to 1
Klatuu wrote:
Hard to tell without the tables to test it with. It looks okay, but if you
are getting a syntax error, obviously it is not.
Are the table name and field names correct?

One thing we may try is changing it to a RunSQL and see that makes a
difference. The syntax is a little different and you don't have to have the
object references outside the qoutes

Docmd.RunSQL "UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
Me.cboBuilding AND [ParkingType] = Me.cboParkingType AND
[SpaceNum] = Me.cboSpaceNum"

"Fred" wrote:

I got a syntax error with the following line highlighted:
CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE
[Building] =
Klatuu wrote:
It doesn't matter what the database is named. CurrentDb references the
database you are working in. The Execute method executes an SQL statement.
What error are you getting?

I think the problem is, I forgot to filter the spacenum table for the update
and it may have a syntax error. See if this helps:

CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
'" & Me.cboBuilding & "' AND [ParkingType] = '" & Me.cboParkingType & "' AND
[SpaceNum] = '" & Me.cboSpaceNum & "';"), dbFailOnError

Note the above assumes all fields exept Assigned are text fields. If any
are numeric fields, then take out the single quotes around them. For
example, if SpaceNum is numeric, it would be like this:

CurrentDb.Execute("UPDATE tblSpaceNum SET [Assigned] = 0 WHERE [Building] =
'" & Me.cboBuilding & "' AND [ParkingType] = '" & Me.cboParkingType & "' AND
[SpaceNum] = " & Me.cboSpaceNum & ";"), dbFailOnError


"Fred" wrote:

since my form is based on the main parking table I just put as my
control source the spcenum field and that populates fine. The Current
DB.Execute statement is not working. Right now the database name is
DB.Eventually it will be called parking database Thanx Fred
Klatuu wrote:
The Main Parking table will be updated by having the value from the combo
(cblSpaceNum) put into the control txtSpaceNum if txtSpaceNum is bould to the
SpaceNum field in the Main Parking Table. The SpaceNum table is updated by
the CurrentDb.Execute statement.

Can you post back which line is not working? I am not sure what you mean by
second line.

Also, One thing I left out. To get the combos to show the correct list, you
have to requery cboParkingType and cboSpaceNumber in the After Update event
of cboBuilding and requery cboSpaceNumber in the After Update event of
cboParkingType. You do it like this:

for cboBuilding:
Me.cboParkingType.Requery
Me.cboSpaceNum.Requery
for cboParkingType
Me.cboSpaceNum.Requery

Pardon the omission.


"Fred" wrote:

Okay I only have 2 buildings so I created a combo box and put a value
list with these 2 values then I created a combo box for parking type
and I put in its row source this query SELECT DISTINCT ParkingType FROM
SpaceNum WHERE (((SpaceNum.Building)=Forms![FOB2 New Parking Allocation
Form]!Building)) UNION select distinct null FROM SpaceNum; Then in the
spacenum combo box I put the following query in the row source, SELECT
DISTINCT SpaceNum.SpaceNum FROM SpaceNUM WHERE
(((SpaceNum.ParkingType)=Forms![FOB2 New Parking Allocation
Form]![Parking Type])) UNION select distinct null FROM SpaceNum;
This all works to perfection. I really need to know how to make the
assigned check box populate 2 tables my main parking table and my
spacenum table. The code you sent me did not work. It did not like the
second line. Thanx for all your help
Klatuu wrote:
Here are some ideas that may help.
I see you have Parking Type and building. It would make life easy for the
users to find an appropriate space if you use what is known as Cascading
Combos. That is, filtering a combo based on another combo. For example,
The first thing a user would want to choose would be the building. Then the
would want to know what parking type to assign, and last, choose a space from
that list. So here is how you do that. You have 3 combos. The first
(cboBldg) would have a row source of the buildings in your table:
SELECT Building FROM tlbSpaceNum;
The next wouldl select a type (cboParkType) available at that building
SELECT [Parking Type] FROM tblSpaceNum WHERE Building = Me.cboBldg AND
NOT Assigned
Then, to choose a space (cboSpaceNum)
SELECT SpaceNum FROM tblSpaceNum WHERE Building = Me.cboBldg AND
[Parking Type] = Me.cboParkType AND NOT Assigned

These combos should all be unbound controls. An unbound control is one that
does not have a field from the form's recordsource as its Control Souce
property. That is how Access knows what values to display in which controls
and what field to update with the value.

Now, how to deal with keeping the Assigned field up to date in the SpaceNum
table.

First, we have to get the value from the combo into the control that has the
spacenum in the Main Parking Table (txtSpaceNum). This you do in the After
Upate event of cboSpaceNum. There are three things to do. First, put the
value in the control, update the Assigned field in the SpaceNum table, and be
sure the assigned check box (chkAssigned) is checked

Me.txtSpaceNUm = Me.cboSpaceNum
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = True & ";",
dbFailOnError
Me.chkAssigned = True

Now to the original question on unchecking the Assigned check box. We do
exactly the reverse. Use the After Update event of the check box.

If Me.chkAssigned = False Then
Me.txtSpaceNum = Null
CurrentDb.Execute "UPDATE tblSpaceNum SET [Assigned] = False & ";",
dbFailOnError
End If

This is all air code, so it may take some tweeking or debugging. Let me
know how it works out.


"Fred" wrote:

Right now that spacenum combo is pulling all the spaces from the
spacenum table based on the parking type(handicap, Lot A, Government,
Executive).
In the Spacenum table I have these fieldsSpacenum, Parking Type,
Building, Assigned)
Yes in the spacenum table I have a yes/no field for assigned.
I have a field in the main parking table called spacenum
Yes I would like it to do that so I can report on who has what spot
I dont think it does at this point nothing updated from combo box to
spacenum table
I am not sure what you mean by bound control?

Thanx for all your help it is much appreciated. Fred


Klatuu wrote:
I don't know what you mean by the spacenum comb alread in use. Isn't this
the combo you use to show unassigned spaces from the table that has the space
numbers?

We can make it work. Tell me a little about your tables.
What fields are in the spacenum table?

Do you have a field in the spacenum table that tells whether the number is
assigned or not?

What field in the main parking table is used to store the assigned space
number?

Do you want the combo box to populate the spacenum field in the main parking
table when you choose a space?

Does it also need to update anything in the spacenum table?

Is the Comb Box a bound control?

If you can answer these questions, I can make some recommendations on how it
will work.

Also, there appears to be a syntax error in the query you posted.

(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);

The way it is written, it is looking for a field named [Main Parking Talbe]
in a tabled named Assigned.
"Fred" wrote:

A couple of problems with this, One my spacenum combo box is already in
use plus I have my space numbers in a different table. I cant get the
assigned check box to populate both tables. I am trying to use a query
like the following:

SELECT SpaceNum
FROM Spacenum
Where SpaceNum Not IN
(Select SpaceNum From [Main Parking Table] Where Assigned.[Main Parking
Table]= false);
This does not work Thanx for the help
Klatuu wrote:
Okay, great. That makes it really easy. Hopefully, you have a field in your
parking space table that tells whether or not it is assigned. If you don't,
you need one. For this, I would suggest a Boolean (yes/no) Field. For
example purposes, lets call it [SPACE_AVAILABLE] and True means it is not
assigned.

Make the Row Source for the combo a query filtered on that field:
SELECT [SPACE_ID] FROM tblParkingSpaces WHERE [SPACE_AVAILABLE]

This will cause only unassigned spaces to be presented in the combo.

What I would is write a Sub in the form module and call it from the After
Update events of the Combo Box and the Check Box.

Private Sub UpdateSpace()
Dim blnAssign As Boolean

blnAssign = Screen.ActiveControl.Name = "chkUnAssign"
Me.chkUnAssign = blnAssign
CurrentDb.Execute "UPDATE tblParkingSpaces SET [SPACE_AVAILABLE] = " &
blnAssign & ";", dbFailOnError
Me.cboSpaceAvailable.Requery
End Function.

Then in the After Update of the Combo Box and the Check Box:
Call UpdateSpace

"Fred" wrote:

I am assigning parking spaces and one is assigned I want that one to be
pulled from the drop down not from the field. When the person leaves I
unassign the parking spot and I need it to reappear in the drop down.
Thanx Fred
Klatuu wrote:
This number you assign - When you check the box to un assign the number, is
the number removed from the record or do you have a field in the record that
tells you whether the number is assigned?

If you un assign a number, then later deside to reassign a number to the
record at a later date, does in get a new number or is it reassigned the
original number?

"Fred" wrote:

Thanx Klatuu
I also need to know the code to remove the numbers and put the numbers
back in. Sorry for the confusion. Fred
Klatuu wrote:
You will need to requery the combo in two places.
1. In the After Update event of the control where you assign the number.
2. In the After Update event of the check box.

"Fred" wrote:

Hi Folks,
I have created a combo box full of numbers. I also have a check box
called assigned. When I create a record with a number that is assigned
I would it to be taken out of the combo box list and then when I
uncheck the box I would like it to reappear in the list. Where is the
best place to put my requery statement? Thanx for all your help Fred