Posts by browncoat

    Still not really clear. A Recordset is an object, so you should be able to simply query all the records you want in one go and assign them to a recordset. If you need to assign each record to a custom object, you can do that in a loop through your recordset, but if you need to make changes and write them back to the database, leaving them in a recordset seems simplest to me.

    Thanks Rory. That's an interesting idea. Is it possible to update a recordset object after the connection has closed? I'm currently populating custom class objects that are used in the application for data manipulation, etc, so the updates would need to be done at a later time after the user has gone through and made changes. For example at one point they might need data from Table A and be working on that and then need to pull data from Table B which will impact information in Table A and at the end they all need to be updated. I liked having the data in custom objects because I also populate the objects with other helpful information from related tables so I can access it very easily, i.e. a table has as field with a primary key of another table so I'll automatically populate a property with the "Name" in the other table's associated record, and I also like that I can easily identify the information I'm looking for with intellisense.

    In the meantime I've just created functions that basically create a new copy of the object to do it that way but the idea of being able to easily update tables using the recordsets seems like it might make things easier. I'm just not sure how I would handle doing the update at a later time and if there would be an easy way to reference the fields like I'm doing with the custom classes.

    Thanks again.

    There is no built-in way to copy your own objects. You'd have to add a copy or clone method (that you write) to your object. From what little you've provided so far, I'm not seeing any need to copy objects at all though. It sounds like you just need to create new instances, populate them, then put each one in the dictionary.

    Thanks Rory. The main reason I wanted to be able to copy was I have many records in a database which I'd like to be accessible via objects and rather than having to repeatedly query which can be slow when working remotely I wanted to just load the records into a bunch of objects in one fell swoop but perhaps I'm thinking about this the wrong way?

    Just when I thought I understood OOP in VBA I realize I'm still not figuring out how best to handle creating new objects. I am using a dictionary to store objects of the same type but am finding that I can't seem to copy the object into the dictionary to create a new object, it just references the original object. Is there a way to "copy" objects?

    I am basically populating the objects from a database and had been hoping it would help avoid having to repeatedly query the database if I could just get the data I wanted and stored into objects but if I have to create a "new" object independent of the object storing all the data then I believe I have to just query the database again which I was hoping to avoid.

    This happens occasionally and it's endlessly frustrating.. most of the time clicking debug when my code errors it takes me directly to the line that errored. But occasionally, it takes me many, sometimes, hundreds of lines of code before the line that actually caused the error and it takes a very long time to find the line that errored. Is there a reason for this behavior and is there a way to get around it? Thanks

    I have a class that has a variant array property. Sometimes this property has not been set but code attempts to get the value from it. I can't figure out how to check this without getting a type mismatch. Thanks for all help

    Thanks, though I am still having trouble. How can I pass a class object without specifying it's type or am I supposed to be doing this differently? I've tried

    I get runtime error 438, Object doesn't support this property or method.

    Thanks for your help

    So I've been using object classes with much success but one thing that is currently holding me up is passing different types of object classes to a userform and determining the type, if that is even possible. I want to customize the userform when it loads based upon the type of object that would be passed from another userform but am not sure how to go about this or if it is even possible. The only way I can currently think to do it is create a global object variable and check if it is not nothing and go from there but wondering if there is a more direct way to pass to the userform.


    I'm finally learning to use class objects and I wish I had done this sooner! It's making things much simpler to deploy and maintain but one bit I'm hung up on is creating an array or collection of a class objects. For example, I can load a record from a database into my class object, save it back to the database, etc, but if I want to load a bunch of records based on some criteria, I can't figure out how to group them. I've tried adding into an array and a collection but in both instances each record in the array, collection refers to the last object that was set because I'm using a 'placeholder' object to load into from a loop, see below (i've commented out the previous attempt at using an array), i.e. the debug line prints out the same PlateID from the last record that was selected in the table, even though it correctly loads each record before setting to the array or collection. Seems like these only point to the object but don't actually store it so my question is, is there any way to do what I'm trying to do or is there a better way I should be doing this?


    I have a bunch of xlsb files that contain data but also macros and there is a bug in the code that crashes the workbook upon opening because it tries to modify a protected sheet. There are hundreds of these files and I really don't want to go through and do this manually. I have a script that works on the workbook structure to pull out the data but it won't run on these workbooks because they crash upon being opened by the script. Is it possible to circumvent this?

    I'm using the first row of the listbox to populate column headings and want to unselect it when the user selects it. I can accomplish this fine by clicking a button that sets the listindex = -1 but I can't get it to do this when the user clicks or when the listbox changes. I've tried using a global boolean to disable the change event but no matter what, after the subs run, listindex returns to 0 and the first row is selected. I've also tried setting .selected(0) = false but this also does not work.

    Here's the code I currently have


    I have three values I'd like to average based on the adjacent cell (@NOMINAL) in a table and it works if all the cells have values but if one of them is empty it returns #DIV/0. Is there a way I can have it average only the two values if the third is empty?


    I found this function somewhere on the internet and it works exactly as I need it, however when the save path is set to the UNC ("//server/folder/subfolder/"), I get "File not found" when the file should be created if it doesn't exist. However, it works if I use the path I have mapped, e.g. Z:\folder\suberfolder\. I need it to use the UNC path since the file will be shared and not everyone has the drive mapped.

    Here is the full function:

    Sorry I don't understand how this is not clear. In the Visual Basic editor there is a button with a blue square on it and it is next to the pause/break button and the run button. Pushing this button ends the current executing code. When an error occurs in my code that cannot be debugged while the code is running it needs to be stopped. I am simply wondering if there is a way for a macro to execute after the code is stopped .

    Obviously but that's not the point. It's not any specific code. I am continually working on this and new errors pop up as I work on it so when it happens I'd like for a macro to automatically execute. It's really a simple answer I'm looking for: whether or not it's possible for a macro to execute when the reset/stop button is hit on executing code.