Darren DICK
d.dick at uws.edu.au
Tue Apr 13 23:08:00 CDT 2004
Thanks to all who responded I went with Drew's suggestion 'Cause he was first :-)) Many thanks Darren ----- Original Message ----- From: "Jim Lawrence (AccessD)" <accessd at shaw.ca> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com> Sent: Wednesday, April 14, 2004 1:35 PM Subject: RE: [AccessD] A2K:Adding the word ALL to a combo > Hi Darren: > > The only way I know to add 'all' to a combo/list box is through a recorsdset > that I supply myself. The gem piece came from an example code segment > supplied in Access97 help files. I can not remember what the request was > that found it but I have used a variation on that theme for years. Below is > the base piece; I just cut and pasted it from one application I have just > finished. > > The call to this function is made from the list/combo boxes by placing the > functions name in the 'Row Source Type' property i.e. : FillFunctionCode > > And a sample of the function: > > Public Function FillFunctionCode(ctlBox As Control, ID As Variant, row As > Variant, col As Variant, Code As Variant) As Variant > Dim bolDBStatus As Boolean > Dim mvReturnVal As Variant > > On Error GoTo Err_FillFunctionCode > > bolDBStatus = True > If rsFunctionCode Is Nothing Then bolDBStatus = > FillRecordsetFunctionCode() > > mvReturnVal = Null > > If bolDBStatus = True Then > With rsFunctionCode > Select Case Code > Case acLBInitialize ' Initialize. > bolStatus = True > > If .BOF = False Or .EOF = False Then > .MoveFirst > mvReturnVal = .RecordCount > Else > mvReturnVal = 0 > End If > Case acLBOpen ' Open. > mvReturnVal = Timer ' Generate unique ID for > control. > Case acLBGetRowCount ' Get number of rows. > mvReturnVal = .RecordCount > Case acLBGetColumnCount ' Get number of columns. > mvReturnVal = ctlBox.ColumnCount > Case acLBGetColumnWidth ' Column width. > mvReturnVal = -1 ' -1 forces use of > default width. > Case acLBGetFormat ' Get format > mvReturnVal = -1 > Case acLBGetValue ' Get data. > .MoveFirst > .Move (row) > mvReturnVal = .Fields(col) > End Select > End With > End If > > FillFunctionCode = mvReturnVal > > Exit_FillFunctionCode: > Exit Function > > Err_FillFunctionCode: > 'Handles error situation caused an apparent unrelated error(s) > 'generated in other modules. (It loses its brains...) > If Err.Number = 3167 Then > rsFunctionCode.Requery > ElseIf Err.Number = 3704 Or Err.Number = -2147467259 Then > Resume Exit_FillFunctionCode > ElseIf Err.Number <> 91 Then > ShowErrMsg "FillFunctionCode" > End If > Resume Exit_FillFunctionCode > > End Function > > There still needs to be a piece of code that fills the recordset, in this > case a recordset named 'rsFunctionCode'. In the recordset creating routine > is where 'All' would be added. If you need I can send the code that > populates the recordset but there are so many ways to do that. > > HTH > Jim > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Darren DICK > Sent: Tuesday, April 13, 2004 8:02 PM > To: AccessD List > Subject: [AccessD] A2K:Adding the word ALL to a combo > > > Hello all > I need to add the word <ALL> to a combo > SO pseudo = select * from MyTable and Then add "<ALL>" > > I have done this before through assistance from this list but I can't find > the Gem > or remember the dB I did it on :-(( > > Any suggestions? > > Many thanks > > Darren > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com