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
|
|||
|
|||
Modify Query on the fly
Hi,
I have this created a query for my "combo box" to list out related columns where QtrID is empty. Is it possible to modify the same query via vba code? What I would like to do is to change the "Is Null" to "not Is Null" to list out columns where QTRID is not empty. Below is the query text: SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name FROM Personnel WHERE ((Not (Personnel.QtrID) Is Null)) ORDER BY Personnel.PersonnelID; Thanks, Garry |
#2
|
|||
|
|||
Modify Query on the fly
This may assist you.
In the Event Procedure of the Combo box: Dim qdfSQL as QueryDef Set qdfSQL = CurrentDb.QueryDefs("Your Query Name That You Created") qdfSQL.SQL = "SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name " _ & "FROM Personnel " _ & "WHERE ((Not (Personnel.QtrID) Is Null)) " _ & "ORDER BY Personnel.PersonnelID;" How the syntax works: 1. You declare a query definition (qdf) variable. 2. You assign qdf variable to the query that you created earlier. 3. You set the SQL to the query. As you mentioned, ensure that your combo box's "Control Source" is set to the query. Based on your object selection, change the "Is Null" to "Is Not Null". -- Maha Aruppthan Pappan Nacap Asia Pacific "Garu" wrote: Hi, I have this created a query for my "combo box" to list out related columns where QtrID is empty. Is it possible to modify the same query via vba code? What I would like to do is to change the "Is Null" to "not Is Null" to list out columns where QTRID is not empty. Below is the query text: SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name FROM Personnel WHERE ((Not (Personnel.QtrID) Is Null)) ORDER BY Personnel.PersonnelID; Thanks, Garry |
#3
|
|||
|
|||
Modify Query on the fly
Thanks Maha, I put the procedure on "On Got Focus" Event of the Combo box.
The Criteria in the Query changes between "Not Is Null" and "Not Is Not Null" but the value list doesn't change not until I close the form then reopen it. Here's the procedu Private Sub txtPersonnelID_GotFocus() Dim qdfSQL As QueryDef Set qdfSQL = CurrentDb.QueryDefs("qryP") If IsNull(ChangeTYpe) Then Me.txtPersonnelID.Locked = True Else Me.txtPersonnelID.Locked = False End If If ChangeType = "Occupation" Then qdfSQL.SQL = "SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name " _ & "FROM Personnel " _ & "WHERE ((Not (Personnel.QtrID) Is Null)) " _ & "ORDER BY Personnel.PersonnelID;" Else: ChangeType = "Vacation" qdfSQL.SQL = "SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name " _ & "FROM Personnel " _ & "WHERE ((Not (Personnel.QtrID) Is Not Null)) " _ & "ORDER BY Personnel.PersonnelID;" End If End Sub "Maha Arupputhan Pappan" wrote in message news This may assist you. In the Event Procedure of the Combo box: Dim qdfSQL as QueryDef Set qdfSQL = CurrentDb.QueryDefs("Your Query Name That You Created") qdfSQL.SQL = "SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name " _ & "FROM Personnel " _ & "WHERE ((Not (Personnel.QtrID) Is Null)) " _ & "ORDER BY Personnel.PersonnelID;" How the syntax works: 1. You declare a query definition (qdf) variable. 2. You assign qdf variable to the query that you created earlier. 3. You set the SQL to the query. As you mentioned, ensure that your combo box's "Control Source" is set to the query. Based on your object selection, change the "Is Null" to "Is Not Null". -- Maha Aruppthan Pappan Nacap Asia Pacific "Garu" wrote: Hi, I have this created a query for my "combo box" to list out related columns where QtrID is empty. Is it possible to modify the same query via vba code? What I would like to do is to change the "Is Null" to "not Is Null" to list out columns where QTRID is not empty. Below is the query text: SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name FROM Personnel WHERE ((Not (Personnel.QtrID) Is Null)) ORDER BY Personnel.PersonnelID; Thanks, Garry |
#4
|
|||
|
|||
Modify Query on the fly
Include Me!txtPersonnelID.Requery after the 2nd EndIf loop and before the End
Sub. If no good. Then I suggest relocating the procedure to either "After Update" or "On Change" event. Ensure to include the Me!txtPersonnelID.Requery statement in any of the Event. -- Maha Aruppthan Pappan Nacap Asia Pacific "Garu" wrote: Thanks Maha, I put the procedure on "On Got Focus" Event of the Combo box. The Criteria in the Query changes between "Not Is Null" and "Not Is Not Null" but the value list doesn't change not until I close the form then reopen it. Here's the procedu Private Sub txtPersonnelID_GotFocus() Dim qdfSQL As QueryDef Set qdfSQL = CurrentDb.QueryDefs("qryP") If IsNull(ChangeTYpe) Then Me.txtPersonnelID.Locked = True Else Me.txtPersonnelID.Locked = False End If If ChangeType = "Occupation" Then qdfSQL.SQL = "SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name " _ & "FROM Personnel " _ & "WHERE ((Not (Personnel.QtrID) Is Null)) " _ & "ORDER BY Personnel.PersonnelID;" Else: ChangeType = "Vacation" qdfSQL.SQL = "SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name " _ & "FROM Personnel " _ & "WHERE ((Not (Personnel.QtrID) Is Not Null)) " _ & "ORDER BY Personnel.PersonnelID;" End If End Sub "Maha Arupputhan Pappan" wrote in message news This may assist you. In the Event Procedure of the Combo box: Dim qdfSQL as QueryDef Set qdfSQL = CurrentDb.QueryDefs("Your Query Name That You Created") qdfSQL.SQL = "SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name " _ & "FROM Personnel " _ & "WHERE ((Not (Personnel.QtrID) Is Null)) " _ & "ORDER BY Personnel.PersonnelID;" How the syntax works: 1. You declare a query definition (qdf) variable. 2. You assign qdf variable to the query that you created earlier. 3. You set the SQL to the query. As you mentioned, ensure that your combo box's "Control Source" is set to the query. Based on your object selection, change the "Is Null" to "Is Not Null". -- Maha Aruppthan Pappan Nacap Asia Pacific "Garu" wrote: Hi, I have this created a query for my "combo box" to list out related columns where QtrID is empty. Is it possible to modify the same query via vba code? What I would like to do is to change the "Is Null" to "not Is Null" to list out columns where QTRID is not empty. Below is the query text: SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name FROM Personnel WHERE ((Not (Personnel.QtrID) Is Null)) ORDER BY Personnel.PersonnelID; Thanks, Garry |
#5
|
|||
|
|||
Modify Query on the fly
Assuming that the combobox is on a form, I would add a button to switch back
and forth. In the click event of the button, you would have the following code Private Sub somebutton_Click() Dim StrSQL as String, strWhere As String Static strWhere as string If strWhere = "Personnel.QtrID Is Null" Then strWhere = "Personnel.QtrID Is Not Null" ELSE strWhere = "Personnel.QtrID Is Null" END IF strSQL = "SELECT PersonnelID, ArmyNumber, Rank, Personnel.[Name]" & _ " FROM Personnel WHERE " & strWhere & _ " ORDER BY Personnel.PersonnelID" Me.NameOftheComboBoxControl.RowSource = strSQL End Sub John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Garu wrote: Hi, I have this created a query for my "combo box" to list out related columns where QtrID is empty. Is it possible to modify the same query via vba code? What I would like to do is to change the "Is Null" to "not Is Null" to list out columns where QTRID is not empty. Below is the query text: SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name FROM Personnel WHERE ((Not (Personnel.QtrID) Is Null)) ORDER BY Personnel.PersonnelID; Thanks, Garry |
#6
|
|||
|
|||
Modify Query on the fly
Same result with the Me!txtPersonnelID.Requiry before the end sub.
I relocated the procedure to "After Update" of "txtChangeType" and it has the same result (the value list refreshes only when closing and reopening the form). "Maha Arupputhan Pappan" wrote in message ... Include Me!txtPersonnelID.Requery after the 2nd EndIf loop and before the End Sub. If no good. Then I suggest relocating the procedure to either "After Update" or "On Change" event. Ensure to include the Me!txtPersonnelID.Requery statement in any of the Event. -- Maha Aruppthan Pappan Nacap Asia Pacific "Garu" wrote: Thanks Maha, I put the procedure on "On Got Focus" Event of the Combo box. The Criteria in the Query changes between "Not Is Null" and "Not Is Not Null" but the value list doesn't change not until I close the form then reopen it. Here's the procedu Private Sub txtPersonnelID_GotFocus() Dim qdfSQL As QueryDef Set qdfSQL = CurrentDb.QueryDefs("qryP") If IsNull(ChangeTYpe) Then Me.txtPersonnelID.Locked = True Else Me.txtPersonnelID.Locked = False End If If ChangeType = "Occupation" Then qdfSQL.SQL = "SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name " _ & "FROM Personnel " _ & "WHERE ((Not (Personnel.QtrID) Is Null)) " _ & "ORDER BY Personnel.PersonnelID;" Else: ChangeType = "Vacation" qdfSQL.SQL = "SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name " _ & "FROM Personnel " _ & "WHERE ((Not (Personnel.QtrID) Is Not Null)) " _ & "ORDER BY Personnel.PersonnelID;" End If End Sub "Maha Arupputhan Pappan" wrote in message news This may assist you. In the Event Procedure of the Combo box: Dim qdfSQL as QueryDef Set qdfSQL = CurrentDb.QueryDefs("Your Query Name That You Created") qdfSQL.SQL = "SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name " _ & "FROM Personnel " _ & "WHERE ((Not (Personnel.QtrID) Is Null)) " _ & "ORDER BY Personnel.PersonnelID;" How the syntax works: 1. You declare a query definition (qdf) variable. 2. You assign qdf variable to the query that you created earlier. 3. You set the SQL to the query. As you mentioned, ensure that your combo box's "Control Source" is set to the query. Based on your object selection, change the "Is Null" to "Is Not Null". -- Maha Aruppthan Pappan Nacap Asia Pacific "Garu" wrote: Hi, I have this created a query for my "combo box" to list out related columns where QtrID is empty. Is it possible to modify the same query via vba code? What I would like to do is to change the "Is Null" to "not Is Null" to list out columns where QTRID is not empty. Below is the query text: SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name FROM Personnel WHERE ((Not (Personnel.QtrID) Is Null)) ORDER BY Personnel.PersonnelID; Thanks, Garry |
#7
|
|||
|
|||
Modify Query on the fly
Got it! What is missing is reassigning the RowSource as John indicated on
his suggestion. "Me.NameOftheComboBoxControl.RowSource = strSQL" Again, thanks a lot. Garu "John Spencer" wrote in message ... Assuming that the combobox is on a form, I would add a button to switch back and forth. In the click event of the button, you would have the following code Private Sub somebutton_Click() Dim StrSQL as String, strWhere As String Static strWhere as string If strWhere = "Personnel.QtrID Is Null" Then strWhere = "Personnel.QtrID Is Not Null" ELSE strWhere = "Personnel.QtrID Is Null" END IF strSQL = "SELECT PersonnelID, ArmyNumber, Rank, Personnel.[Name]" & _ " FROM Personnel WHERE " & strWhere & _ " ORDER BY Personnel.PersonnelID" Me.NameOftheComboBoxControl.RowSource = strSQL End Sub John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Garu wrote: Hi, I have this created a query for my "combo box" to list out related columns where QtrID is empty. Is it possible to modify the same query via vba code? What I would like to do is to change the "Is Null" to "not Is Null" to list out columns where QTRID is not empty. Below is the query text: SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name FROM Personnel WHERE ((Not (Personnel.QtrID) Is Null)) ORDER BY Personnel.PersonnelID; Thanks, Garry |
#8
|
|||
|
|||
Modify Query on the fly
Good Luck Garu.
Thanks John. -- Maha Aruppthan Pappan Nacap Asia Pacific "Garu" wrote: Got it! What is missing is reassigning the RowSource as John indicated on his suggestion. "Me.NameOftheComboBoxControl.RowSource = strSQL" Again, thanks a lot. Garu "John Spencer" wrote in message ... Assuming that the combobox is on a form, I would add a button to switch back and forth. In the click event of the button, you would have the following code Private Sub somebutton_Click() Dim StrSQL as String, strWhere As String Static strWhere as string If strWhere = "Personnel.QtrID Is Null" Then strWhere = "Personnel.QtrID Is Not Null" ELSE strWhere = "Personnel.QtrID Is Null" END IF strSQL = "SELECT PersonnelID, ArmyNumber, Rank, Personnel.[Name]" & _ " FROM Personnel WHERE " & strWhere & _ " ORDER BY Personnel.PersonnelID" Me.NameOftheComboBoxControl.RowSource = strSQL End Sub John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Garu wrote: Hi, I have this created a query for my "combo box" to list out related columns where QtrID is empty. Is it possible to modify the same query via vba code? What I would like to do is to change the "Is Null" to "not Is Null" to list out columns where QTRID is not empty. Below is the query text: SELECT Personnel.PersonnelID, Personnel.ArmyNumber, Personnel.Rank, Personnel.Name FROM Personnel WHERE ((Not (Personnel.QtrID) Is Null)) ORDER BY Personnel.PersonnelID; Thanks, Garry |
Thread Tools | |
Display Modes | |
|
|