[ms office - 2010] Access 2010 - VBA Assistance

[_kstone]

Hi all,

 

I have been passed over an Access database that needs some modifications. It is a relatively simple database, and most of the modifications I have done. However, I need to now dump some of the data out into a word document, which I can partially do, however I don't have all the code required. I was hoping there might be some Access gurus on here that might be able to lend a hand / point me in the right direction

 

I am aiming to:

 

The database stored 'bookings' and has multiple tables with lookup data that all are matched in the bookings table. Ie. Students table, Tutors table, Rooms table and subjects table.

 

I need to present all the bookings for a particular tutor or student in a grid 'timetable' view. I have created the timetable template in Word and I have inserted bookmarks in each cell.

 

I currently have some VBA code that will dump a particular field into a particular bookmark area

 

The Issue:

 

There are three parts that I need some assistance with.

 

1. I need to be able to select a tutor's name (from a listbox preferably) and it only give me the bookings for that tutor. I can try and pull data from a query, however as it stands i've only managed to get it to prompt me for manual input of a name rather than look at a listbox and set that name as the criteria.

 

2. Only IDs are stored in the bookings table, the data I want to pull out is the actual friendly names, which are stored in the seperate tables.

 

3. I need to input the bookings into the correct cell in the table based on the timeslot that is selected. Ie. If the booking is put against timeslot: "Monday A Session 1", then it needs to go into cell with bookmark 'mon1_session1_subjectname'. I can do a lost of if else statements, but it's how do I get all the data together to take from?

 

The database is attached if you wish to have a look:

 

LFC LSA_Lauren Monks.zip

 

At the moment I have none of the above functionality set up, and a few bits are broken (work in progress), but more so that you can have a better understanding of the database.

 

Thanks in advance for any assistance! I haven't done any Access work for a few years, and I'm a bit rusty!

[spadam]

1. In the Recordsource of the form that shows the bookings create a query that pulls all the data that you want to show on the form. Set the criteria for the tutorID to the listbox control. Set an onclick event for the listbox to requery the form.

 

2. Create a query that joins the tables which hold the data you want to display to the bookings table. Output the fields that you want to display from this query and use this query as the recordsource for the form.

 

3. Not entirely sure what you mean by this... Which table or form or cells are you referring to?

 

Also, are those actual student names in the database? It would be strongly advisable to use dummy names when sharing it online if you havn't already?

Edited October 20, 2014 by spadam

[_kstone]

Thanks for the response.

 

I will have a go at number 1 and let you know.

 

2. I did try this, however I couldn't get it to display data from the other table based on the ID from the booking table. It would error if I tried to show data not directly on the that table.

 

3. I want to put bookings into a table in an external word document based on the name of the timetable slot. I've attached the timetable document I have, if that gives you more of an idea?

 

Timetable.docx

 

Yep, all sample data, no real names used yet.

[spadam]

LFC LSA_Lauren Monks.zip Ok, I've looked a bit further at your file today and have made a few changes which should help to point you in the right direction. I haven't used Access for a few years now so there may be better ways of achieving what you're trying to do too.

 

I have added fields to tbl_Timetable for the week, day and period as between them these characteristics identify a distinct timetable slot. This allows us to filter on these separately.

 

I have created 2 saved queries - qryTimetableData and qryTimetableData2. The first selects data which is then fed into the second. The results of the second are then used as the data source for a crosstab query. By filtering the data selected by the second query to only select a single student and week I think this will show the timetable in the format you require.

 

You should consider using id's for the subjects too and store the id in the booking table rather than the subject name directly like with the students. Read up on Database Normalisation too if you're unfamiliar with the term.

 

I have also changed a couple of the combo boxes on frm_Booking to show the names rather than the ids.

 

Finally - It is not advised to use spaces in field or table names and I would advise against doing this.

 

Hope this is helpful but feel free to ask if you need further help.

[_kstone]

Thanks for this! Looks very close to what i'm after. I can definitely work on from this to tweak a report to show it in a pretty format.

 

Thanks for all your help!

 

In terms of spaces in names etc. - I didn't design this database, it was lumbered on me the other day, so I'm slowly going to work through and tweak it.

 

Thanks again!

[spadam]

You're welcome. Best of luck with it.

 

One other thing I forgot to mention - It might be a good idea to add a unique index on the bookings table across the 'studentID' and 'timetable slot' fields to prevent multiple booking being made for the same slot.

[_kstone]

Right - I've recreated most of what you did - I wanted to do it from scratch so I can understand it fully... Got to the end with the crosstab query, but it only displays those with data, whereas in yours, any without bookings just have a '-' in the field. How did you get it to do that?

[spadam]

You need to change the join type on the query which passes data through to the crosstab to a Left Join (Option 2 in Access). This will select all records from the Timetable table.