This query for a rowsource not working

[dragginthru]

Ive posted similar in the forms section but it has gone unanswered so I've pulled out the offending query and hope someone here can offer insight into this frustrating problem.

I have a form [Training Record] that has 2 combo boxes.

Box A (course_title_combo) has the titles of the various training courses. It pulls 2 columns from the training_courses table, namely courseID and course_title. Only course_title is shown in the box. The control source of this is set to CourseID as this is the field to be written to the training_record table, showing who attends what course.

Box B (course_date_combo) is MEANT to pull through the dates that each course is on, by pulling through the course dates for each course where courseID (as selected by combo box A) is equal to the courseID in the course_dates table.

Here is the current rowsource query for box B - which is basically showing blank fields...

SELECT tbl_course_dates.course_date, tbl_course_dates.CourseID FROM tbl_course_dates WHERE forms![training record test form]!course_title_combo=tbl_course_dates.CourseID

I cant see what is wrong with the query, but Im guessing it must be wrong as it is the rowsource which determines what is included in combo box B.

Any help would be appreciated no end - im totally stuck now

 

[neileg]

Your WHERE clause looks the wrong way round. I suppose it shouldn't matter, but maybe it does. Also, is CourseID the first column in the combo? If not, you may have to specify the column number.

 

[dragginthru]

Thanks for the reply...

I swapped the query round but no difference.

Re: 1st column - i think it is, yeah. But im pretty new to all this so unsure.
how do i confirm this?

http://www.access-programmers.co.uk/forums/showthread.php?t=94618

A version of the DB is there if you fancy a peek at it, ive took out all the tables/forms except the offending ones!!!

 

[neileg]

You need to requery the date combo. In the after update event of course_title_combo add this code:

combo43.requery

This will cause the rowsource to be updated with the course title as the criterion. When you select combo43, only the dates linked to the course title will be displayed.

 

[dragginthru]

OK ive done that now - ie. put combo43.requery in the afterupdate property of the course_title_combo

Its still not working, there are 2 probs with it...

1. It is still showing incorrect dates - if you look in the table, the dates are quite distinct for each course - but its showing the same dates for either
2. Also when you change the course in open view, the message: "Microsoft Access can't find the macro 'combo43.' The macro (or its macro group) doesnt exist or the macro is new but hasnt been saved"

 

[neileg]

Sorry, you need to open the code window for the AfterUpdate event and past in the code. Since you already had code in this form I assumed you would realise this.

This should address the dates problem.

By the way, when you compact and repair you db it drops from 4Mb to 370kb!

See the attachment.

 

[dragginthru]

I can see you have made alterations, IE added a query etc. However still I get incorrect dates in the form... also it wont let me change the value in the first form :S

Sorry to keep coming back saying "duh, i cant do it" - sadly i am pretty new to this...

 

[dragginthru]

aha i think it was in read only mode coz i was looking at zip file - i THINK thats it sorted.... thanks very much

 

[dragginthru]

This works fine mate and I appreciate your help.

However Ive noticed one little glitch and I cant work out how to fix it.

When using the form it is always displaying multiple records one above each other. I have found that if one selects a course in box A - ALL the box B values change!!! So for example if you click on combo B 4 rows down, even if the combo A on that row shows a different course, it will show the dates for the last one you PHYSICALLY SELECTED. Which is fine, if each time you click on combo A first - but I guess it wont always be that way.

Not sure if that makes sense but if you try out on the example you'll see what i mean