A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Combo Box question



 
 
Thread Tools Display Modes
  #1  
Old September 25th, 2006, 03:11 PM posted to microsoft.public.access.forms
Fred
external usenet poster
 
Posts: 16
Default Combo Box question

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

  #2  
Old September 25th, 2006, 03:28 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default Combo Box question

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


  #3  
Old September 25th, 2006, 03:48 PM posted to microsoft.public.access.forms
Fred
external usenet poster
 
Posts: 16
Default Combo Box question

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



  #4  
Old September 25th, 2006, 03:57 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default Combo Box question

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




  #5  
Old September 25th, 2006, 04:58 PM posted to microsoft.public.access.forms
Fred
external usenet poster
 
Posts: 16
Default Combo Box question

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





  #6  
Old September 25th, 2006, 05:43 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default Combo Box question

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






  #7  
Old September 25th, 2006, 06:29 PM posted to microsoft.public.access.forms
Fred
external usenet poster
 
Posts: 16
Default Combo Box question

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







  #8  
Old September 25th, 2006, 07:33 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default Combo Box question

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








  #9  
Old September 27th, 2006, 12:48 PM posted to microsoft.public.access.forms
Fred
external usenet poster
 
Posts: 16
Default Combo Box question

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









  #10  
Old September 27th, 2006, 02:42 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default Combo Box question

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










 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.