Filling drop list

  • Thread starter Thread starter Pastor Del
  • Start date Start date
P

Pastor Del

how can i fill a single field in a drop list with data from 2 fields in
seperate tables. my db has 2 tables, one for data that is less than a month
old and one for older data. i need to provide my users the option to select,
from a single drop list, serial numbers both tables. hopefully there is a
way to do it with a query in the row source of the combo box instead of
creating a new record set, combining all the data into it & setting it as the
row source.
 
how can i fill a single field in a drop list with data from 2 fields in
seperate tables.  my db has 2 tables, one for data that is less than a month
old and one for older data.  i need to provide my users the option to select,
from a single drop list, serial numbers both tables.  hopefully there is a
way to do it with a query in the row source of the combo box instead of
creating a new record set, combining all the data into it & setting it asthe
row source.
Click to expand...

You can use a UNION query to populate the drop list:
SELECT YourField FROM T1
UNION
SELECT YourField FROM T2

This will provide yoy with values from both tables. But is that
enough? What are you doing with the values?
If you need to knowe from wigh table a value is derived from you could
have something like:
SELECT YourField 1 as Source FROM T1
UNION
SELECT YourField 2 as Source FROM T2

On the other hand, you might consider to use only one table. Why
having the same kind of data in two tables?

Groeten,

Peter
http://access.xps350.com
 
I'll echo Peter's sentiment ... why do you have two tables?

If both tables have essentially the same types of fields, you've committed
spreadsheet on Access. Access is a relational database, not a spreadsheet
on steroids. If you want to get the best use of the relationally-oriented
features/functions Access offers, you can't feed it 'sheet data...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
There are about 100,000 records accumulated in the table in the past 2 years
and they will continue to accumulate because QA wants to maintain these
records as a 7 year backup. The main db user has to wait several seconds
each time she accesses the combo box, which is many times a day. By moving
the older records to an archive table and leaving the records my main user
needs in the current table I can speed up the precess & relieve her
frustration. That part is simple. My problem arises from trying to provide
QA a way to search through all the data. I'll try your code this Monday. I
don't need to know which table the data comes from because they are unique
serial numbers and QA simply needs to be able to pick one from the drop list.

Thanks for your help.
 
Hi all,

I have no connection with Microsoft and not interested in receiving these
email. Do not know why I am getting them in my Ms Entourage.

Is it by mistake or is it Entourage's standard feature..BTW I am new to
Entourage.

Curious to know.

Ruchika
 
It may be that you don't need to move the records at all. Most of the time
would be taken up with filling the array for the combobox. Also you are going
to have a problem since a combobox is limited to 65,000 (approx) records.

I would change the combobox in two ways. Filter the records by the date field
and make sure the date field is indexed.

Normal row source of the combobox would be
SELECT FieldA, FieldB
FROM TheTable
WHERE THeDateField >= DateAdd("d",-30,Date())
ORDER BY FieldA

Next to the combobox, I would add button or an option frame that would let you
build a different row source - using the union query if you insist on
archiving records - usually a bad choice. However, you are going to run into
problems with the number of records exceeding the 64K limit.

I suggest you take a look at

Combos with Tens of Thousands of Records

Combos become unworkable with many thousands of records, even many hundreds in
Access 2. By loading records into the combo only after the user has typed the
first three or four
characters, you can use combos far beyond their normal limits, even with the
AutoExpand property on.

This is the idea:

1.Leave the combo’s RowSource property blank.
2.Create a function that assigns the RowSource after a minimum number of
characters has been typed. Only entries matching these initial characters are
loaded, so the combo’s
RowSource never contains more than a few hundred records.
3.Call this function in the combo’s Change event, and the form’s Current
event.

This tip has been posted by Allen Browne for years:

http://allenbrowne.com/ser-32.html

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top