Question Check boxes within combo box

[Fmeister]

Hi,

I am designing a database with very very basic knowledge of Microsoft access.
In a form I have a combo box which enables me to select attendees of a meeting. My dilemma is that I need to show the total number of attendees selected as a number. So I can then calculate what the total entry fee has been for the meeting.

Any help would be appreciated.

 

[GPGeorge]

That would be done with a query. In the absence of specifics regarding your table, here's a generic template to get you started.

SELECT MeetingDate, Count(*) FROM tblTheNameofYourAttendanceTableGoesHere 
WHERE tblTheNameofYourAttendanceTableGoesHere.MeetingDate = 
FORM!frmWhereYouHaveaDateFieldToSelecttheMeetingDate.txtControlOnTheFormWithTheMeetingDate

 

[Fmeister]

Thanks for the response. I was hoping to display the number of attendees on the same form i.e. A text box. As I select the attendees the textbox will reflect the attendees selected. The number in the text box will automatically be multiplied by the entrance fee for that day and will consequently be stored in the same table

If that makes sense

 

[Mr Smin]

I use a ListBox with multi-select for this purpose, in a modal form that pops up from the main form. A button on the pop up runs code which iterates through the items selected in the listbox and inserts them into a table.
The last items in the VBA are refresh the main form (which will then display the selected staff in a box there) and close the modal form.
My list on the main form is filled using a query - to get a tally I could point a DCount at the same query.

 

[GPGeorge]

Thanks for the response. I was hoping to display the number of attendees on the same form i.e. A text box. As I select the attendees the textbox will reflect the attendees selected. The number in the text box will automatically be multiplied by the entrance fee for that day and will consequently be stored in the same table

If that makes sense

The multi-select list box approach will work; if you are comfortable with VBA it's a good option.

A query with an Aggregate Function, DCount(), can be used to populate your text box. Create the text box and use the following (modified to use your actual field and table names) as the control source for the text box.

=DCount("AFieldInyourAttendanceTable", "tblTheNameofYourAttendanceTableGoesHere","tblTheNameofYourAttendanceTableGoesHere.MeetingDate = #" &
FORM!frmWhereYouHaveaDateFieldToSelecttheMeetingDate.txtControlOnTheFormWithTheMeetingDate" &"#")*[EntranceFee]

You should NOT try to store this calculated value in the table. You can recalculate it anytime you need to display it in a report or on a different form.
George

 

[boblarson]

Hey, welcome back George. It's been a while since you've graced us with your presence.

 

[Fmeister]

Thanks guys, I think its way over my head - i dont have an attendance table as such, my combo box is linked to the members table of which there are only twelve (using multiple selection)

I'll give it a go using the code.

Is there anyway some one can take a look at this very basic, but tedious, database - so I can see the error of my ways

 

[boblarson]

Is there anyway some one can take a look at this very basic, but tedious, database - so I can see the error of my ways

Here's how to upload a database to the forum.

 

[Fmeister]

A very small db. Only 2.5 Mb have compressed it even further

 

[Fmeister]

I am also having problems with a report showing each member, the group they belong to and the books they have read

Thank you

 

[Pyro]

Hi Fmeister, part of your problem is that you have decided to use a multi-valued field. They can be useful, but they can also be tricky little things.

Just like attachment fields, multi-valued fields can be thought of as a table within a table. When dealing with individual records within a multi-valued field, you have to use the value property (e.g. Attendance.Value)

In your scenario, you could get a count of how many Attendees you have by using a DCount() function. Something like DCount("Attendance.Value","tblMeeting","Week=" & [Week]) should give you the result that you're looking for.

IMO, multi-valued fields are more work than they're worth and i prefer to use multi-select list boxes or continuous forms, but i have used them successfully on occasion.

 

[Fmeister]

Wow. Fantastic, Thank you so much - I can't believe that it has worked.

Re: the relationships......do they look fine?

 

[Fmeister]

That is a solution to the problem I was having. But now, although the value is calculated for me on the form - it is not being saved in the relevant table.

Are there any ways around this?

 

[Pyro]

It is considered best practice to not store calculated values, but rather to recalculate them wherever necessary.

 

[Fmeister]

Thanks guys, much appreciated. All sorted (he says)
Access is a lot take in - will there ever be an end to this madness!!!

 

[Fmeister]

Hi Fmeister, part of your problem is that you have decided to use a multi-valued field. They can be useful, but they can also be tricky little things.

Just like attachment fields, multi-valued fields can be thought of as a table within a table. When dealing with individual records within a multi-valued field, you have to use the value property (e.g. Attendance.Value)

In your scenario, you could get a count of how many Attendees you have by using a DCount() function. Something like DCount("Attendance.Value","tblMeeting","Week=" & [Week]) should give you the result that you're looking for.

IMO, multi-valued fields are more work than they're worth and i prefer to use multi-select list boxes or continuous forms, but i have used them successfully on occasion.

I understand the other approach to this would be the multi listbox approach. This may be better due to the additional combobx within the form. Since the combo determines which group is selected, can the listbox be cascaded to show the selected (combo) groups members. If this was the case, there would also be a 2 qrys to separate the groups members. Which may cause problems if more groups are added?
Whether the cascading method could be possible or not, I would be very eager in finding the procedure for calculating the total number of selections if the listbox is used.