Design Updateable Query

[Winger]

I need to amend a query but when I do it becomes un-updateable, so need to
find a solution whereby I can update and add new records etc.

The issue is as follows:-

I have a frmWorkShops which lists the main details of each event, and a
sbfrmAttendees which has details of the people booked onto each Workshop.

A command button on sbfrmAttendees takes the ID of a person from a combo
box, and creates a new record on sbfrmAttendees.

This worked fine until I tried to change it.

I would like sbfrmAttendees to show more information about the person based
on their details in tblPeople.

tblAttendees can be linked to tblPeople by the person ID. But when I change
the record source for sbfrmAttendees from a single table select query (from
tblAttendees) to a two table query tblAttendees and tblPeople - my comand
button dosen't work because the query can't be updated.

I'm not sure how I tackle this - any pointers of what I'm doing wrong
gratefully received.

Many thanks

Winger

 

[John W. Vinson]

I need to amend a query but when I do it becomes un-updateable, so need to
find a solution whereby I can update and add new records etc.

The issue is as follows:-

I have a frmWorkShops which lists the main details of each event, and a
sbfrmAttendees which has details of the people booked onto each Workshop.

A command button on sbfrmAttendees takes the ID of a person from a combo
box, and creates a new record on sbfrmAttendees.

Ummmm... Why?

If you're really using a Subform you can use the combo box as the Master Link
Field and the ID as the child link field of the subform. There's no need to
have code or a button.

What are the Recordsources of the main and subforms?

This worked fine until I tried to change it.

I would like sbfrmAttendees to show more information about the person based
on their details in tblPeople.

tblAttendees can be linked to tblPeople by the person ID. But when I change
the record source for sbfrmAttendees from a single table select query (from
tblAttendees) to a two table query tblAttendees and tblPeople - my comand
button dosen't work because the query can't be updated.

Post the SQL of the query - it's probably NOT necessary to use a query to do
this. One trick is to include the fields that you want to see in the combo
box's RowSource; they can be set to zero width in the ColumnWidths property of
the combo. You can then use textboxes with control sources

=combobox.Column(n)

where n is the zero based index of the field in the combo's rowsource.

John W. Vinson [MVP]

 

[Winger]

John,

Thanks for you reply.

frmWorkshops record source is tblWorkshops, and sbfmAttendees recoure source
is tblAttendees. I have 50+ workshops, and hold details such as venue,
speakers, dates and times etc. Each workshop can have 1 -200 attendees, so
the sbfrmAttendees shows the details of who is attending.

The attendees have to be part of the programme and the combo box on
sbfAttendees lists eligable clients. As a person often books onto a series of
workshops, the user selects the person making the booking from the combo box,
scrolls through each of the workshops and clicks "book" for each appropriate
workshop. The persons details are then transfered to each attendance list for
each workshop. (Which can be emailed to or printed out etc)

My problem stems from inexperience and mistakes on my part.

Stage1
I initially used the Attendees email address as their unique identifier and
no ID field. My code put the email address for each booking into the subform,
ready for emailing.
Students then changed/updated their email address and meant the emailing
function didn't work as data was out of date.


Stage3
I have now made changes so that a Unique ID is used for each person. This is
used for the workshop booking and placed in the sbformAttendees. When the
workshop attendees are emailed reminders prior to the course, the code only
picks up the ID from tblAttendees and then gets the most up to date data from
tblPeople.


However I can't fully implemntm stage3 yet, as there are inaccuraies in the
data, so am going through stage2 first.

Stage2
My aim in stage2 was to change the record source for sbfrmAttendees to a two
table select query (tblAttendees + tblPeople). This would bring in the
historical workshop booking information for each event but also the most up
to date email details for the clients. A conditional format on the booking
information highlighted in red where there was a difference between the
historical booking information and the latest details held on tblePeople.
Staff could then work through these to find out which was the most upto date
/ correct email for the client.

All this worked fine until I realised I couldn't create new records via
sbfrmAttendees as it was based on an un-updatebale query!!

A cut down version of the two table query is as follows:

SELECT tblAttendees.ID, tblAttendees.EventRef, tblAttendees.UCLANEmail,
tblPeople.UCLanEmail AS PeopleTableEmail
FROM tblAttendees LEFT JOIN tblPeople ON tblAttendees.[Personal Email] =
tblPeople.ID;


Can a query be designed that would pick the information needed and still be
updateable?

thank you for looking into this.

Regards

Winger