lookup fields

[Peter]

Hello everyone,




I would like to do the following.



I have a form that is meant to maintain client-location information. The
client-locations are numbered. When looking for a record the user has to be
able to find that record by either typing in the location number if known,
or be able to find the proper record by typing the location name, which
highly resembles the client name in most cases. If the record is found by
looking up the clients name (lookup field number 2), I would like the value
of lookup field number one to show the location number anyway, as if that
was used to find the proper record.



Maybe you've noticed this behaviour yourselves; when any record is found
through the use of a lookup field, if you keep pressing PageDown you see the
records moving down the sequence of the table they are in or the order they
are sorted by, but the lookup field remains static. Keeping it synchronized
with the currently shown record would be a nice touch.

If that isn't possible maybe field number one can then be emptied. Same
thing goes of course if lookup field number one is used and lookup field
number two can keep up.



Greetings,

Peter

 

[Arvin Meyer [MVP]]

Lookup fields have no place in a relational database. One of the problems
they cause is exactly what you are seeing.

http://www.mvps.org/access/lookupfields.htm

Instead, use a query with both tables and then use the results in your form
or query.

You can build a simple combo box search that will do exactly what you want.
As a matter of fact, the 3rd option in the combo box wizard will do just
that.

You can add as many columns as you need to in the combo, then display them
on your form with unbound text boxes controlsource property set to read the
columns (starting with zero) of the combo, like:

=cboMyCombo.Column(0)

which would be the contents of the first (hidden column).

 

[Peter]

Hello Arvin,



I'm afraid I haven't been as precise as I needed to be. To your reply, I can
say that what I have put onto my form are actually two combo box controls to
which I applied the third option in the wizard you mentioned. That goes for
both of them. In my first post I called them lookup fields because that is
their purpose. I trust the problem I first described has a solution but
since I actually used the combo boxes it's probably very different from
using the lookup fields that you say not to use.



Thank you,

Peter

 

[Arvin Meyer [MVP]]

So what you need to do is use the Current event to synchronize the combos to
the record ID:

Private Sub Form_Current()
Me.cboClientSearch = Me.txtID
Me.cboLocationSearch = Me.txtID
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Peter]

Hello Arvin,

This does the trick. The form is now showing the proper information in those
comboboxes. Thank you very much.

I changed the SQL for the client name in such a way that the list shown
alphabetically (the list of clientnumbers was already in ascending order).
Just as I had in mind.

When pressing the pagedown button, the dominating sequence is by
clientnumber. Even if you've just chosen the client by looking him up by
name, continuing to press page down will result in showing the next record
for the next client number.
Here's what I have in mind for the next step. Adding option buttons for the
user to choose between "walking through" the clients by number or by (alph)
name. Therefore, I need the underlying table to be sorted in a different way
each time the user changes his or her preferred sequence. Can you give me a
hint how I can program this or is there a (non-vba) macro that comes with
Access that can arrange for this?

Greetings,
Peter

 

[John W. Vinson]

Therefore, I need the underlying table to be sorted in a different way
each time the user changes his or her preferred sequence.

No, you do not; in fact you cannot. Tables HAVE NO ORDER.

Instead base the form on a Query, and change that query's Order By clause as
needed.

 

[Peter]

You're absolutely right of course, John. Again I wasn't thinking through
what my question should be formulated like. The comboboxes desribed in this
thread are indeed based on a query. What I should try, is to sort the
outcome of the query the right way. I think this means the sql lines need to
change from having the "ORDER BY" part in front of either Client Name or
Client Number, depending on the option button chosen. I'm shure you have
some ideas on how to do that best. Is this a complicated matter? I guess it
should also be possible to remember the record currently shown and make
shure that record is shown after changing option buttons.

Thank you in advance.

Peter

 

[John W. Vinson]

You're absolutely right of course, John. Again I wasn't thinking through
what my question should be formulated like. The comboboxes desribed in this
thread are indeed based on a query. What I should try, is to sort the
outcome of the query the right way. I think this means the sql lines need to
change from having the "ORDER BY" part in front of either Client Name or
Client Number, depending on the option button chosen. I'm shure you have
some ideas on how to do that best. Is this a complicated matter? I guess it
should also be possible to remember the record currently shown and make
shure that record is shown after changing option buttons.

You'll need to actually construct the SQL string in code, and then assign the
form's recordsource to the constructed string. It makes no difference what the
rowsources of the combo boxes are.

If you only want to change the order of records as displayed on a Form, you
may be able to instead define the Form's OrderBy property, and set its
OrderByOn property to True, without needing to mess with the query.