Restricting display items in a ComboBox

  • Thread starter Thread starter Duncan Edment
  • Start date Start date
D

Duncan Edment

I have a ComboBox on a form, that lists projects. These
projects come from a table with the following definition:

tblProjects

fldProjectID = Number
fldProjectDesc = Text
fldValidUntil = Date / Time

The theory is that each project has a valid until date,
after which the project can no longer be selected. i.e.
ProjectAlpha has a ValidUntilDate of 20/05/04, so work can
be recorded against this project up until this date, but
not after.

The "AfterUpdate" event of the project description control
has the following code:

Private Sub txtProjectID_AfterUpdate()

Dim datCRecDate As Date
Dim datWkStart As Date
Dim datWkEnd As Date
Dim rstPrevious As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim strMsgText As String

strSQL = "SELECT * from tblProjects " & _
"WHERE fldProjectID = " & txtProjectID.Value & _
";"
Set db = CurrentDb
Set rstPrevious = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rstPrevious!fldValidUntilDate.Value < Date Then
strMsgText = rstPrevious!fldProjectDescription.Value
MsgBox "I am sorry, but the project code" & vbCrLf & _
vbCrLf & "'" & strMsgText & "'" & vbCrLf & vbCrLf & _
"is no longer valid." & vbCrLf & vbCrLf & _
"Please select a valid entry", vbExclamation, _
"!! Out of date code !!"
Me.Undo
txtProjectID.SetFocus
End If

Set rstPrevious = Nothing

End Sub

This seems to work fine...if an out-of-date code is
selected, the msgbox displays. Everything fine so far.

However, what I now want to do is limit the display in the
ComboBox, so that projects that have a "ValidUntiLDate" in
the past, will not be displayed. In the 'RowSource' for
the Combo control, I have the following:

SELECT [tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
((([tblProjects].[fldValidUntilDate])>Date()));

This seems to work fine, except that where the project has
genuinely been selected, it no longer appears on the
form. For records that have the project against them,
with a DateWorked in the past, the Project Combo Control
is blank.

Can someone help me?

Many thanks

Duncan
 
I would suggest building the RowSource SQL on the fly in
the form's Current eventhandler. If the combo control is
cmbProject, you could try a Union query that incorporated
its current value into the RowSource along the limes of

cmbProject.RowSource = "SELECT
[tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
[tblProjects].[fldProjectDescription]= '" &
cmbProject.value & "' UNION SELECT
[tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
((([tblProjects].[fldValidUntilDate])>Date())) AND
[tblProjects].[fldProjectDescription]<> '" &
cmbProject.value & "';

The above is untested aircode.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a ComboBox on a form, that lists projects. These
projects come from a table with the following definition:

tblProjects

fldProjectID = Number
fldProjectDesc = Text
fldValidUntil = Date / Time

The theory is that each project has a valid until date,
after which the project can no longer be selected. i.e.
ProjectAlpha has a ValidUntilDate of 20/05/04, so work can
be recorded against this project up until this date, but
not after.

The "AfterUpdate" event of the project description control
has the following code:

Private Sub txtProjectID_AfterUpdate()

Dim datCRecDate As Date
Dim datWkStart As Date
Dim datWkEnd As Date
Dim rstPrevious As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim strMsgText As String

strSQL = "SELECT * from tblProjects " & _
"WHERE fldProjectID = " & txtProjectID.Value & _
";"
Set db = CurrentDb
Set rstPrevious = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rstPrevious!fldValidUntilDate.Value < Date Then
strMsgText = rstPrevious!fldProjectDescription.Value
MsgBox "I am sorry, but the project code" & vbCrLf & _
vbCrLf & "'" & strMsgText & "'" & vbCrLf & vbCrLf & _
"is no longer valid." & vbCrLf & vbCrLf & _
"Please select a valid entry", vbExclamation, _
"!! Out of date code !!"
Me.Undo
txtProjectID.SetFocus
End If

Set rstPrevious = Nothing

End Sub

This seems to work fine...if an out-of-date code is
selected, the msgbox displays. Everything fine so far.

However, what I now want to do is limit the display in the
ComboBox, so that projects that have a "ValidUntiLDate" in
the past, will not be displayed. In the 'RowSource' for
the Combo control, I have the following:

SELECT [tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
((([tblProjects].[fldValidUntilDate])>Date()));

This seems to work fine, except that where the project has
genuinely been selected, it no longer appears on the
form. For records that have the project against them,
with a DateWorked in the past, the Project Combo Control
is blank.

Can someone help me?

Many thanks

Duncan

.
Click to expand...
 
Gerald, I tried your code, but to no avail. The combo
still does not display the information if the valid until
date is in the past.

Duncan
-----Original Message-----
I would suggest building the RowSource SQL on the fly in
the form's Current eventhandler. If the combo control is
cmbProject, you could try a Union query that incorporated
its current value into the RowSource along the limes of

cmbProject.RowSource = "SELECT
[tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
[tblProjects].[fldProjectDescription]= '" &
cmbProject.value & "' UNION SELECT
[tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
((([tblProjects].[fldValidUntilDate])>Date())) AND
[tblProjects].[fldProjectDescription]<> '" &
cmbProject.value & "';

The above is untested aircode.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a ComboBox on a form, that lists projects. These
projects come from a table with the following definition:

tblProjects

fldProjectID = Number
fldProjectDesc = Text
fldValidUntil = Date / Time

The theory is that each project has a valid until date,
after which the project can no longer be selected. i.e.
ProjectAlpha has a ValidUntilDate of 20/05/04, so work can
be recorded against this project up until this date, but
not after.

The "AfterUpdate" event of the project description control
has the following code:

Private Sub txtProjectID_AfterUpdate()

Dim datCRecDate As Date
Dim datWkStart As Date
Dim datWkEnd As Date
Dim rstPrevious As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim strMsgText As String

strSQL = "SELECT * from tblProjects " & _
"WHERE fldProjectID = " & txtProjectID.Value & _
";"
Set db = CurrentDb
Set rstPrevious = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rstPrevious!fldValidUntilDate.Value < Date Then
strMsgText = rstPrevious!fldProjectDescription.Value
MsgBox "I am sorry, but the project code" & vbCrLf & _
vbCrLf & "'" & strMsgText & "'" & vbCrLf & vbCrLf & _
"is no longer valid." & vbCrLf & vbCrLf & _
"Please select a valid entry", vbExclamation, _
"!! Out of date code !!"
Me.Undo
txtProjectID.SetFocus
End If

Set rstPrevious = Nothing

End Sub

This seems to work fine...if an out-of-date code is
selected, the msgbox displays. Everything fine so far.

However, what I now want to do is limit the display in the
ComboBox, so that projects that have a "ValidUntiLDate" in
the past, will not be displayed. In the 'RowSource' for
the Combo control, I have the following:

SELECT [tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
((([tblProjects].[fldValidUntilDate])>Date()));

This seems to work fine, except that where the project has
genuinely been selected, it no longer appears on the
form. For records that have the project against them,
with a DateWorked in the past, the Project Combo Control
is blank.

Can someone help me?

Many thanks

Duncan

.
Click to expand...
.
Click to expand...
 
My code assumes that the value of the comboBox is
fldProjectDescription. If that assmption is wrong and the
value of the comboBox (as dictated by the BoundColumn) is
fldProjectId then the code needs to be changed to

cmbProject.RowSource = "SELECT
[tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
[tblProjects].[fldProjectID]= '" &
cmbProject.value & "' UNION SELECT
[tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
((([tblProjects].[fldValidUntilDate])>Date())) AND
[tblProjects].[fldProjectID]<> '" &
cmbProject.value & "';

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gerald, I tried your code, but to no avail. The combo
still does not display the information if the valid until
date is in the past.

Duncan
-----Original Message-----
I would suggest building the RowSource SQL on the fly in
the form's Current eventhandler. If the combo control is
cmbProject, you could try a Union query that incorporated
its current value into the RowSource along the limes of

cmbProject.RowSource = "SELECT
[tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
[tblProjects].[fldProjectDescription]= '" &
cmbProject.value & "' UNION SELECT
[tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
((([tblProjects].[fldValidUntilDate])>Date())) AND
[tblProjects].[fldProjectDescription]<> '" &
cmbProject.value & "';

The above is untested aircode.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a ComboBox on a form, that lists projects. These
projects come from a table with the following definition:

tblProjects

fldProjectID = Number
fldProjectDesc = Text
fldValidUntil = Date / Time

The theory is that each project has a valid until date,
after which the project can no longer be selected. i.e.
ProjectAlpha has a ValidUntilDate of 20/05/04, so work can
be recorded against this project up until this date, but
not after.

The "AfterUpdate" event of the project description control
has the following code:

Private Sub txtProjectID_AfterUpdate()

Dim datCRecDate As Date
Dim datWkStart As Date
Dim datWkEnd As Date
Dim rstPrevious As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim strMsgText As String

strSQL = "SELECT * from tblProjects " & _
"WHERE fldProjectID = " & txtProjectID.Value & _
";"
Set db = CurrentDb
Set rstPrevious = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rstPrevious!fldValidUntilDate.Value < Date Then
strMsgText = rstPrevious!fldProjectDescription.Value
MsgBox "I am sorry, but the project code" & vbCrLf & _
vbCrLf & "'" & strMsgText & "'" & vbCrLf & vbCrLf & _
"is no longer valid." & vbCrLf & vbCrLf & _
"Please select a valid entry", vbExclamation, _
"!! Out of date code !!"
Me.Undo
txtProjectID.SetFocus
End If

Set rstPrevious = Nothing

End Sub

This seems to work fine...if an out-of-date code is
selected, the msgbox displays. Everything fine so far.

However, what I now want to do is limit the display in the
ComboBox, so that projects that have a "ValidUntiLDate" in
the past, will not be displayed. In the 'RowSource' for
the Combo control, I have the following:

SELECT [tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
((([tblProjects].[fldValidUntilDate])>Date()));

This seems to work fine, except that where the project has
genuinely been selected, it no longer appears on the
form. For records that have the project against them,
with a DateWorked in the past, the Project Combo Control
is blank.

Can someone help me?

Many thanks

Duncan

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Gerald,

Please excuse me, but this is what I have.

cboProjectID has a ControlSource = tblTimeSheet.fldProjectID
cboProjectID had a BoundColumn = 2 = The numeric code for each project

The OnCurrent method for the form, has the following code:

cboProjectID.RowSource = "SELECT [tblProjects].[fldProjectDescription]," & _
"[tblProjects].[fldProjectID] FROM tblProjects WHERE " & _
"[tblProjects].[fldProjectDescription]= '" & cboProjectID.Value & "' " & _
"UNION SELECT [tblProjects].[fldProjectDescription], " & _
"[tblProjects].[fldProjectID] FROM tblProjects WHERE " & _
"((([tblProjects].[fldValidUntilDate])>Date())) AND " & _
"[tblProjects].[fldProjectDescription]<> '" & cboProjectID.Value & "';"

Using this code, the combo box displays all codes perfectly, EXCEPT the ones
that have a ValidUntilDate in the past.

If I use the new code you suggested, the combo box displays NO TEXT at all.

Am I missing something?

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

Gerald Stanley said:
My code assumes that the value of the comboBox is
fldProjectDescription. If that assmption is wrong and the
value of the comboBox (as dictated by the BoundColumn) is
fldProjectId then the code needs to be changed to

cmbProject.RowSource = "SELECT
[tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
[tblProjects].[fldProjectID]= '" &
cmbProject.value & "' UNION SELECT
[tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
((([tblProjects].[fldValidUntilDate])>Date())) AND
[tblProjects].[fldProjectID]<> '" &
cmbProject.value & "';

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Gerald, I tried your code, but to no avail. The combo
still does not display the information if the valid until
date is in the past.

Duncan
-----Original Message-----
I would suggest building the RowSource SQL on the fly in
the form's Current eventhandler. If the combo control is
cmbProject, you could try a Union query that incorporated
its current value into the RowSource along the limes of

cmbProject.RowSource = "SELECT
[tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
[tblProjects].[fldProjectDescription]= '" &
cmbProject.value & "' UNION SELECT
[tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
((([tblProjects].[fldValidUntilDate])>Date())) AND
[tblProjects].[fldProjectDescription]<> '" &
cmbProject.value & "';

The above is untested aircode.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a ComboBox on a form, that lists projects. These
projects come from a table with the following definition:

tblProjects

fldProjectID = Number
fldProjectDesc = Text
fldValidUntil = Date / Time

The theory is that each project has a valid until date,
after which the project can no longer be selected. i.e.
ProjectAlpha has a ValidUntilDate of 20/05/04, so work can
be recorded against this project up until this date, but
not after.

The "AfterUpdate" event of the project description control
has the following code:

Private Sub txtProjectID_AfterUpdate()

Dim datCRecDate As Date
Dim datWkStart As Date
Dim datWkEnd As Date
Dim rstPrevious As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim strMsgText As String

strSQL = "SELECT * from tblProjects " & _
"WHERE fldProjectID = " & txtProjectID.Value & _
";"
Set db = CurrentDb
Set rstPrevious = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rstPrevious!fldValidUntilDate.Value < Date Then
strMsgText = rstPrevious!fldProjectDescription.Value
MsgBox "I am sorry, but the project code" & vbCrLf & _
vbCrLf & "'" & strMsgText & "'" & vbCrLf & vbCrLf & _
"is no longer valid." & vbCrLf & vbCrLf & _
"Please select a valid entry", vbExclamation, _
"!! Out of date code !!"
Me.Undo
txtProjectID.SetFocus
End If

Set rstPrevious = Nothing

End Sub

This seems to work fine...if an out-of-date code is
selected, the msgbox displays. Everything fine so far.

However, what I now want to do is limit the display in the
ComboBox, so that projects that have a "ValidUntiLDate" in
the past, will not be displayed. In the 'RowSource' for
the Combo control, I have the following:

SELECT [tblProjects].[fldProjectDescription], _
[tblProjects].[fldProjectID] FROM tblProjects WHERE _
((([tblProjects].[fldValidUntilDate])>Date()));

This seems to work fine, except that where the project has
genuinely been selected, it no longer appears on the
form. For records that have the project against them,
with a DateWorked in the past, the Project Combo Control
is blank.

Can someone help me?

Many thanks

Duncan

.

.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top