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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Combo Box question
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 |
#13
|
|||
|
|||
Combo Box question
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 |
#14
|
|||
|
|||
Combo Box question
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 |
#15
|
|||
|
|||
Combo Box question
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 |
#16
|
|||
|
|||
Combo Box question
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 |
#17
|
|||
|
|||
Combo Box question
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 |
#18
|
|||
|
|||
Combo Box question
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 |
#19
|
|||
|
|||
Combo Box question
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 |
|
Thread Tools | |
Display Modes | |
|
|