Subform ComboBox (1 Viewer)

RuralGuy

AWF VIP
Local time
Yesterday, 18:45
Joined
Jul 2, 2005
Messages
13,826
It would be nice if the combobox only listed survey types available for the specific pest (i.e., the selected record of the main form).
I'm probably just stupid here Justin but how do you know what "survey types" are available for each "specific pest"?
 

JChase

Registered User.
Local time
Yesterday, 17:45
Joined
Apr 13, 2018
Messages
12
Hey Ruralguy.

So, I've setup the database so there's a set list of survey types (in "Survey types" table) to choose from to populate the Survey Type field of the "Protocols" table. See screenshot of protocols table. When we add a new pest record (via my Data Entry form, or by me going into the backend and just populating the tables), we'll already have researched the species and know what types of surveys are available. As you can see in the screenshot, there are usually only 1-4 survey types applicable to the species (from the ~10 possible types). I'm not sure if this answers your question.

One more thing: perhaps this is obvious, but a particular survey type (e.g., pheromone) for a particular pest (e.g., blackheaded budworm) is not the same as that same survey type for a different pest (e.g., pheromone for spruce budworm). The "Overview" (description) will be specific to each case.
 

Attachments

  • protocols screenshot.jpg
    protocols screenshot.jpg
    98 KB · Views: 84

mike60smart

Registered User.
Local time
Today, 00:45
Joined
Aug 6, 2017
Messages
1,899
Hi
PMFJI but I suggest that you need to setup your tables so that you can Cascade your requirements between 2 Combobox's.

Your current Protocols table lists all of the PestID's as text.

I would recommend that you change the structure of the Protocols table so that it only has the following fields:-

tblProtocals
-ProtocolID - PK - Autonumber
-Pest - text

tblSurveyTypes
-SurveyTypeID - PK - Autonumber
-ProtocolID - FK - Number (Linked to PK from tblProtocols)
-SurveyType - Text

If you then create a Main Form / Subform for Data Input of Protocols & Survey TYpes

Then on your Main Data Input Form you can have 2 Combobox's -
1st Combobox would display a List of All Protocols
2nd Combobox wouldONLY show those Survey Types associated with the Protocol Selected
 

RuralGuy

AWF VIP
Local time
Yesterday, 18:45
Joined
Jul 2, 2005
Messages
13,826
You probably already know Justin that computers are pretty dumb. They only know what you tell them. Mike has a pretty good idea on how you could tell the computer what surveys go with what protocols.
 

JChase

Registered User.
Local time
Yesterday, 17:45
Joined
Apr 13, 2018
Messages
12
Hi
PMFJI but I suggest that you need to setup your tables so that you can Cascade your requirements between 2 Combobox's.

Your current Protocols table lists all of the PestID's as text.

I would recommend that you change the structure of the Protocols table so that it only has the following fields:-

tblProtocals
-ProtocolID - PK - Autonumber
-Pest - text

tblSurveyTypes
-SurveyTypeID - PK - Autonumber
-ProtocolID - FK - Number (Linked to PK from tblProtocols)
-SurveyType - Text

If you then create a Main Form / Subform for Data Input of Protocols & Survey TYpes

Then on your Main Data Input Form you can have 2 Combobox's -
1st Combobox would display a List of All Protocols
2nd Combobox wouldONLY show those Survey Types associated with the Protocol Selected

Thanks for your insight, Mike! I expect your idea has a lot of potential, but I'm struggling a bit to get my head around how it would all work. I think I'll have to use the database as is for now, and come back to this for a "version 2.0" rebuild sometime I get a slow day at work :) I think my main issue is a lack of good understanding of relationships. My relationships are 'working' for the most part, but my overall database structure is clearly a bit cumbersome. Thanks so much to everyone for the guidance so far though!

Justin
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:45
Joined
Jul 9, 2003
Messages
16,245
Thanks for your insight, Mike!

Due to a bug in the forum software this message was "unapproved" (hidden) for some considerable time. I have just approved it. I hope no one has been inconvenience too much! The new forum software no longer has this bug, so this problem should not reoccur.
 

AccessBlaster

Registered User.
Local time
Yesterday, 17:45
Joined
May 22, 2010
Messages
5,828
Due to a bug in the forum software this message was "unapproved" (hidden) for some considerable time. I have just approved it. I hope no one has been inconvenience too much! The new forum software no longer has this bug, so this problem should not reoccur.
Uncle, did the old software not alert the mods there were pending approvals? This is interesting the way older threads keep popping in.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:45
Joined
Jul 9, 2003
Messages
16,245
Uncle, did the old software not alert the mods there were pending approvals?

No I don't recall there being any notification, however they did show up as "unapproved" in the the moderators view of the list of posts and we spotted most of them. Obviously, some were missed! The new forum software shows a list of about 300 going back over 10 years which we are gradually correcting.
 

Users who are viewing this thread

Top Bottom