Combo Box

[Guest]

Hi - I have a combo box on a form with the following SQL

SELECT tblAdmin.ID, tblAdmin!FirstName & " " & tblAdmin!LastName AS Expr1
FROM tblAdmin WHERE (((tblAdmin.Expire)=No)) ORDER BY tblAdmin.LastName;

When I open the form in Edit Mode, I would like to remove the WHERE clause
from the Combo Box. I only need the WHERE when the form is in Add Mode.

I am not sure how to accomplish this.
Thanks

 

[Guest]

Use the OnCurrent event of the form that activate when you enter a record,
and use the code that check if it's a new record.\, and change the RowSource
of the combo

If Me.NewRecord Then
me.[ComBoName].RowSource = "SELECT tblAdmin.ID, tblAdmin!FirstName & ' '
& tblAdmin!LastName AS Expr1 FROM tblAdmin WHERE (((tblAdmin.Expire)=No))
ORDER BY tblAdmin.LastName"
Else
me.[ComBoName].RowSource = "SELECT tblAdmin.ID, tblAdmin!FirstName & '
' & tblAdmin!LastName AS Expr1 FROM tblAdmin ORDER BY tblAdmin.LastName"
End If

 

[Guest]

Do I need to remove the SQL statement from the combo box on the form?

Use the OnCurrent event of the form that activate when you enter a record,
and use the code that check if it's a new record.\, and change the RowSource
of the combo

If Me.NewRecord Then
me.[ComBoName].RowSource = "SELECT tblAdmin.ID, tblAdmin!FirstName & ' '
& tblAdmin!LastName AS Expr1 FROM tblAdmin WHERE (((tblAdmin.Expire)=No))
ORDER BY tblAdmin.LastName"
Else
me.[ComBoName].RowSource = "SELECT tblAdmin.ID, tblAdmin!FirstName & '
' & tblAdmin!LastName AS Expr1 FROM tblAdmin ORDER BY tblAdmin.LastName"
End If

--
Good Luck
BS"D

Hi - I have a combo box on a form with the following SQL

SELECT tblAdmin.ID, tblAdmin!FirstName & " " & tblAdmin!LastName AS Expr1
FROM tblAdmin WHERE (((tblAdmin.Expire)=No)) ORDER BY tblAdmin.LastName;

When I open the form in Edit Mode, I would like to remove the WHERE clause
from the Combo Box. I only need the WHERE when the form is in Add Mode.

I am not sure how to accomplish this.
Thanks

 

[Guest]

You don't have to do, but keep in mind that it will be replaced when the form
runs

 

[Guest]

I am having difficulty getting this to work. Would it be best to copy and
paste my code to show what I have entered. I am not very knowledgable about
VBA.

You don't have to do, but keep in mind that it will be replaced when the form
runs

[/QUOTE]