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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|