Listing specific requirements on Access Database form

Question

I am trying to create a database using Microsoft Access Database, but I have a problem. I want to create a database that holds information for computer components and a form to list certain components to build a computer.

For example, I want to build computer A, and I have a list of all the hard drives: hdd1, hdd2, hdd3, hdd4, and hdd5, but only hdd2 and hdd4 is compatible.

My approach would be (where bold = primary key, and italic = foreign keys:

HDD (hhd#, size, cost,compA, compB, compC)

COMP(comp#, hdd#)

Would this be the right approach?

Answer 1

Please Note:

I've provided an answer here because I'm feeling nice, but in future do bear in mind that your question is a bit too broad to be useful on this site. However if you're a beginner, hopefully this will of some help.

I've mocked-up my own example based on your descriptions purely to illustrate an idea on how to approach this.

I've created 2 tables. The first, tblCompBuilds will list computer builds. Here's its structure:

...and here's some dummy data for it:

The other table I've created is tblHdds, which will list Hard Disk Drives and a few specs. Here's its structure:

...and here some dummy data for it:

I've already decided that I will be using the HddFormFactor field that appears in both tables as what determines whether a hard drive record in tblHdds is compatible with a computer record in tblCompBuilds.

This is an important question that you need to be asking yourself when designing your database and form...

"what data can I use to establish that a hdd is compatible with a computer?"*

For my example I've invented 1 field, but you may have a more complex set of criteria to apply (possibly multiple fields and varying conditions / situations to consider).

Anyway, once I've established the data and how I intend to show compatible HDDs, I need to make a form that will allow me to select computer builds and the compatible HDDs.

With a blank form created I select the form by clicking in the top-left square; a black square denotes that the form is selected:

In the Property Sheet for the form I go to the "Data" tab and against the Record Source property I use the drop down to find and select the tblCompBuilds table:

This will bind the form to this table, meaning you can add the fields from the bound table to this form and move through those records using the form to display them.

Instead of adding the fields from the bound table to the form I'm going to create a new combo box using the combo box wizard (a combo box is essentially a drop-down list of options).

First I state that I want to get the options for my combo box from a table. This is because the data listed under the ComputerBuildName field in the tblCompBuilds table will give me the list of computers to choose from:

I then specify the table...

...and then the fields:

In this case I'm using just the ID and ComputerBuildName fields. The ComputerBuildName field will be what the user sees as options when they use the combo box, and the ID field will help me later when I use the combo box to manipulate the form.

I then specify an order that I want the options in the combo box to appear. As the data from the field ComputerBuildName is ultimately what I want to display in my combo box, it would make sense to have this showing in alphabetical order for the user:

Finally you can specify the width of the columns showing in the combo box; I tend to ignore this and do it manually later (note that I've asked the wizard to hide my primary key field, which is the ID field):

If you go in to Form View, the combo box looks like this:

So we now have the ability to select our Computer Build, next we need to show the related/compatible HDDs. There's lots of different ways this can be done, but I'm going to illustrate this using a subform.

Add a subform to your form and go through the subform wizard; beginning with specifying that I want to use an existing table:

...I then specify the table (tblHdds) and the fields I want to display:

The next stage is where we specify how we relate records on our form (which we specified earlier by setting its Record Source to tblCompBuilds) to records on our sub form (which we just specified as tblHdds).

The subform wizard will attempt to provide the relationship between form and subform by looking for fields in the tables bound to the form and subform that have the same name.

When I designed the tables, I was careful to ensure that:

1. There would be a field (HddFormFactor) that would relate both to tblCompBuilds to tblHdds, and
2. This related field was named exactly the same in both tables

This means there's a relationship already waiting for me to choose in the subform wizard, rather than having to try and create my own:

After selecting that I want my form's tblCompBuilds records to relate to my subform's tblHdds records using HddFormFactor I just need to name my subform to finish the wizard:

In Form View, the form should now look something like this:

What we want to happen is that when you select a computer from the Computer Build combo box, the subform of HDDs will update to list only those that have a compatible HDD Form Factor.

If you try and do this, the subform doesn't update.

Notice at bottom of both the form and subform there is some information about the records bound to form and subform:

When you select any Computer Build in the combo box, notice that the form's record number does not change; it just stays on 1 of 3 and doesn't move.

The subform cannot update because the currently loaded record for the form does not change so neither can the subform that's related to it.

To change the loaded record on our form using the combo box, we'll need to employ a bit of VBA.

Go in to Design View, select the Computer Build combo box and from the Property Sheet's "Event" tab, click the [...] button against the After Update event:

Select "Code Builder" and you'll be taken to the VBA editor with the following code already written for you:

Fill-out the middle of the combo box's After Update VBA so it reads like this:

Private Sub cboCompBuild_AfterUpdate()

    Dim rs As DAO.Recordset

    Set rs = Me.RecordsetClone

    rs.FindFirst "[ID] = " & Me.cboCompBuild

    Me.Bookmark = rs.Bookmark

    Set rs = Nothing

End Sub

Note that cboCompBuild is the name I gave the combo box. You'll need to make sure that this line in the VBA refers to the name of whatever you've called your combo box; you can find the name of your combo box in the Property Sheet:

Go back in to Form View and hopefully, when you select different Computer Builds, the subform should update to show those that are compatible.

There's more you can do from here, like set the subform's Allow Additions property to "No" so you don't get that weird empty record at the bottom. You may also want to consider disabling edits and deletions from the subform as well if you don't want users to be able to change the data in the subform's bound table (tblHdds). It's also worth noting that for the VBA I used a watered-down version of Allen Browne's method for Using a Combo Box to Find Records; his full version is more robust than what I've used for quick illustration.

Here's a link to Access file I created for this example.

Hope this helps.