Finding Articles easiely in long List of Items (1 Viewer)

silentwolf

Active member
Local time
Today, 07:41
Joined
Jun 12, 2009
Messages
545
Hi guys,

Would like to get some input or thoughts about following situation.

Background of the Database/Situation:

1. A Company gets an Order to do some Work(maintenance) for some Bath/Sauna Equipments

2. There are one or many different Equipments with different Manufacturers in one Order.

3.Each Equipment needs Parts for completion of this maintainence or repair tasks.
Changing all Seals or need a new Pump, Cables and so on...

4. If these Parts are required to complete these maintainance tasks we need to find those parts quickly and order them from the
manufacturer.

5. Each Manufacturer has given us a Partlist and as you can guess there are several thousands of it wich of course not all of them are
relevant for our "Flexline Superbath" maintanince work.

For better understanding I did include the Database Structure.

Workflow:
1. In the Orderform I did create a sfm_AuftragModelle
gives me all the Models related to the Order

2. A button on that subform to open only the parts for that relevant supplier

3. in frmSupplier1Pop is a textbox to search for a part

4. I created a seperate table tbl_ArtikelModell as a linked table to tbl_Modell & tbl_Artikel
in Order to save the selected Item to the appropiate article with a many to many relationship

So the Art_ID = 1 can be used in Mod_ID = 5, 15, 22

In my sfm_Auftrags Modelle (included as a picture) I got a button with three dots... that opens
the frmSupplierPop

I do the search and press Ok which fills thanks to you guys the ModelID
and the Art_ID into tbl_ArtikelModell

So finally to my question :)
When I found for example "Dichtungsring f. Ansaugstück" and added it to my tbl_ArtikelModell by pressing the ok button.

So there will be in later time for Mod_ID = 1 Art_ID, 1,125,12545,33...
what has been added to that Modell.

So how could I switch between all "Articles" and say only those who where already once added to my table tbl_ArtikelModell?

Sorry for this rather long threat!

Can someone give me a hint or solution how to go about this?

Hope it is clear what I am trying to do here.


Many thanks in advance!!

Albert
 

Attachments

  • SearchForm.JPG
    SearchForm.JPG
    60 KB · Views: 308
  • Auftragsmodelle.JPG
    Auftragsmodelle.JPG
    30 KB · Views: 279
  • FD_AuftragsStrukture.JPG
    FD_AuftragsStrukture.JPG
    63.8 KB · Views: 223

CJ_London

Super Moderator
Staff member
Local time
Today, 14:41
Joined
Feb 19, 2013
Messages
16,555
I think I understand the principle of what you are trying to do, but not sure I follow your description well enough to suggest how you can achieve it.

I think you are asking how to modify your search form ( frmSupplier1Pop?) so when a user selects the article suche, the list generated is limited to the model selected in your auftrags modelle form.

If this is correct the recordsource to your search form would need to have an inner join to your tbl_ArtikelModell table which in turn would have criteria to limit those records to the model selected in your auftrags modelle form (aufm_ID?). This could be passed to your search form as an openarg

Suggest provide some example data and the outcome required
 

silentwolf

Active member
Local time
Today, 07:41
Joined
Jun 12, 2009
Messages
545
Hi CJ_London,

thanks for your reply!!

I think you are asking how to modify your search form ( frmSupplier1Pop?) so when a user selects the article suche, the list generated is limited to the model selected in your auftrags modelle form.
Yes that is correct :)
But also it would be great to switch from finding all or just the limit version so to speak.

auftrags modelle form (aufm_ID?)
Yes that would be the AufM_ID which is the ID from sfm_AuftragModelle

Suggest provide some example data and the outcome required
Would you a small database of the forms too?

I would need to create a small version of it so bare with me for a little as I need to create a smaller version of it.
Or do you need just a Database with those tables?

Many thanks for your Help!!

Much appreciated!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:41
Joined
Feb 19, 2013
Messages
16,555
small database would be good - you could use a checkbox, button or option group next to your textbox to enable the 'switch' and change the recordsource criteria or form filter to apply the filter or not
 

silentwolf

Active member
Local time
Today, 07:41
Joined
Jun 12, 2009
Messages
545
Hi,

I add that file I hope it is not to big?

Maybe you could have a look?

Thanks!
 

Attachments

  • TestDatabase.zip
    116.2 KB · Views: 307

CJ_London

Super Moderator
Staff member
Local time
Today, 14:41
Joined
Feb 19, 2013
Messages
16,555
I've modified your db to do what I think you want. Unfortunately tbl_ArtikelModell is not populated with any values so I entered a few to test it works.

I've only modified frmHschPop, you will need to change the other one.

By each change I have put a comment 'CJ Change'. in VBA do a find to find all the changes.

There is 1 change in sfm_AuftragModelle and 3 in the frmHschPop form.

You will need to make the changes for frmHroPop

To test, select either of the Greatebath records in the Auftrags Modelle subform. The frmHschPop will open filtered to the appropriate article. Click show all to show all the records

I have not looked at how your app works - the assumption is the frmHschPop form is closed to be reopened when a new modell is opened

hope that it meets your needs
 

Attachments

  • TestDatabaseCJ.zip
    123.8 KB · Views: 279

silentwolf

Active member
Local time
Today, 07:41
Joined
Jun 12, 2009
Messages
545
Hi CJ_London,

Many thanks for your help!
It looks fantastic!

Just need to get my head around it to see how you managed it and how it all works but wanted to thank you for your input right away !

Many many thanks to you!

(y)(y)(y)
 

Users who are viewing this thread

Top Bottom