Modify Query on the fly

  • Thread starter Thread starter Garu
  • Start date Start date
G

Garu

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".
 
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 procedure:

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
 
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.
 
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
 
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).
 
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 said:
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
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
Click to expand...
Click to expand...
 
Good Luck Garu.

Thanks John.

--
Maha Aruppthan Pappan
Nacap Asia Pacific


Garu said:
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 said:
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
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top