Bound Column in Combo Box

[Carl]

Is it possible to have more than one bound column in a combo
box? I need to search 2 txt fields of a query and would like to start
to enter the text with autofill to select an item in either column for
the parameter for a report.
If it is possible, how would the [FORMS] be attached to the 2 separate
fields in the query.

carl

 

[Guest]

Combo boxes can only have one bound column.
You may need to use two combos.

Steve

 

[Guest]

SteveM is correct.
There is one thing you need to be aware of. Using combo boxes as bound
controls can be dangerous. If you select a different item from the combo
that the current value, it will change the value and therefore change the
bound field. It is usually best to use combos only as unbound search
controls.

 

[Rick Brandt]

SteveM is correct.
There is one thing you need to be aware of. Using combo boxes as
bound controls can be dangerous. If you select a different item from
the combo that the current value, it will change the value and
therefore change the bound field. It is usually best to use combos
only as unbound search controls.

Huh? What if changing the bound value in the table is the desired result?

While ComboBoxes *can* be used for searching that is not what they are used
for the vast majority of the time.

 

[Guest]

Let me qualify that by saying for Primary or Unique key values.
If you use a bound combo to search for a record using a primary key, you
change the value of the primary key for the current record. I doubt
seriously any wants to do that on purpose.

While ComboBoxes *can* be used for searching that is not what they are used
for the vast majority of the time.

Depends on who is doing the development. I use combo's sparingly. Hard
coded value lists often require modification and redeployment.
table or query row sources can degrade performance.
It is all a matter of style.

 

[Carl]

What then is the best solution? I have a query that among
other fields pulls 2 text fields that are mnemonics for a single item.
Call them mnemonic1 and mnemonic2. It is a select query for a report
that has a macro attached to initiate a popup form with the combo box.
The current row source for the combo box is mnemonic1 from table PDM
and [Forms]![frm_Select Drug by mnemonic][combobox] is the criteria
for mnemonic1 in the query.This all works well but I can't search
mnemonic2 this way. A drug like Lanoxin 0.125mg tab may have a
mnemonic1= L125T or mnemonic2=LANO1T, both of which would callup the
drug.

I have been trying to be able to search as a query parameter
both mnemonic1 and mnemonic2 easily with one entry. I am unable to do
an append query because the table is generated from a server that will
not allow it and for some reason I have been unsuccessful at making a
union query.

Select PDM.mnemonic1 from PDM
Union All Select PDM.mnemonic2 from PDM

Even if it did work I am unsure about how to bring together
the union query, select query and combo box.
Is there no way to create an appended field (mnemonic1
appended with mnemonic2) within the query via some language to make
that work?

Thank you and sorry if my terminology is incorrect as I am
very new to this.

Carl






OnLet me qualify that by saying for Primary or Unique key values.
If you use a bound combo to search for a record using a primary key,
you
change the value of the primary key for the current record. I doubt
seriously any wants to do that on purpose.

While ComboBoxes *can* be used for searching that is not what they are used
for the vast majority of the time.

Depends on who is doing the development. I use combo's sparingly.
Hard
coded value lists often require modification and redeployment.
table or query row sources can degrade performance.
It is all a matter of style.

 

[Rick Brandt]

Let me qualify that by saying for Primary or Unique key values.
If you use a bound combo to search for a record using a primary key,
you change the value of the primary key for the current record. I
doubt seriously any wants to do that on purpose.

But one would never use a bound ComboBox to search for a record. It is either
bound OR used for searching. To say that it is dangerous to use a bound
ComboBox to perform searches makes no sense because you would never do that.

However, to state as you did "Using combo boxes as bound controls can be
dangerous" is just plain wrong. A bound ComboBox does exactly the same thing as
any other bound control. You change its value and that new value is written to
the table which is exactly what one would expect a bound control to do.

Now if one attaches "search code" to a bound ComboBox it will not behave as
desired, but that is just a mistake and any coding mistake like that is
dangerous. One could attach the same search code to a bound TextBox and the
same bad things would happen. It is nothing that is unique about a ComboBox.

 

[Guest]

If I understand correctly, you want to find a drug using either mnemonic1 or
mnemonic2. How does that relate the the drug's record? Are the mnemonics
fields in the same record with the drug?
--
Dave Hargis, Microsoft Access MVP

What then is the best solution? I have a query that among
other fields pulls 2 text fields that are mnemonics for a single item.
Call them mnemonic1 and mnemonic2. It is a select query for a report
that has a macro attached to initiate a popup form with the combo box.
The current row source for the combo box is mnemonic1 from table PDM
and [Forms]![frm_Select Drug by mnemonic][combobox] is the criteria
for mnemonic1 in the query.This all works well but I can't search
mnemonic2 this way. A drug like Lanoxin 0.125mg tab may have a
mnemonic1= L125T or mnemonic2=LANO1T, both of which would callup the
drug.

I have been trying to be able to search as a query parameter
both mnemonic1 and mnemonic2 easily with one entry. I am unable to do
an append query because the table is generated from a server that will
not allow it and for some reason I have been unsuccessful at making a
union query.

Select PDM.mnemonic1 from PDM
Union All Select PDM.mnemonic2 from PDM

Even if it did work I am unsure about how to bring together
the union query, select query and combo box.
Is there no way to create an appended field (mnemonic1
appended with mnemonic2) within the query via some language to make
that work?

Thank you and sorry if my terminology is incorrect as I am
very new to this.

Carl






OnLet me qualify that by saying for Primary or Unique key values.
If you use a bound combo to search for a record using a primary key,
you
change the value of the primary key for the current record. I doubt
seriously any wants to do that on purpose.

While ComboBoxes *can* be used for searching that is not what they are used
for the vast majority of the time.

Depends on who is doing the development. I use combo's sparingly.
Hard
coded value lists often require modification and redeployment.
table or query row sources can degrade performance.
It is all a matter of style.
--
Dave Hargis, Microsoft Access MVP

Huh? What if changing the bound value in the table is the desired result?

While ComboBoxes *can* be used for searching that is not what they are used
for the vast majority of the time.

 

[Guest]

Rick,
I think you are over reacting the the word dangerous. Perhaps I could have
used a better word. I have no issue with using combos to load specific
values into a bound field. As I said before, I try to minimize using them
because over use of combos, particularly those with row sources that will
pull large volumes of data, for performance reasons. You cannot deny a form
with a lot of combos loads more slowly. But, for navigating to a specific
record, it has to be unbound.

You know you have seen posts here where people are getting key violationg
errors because they are using a bound combo to search for a record. That is
the danger.

 

[Carlgardner]

Yes that is correct. Both are in the same record. The PDM table is linked to
a current history table, but I didn't think that would matter to solve the
search problem. Searching by the mnemonic brings up all the people on that
drug and when it was started...

Carl

If I understand correctly, you want to find a drug using either mnemonic1 or
mnemonic2. How does that relate the the drug's record? Are the mnemonics
fields in the same record with the drug?

What then is the best solution? I have a query that among
other fields pulls 2 text fields that are mnemonics for a single item.

[quoted text clipped - 53 lines]

 

[Guest]

It shoud be fairly straight forward. You could use an unbound combo with a
union query that will include a list of each unique mnemonic:

SELECT DISTINCT mnemonic1 FROM DrugTable;
UNION SELECT DISTINCT mnemonic2 FROM DrugTable;

--
Dave Hargis, Microsoft Access MVP

Yes that is correct. Both are in the same record. The PDM table is linked to
a current history table, but I didn't think that would matter to solve the
search problem. Searching by the mnemonic brings up all the people on that
drug and when it was started...

Carl

If I understand correctly, you want to find a drug using either mnemonic1 or
mnemonic2. How does that relate the the drug's record? Are the mnemonics
fields in the same record with the drug?

What then is the best solution? I have a query that among
other fields pulls 2 text fields that are mnemonics for a single item.

[quoted text clipped - 53 lines]

While ComboBoxes *can* be used for searching that is not what they are used
for the vast majority of the time.

 

[Carlgardner]

I will try the union query again, but previously it kept coming back with a
syntax error but I don't think I used DISTINCT and I used ALL. Just to
reclarify, you cannot created an appended field within a query itself...only
in a table?

It shoud be fairly straight forward. You could use an unbound combo with a
union query that will include a list of each unique mnemonic:

SELECT DISTINCT mnemonic1 FROM DrugTable;
UNION SELECT DISTINCT mnemonic2 FROM DrugTable;

Yes that is correct. Both are in the same record. The PDM table is linked to
a current history table, but I didn't think that would matter to solve the

[quoted text clipped - 11 lines]

 

[Guest]

what do you mean by "appended field"?
--
Dave Hargis, Microsoft Access MVP

I will try the union query again, but previously it kept coming back with a
syntax error but I don't think I used DISTINCT and I used ALL. Just to
reclarify, you cannot created an appended field within a query itself...only
in a table?

It shoud be fairly straight forward. You could use an unbound combo with a
union query that will include a list of each unique mnemonic:

SELECT DISTINCT mnemonic1 FROM DrugTable;
UNION SELECT DISTINCT mnemonic2 FROM DrugTable;

Yes that is correct. Both are in the same record. The PDM table is linked to
a current history table, but I didn't think that would matter to solve the

[quoted text clipped - 11 lines]

While ComboBoxes *can* be used for searching that is not what they are used
for the vast majority of the time.

 

[Carlgardner]

I suppose a new field of the union of the 2 mnemonic fields within the
original query so I could attach the form with the combo box in it. I was
worried that if I do get the UNION query to work, can I then attach the form
to mnemonic1 as before and still have it popup when the report starts?

what do you mean by "appended field"?

I will try the union query again, but previously it kept coming back with a
syntax error but I don't think I used DISTINCT and I used ALL. Just to

[quoted text clipped - 12 lines]

 

[Guest]

Sorry, Carl, I am not understanding what you are asking.
--
Dave Hargis, Microsoft Access MVP

I suppose a new field of the union of the 2 mnemonic fields within the
original query so I could attach the form with the combo box in it. I was
worried that if I do get the UNION query to work, can I then attach the form
to mnemonic1 as before and still have it popup when the report starts?

what do you mean by "appended field"?

I will try the union query again, but previously it kept coming back with a
syntax error but I don't think I used DISTINCT and I used ALL. Just to

[quoted text clipped - 12 lines]

While ComboBoxes *can* be used for searching that is not what they are used
for the vast majority of the time.

 

[Carlgardner]

Thank you for your time...I will work on the union query

Sorry, Carl, I am not understanding what you are asking.

I suppose a new field of the union of the 2 mnemonic fields within the
original query so I could attach the form with the combo box in it. I was

[quoted text clipped - 7 lines]