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

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

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

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

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

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

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

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