link combobox with link record

[Charlie]

I am having difficulties linking two comboboxes.

1) I've set up combobox1 to received values from field1 in
table1. However there are repeated values and I would
like to filter out the repeated values.

2) Then from the numbers that appear, i'd like combobox1
to link to combobox2. Combobox2 receives values from
field2 in table1. However, I don't want all the values
from field2 in table1 to apear (since it is about 80
numbers). I want combobox2 to only display the data that
is associated with the data chosen from combobox1.

3) When the value on combobox2 is pressed, I would like
for form1 to appear and take me to the exact record that
is associated with the value that was clicked on
combobox2. I've already created a form with the wizard
that has all the fields from table1 on it. And I've made
sure that the combobox2 values are unique. So I know
there is a way to do this since I've done it in VBE in
excel; but it happens to be different for access.


Please help. I really need to know this asap. So I need
to know how to
1) filter repeated values
2) link the comboboxes
3) link combobox2 values selected to the form's specific
record

 

[Tim Ferguson]

1) I've set up combobox1 to received values from field1 in
table1. However there are repeated values and I would
like to filter out the repeated values.

cboOne.RowSource = "SELECT DISTINCT FieldOne FROM MyTable " & _
"ORDER BY FieldOne"

2) Then from the numbers that appear, i'd like combobox1
to link to combobox2. Combobox2 receives values from
field2 in table1. However, I don't want all the values
from field2 in table1 to apear (since it is about 80
numbers). I want combobox2 to only display the data that
is associated with the data chosen from combobox1.

cboTwo.RowSource = "SELECT ALL FieldTwo FROM MyTable " & _
"WHERE FieldOne = Forms!ThisForm!cboOne.Value " & _
"ORDER BY FieldTwo"

and put this in the cboOne_AfterUpdate event

cboTwo.Requery

3) When the value on combobox2 is pressed, I would like
for form1 to appear and take me to the exact record that
is associated with the value that was clicked on
combobox2.

This is the cboTwo_AfterUpdate event:

Docmd.OpenForm "MyOtherForm", ,, "FieldTwo = " & cboTwo.Value

which uses the WHERE parameter for the form. If the FieldTwo field is text
rather than numeric, you'll need

"FieldTwo = """ & cboTwo.Value & """"


You might find it kinder to attach this to a separate command button, since
it's so easy to click a combo box entry before meaning to, and then
there'll be a long wait for the form to open and having to cancel it.

HTH

Tim F

 

[To: Tim]

How do I attach a separate command button like you
mentioned? That sounds like a good idea. So far I only
know how to create the command buttons and link them to
forms. How does it work for command buttons and specific
values of the combobox?

 

[Tim Ferguson]

How do I do what you suggested? Having a command button.
That sounds like a great idea! I know how to link forms to
commandbuttons adn whatnot, but not a value from a
combobox. Can you tell me how to do that pleasE?

Ummm: you just drag a command button from the toolbox onto the form, and
set its properties, like Name and Caption etc., just the same as all the
other controls. Right-click the button and choose Build Code, and it'll
default to the Click event, which is what you want.

If you are being harassed by the Forms Wizard, just hit Cancel and it'll go
back to its cave -- it'll leave the button there and stop trying to tell
you how to make it work.

There is life in Access without the wizards

HTH


Tim F