By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,704 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Configurable lookup?

P: n/a
Els
Hi,

I have create a database application and now I am confronted with the
following user requirement:

In the database the first name, last name and initials of a person are
stored.

A user should be able to set a personal preference on how lookup
comboboxes with person names in it should behave. Three different
options should be available:

[FirstName] & " " & [LastName] (e.g John Doe)
[LastName] & ", " & [FirstName] (e.g. Doe, John)
[Initials] (e.g. JD)

I store this preference in a table tblConfig as either option 1, 2 or 3.

But now the difficult part: how to make the displayed column in the
combobox dependent on the user preference? Something like:

Case option 1
Display: [FirstName] & " " & [LastName]
Case option 2
Display: [LastName] & ", " & [FirstName]
Case option 3
Display: [Initials]
I hope my question makes sense. All help is highly appreciated!

Regards,

Els
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
In your select statement, builld the complete query for each case,
then after the End Select, set the row source to that query:

Select Case
Case 1
q="SELECT [FirstName] & ' ' & [LastName], ......"
Case 2
q="SELECT [LastName] & '. ' & [FirstName]..."
case 3
q="SELECT Initials,..."
End Select
me.cboWhatever.RowSource = q

Depending on the length of the query you could clean up the code, too.

On 01 Mar 2004 19:29:38 GMT, Els <El*@nospam.com> wrote:
Hi,

I have create a database application and now I am confronted with the
following user requirement:

In the database the first name, last name and initials of a person are
stored.

A user should be able to set a personal preference on how lookup
comboboxes with person names in it should behave. Three different
options should be available:

[FirstName] & " " & [LastName] (e.g John Doe)
[LastName] & ", " & [FirstName] (e.g. Doe, John)
[Initials] (e.g. JD)

I store this preference in a table tblConfig as either option 1, 2 or 3.

But now the difficult part: how to make the displayed column in the
combobox dependent on the user preference? Something like:

Case option 1
Display: [FirstName] & " " & [LastName]
Case option 2
Display: [LastName] & ", " & [FirstName]
Case option 3
Display: [Initials]
I hope my question makes sense. All help is highly appreciated!

Regards,

Els


Nov 12 '05 #2

P: n/a
Els
Thanks Larry, and where do I implement this kind of code? The BeforeUpdate
event of the combobox?

Another question: Is it best to read the preference settings each time from
the table or should I read it once and declare a public variable? What is
best practice?

Regards, Els

Larry Johnson <la***@gjerager.com> wrote in
news:hr********************************@4ax.com:
In your select statement, builld the complete query for each case,
then after the End Select, set the row source to that query:

Select Case
Case 1
q="SELECT [FirstName] & ' ' & [LastName], ......"
Case 2
q="SELECT [LastName] & '. ' & [FirstName]..."
case 3
q="SELECT Initials,..."
End Select
me.cboWhatever.RowSource = q

Depending on the length of the query you could clean up the code, too.

Nov 12 '05 #3

P: n/a
If the option value is in a preference setting, the code should
probably go in go in Form_Load.

On when to read the settings, you'll find a variety of opinions. If an
application has a main menu form (frmMenu) with buttons to open other
forms (frmWork) to do the work, I read each time preferences in
frmWork's Form_Load. That way if you have an error, opening the form
again sets the preferences properly. If you just do it once in the
frmMenu you'd have to go into design view for frmMenu to read the
preference.

On 01 Mar 2004 23:13:27 GMT, Els <El*@nospam.com> wrote:
Thanks Larry, and where do I implement this kind of code? The BeforeUpdate
event of the combobox?

Another question: Is it best to read the preference settings each time from
the table or should I read it once and declare a public variable? What is
best practice?

Regards, Els


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.