Trying to learn access again

[ScubaBart]

I used to be pretty proficient but after almost 20 years of inactivity, the brain just isn't remembering.



So, building a database to log inspections of my bee hives. Have a table, Log_Apiary to define the Apiaries (An apiary is a yard containing one or several hive colonies). Another table Log_Hive to define the actual hives.

Log Apiary Log Hive

In Log_Hive, the Apiary field is selected through a dropdown lookup pulling from Log_Apiary. So far so good.

Now, I go to another table, Log_Inspection where I will start recording each individual hive inspection. This includes date, time, weather and such and then I select the hive by first selecting the Apiary (same dropdown lookup used in Log_Hive) and then I want to select the Hive using a dropdown lookup but I want to only see the hives that are linked to the previously selected Apiary. and for the life of me, I cannot remember what to do to accomplish this.

Log_Inspection

AS you can see, the dropdown lists all the hives but I want to only list the hives specific to the already selected Apiary. If no Apiary is selected, it would likely show no Hive options. (null).

Tried building an embedded query and also a separate query but I just don't remember how to set this up. Seems like it should be super simple.

Note the actual field names do not contain spaces, I use underscores in my naming convention.

Thanks for any input and unfortunately, this probably won't be the last of my re-learning questions.

[CJ_London]

You are using lookups in a table. They appear to be useful but as you have now discovered, they have limited functionality

Use a form (where lookups belong) and Google the term ‘cascading combos’

[ScubaBart]

I vaguely remember that being the case. Just kind of figured if it was set up at the Table level, it would automatically work at the Form level. Oh well. Thx. Working on the form version now but so far, not successful but will make a separate post with fresh pics when I get too frustrated.

[CJ_London]

Just kind of figured if it was set up at the Table level, it would automatically work at the Form level.

for a simple requirement, yes - ignoring the typical issues with table level lookups (seeing text, but actually a number, weird naming to match what you see rather than what you actually have, confusing queries, etc). The same applies to formatting - don't format in the table (in particular dates and decimal numbers) otherwise you hide what the actual value is. You are trading convenience for obscuration.

But yours is not a simple requirement as what you see in the dropdown is dependant on another value (in the same record in this case, but not always). If your form is continuous or datasheet you have additional issues to consider which requires dynamically changing the rowsource to limit the list by filtering or restating when using the dropdown and removing the filter when the control loses focus. You also need to consider what happens if the user changes the filtering value - what is already selected may no longer be valid.

Depends on how complex the requirement actually is and how many hives you have in total - you might find it easier to combine apiary and hive in the rowsource since you should not need to store the apiary id in your log inspection - it can easily be determined by referencing the hive record

So your rowsource might be

SELECT HiveID, HiveName & ":" & ApiaryName
FROM LogHive INNER JOIN LogApiary ON LogHive.ApiaryID=LogApiary.ApiaryID


Otherwise if you insist in storing the ApiaryID then in the Hive Combo enter event put something like

cboHive.RowSource = "SELECT HiveID, HiveName FROM LogHive WHERE ApiaryID=[cboApiary]"


and in the exit event restore the full list with is code


cboHive.RowSource = "SELECT HiveID, HiveName FROM LogHive"


There are other methods such as main form (apiary) and subform (hives) which might be a better solution

EDIT: I also suspect your inspection table is incorrect - if you have 20 hives, are you really going to write 'warm & overcast' 20 times? I would think you need an inspection header table which would store the date, time and weather (or perhaps an aviary sub table to store that info if you have them at different ends of the country) then a simple sub table to store the inspectionID and the hiveID, together with any hive related data such as state of the bees and the condition of the hive.

[ScubaBart]

So, yes. It looks like you get what I am going for. I have added here a screenshot of my relationships to make it clearer.



For each top level Aviary inspection entry row (one date, weather, etc.), there can be multiple hive inspection rows, one for each individual hive in the apiary. There could be one, there could be thirty.

Because there are so many variables in an individual hive inspection, I broke out main topics into separate tables with 1:1 relationship versus having it all in one table. I use a query to in the Hive_Inspection_Detail Form to collect all the fields for a given row. Not sure if this is proper form but it always kinda bugged me if there were too many fields in a given table.

I also separately have a many to one Hive to Apiary relationship as I think I previously referred to.

A standard sequence of entry would be to open the Form for the top level Log_Inspection an enter the common details and selecting the specific Apiary. This currently works without a problem. This Form is configured as a Form with a Continuous Subform to cover each Hive.



When I open the top level Form, a blank, unrelated subform is visible. I also have a more expanded version of the individual Hive Inspection Form I can call upon that is set to pop up as a pop-up / modal type form.

I am not opposed to having a button command to open the pop-up form to create the initial entries for each hive inspection and I have already been able to create a button to expand each hive entry to show the pop-up version. (works when subform is open on its own currently).

I will be experimenting with this off and on today as time allows. Will make attempts to try what you suggested above.

Thanks for the continued feedback.

[mike60smart]

Hi
It is quite unusual to have 1 to 1 Relationships as this type of Relationship is normally used for Personal Information like bank Details.

Your fields which are Checkbox's (Yes/No), in the Brood Pattern , Queen Cells, Brood Stages & Hive problems should really be records in a related Table and not fields.

Also you have ID PK's in all tables. It is recommended that you name the PK with the name of the actual Table.
eg tblOrders would have a PK of OrderID

[ScubaBart]

Hi
It is quite unusual to have 1 to 1 Relationships as this type of Relationship is normally used for Personal Information like bank Details.

Your fields which are Checkbox's (Yes/No), in the Brood Pattern , Queen Cells, Brood Stages & Hive problems should really be records in a related Table and not fields.

Also you have ID PK's in all tables. It is recommended that you name the PK with the name of the actual Table.
eg tblOrders would have a PK of OrderID

I understand your first statement and I may end up combing all the fields into a single table.

The fields with the checkbox's are the fields that are in the 1 to 1 relationship tables. I initially tried to use sub-forms to display that info in the main log form but that doesn't work when using cascading forms for the log entries.

Naming the PK's makes sense and I have done that.

I looked back on an old database I had worked on some 20 years ago that I happened to find a copy of. Unfortunately it is only the front end with links to all the tables that was on a server and for some reason, don't have a copy of that so can't actually open the forms and navigate through it but I can look at teh forms and macros I used at the time.

When I wished to create a new entry in a sub-table, it appears I had a macro using the "Set Value" command.



Seems that command is no longer available ?? Even when I open the macro in design view, it gives a warning that it is an unsafe action. And I cannot find that command if I attempt to use it in the new database I am working on, even if I enter it manually, it doesn't recognize it.

Still working on other aspects of the overall application but wanted to get thoughts on that macro and input on the "Set Value" command.

Thx......

[mike60smart]

Hi
Are you able to upload a copy of the database?

[ScubaBart]

I can but let me wait until I get it a little more functional with some form navigation and such. That will make it more understandable I think.