Subform ComboBox (1 Viewer)

JChase

Registered User.
Local time
Today, 09:33
Joined
Apr 13, 2018
Messages
12
Hi all. I'm relatively new to Access and am not a programmer. I apologize in advance if this question has been addressed in another thread.

I'm compiling in-house information on forests insects and diseases in a database with a main dashboard form used for searching individual species (there is a separate DATA ENTRY form that works very well). The Pest Database form is controlled by a main combobox "Select Pest" to search records by pest ID. I have a subform for survey protocols that also works well. The "Select Type" combobox works like a filter, allowing users to view details on only one type of survey for the selected pest. My only issue, is that the "select type" combobox shows all possible survey types for all species (see photo attachment 2). 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).

If the only solution here requires a bit of code in an event, please explain your proposed code in detail.

Attached screenshots are of my main "Pest Search" dashboard - the "Survey Protocols" section is the subform in question. The second image is of the subform, showing the drop-down combobox that controls which survey type is shown.

**Alternatively, I'd settle for a separate list box (or similar control) that simply lists "survey types available". I know I could acomplish this with a seperate subform, but that would be really ugly (see my "Hosts" subform for an example of an ugly list :) ).

Thanks!!!

Justin (Fredericton, NB, Canada)
 

Attachments

  • PestSearch_Dashboard.jpg
    PestSearch_Dashboard.jpg
    92.7 KB · Views: 92
  • Combobox.jpg
    Combobox.jpg
    68 KB · Views: 101

RuralGuy

AWF VIP
Local time
Today, 10:33
Joined
Jul 2, 2005
Messages
13,826
I'm going to assume you are using the LinkMaster/ChildFields properties of the SubFormControl to do your filtering. Are you aware that you can have more than one field listed for each property separated by a ";" ? That may be all you need.
 

JChase

Registered User.
Local time
Today, 09:33
Joined
Apr 13, 2018
Messages
12
Hi Ruralguy. Thanks for the quick response!

See attached screenshot for the Link child/master properties.

Number is a primary key in my "Pest List" table, which corresponds to individual pest ID's - this makes the subform only show data for the particular pest selected on the main form's combobox. However, the combobox "Select Type" of the subform still shows all possible types.

Justin
 

Attachments

  • subformcontrol.jpg
    subformcontrol.jpg
    98.1 KB · Views: 81

RuralGuy

AWF VIP
Local time
Today, 10:33
Joined
Jul 2, 2005
Messages
13,826
Is there a field in the RecordSource of the mainForm that also appears in the RecordSource of the SubForm that would limit the SubForm to the records you want?
 
Last edited:

RuralGuy

AWF VIP
Local time
Today, 10:33
Joined
Jul 2, 2005
Messages
13,826
Hmm...I'm starting to understand your db a bit better. What is the RowSource of the "Select Type" combo box? BTW, I like the looks of your form.
 

JChase

Registered User.
Local time
Today, 09:33
Joined
Apr 13, 2018
Messages
12
Thanks again!

In response to your first question:
The table "protocols", which has the field "Survey Type" is in the record source for both the main form and the subform (main form record source details in first attached image).

To the second question:
the Row Source of the select type combo box is as follows:

SELECT DISTINCT Protocols.[Survey Type]
FROM Protocols;

I was able to use the "Unique Values" property to remove a massive amount of duplicate options. So... the combobox is tolerable now. There's just no way for a user to know what survey protocol types are available for a given pest, without selecting each survey type to see what pops up in the subform.

Cheers.
 

Attachments

  • MainForm_RecordSource .jpg
    MainForm_RecordSource .jpg
    93.2 KB · Views: 82

RuralGuy

AWF VIP
Local time
Today, 10:33
Joined
Jul 2, 2005
Messages
13,826
While I'm giving this some thought I thought I'd point out that it is never a good idea to use a *reserved* word for the name of any object you have defined. In this case you have used NUMBER as the name of a field in the "Pest List" table. Here's a link for you to check a name you might want to use: http://www.allenbrowne.com/AppIssueBadWord.html#N
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:33
Joined
Jan 23, 2006
Messages
15,364
Hi Justin,
Welcome.
Is is possible to provide a copy of your database with just enough info to test the form and combo involved? If so, you'll have to create and post a zip file because of your post count.
 

RuralGuy

AWF VIP
Local time
Today, 10:33
Joined
Jul 2, 2005
Messages
13,826
In looking at your relationship picture, it appears as though you have not defined a relationship for the Protocols table yet.
 

RuralGuy

AWF VIP
Local time
Today, 10:33
Joined
Jul 2, 2005
Messages
13,826
I might also suggest that 1:1 relationships are not very common. They usually indicate (though not always) fields that should be in the same table.
 

JChase

Registered User.
Local time
Today, 09:33
Joined
Apr 13, 2018
Messages
12
Great point, Ruralguy! Thanks for the link.

I've attached a copy of the database. However, the database is split and this is only the front end (back end was too big). I'm not sure what exactly you'll see when you open it, but I'm guessing you'll see a sortof 'blank' version with no actual records. Let me know if this doesn't work for you.

Thanks again, guys!
 

Attachments

  • PEST DATABASE.zip
    233.8 KB · Views: 85

JChase

Registered User.
Local time
Today, 09:33
Joined
Apr 13, 2018
Messages
12
I might also suggest that 1:1 relationships are not very common. They usually indicate (though not always) fields that should be in the same table.

So, the relationships are defined (to the best of my understanding). See attached pic of the relationships in the backend of my database (for some reason the front end shows them all as 1:1 relationships...). "Protocol_Types" is in it's own table because the corresponding field in the "protocols" table is a drop-down menu. I didn't want it to just be a text field that people could potentially make spelling mistakes in or add values I didn't want in there.
thanks!
 

Attachments

  • relationships.jpg
    relationships.jpg
    83.6 KB · Views: 72

jdraw

Super Moderator
Staff member
Local time
Today, 12:33
Joined
Jan 23, 2006
Messages
15,364
No, it isn't of much value as is.
If the backend is large, you could try doing a Compact and repair.
Then make a zip with both front end and back end.

I agree with Allan re your table relationships in the jpg. 1:1 is not common.
 

RuralGuy

AWF VIP
Local time
Today, 10:33
Joined
Jul 2, 2005
Messages
13,826
Justin, what version of Access are you using and 32/64 bit?
 

JChase

Registered User.
Local time
Today, 09:33
Joined
Apr 13, 2018
Messages
12
Ruralguy: let's see, it's Access 2010. My desktop has a 64-bit O.S., but apparently we're running a 32-bit version of Access... Seems to work fine though, but that's what our administrators installed for some reason.
 

RuralGuy

AWF VIP
Local time
Today, 10:33
Joined
Jul 2, 2005
Messages
13,826
It should work just fine. I just wanted to see if there was anything unusual in your environment.
 

RuralGuy

AWF VIP
Local time
Today, 10:33
Joined
Jul 2, 2005
Messages
13,826
If a Compact and Repair and zipping do not decrease the size enough, it should be possible to create a BackEnd with no data, just the structures. Import just some of the relevant records and then C&R and zip.
 

RuralGuy

AWF VIP
Local time
Today, 10:33
Joined
Jul 2, 2005
Messages
13,826
My 2002 system FE/BE has the relationship the same in both FE and BE and I'm certain I only defined those relationships in the BE. At least I'm pretty sure. :eek:
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:33
Joined
Jan 23, 2006
Messages
15,364
Yes Allan define relationships in backend.
Here's a link to Devhut describing same.
 

JChase

Registered User.
Local time
Today, 09:33
Joined
Apr 13, 2018
Messages
12
Ok, I finally got the thing small enough to upload. I removed almost all records, then compacted, then zipped :) See attached.

Justin
 

Attachments

  • PEST DATABASE_be-REDUCED.zip
    397.7 KB · Views: 98

Users who are viewing this thread

Top Bottom