Retrieving combobox text in VBA (1 Viewer)

[JohnSmith]

Hey there folks, got a bit of an odd question here, wondering if you could help out. Recently I've been writing an application in Access, and one of the things it was requested to be able to do is automatically generate a letter to be printed out and mailed to a recipient based on some database information. I decided to use reports to do this, basically creating a single huge blank textbox and populating it with the letter and its associated variables during the onload event. It seems to work fairly well for the most part, but I've run into a couple of snags.

The biggest one is one of the variables. It's a combo box, with three different possible values - Junked, Abandoned, or Nuisance. Now, when I create the report using the report wizard, I can see the combo box pop up clearly, and it has the right value selected and everything. When I try to get at this value in VBA to put it in the letter, however, things go wrong.

I actually have the combo box on the page and I pull the data from there, I just mark it hidden. This works fine for all my other various text fields, but any time I try it on a combo box like so...

MyString = MyString & Me.status_box

where status_box is the name of the combo box, it seems to retrieve the integer ID rather than the text value. Trying to use Me.status_box.Text or Me.status_box.value doesn't work either, as Access complains about status_box not having focus for the Text method and just continues to give an integer for the Value method. Is there a way for me to retrieve the selected text from the combo box? Whenever I temporarily mark it as visible again, I can see that it DOES have the correct value selected there. I just can't get to it in VBA.

I'm very close on completing this project, this is my last major roadblock. Any help you could give would be much appreciated. Thanks!

 

[SOS]

Try:

MyString = MyString & Me.status_box.Column(1)

 

[SOS]

forgot to mention that columns for a listbox or combobox in VBA are ZERO-Based -

so the first column is Column(0), second is Column(1), third is Column(2), etc.

 

[JohnSmith]

Try:

MyString = MyString & Me.status_box.Column(1)

Thanks for the quick reply! Unfortunately it doesn't seem to have worked... I tried the line:

MyString = MyString & Me.status_box.Column(1) & vbCrLf

And just got a blank line. I even tried Columns 0 and 2 just in case and got blank lines there as well. Conversely, just a plain old Me.status_box returns a value of 4. Weird...

 

[SOS]

You also have to make sure your Combo's COLUMN COUNT is set to 2 and not 1.

 

[JohnSmith]

You also have to make sure your Combo's COLUMN COUNT is set to 2 and not 1.

I did not think to check that, but it is set to 2. I tried creating a new combo box from the Existing Fields menu as well just in case I messed something up, but it gives me the same result.

 

[SOS]

Want to post what you have and we can see what is up?

 

[vbaInet]

Try this:

MyString = MyString & Me.status_box.Column(1, Me.status_box.ListIndex)

 

[JohnSmith]

I think I finally gt at least part of what is going on here... Part of the database in question was designed by someone else, and I may've been taking things for granted. It seems the previous guy was actually storing the value needed in the Contacts list (though God knows why), and was storing an integer number in the status data field that referenced the contact ID. I'm not real sure how he did this, but there you go.

I'm still kind of confused by why I can't get to the value when I have it clearly showing up in a combo box on the report, though.

 

[SOS]

I'm still kind of confused by why I can't get to the value when I have it clearly showing up in a combo box on the report, though.

If you can post the database, I'll bet we can clear it up in about 30 seconds or so.

 

[vbaInet]

Loop through the combo box and print it (either using debug.print or msgbox). It may just be a facade.

 

[JohnSmith]

If you can post the database, I'll bet we can clear it up in about 30 seconds or so.

Really sorry SOS, I don't mean to be difficult, but I'm afraid it's got a little bit of sensitive information in it - nothing anything here would care about I'm sure, but enough to make some people unhappy if I let it out. Tax values on property and such. Plus, it's fairly large and covers several forms.

Thank you very much for all your help though!

 

[SOS]

Really sorry SOS, I don't mean to be difficult, but I'm afraid it's got a little bit of sensitive information in it - nothing anything here would care about I'm sure, but enough to make some people unhappy if I let it out. Tax values on property and such. Plus, it's fairly large and covers several forms.

Thank you very much for all your help though!

How about clearing it of all of the information (Bob Larson has a great reset tool you can use to do it quickly) and then put in one or two records of bogus data just to upload.

 

[JohnSmith]

Hey, sorry about not getting back to you earlier. I managed to take care of the problem by just querying the appropriate field directly from within the VBA. I do have one new question though, if anybody has an idea. If not, I'll just find a workaround, but this one is so confusing I just had to see if anyone had heard of it before.

On report generation, I'm getting a compile error: A fairly standard 'Method or Data Member not found.' Here's the thing, though: The field I'm trying to pull data from? A hidden text box which is not only very clearly there, but even contains a value that is plainly visible when I set the box to visible temporarily. Heck, I even reference it earlier on in the VBA without issue, with

Me.Deadline

I'm using the same report to generate different letters, depending on the category of the current record the report is generated from. They all have the Deadline field. But for some reason one of the letters is fine with me referencing it and the other two aren't, despite being invoked in the exact same way. Any ideas?

 

[SOS]

You can't reference another form or report using ME. It is only good for code that is on the actual form you are referencing. If you want a value from the hidden form, you would need

Forms!YourFormnameHere.Deadline

 

[JohnSmith]

You can't reference another form or report using ME. It is only good for code that is on the actual form you are referencing. If you want a value from the hidden form, you would need

Forms!YourFormnameHere.Deadline

Oh, it's on the report in question... As I said, it's a hidden textbox that I slapped onto the report from the Existing Fields menu and hid. This is what I do for most of the fields in the report. Why it doesn't work for this one - and what's more, works for one If statement and not another - is beyond me.

 

[SOS]

Try this instead:

Me!Deadline

if the field is named Deadline. If you use the BANG and there is a field by that name it will use the FIELD instead of the control and that's what you want most of the time for REPORTS.

 

[JohnSmith]

Huh, I'll have to keep that in mind for the future... One less step to complicate things.

Unfortunately it still isn't working, which makes me think something is more wrong here than I originally guessed. I think I'll scrap my current approach and come at this from a new angle. Thanks!

 

[JohnSmith]

Okay, finally got everything figured out and the stupid thing is running the way it should. I eventually wound up scrapping all the auto-generated fields and just wrote a huge query in VBA proper, which is what I probably should have done to begin with; serves me right for trying to cut corners. Much neater. Thanks for all your help.