form with list of check boxes 1

[smiley0q0]

ok, i am fairly new to access, so i'm looking for some help on this form i am trying to develop.

i have a table that has a list of serial numbers and some other data like location etc... but what i am trying to do is create a form that shows the serial number and a list of all the available software products we have available with a checkbox next to it so the user can check off what software is installed on that serial number if any.

how do i go about making my tables and query? i have a table that lists all the available software, but what do i link it to in my serial number table? should i make a software field? but if i do that, it would only allow one, right? i'm just a little stumped...

thanks for your help...
Smiley

 

[MacCaillean]

Hi Smiley

Create a third table (tblLink) with a minimum of two fields, SerialNum and SoftwareID.

In Access relationships view, show all three tables and create a one-to-many from your Serials table (tblSerials) Primary Key to the SerialNum in tblLink, and likewise from your Software table (tblSoft) Primary Key to the SoftwareID field in tblLink. This effectively provides a many-to-many relationship between tblSerials and tblSoft, whilst still allowing unique records in each of those tables.

Create a query (qrySoftLink), include the tblSoft and tblLink, do not include the PK field from tblSoft but do include the two fields from tblLink, and then any other fields with meaningful info that you may need.

Create a form based on tblSerials (frmSerials), then create a second form (frmSoftLink) based on qrySoftLink created earlier.

Open frmSerials in design view and from the database window, drag frmSoftLink into it. This creates a main form and subform. Save changes. Back into design view on the frmSerials and select the subform object you've just dropped into it (it will be called frmSoftLink, I think). Check the data properties for that object, specifically the Link Child Fields, Link Master Fields entries. These should equate to the Serial number field in qrySoftLink and the PK field from tblSerials respectively.

You now have a form which allows you to enter a serial number (and other serial information) and then enter multiple software types which are installed on it.

I hope you can follow this!

Regards

Mac

 

[smiley0q0]

Thank you Mac.
It proved most helpful.
I appreciate the easy walk through you provided.

Thank you.

Smiley

 

[MacCaillean]

Hi Smiley

You're welcome - good luck with your db.

Regards

Mac