Combo box to find record on subform

[Aximboy]

I am working on a database where I have a subform in datasheet view. Is it possible to use a combo box on the main form to make the arrow on the subform's datasheet view point to the information the combo box requests?
I tried to use the "GoToControl" & "FindRecord" macros but it only sets the focus on the first record in the subform.

 

[BillPower]

Hi Aximboy,

Take a look at Buttons.zip at

http://www.wpower.fsbusiness.co.uk/

Buttons.zip shows how to do this.

Bill

 

[Manaburrn]

Buttons.zip is riddled with erorrs - I can't even click the first button without getting errors.

 

[BillPower]

Hi Manaburrn,

That's quite a profound statement of yours. Enlighten us to which errors you are receiving.

You have my word that I will stay up all night to put them right if necessary. It's now 22:00 BST in other words 10 'O clock in the evening in London.

Bill

 

[Aximboy]

Thanks Bill, that's quite a program! However, I'm still figuring out how to do that on my datasheet view form (I'm a new Access user).

 

[Aximboy]

Ok I finally figured out how to do it using macro. I used 2 "GoToControl" macros, first to set the focus to the subform and the second to set focus to the text box. Then I used "FindRecord" macro to match whatever is on my combo box. But what makes it difficult for me is (and I should've mentioned this on my first post, sorry) that I also have another combo box on the main form to select companies, and under each of those companies are the contract numbers which are listed on a subform on datasheetview. And this is where my problem begins, the 2nd combo box on the main form needs to contain only the contract numbers from the selected company.

 

[BillPower]

Hi Aximboy,

My apologies for not getting back to you sooner, but have been on holiday.

Basically, the example I've given works the same for both continuous forms and datasheet views.

Note that I have 3 combos, cmbAuthor, cmbTitle and cmbPublisher. I also have 3 hidden unbound text boxes corresponding to the combos, txtAuthor, txtTitle and txtPublisher. The default values of these text boxes are "*".

The query "qry_Find/Filter" which the FindExampleSubForm is bound to uses the hidden unbound text boxes as it's criteria.

When the user makes a selection from any of the combos the corresponding text box is updated with that selection, the other combos and the sub form are requeried.

If you look at the code in the After Update event of each combo in the FindExample form and the On DblClick event of txtAuthor in the FindExampleSubForm form you should get a good idea of what is going on and be able to adapt your own DB to this method.

I strongly advise to use VBA code to build your applications, not macros as you have intimated above.

If you have any further questions, don't hesitate to ask.

Bill