 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / January 2005UDF in RowSourceType not working
Hi- I'm trying to use a UDF for the RowSourceType of a Combo Box in Access2000 (Windows2k). I'm finding that the UDF is not being called multiple times as advertised in code samples I have. The result is the I only get the one call to the UDF, which works on the Intiialize codde value only, and then close out....the result being the box does not get filled, and I get a fatal error when trying to open the box. Any thoughts? Should I assign the RowSourceType in code? What am I overlooking?  Signaturewolfie I'm guessing that by UDF you mean "User Defined Function" or, more commonly, Callback Function? Try compiling code and then compacting and repairing database. If this doesn't fix the problem then please supply the function call as it appears in the RowSourceType of the combo box and the function declaration as it appears in code. For example, the row source should be (no quotes, no argument list): ComboBox.RowSourceType = MyRowSource and the callback function would like Public Function MyRowSource(ByRef rctlControl As Control, ByVal vlngID As Long, ByVal vlngRow As Long, ByVal vlngColumn As Long, _ ByVal vintCode As Integer) As Variant Dim varReturn As Variant Select Case vintCode Case acLBInitialize varReturn = True Case acLBOpen varReturn = Timer Case acLBGetRowCount varReturn = getUBoundArray() + 1 ... Graeme. Mr. Richardson- In the RowSourceType property of the combo box's property sheet, I have- ListMDBs no quotes, no brackets, no equal sign...just that, and nothing in the RowSource property. The Code I am trying to run is as follows- Function ListMDBs(fld As Control, ID As Variant, _ row As Variant, col As Variant, _ Code As Variant) As Variant Static dbs(127) As String, Entries As Integer Dim ReturnVal As Variant ReturnVal = Null Select Case Code Case acLBInitialize ' Initialize. Entries = 0 dbs(Entries) = Dir("*.MDB") Do Until dbs(Entries) = "" Or Entries >= 127 Entries = Entries + 1 dbs(Entries) = Dir Loop ReturnVal = Entries Case acLBOpen ' Open. ' Generate unique ID for control. ReturnVal = Timer Case acLBGetRowCount ' Get number of rows. ReturnVal = Entries Case acLBGetColumnCount ' Get number of columns. ReturnVal = 1 Case acLBGetColumnWidth ' Column width. ' -1 forces use of default width. ReturnVal = -1 Case acLBGetValue ' Get data. ReturnVal = dbs(row) Case acLBEnd ' End. Erase dbs End Select ListMDBs = ReturnVal End Function Thank you for looking at this! wolfie > I'm guessing that by UDF you mean "User Defined Function" or, more commonly, > Callback Function? [quoted text clipped - 23 lines] > > Graeme. Hi wolfie The Case acLBInitialize should return true, if it returns false (for whatever reason) then the callback is aborted. Select Case Code Case acLBInitialize ' Initialize. Entries = 0 dbs(Entries) = Dir("*.MDB") Do Until dbs(Entries) = "" Or Entries >= 127 Entries = Entries + 1 dbs(Entries) = Dir Loop ReturnVal = True Case acLBOpen ' Open. ' Generate unique ID for control. ReturnVal = Timer Case acLBGetRowCount ' Get number of rows. ReturnVal = Entries Case acLBGetColumnCount ' Get number of columns. ... HTH, Graeme. Thanks Graeme... Your tip got me further along. I found I had to specify a more specific path than Dir("*.MDB") to get the code to cycle thru the loop. However, the combo box does not show the list when I try to open it. What happens is it goes back into the SELECT CASE again when the combo arrow is clicked, and I am suspecting the data has already gone out of scope so when the new codes are presented, there is nothing left to show. Any help? wolfie > Hi wolfie > [quoted text clipped - 19 lines] > > HTH, Graeme. Hi Graeme- I stepped thru the code, and what happens when I open the combo box with the arrow is the list is sized to the number of rows that should appear, but no data appears. The code appears to work fine until it gets to code 6 (acLBGetValue). I have 8 records that should appear. dbs(row) has a row value of 7, indicating only the last record, and a mouse over of dbs while stepping thru indicates the proper list item for that one entry. Problem- List should show 8 entries, but does not show even one. wolfie > Hi wolfie > [quoted text clipped - 19 lines] > > HTH, Graeme. HI, this code (below) works fine for me. Check that file names are populated to the array by placing a debug.print statement in the loop (shown). It sounds like the files are being populated to the array, but there not being shown. Try deleting the ReturnVal = -1 statement from Case acLBGetColumnWidth. I normally leave this Case blank and set the column width in the controls property. I suspect that this is the cause of not seeing values. Check that the column width property of the combo box is not set to 0 also. This is probably not the cause of problems, but if the above doesn't work then try deleting the Erase dbs statement in Case acLBEnd Graeme. Public Function ListMDBs(fld As Control, ID As Variant, _ row As Variant, col As Variant, _ Code As Variant) As Variant Static dbs(127) As String, Entries As Integer Dim ReturnVal As Variant ReturnVal = Null Select Case Code Case acLBInitialize ' Initialize. Entries = 0 dbs(Entries) = Dir("C:\Temp\*.MDB") Do Until dbs(Entries) = "" Or Entries >= 127 Entries = Entries + 1 dbs(Entries) = Dir Debug.Print dbs(Entries) Loop ReturnVal = True Case acLBOpen ' Open. ' Generate unique ID for control. ReturnVal = Timer Case acLBGetRowCount ' Get number of rows. ReturnVal = Entries Case acLBGetColumnCount ' Get number of columns. ReturnVal = 1 Case acLBGetColumnWidth ' Column width. ' -1 forces use of default width. 'ReturnVal = -1 Case acLBGetValue ' Get data. ReturnVal = dbs(row) Case acLBEnd ' End. 'Erase dbs End Select ListMDBs = ReturnVal End Function Thank you Graeme! I found that your code ran fine with the acGetColumnWidth included and the Erase function commented out, but I am omitting acGetColumnWidth in my production work, as I need to set these specific for multiple columns. I have taken what I learned here and successfully applied it to using a recordset derived from a SQL stored procedure to fill the array with GetRows, adUseClient so I can get the RecordCount for acLBGetRowCount, and MyArray(col,row) to pass on the return in acLBGetValue to fill the combo box, which was my goal all along. I have about 40 more combo boxes to convert to this method, from that method which uses Table/Query and a SELECT statemnet in the property sheet of the combo box. A couple last questions.... Are there any memory considerations stemming from not using the Erase function? Is there any documentation I can look at to give me all the code value meanings being passed into the function? Thanks again! wolfie > HI, this code (below) works fine for me. > Check that file names are populated to the array by placing a debug.print [quoted text clipped - 44 lines] > ListMDBs = ReturnVal > End Function > I found that your code ran fine with the acGetColumnWidth included and the > Erase function commented out, but I am omitting acGetColumnWidth in my > production work, as I need to set these specific for multiple columns. The width of columns can be set in the ColimnWidth property of the ComboBox (e.g. 0cm,1cm,2.5cm). Either way is fine. > I have taken what I learned here and successfully applied it to using a > recordset derived from a SQL stored procedure to fill the array with [quoted text clipped - 11 lines] > Are there any memory considerations stemming from not > using the Erase function? Memory should be fine. You will notice a decrease in performance if you try display many rows. I generally use UNION queries when I want to show dirrerent things in a list: Jet is much faster than VBA. > Is there any documentation I can look at to give me all the code value > meanings being passed into the function? Pretty much only the help files. You could try searching the MS Knowledgebase. Nice section MS Access Developer's Handbook. Happy that I could help, Graeme. Thanks again Graeme! None of the combo boxes will show too many rows, so I should be ok there. Over and out, and Have A Nice Day!!! wolfie > > I found that your code ran fine with the acGetColumnWidth included and the > > Erase function commented out, but I am omitting acGetColumnWidth in my [quoted text clipped - 27 lines] > > Happy that I could help, Graeme. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.