CODEWITHSUNDEEP
vbaformsms-accessdropdownmulti-user
Qudsia
Qudsia

Reputation: 53

MS Access Quiz Form: Using VBA to change record source of form controls based on course selected from dropdown on Test form for multiple users

I have a table of questions. This table contains questions for, let's say, 3 courses. "Course" is one of the fields in this table. There are 3 queries (Course1_extended, Course2_extended, Course3_extended) set up to get all the records for each of the 3 courses.

I currently have a form set up for Course1 which works well using Course1_extended query table as its Record Source. I don't want to create separate forms for each course as there will be many more courses added. I want to add a dropdown to the test form, or some initial login form, which prompts some code to change the record source of that form's controls to the related course's query. I'm new at Access coding. It seems I can do this using 'Me.Form.RecordSource' etc. Want to know if this will cause any issues with multiple users doing the quiz at the same time for different courses.

Upvotes: 0

Views: 53

Answers (1)

Qudsia
Qudsia

Reputation: 53

Thanks, both comments will be helpful in development of my forms. However, I achieved the right set of data as thus:

  • On initial Login form I placed a combobox with courses (SQL query populates the list from Questions table).

  • When user enters their info and clicks button to move to the 2nd form (test form) the value in Course selection combobox is saved to TempVars!sCourseID (where sCourseID is a private variable of string datatype).

  • Questions for all the courses are in a single Questions Table. There's a query which selects all the fields from Questions table. For the CourseID field, a criteria is entered to match the TempVars("sCourseID"):

Criteria: [TempVars]![sCourseID]

The Test form's record source property is set to this filtered query.

(Originally I had my TempVars set in VBA as variable name in quotations as TempVars("sCourseID"), which was working fine when I used in captions etc., but I wasn't able to set filter criteria (probably because of unrelated syntax issues). Then changed to the "!" style as TempVars!sCourseID in my VBA code. Not sure if it mattered or not, but it's working now. Had other changes too so can't say if that was the reason.)

Upvotes: 0

Related Questions