desperate please help

[Kyle.H.Green]

Hello everyone and thankyou for taking your time to read this. I
apologize for posting in multiple groups (for some of you have noticed)
but I need maximum feedback. Here's the nitty gritty.

The database is for BF Goodrich (Michelin's company). This could be
distributed worldwide, so if you have a direct helping hand in it, I
will have no problem with putting names and contact details in the
information somewhere.

Lets start with the table. Here's a snapshot of what it looks like.

http://i16.photobucket.com/albums/b17/bamabmw/Michelin/table.jpg

What is being stored is information about engineering schematics. Large
G sized drawings that are about 3 feet tall and 4 feet wide. The table
holds all details including a link to the file on the network.

The form I've constructed looks like this.

http://i16.photobucket.com/albums/b17/bamabmw/Michelin/template.jpg

=================================================================
FIRST AREA OF TROUBLE
=================================================================

If you'll notice I labed 4 fields as filters. I would like these to be
dropdown boxes. PROJECT, PRIMARY, SECONDARY, and DISCIPLINE, while
holding 22,000 values, are of a limited variety. Project for example
has Group9, Group7, VMI, Electrical, but over and over.

I would like the first filter (DROPDOWN) to only show a list of all
possible ones, and then that filtered list is passed on to the 2nd,
then to the 3rd, and finally to the 4th.

At this point I think a LISTBOX could be overlayed ontop of the NOTE2
area so that the filtered drawings could be selected (which will update
the preview image placeholder on the right).

=================================================================
SECOND AREA OF TROUBLE
=================================================================

Examine note 3. I put this search field here because this program is
supposed to make life easier for a whole load of engineers, however I
have been unable to get the following results.

1 > user enters keyword(s) in formbased search field
2 > all fields in the table are searched. ALL of them, for any and all
occurences
3 > listbox (organized by the drawing numbers @ note1) appears for them
to select from, updating preview on right side




-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

I'm not a professional coder, and my Access experience is limited. Not
the ideal situation for replacing old software
(http://i16.photobucket.com/albums/b17/bamabmw/Michelin/workflowsnapshot.jpg)
but it's something that must be done.

My email is visible to you but thanks to ISIS, IBM and Michelin
internet security standards and practices, I'm unable to access gmail
from work (my replies could be slow.)

If you think you can directly help me over the phone, please feel free
to contact me (Kyle) at the following work number. Our automated
attendant is reachable at 1-205-752-1529 extension 433 (dial anytime
after the female voice begins talking). Thankyou everyone for posts,
emails, and calls. You don't know how much I appreciate it.

~ Kyle Green

 

[Rick B]

PLEASE refrain from posting your question in multiple groups.

Netiquette FAQ
http://www.mvps.org/access/netiquette.htm

 

[Immanuel Sibero]

Kyle,

apologize for posting in multiple groups (for some of you have noticed)
but I need maximum feedback. Here's the nitty gritty.

I'm sure someone had called or will call you on this. You should just post
in one group that's closely related to your question. As far as maximum
feedback/exposure, you have probably hurt your chances of maximum feedback
by posting in multiple groups.

I have done something similar to what you're describing.

FIRST AREA OF TROUBLE
=================================================================

If you'll notice I labed 4 fields as filters. I would like these to be
dropdown boxes. PROJECT, PRIMARY, SECONDARY, and DISCIPLINE, while
holding 22,000 values, are of a limited variety. Project for example
has Group9, Group7, VMI, Electrical, but over and over.

I would like the first filter (DROPDOWN) to only show a list of all
possible ones, and then that filtered list is passed on to the 2nd,
then to the 3rd, and finally to the 4th.

I'm assuming you that the order in which you evaluate the search criteria
(i.e. Project, Primary, Secondary, Discipline) is preset in the order that
you listed above. For example, Primary comes after Project, Secondary comes
after Primary, and so on.


Conceptually, this could be an alternative:

You would have four comboboxes each corresponding to the four filters. You
would make heavy use of the After Update event for each combobox. For
example, the After Update event of the Project combobox would set the
rowsource of the Primary combobox. Similarly, the After Update event of the
Primary combobox would set the rowsource of the Secondary combobox, and so
on and so forth. Use SELECT DISTINCT to eliminiate duplicates.

You would need a subform to list all records satisfying all four filters
(i.e. in datasheet format). Similar to the *Find Record* button you have,
you would also have a command button to *build* the SQL string based on the
values of comboboxes (i.e. filters). This SQL string would in turn be set as
the RecordSource of the subform.

The user can then select a record (i.e. drawing) from the datasheet. The
OnCurrent event of the subform can then set the selected drawing to be
displayed.

SECOND AREA OF TROUBLE
=================================================================

Examine note 3. I put this search field here because this program is
supposed to make life easier for a whole load of engineers, however I
have been unable to get the following results.

1 > user enters keyword(s) in formbased search field
2 > all fields in the table are searched. ALL of them, for any and all
occurences
3 > listbox (organized by the drawing numbers @ note1) appears for them
to select from, updating preview on right side

You could use the same subform/datasheet as above for this section. The
existing *Find Record* button should, again, build a SQL string which would
be set as the RecordSource of the subform/datasheet. Since you want to
search all fields in the table, you may have to loop through all the fields
in the table while building the SQL string looking for a match against the
user supplied search keyword and use UNION to combine all the records to be
displayed in the subform/datasheet. (NOTE: there may be a better strategy to
search the whole table, the above is what I can think of quickly).

I realize that this post is more conceptual than detailed steps. We could
get into the detail if we decide this is the way to go. I'm sure there would
be other strategies offered by other posters.


HTH,
Immanuel Sibero

 

[Keith Wilby]

Hi Kyle, comments in-line ...

=================================================================
FIRST AREA OF TROUBLE
=================================================================

If you'll notice I labed 4 fields as filters. I would like these to be
dropdown boxes. PROJECT, PRIMARY, SECONDARY, and DISCIPLINE, while
holding 22,000 values, are of a limited variety. Project for example
has Group9, Group7, VMI, Electrical, but over and over.

If you want the DISCIPLINE combo to have unique entries, use the "Distinct"
keyword in your query, ie "Select Distinct MyField From tblMy table ...".
You might want to consider an "is not null" in there too.

I would like the first filter (DROPDOWN) to only show a list of all
possible ones, and then that filtered list is passed on to the 2nd,
then to the 3rd, and finally to the 4th.

I would use the combo box's after update events to dynamically build up a
SQL string to use as the form's record source. There would be no "passing"
of data between combos.

At this point I think a LISTBOX could be overlayed ontop of the NOTE2
area so that the filtered drawings could be selected (which will update
the preview image placeholder on the right).

=================================================================
SECOND AREA OF TROUBLE
=================================================================

Examine note 3. I put this search field here because this program is
supposed to make life easier for a whole load of engineers, however I
have been unable to get the following results.

1 > user enters keyword(s) in formbased search field
2 > all fields in the table are searched. ALL of them, for any and all
occurences
3 > listbox (organized by the drawing numbers @ note1) appears for them
to select from, updating preview on right side

Again, if it were me doing it, I'd be looking to dynamically build a SQL
string based on user input. There's no doubt that this is do-able, but if
you're considering employing a professional developer (and by your own
admission you're not a professional coder and have limited Access
experience) then they might want to start with a clean sheet. It's
sometimes quicker to do this than to fix something that's been put together
by a novice (please don't be offended, we all have to start somewhere!).

Regards,
Keith.
www.keithwilby.com

 

[Guest]

You can build your own drop-downs (populate them with whatever you want) in
Visual Basic. Procedure is quite straightforward and easy. Thus, you can use
the "results" of each preceding "filter" (selection) to "seed" the next one.
Feel free to contact me for more information:

jor_el@NO SPAMspinfinder.com

Please include, in your email, how much VB experience you have.

Dennis