A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Restricting display items in a ComboBox



 
 
Thread Tools Display Modes
  #1  
Old May 25th, 2004, 03:29 PM
Gerald Stanley
external usenet poster
 
Posts: n/a
Default Restricting display items in a ComboBox

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

.

  #2  
Old May 26th, 2004, 10:56 AM
Duncan Edment
external usenet poster
 
Posts: n/a
Default Restricting display items in a ComboBox

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

.

.

  #3  
Old May 26th, 2004, 02:36 PM
Gerald Stanley
external usenet poster
 
Posts: n/a
Default Restricting display items in a ComboBox

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

.

.

.

  #4  
Old May 27th, 2004, 11:11 PM
Duncan Edment
external usenet poster
 
Posts: n/a
Default Restricting display items in a ComboBox

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" - wrote in message
...
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

.

.

.






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.