Posts by Zzyzzyth

    Re: Null a date field in a recordset


    The date is a field in a database, not in a cell. I need the equivalent of clearcontents or null in DAO terminology.


    I need something to the effect of:

    Code
    with rs1
    .edit
    !Date = vbNull  or rs1.fields("Date") = vbNull
    .update
    end with

    Re: lookup value within range in single cell


    If your data is in seperate columns, you could use
    =VLOOKUP(1080,A1:d4,3,TRUE)
    and it will return the row as close to or equal to what you're looking up.
    You can replace the hard-coded "1080" with a cell reference, and change A1:D4 to whatever your actual table range is. The key is the TRUE part. You must make sure that your data is sorted by the column that your key is in. In this case column A. This is also assuming that 1080 can only appear once in a range, which zip codes should be.


    Otherwise, you'll need to parse it like Mark said.

    I'm returning a recordset using DAO that contains a date field. In some instances (like a date value is more than 1 year old), I want to null the field in the recordset.


    I've tried !Date = vbNull, but all that does is enter 12/31/1899 in the field. I want it to be null. Any suggestions?

    Re: Textbox.SetFocus


    I ended up going with this. It works pretty well, but I am always interested in how others would have approached it. Especially when it is more efficient.



    I was trying to validate a phone number before proceeding and was using TextBox.SetFocus to return to the textbox in question when there was an error, but it proceeded to the next control anyway. I gave up on that and modified some code I found from Dave (which cured the SetFocus problem).


    The code below gives me the msgbox no matter what. I changed the code to allow for just the 10 digit phone number, and for the () and spaces. What am I doing wrong? Is there a better way?


    All I'm trying to do is verify that the user has plugged in a 10-digit phone number. If not, set focus back to the offending textbox, otherwise, format the textbox and move on.



    Re: VBA control array


    Interesting. I dumped my code into the workbook that Norie supplied and it works just fine. But, if I import the code from Norie's workbook to mine, I get the '444' message. I don't understand why, but I'll take it! Thank you so much for all of your help. And once again, sorry about the cross-post.

    Re: VBA control array


    I'm not sure. The examples are working great. I'm pulling all of the code and changed all of my variables to match the imported code. The class module with the latest example looks like the exact class module that Norie got me started with, just attached to a different workbook. The only thing I'm doing different (that I can think of) is putting the AddRemoveCheckboxes code in my cbo_Change event. Mainly because I don't know how many to AddRemove until I retrieve the recordset.

    Re: VBA control array


    Thanks Norie. I tried importing the code and kept getting the same error message. I'll use this as a skeleton and build my code around it and see what happens.

    Re: VBA control array


    Same thing. Runtime '444'.


    Another thing I though was interesting, was when I stepped through the code, it was not recognizing Left(ctl.Name, 8) unless I capitalized the "B" in CheckBox. That was strange. Anyway, I put it in a seperate routine, and got the same thing. It must be the array, or possibly a reference to the class module?

    Re: VBA control array


    Sorry about the delay guys. Just waking up here. Thanks for the example Will. It works great by itself, but not with my code. When I use the line to delete the controls, I get runtime '444' , "Could not delete the controls. This method cannot be used in this context". Is it because of the array, rather than looping through Me.Controls?

    Re: VBA control array


    Yeah, it's telling me to use Me.Controls.Remove, which I have. I must not be using the correct reference or name of the control. I've tried (I thought) of every possible combo of .Remove using the index, the class name, the checkgroup. I've tried decreasing the index by 1 and redim'ing each time... Maybe I'm overthinking it at this point.

    Re: VBA control array


    I'm sorry. I won't do that again. I was getting frustrated and just thought I would search around for another board. I didn't realize I was about to step on any toes! I know this is all volunteer work, and I understand your responses. If anyone is still willing to talk to me....

    Re: VBA control array


    Thank you for the warm welcome, Roy. I've visited and scalped this website a thousand times for useful code, but this was my first post. Also, thank you & Will for such a quick response. When I first replied with my code, it posted with a lot of stuff that wasn't relevant to the post. I've since edited it and cleaned it up a little. I hope that helps. I have tried [B][I]many[I][B] different code snippets to remove the checkboxes from the form, but none of them work. It seems as though if you Me.Controls.Add, you should be able to Me.Controls.Remove. Any suggestions?

    Re: VBA control array


    I have a form that launches when a user wants to confirm an order from a customer. It loads a drop-down combo box with our current customer list. When the user selects a customer, a checkbox array is loaded with the items that a customer has ordered. The checkboxes confirm (or un-confirm) that the order has been processed. Everything works fine up to this point. Let's say the user selects a customer, and this customer has 7 orders. 7 checkboxes appear on the form, either checked or unchecked. The problem is: when the user selects another customer from the drop down that only has 3 orders. The 3 checkboxes appear just fine, however, the balance of the previous customer (orders 4-7) also still show on the form. How do I remove the previous checkboxes from the form and prepare for a new recordset (or new checkbox array). I have tried to run backwards through the code that creates the array using Me.Controls.Remove instead of .Add, and this is just not working. Any help would be greatly appreciated.