Going to a record from combobox selection 2

[as0125]

I have a combobox on a form, with other textboxes whose values depend on the value selected from the combobox. When I make a selection from the combo, the other textboxes are correctly updated with the associated data.

However, I've noticed that I'm always on the first record. The problem I'm having is that, whenever I make a selection from the combobox, the first record on the underlying table is changed to that selection. But that's not what I want to do. My intention with the combobox is to move the user to the actual record associated with the selection just made, without making changes to the table. How do I do this?

Hope my explanation is clear.
Thanks for any help!

 

[PHV]

Take a look in the FAQ area of this forum as a whole section is devoted to combo.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

...Is this something along the lines on what you are looking for?

http://www.tek-tips.com/viewthread.cfm?SQID=876451&SPID=705&newpid=705&page=1

 

[TheAceMan1]

How are ya as0125 . . . . .

Providing the form is [blue]continuous[/blue], this is one of the functions of the Combobox Wizard. It will set code to go the selected record for you.

Just delete the one you have an re-instantiate the wizard. Select the [blue]Find a record on my form based on the value I selected in my combo box[/blue] radio button and step thru the rest of the wizard. You can add your code for populating when done.

See Ya! . . . . . .

 

[as0125]

hello all,

phv: i've gone through the combo section in the FAQs already and did not find anything that helped me solve my problem.

willir: i read the thread you included, but that too does not really help me with my current issue.

theaceman1: because i have a subform on the current form that i'm having an issue with, it is a single form, rather than a continuous form.

 

[as0125]

by the way, i've been looking into some way of coding this through vba. but i'm still new to vba (as well as access), so please let me know if this idea is possible to achieve what i want to do here:

is there some way of using the "GoToRecord" method to get the record number i want. since the list of values in the combobox is based on a table, i would need a way of looking up and storing the record number of the selected combo value in a variable. then, on the form, move to the record number, based on the variable. The problem with this is that GoToRecord needs to be "told" how many records to move.

any ideas?

 

[KenReay]

Hi

How to use Combo box as a Navigation tool faq702-4398 in this forum

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TheAceMan1]

as0125 . . . . .

So there's a subform involved. If I read you correctly, you want to goto the record on the subform selected in the combobox. Is this correct?

Is the combobox on the main form or subform?

See Ya! . . . . . .

 

[as0125]

the combobox is on the main form. when a value is selected on the combobox, i want the main form to go to the associated record number.

 

[TheAceMan1]

as0125 . . . . .

Simple enough. I made a wrong statement before, the Combobox Wizard will make a goto record combo. So to reitereate:

[blue]Just delete the one you have an re-instantiate the wizard. Select the [purple]Find a record on my form based on the value I selected in my combo box[/purple] radio button and step thru the rest of the wizard. You can add your code for populating when done.[/blue]

See Ya! . . . . . .

 

[RoyVidar]

From the initial post, it seems you are trying dual purposes with your combo - both moving to the correct record and change the value of a field (and populate some controls?).

I'd say that's not encouraged, If I need a combo to search for/find records and a combo to update a control (bound to a field in the forms recordsource) - to me, that's two different combos.

I'd say the easiest would be to delete the current combo, then create two new combos according to the advice given previously.

Often (at least on my version), the wizard doesn't provide the third alternative when created in a form with subform, the code might look something like the faq KenRey pointed to, excpet two lines seems to have been combined to one in the first example, which should probably read:

[tt]Me.RecordsetClone.Findfirst "[Id] = " & Me![ComboboxName]
Me.Bookmark = Me.RecordSetClone.Bookmark[/tt]

Roy-Vidar

 

[as0125]

Thank you all for the help! The combobox now correctly moves to the appropriate record depending on the selection made and does not change the records in the underlying table. TheAceMan1, based on your suggestion, I used the combobox wizard.

Now I have one (last) question regarding this: whenever I open the form, the combobox is blank by default. Can I have it display the first record or first value in the combobox list instead?

 

[KenReay]

In the oncurrent event of teh form put code like so

MyCombo = PrimeKeyControl

Using the approriate names for the the combo box and control of course according to your names

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Or Read the FAQ as I said several posts back, it explains how to do that as well:

Place combo box on the form

in the after update event put:

Me.RecordsetClone.Findfirst "[Id] = " & Me![ComboboxName] Me.Bookmark = Me.RecordSetClone.Bookmark

or if [Id] is a string

Me.RecordsetClone.Findfirst "[Id] = '" & Me![ComboboxName] & "'"
Me.Bookmark = Me.RecordSetClone.Bookmark

if you want the combo box to reflect the Id as you navigate through the forms dataset put (in the On Current event of the form

Me![ComboboxName] = Me![Id] <-----------********


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[as0125]

Hi Ken,

Thanks a lot for the info. I did read your FAQ previously, but overlooked the end of it.

I love the way my comboboxes are working now!!!