Use combo box to update sub-form.

[jaeezzy]

Hi I'm a newbie. Recently I'm trying hard to figure things out in Access. I've made a form named Order and inside it is a sub-from with the name order_details. I have a combobox with all the order numbers displayed. Now, when my form first loads combobox is selected to nil and nothing is displayed in the order_details subform. But as I select order number in the combo box I want order_details to be updated with all the details of that particular order number. Below is the Order table:

Order:
----------------------------------
OrderNbr | OrderDate | CustomerID
----------------------------------
O1001 | 2006-05-10 | 1
O1002 | 2006-06-05 | 2
O1003 | 2006-06-15 | 3

Customer:
----------------------------
CustomerID | CustomerName
----------------------------
1 | John
2 | Hary
3 | Smith

How I'm trying to achieve it now is I've written one query and saved it as subformquery and while making order_details subform I have selected all the fields from this query by selecting "Use existing tables and queries". Later I edited this query with this at the end:
"WHERE ((OrderNbr)=[forms]![Order]![combobox]);"

And from VBA in which I'm completely dumb, I have called the after_update method as:
DoCmd.OpenQuery "subformquery"

But it opens a new dialog box where I have to put again OrderNbr and after pressing Ok it updates the subform but also opens a query output window separately. So, how can I jus update the subform without all these. Thanks.

 

[wazz]

welcome to awf.

in "WHERE ((OrderNbr)=[forms]![Order]![combobox]);", make sure 'combobox' is the name of your combobox.

in the afterupdate routine, you want to requery the subform:
me.nameofsubform.requery

there might be some other small things but start with those.

 

[jaeezzy]

Thank you, it worked like a magic. Thought now it would be easy to do the same to a text field as well but found out there's a different method. So what steps should I follow to just make the "CustomerName" appear in the text field when selecting a combo box. I tried defining the source while making the form. It works but it just comes in a text field. So, I changed it to combo box but when I try to click a particular item from the drop down list of combo box it beeps and nothing happens. It only works fine with the default right and left key that comes with the form. Moreover, I didn't find this way much flexible either, coz I might have numerous text fields reflecting different table and plus later I might need to add more text field again. So, I would be very grateful if I could get the steps required to achieve my goal. Thank you

 

[jaeezzy]

Ok, I sorted it out. That was hell easy but until you don't know the way it can make life hell. Thanks..

 

[jaeezzy]

Hi, again got in problem. How do I do with the textfield when its in the tab controller in the same form. This code:

Me.textfieldName = comboboxname.column(number)

didn't work for the text field inside the tab controller. I tried entering the tabcontroller name but when I enter Me.tabctrlname.(I don't get the names of those text fields) So, plz help me out.