I am trying to get a combo box working. It has the following select statement:

But there WHERE statement seems to be ignored or is just wrong. In trying to figure this out I added a message box for the after update event as follows:



The purpose is to see the values that are used in my equals statement. But VBA does not like that statement complaining that it needs an object. Seems to me that:

is about as object as I can get. What do I have wrong here?

When I get that working, I will change the message box code to:


So I can see why the equals statement in my WHERE phrase is failing.

What do I need to change to get an object in my message box code?

PS When I forget to click the box to get an email on reply, how can I enable that later. (I.e. Subscribe to this thread.)

Edited by: bkelly on Tue Sep 15 22:19:17 EDT 2009.

Hi Bryan,

You can't refer to a table like that from a form. Try something like:

MsgBox "Club = " & Me.ComboboxName

Hope that helps...

Looks like you have 2 combo boxes on your form.
1 for Golf Club
1 for Golf Course (which shows just the Courses for the selected Club)

If your Course combo is showing NO entries, then I suspect you are not doing a Requery on the Course combo after the Club is selected.
e.g. Me.cboSelectGolfCourse.Requery

Nick

The DBGuy helped with my VBA and Nick told me about what I had forgotten on my first combo box. Yes Nick, you were exactly right about the two combo boxes. It was showing entries, but the wrong set. (To simplify my question I had left some other things out.)

But this leads to something about troubleshooting and working with data. Given a select statement such as:


If I want to use VBA, for example, and assuming that the combo box is named cboClub and is bound to tblA.first, how can I write the value of tblA.second to a text box named txtShowme or to a message box?

Thanks again for your reply.

Hi Bryan,

If tblA.second is part of the RowSource for the combobox, you can use the Column() property.

Combobox columns as zero based, so the second column can be referred to by using:

Me.ComboboxName.Column(1)

Hope that helps...

Yes, that does help.
Thank you.

After getting the combo boxes to work, I added a subform. The second combo box is bound to Golf_Course_Index and it gets the right value. The Child and Master Link attributes of the sub form are both set to Golf_Course_Index and the table that feeds the subform has that as a foreign key.

When the form with the subform is opened it immediately prompts asking for a value for Me.cboSelectGolfCourse, which is the second combo box. There is no button to click that says “Be patient, you will get the value when I click the combo box.”

When a valid number is entered, the form opens with good data. When a new value is selected for the combo box, the new value is ignored and the data is unchanged.

Just to see what is going on, I created a text box in the sub form, named txtCourseIndex, and added some code to the sub form open event. After getting errors and then just trying to write to the text box, I have failed. Here are some of my attempts.



I really expected to be able to write some text to the text box in the sub form.

How do I write to a text box in the subform?
Can someone tell me why the subform won’t get the index value from the main form?

I'm not in a position to analyze your attachment just yet, but:

- in your subform vba code, you refer to a control on the main form using the Parent property:
Me.Parent.cboSelectGolfCourse

- if this reference is in the RowSource of a ComboBox on the subform, you need to use Forms!... :
Forms!frmHoleEdit2.cboSelectGolfCourse


Hope this helps!

Nick

My main form is named frmHoleEdit.
The subform I created was named sfrmHoleEdit.

I say was because I do this:
Select the subform as it is a part of the main form (clicking the little grey square in the top left corner of the sub form until it has a smaller black square inside it)
Click the properties icon in the tool bar
Select tabs Format, Data, Event, and Other in turn
I do not find a property saying the name of the subform.
I wanted to verify that to ensure I am using the right name in the VBA code.

Within the subform is a text box named txtSubCourseIndex.
In the parent form is a combo box names cboSelectGolfCourse
So if I were to understand what Nick wrote, I could put this in the VBA code:



Access did not like that so I did a test by simplifying to:



That should just put some text in the text box. But the popup message box says:


When I type in Forms! I hope that Intellisense will prompt me for a list of options to follow Forms!
Intellisense does nothing.

To sumarize my question: Parent form name frmHoleEdit, subform name is sfrmHoleEdit, text box within the subform named txtSubCourseIndex.
How do I put the characters "text" into that text box using VBA code.

PS The code is in the after update event of the combo box in the main form named cboSelectGolfCourse.