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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|