This must be simple but.....

[adi]

Hi all, really pleased I found this forum and I hope at some point in the future to be able to help you as I am asking for help now.

I'm doing a database for a friend that runs a dance business. He's given me all his client records and I've imported them into Access, no worries!

I want to be able to keep a record of customers coming in to different events by either using their membership number or by searching for them under their surname and then adding them to the attendance table.

I've set up a many-to-many relationship by having a three tables, the customer details table, the event table and the attendance table. The attendance table has the customerID and the event ID.

How can I add somebody to the attendance table using either of the two fields mentioned? The idea is that somebody on the door at the venue can quickly get the customer's membership number from their membership card and type it in. If they don't have their card then they can search using their surname. Once the right record is displayed then it needs to be added to the attendance table.



Oh and also he has people attend who aren't members and they need to be recorded as well.

I can do it relatively easily using a subform with a dropdown list but this is not convenient with 2000 customers. Help please!!

And thanks for putting up with my ramblings. Ade

[jzwp11]

Assuming that your main form is bound to your customer table with a subform bound to the attendance table, you should be able to add a little search utility in the header of the main form. You can have an option box that allows the user to choose between a search based on memberID or last name. Based on the choice, you would populate a combo box (also in the header) that lists either memberID numbers or last names. You would add some code to the after update event of the combo box to move to the record of the person selected. You can then enter the event they are attending in the subform.

If you want to do a prefilter of the ID's or last names, you can use the cascading comb box technique. The first combo box would present let's say the letters of the alphabet (representing the first letter of the the last name); you would use that to show only people whose last names begin with that letter in the second combo box.

[adi]

Thanks, that sounds really good and I'll need to do a bit of studying to work out how to do it, but it makes sense.

The main form will be the event form (fyi), but that shouldn't make any difference, should it?

[jzwp11]

Yes, it will. The approach I proposed finds the person's record in the main form's recordset. If you base the main form on the event, then your subform will have to be based on the attendence table. But, the attendance table only has a field that references (foreign key field) the customer in another table. So you will not be able to use the search utility in the header of the subform since the subform's recordset is not based on your customer table. You can still use the search utility & the cascading combo box approach but it will have to be within the detail section of the subform. Basically, you would not need any code since all you need to store it the ID of the customer selected (and that combo box should be bound to the customerID field of the attendence table).

[adi]

Thanks very much, I'll keep you posted with how I get on.

[jzwp11]

You're welcome & yes, please keep us posted on your progress.

[adi]

So, I've created a form which I'll use as a subform on the attendance main form. This form has basic details from the customers. On the header of this form, I've created an unbound list box with the 2 options; "membership number" or "surname". (I've named it "listbxSearchOptions").

How do I get the combo box to populate from there?

[jzwp11]

In the after update event (if I remember correctly) of the option group, you want to change the row source of the combo box. I'll just illustrate the simple option without using the cascading combo box approach

It might go something like this assuming that you only have 2 options available with the option assigned a 1 set for the last name search:

If me.optionGroupName=1 then

me.comboboxname.rowsource="SELECT LastName, MemberID FROM Customertable"

Else

me.comboboxname.rowsource="SELECT MemberID, Lastname FROM customertable"

End if

[adi]

Haven't got it to work yet, but it's bedtime now (gone midnight), so I'll try again tomorrow. Thanks again for your help.

[jzwp11]

I've attached a basic database to illustrate the technique.

[adi]

Thanks. It's all turning out to be a bit of a nightmare, but I really appreciate your help.

[jzwp11]

I've updated the example database with a second form (frmCustomers) that illustrates having the search utility in the header of the main form as I had originally described.