Access Novice - Basic form help please

[Dannyboy11]

I have a form called frmEnterAvailableDates and I want it so you can select the referee from the drop down list and then you can tick which dates they are available, and it will update this in tblAvailableDates. The drop down list is derived from qryRegistered9v9 (referees who are registered with the league) but I don't really know what to do.

I've bought books on Access;

Schwartz, Steve: Microsoft Office Access 2003 for Windows: Visual QuickStart Guide

Microsoft Office Access 2003 Step by Step

How do you rate these books?

 

[Kryst51]

Good Morning! I think you will need a junction table. Can more than one referee be available for the same date? That Means Many referees to many dates and is known as a many-to-many relationship this means you need a junction table between the referee table and the available table. Make sure you form the relationships between them on the relationship screen. This will be a start.

I would then (Not sure how exactly this will work and I don't have time to try it now) create a form based off of your referee table. add a combo box based off the referee query that is filtered for registered only. and use the combo wizrd to make it find a record based on your selection.

Then you probably need a subform based on the junction table. Then on that subform you can create another subform or list box for the available dates....

 

[Dannyboy11]

Hey

It will be many-to-many yes. The only referees that will have available dates will be those that are 'Registered for 9v9' - does the relationship have to be between the referees table or the qryRegistered9v9 ?

So do I need to create an extra table? I have looked up what a junction table is and so it is a table that has fkRefereeID and fkRefereeAvailableDate(?) - I don't quite understand how this works as there won't be a key for AvailableDate yet until you've added one?

A relationship that I have at the moment is between pkRefereeID in tblReferees with fkRefereeID in tblAvailableDates

 

[Kryst51]

Hey

It will be many-to-many yes. The only referees that will have available dates will be those that are 'Registered for 9v9' - does the relationship have to be between the referees table or the qryRegistered9v9 ?

So do I need to create an extra table? I have looked up what a junction table is and so it is a table that has fkRefereeID and fkRefereeAvailableDate(?) - I don't quite understand how this works as there won't be a key for AvailableDate yet until you've added one?

A relationship that I have at the moment is between pkRefereeID in tblReferees with fkRefereeID in tblAvailableDates

I don't think you understand the relationship that we are talking about. Each referee can be available many days, and each day can have several referees that are able to work. That makes it a many to many relationship. Whether they are registered or not doesn't come into play for this. So yes you need a junction table between your referee, and available dates table.

 

[Dannyboy11]

So I have created the junction table (I think!) and a relationship is already established between referees table and available dates table.

I've attached what I think I do. So in the junction table you enter in the referee id and then the corresponding set of available dates (available date id) that belongs to them.

So how will I get a form to create the set of available dates (by ticking boxes) and then aligning them to a referee? After this, I'd also need to create a report to see which referees are available on each date.

 

[Dannyboy11]

Do I need to create a form based from an append query that will add a row of available dates to that table? How do I set up a query to tick either yes/no?

I'm a bit stuck.

 

[Kryst51]

Hey! I looked at your db yesterday.... A couple of things I noticed.

1. Your available dates table needs its own unique ID, and you need to remove the fkRefereeID.

2. Your junction table also needs its own autonumberID. Also, the fks need to be number format not text.

3. In your relationship screen you need to relate your available date table to the junction table and your referee table to the juntion table, not to eachother.

4. The check box thing is going to be very hard, here is what I suggest instead. TAKE OUT ALL THE FIELDS WITH THE DATES and make them RECORDS in the table instead. Also add a time slot field, then each date will have two records in the table, one for morning one for afternoon.

tblAvailableDates
pkAvailableDateID - Autonumber, pk
AvaialbleDate - Date/Time format
fkTimeSlotID - Number Format

tblTimeSlots
pkTimeSlotID - Autonumber, pk
TimeSlot - text (i.e. Morning, Afternoon)

For your form I suggest using a multiselect list box instead, make your selections then click a button to add those records to the referee via the junction table.

Main form - bound to the referee table.
Combo box on the main form pulls from the query which filters referees by those with the registration

Sub form - bound to the junction table, linked with the main form
list box - pulls from the available dates table
button - takes the selections from the list box and creates records in the junction table one for each selection, with the referee ID selected in the combo box on the main form.

I don't know exactly how this will work, you will have to play around with the button code, you may need another subform in there, but this will get you started.

Relate these two in the relationship screen

 

[Dannyboy11]

Ok so i've got down to the form. I know little about Access, but Forms are my weakest area.

How do I bind the form to the referees table?

 

[Kryst51]

Ok so i've got down to the form. I know little about Access, but Forms are my weakest area.

How do I bind the form to the referees table?

You can use the wizard, or on the properties screen, on the data tab, the first option is the "record source" if you click in this a drop down box appears and should list every query and table in your db, you then select your referee table from this.

 

[Dannyboy11]

For your form I suggest using a multiselect list box instead, make your selections then click a button to add those records to the referee via the junction table.

Main form - bound to the referee table.
Combo box on the main form pulls from the query which filters referees by those with the registration

Sub form - bound to the junction table, linked with the main form
list box - pulls from the available dates table
button - takes the selections from the list box and creates records in the junction table one for each selection, with the referee ID selected in the combo box on the main form.

I don't know exactly how this will work, you will have to play around with the button code, you may need another subform in there, but this will get you started.

Relate these two in the relationship screen

What is in bold I have done. How do I allow multiple selections? And is it going to be so you have to hold down 'CTRL'? How do I link the subform? Unsure on how to even approach the button too.

 

[Kryst51]

Link the subform - Use the subform wizard and follow its prompts.

Listbox - Right click on the listbox in design view, select the "other" tab, then select the "multiselect" option, and change it to "extended. See here for more details about the list box. I would read it, so you learn a little more about the options available.

Button - Create a button and rename it something meaningful like btnRelateRefDates.
In the button "On Click Event" pull up the vba editor. Delete the code, if there is any, that is under the button's previous name.

For the code, Read this and see if it gets you started in the right direction, post here with an updated version of your db once everything else is working, and I'll help you figure it out, this stuff takes me a lot of time too. Also check out this thread and see if you can learn something from it as well concerning this issue.