Adding records based on Subform

[CrostonScottish]

I am having a few problems that i am sure someone can set me right with.

I have a database for students and enrolements on courses. Each Student Record form has a subform based on the SQL below:

SELECT tblLINKStudent_Course.lngStudentID, tblLINKStudent_Course.lngCourseID, tblLINKStudent_Course.Courseattended, tblLINKStudent_Course.CoursePaid, tblLINKStudent_Course.CourseComplete, tblLINKStudent_Course.Heardaboutcourse, tblLINKStudent_Course.CourseInfoSent, tblLINKStudent_Course.MemNotes, tblLINKStudent_Course.DateBooked, tblLINKStudent_Course.BookedBy, tblCourselist.lngCourseID, tblCourselist.strCourseTitle, tblCourselist.curCourseCost, tblCourselist.strLunchProvided, tblCourselist.memNotes, tblCourselist.CourseDate, tblLINKStudent_Course.PaidBy, tblLINKStudent_Course.strDietryRequ, tblLINKStudent_Course.BookingReference, tblLINKStudent_Course.ReservedBooked
FROM tblCourselist INNER JOIN tblLINKStudent_Course ON tblCourselist.lngCourseID = tblLINKStudent_Course.lngCourseID
ORDER BY tblLINKStudent_Course.DateBooked DESC;

The problem i have is i am trying to create a form which opens from a control on my form so i can enrole a student on a new course. The code for my control is:

Private Sub Add__Booking_Click()
On Error GoTo Err_Register_Click
    If IsNull(Me![lngStudentID]) Then
        MsgBox "Enter attendee information before registering for an event."
    Else
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        DoCmd.OpenForm "frmRegistration"
    End If

Exit_Register_Click:
    Exit Sub

Err_Register_Click:
    MsgBox Err.Description
    Resume Exit_Register_Click
End Sub

Ammended from a MS Template as i liked it.

The new form [frmRegistration] is based on the SQL:

SELECT tblstudentInformation.StrTitle, tblstudentInformation.StrFirstName, tblstudentInformation.StrLastName, tblCourselist.strCourseTitle, tblCourselist.CourseDate, tblCourselist.curCourseCost, tblCourselist.strLunchProvided, tblCourselist.memNotes, tblLINKStudent_Course.Courseattended, tblLINKStudent_Course.CoursePaid, tblLINKStudent_Course.PaidBy, tblLINKStudent_Course.CourseComplete, tblLINKStudent_Course.Heardaboutcourse, tblLINKStudent_Course.CourseInfoSent, tblLINKStudent_Course.MemNotes, tblLINKStudent_Course.DateBooked, tblLINKStudent_Course.BookedBy, tblLINKStudent_Course.strDietryRequ, tblLINKStudent_Course.BookingReference, tblLINKStudent_Course.ReservedBooked, tblLINKStudent_Course.lngCourseID, tblCourselist.lngCourseID
FROM tblstudentInformation INNER JOIN (tblCourselist INNER JOIN tblLINKStudent_Course ON tblCourselist.lngCourseID = tblLINKStudent_Course.lngCourseID) ON tblstudentInformation.lngStudentID = tblLINKStudent_Course.lngStudentID
WHERE (((tblstudentInformation.lngStudentID)=[forms]![frmStudentCourseRecord]![lngStudentID]));

I get a form with the currently enroled courses for a student but not sure how i add them. I have changed my [strCourseTitle] to a combo box, but the control source is tbl.CourseList. and trying record source as various things has not worked.

I want to be able to select a course title from tblcourselist and populate all the other related info i.e. course cost, lunch provided memo notes and to create a new booking reference number.

Any ideas?

 

[Premy]

Well a simple enough solution would be to transform frmRegistration's underlying query into an append query:

Sub btnRegister_Cllick()
Dim S as string
S = "Insert Into... Select...WHERE tblCourse.CourseTitle= " & "'" Me.cboCourseTitle & "'" & " Order by...;"
Docmd.RunSQL S
End Sub

This query will use the CourseTitle chosen in the combo as a criteria to retrieve all the other related fields then append (add) this new record to your student's table.

If u have trouble building the query directly in VBA, u might first build it in the query designer then transform it to a VBA append query (transform just meaning u should buid a string with it like in the example above).

So in this scenario, frmRegistration is unbound and the combo cboCourseTitle is also unbound and just has the purpose of retrieving the CourseTitles from tblCourseTitles. In fact u don't even need frmRegistration and can implement the whole shebang on the student main form.

HTH
Premy

 

[CrostonScottish]

Append Query

Thanks for the prompt response. I am afraid you are dealing with quite a novice in terms of Access. I am not sure i fully understand you. My VBA is almost non-existant so not sure i understand transforming the append query.

My problem before i get in to that is building a simple append query as i am not sure where the values will be appended to. My tblStudentInformation contains stuff like demographics, tblcourselist contains course details like title of course, course date. and a table which links the two tblLINKstudent_Course which is linked by the CourseID and StudentID. My subform which displays the courses enroled on is built on the qry with linked tables tblcourselist and tblLINKStudent_course. The only value i can see that i can append is the course ID. But if i select the course title from the combo box bound to the titles in tblcourselist i can't see how i can get all the course details into the student record.

I think i am getting myself more confused but any ideas? Should i attach a copy of the database so the design can be seen?

 

[Premy]

Well u can also build a separate append query; in that case the criteria collection changes
Insert Into... Select...WHERE tblCourse.CourseTitle= Forms!StudentMainForm!cboCourseTitle Order by...;"

U can then run this query thru cboCourseTitle's Afterupdate event.

Sub cboCourseTitle_Afterupdate
DoCmd.RunQuery "qryAppStudents"
End sub

U may also attach your DB for me to take a look, but it might take a few days.

Regards,
Premy

 

[Premy]

Ok, there are quite some remarks to be made about your db and application setup/design, but since I won't have the time to go into a detailded analysis I'll just give u some general hints based upon a quick glance.

1. your tblCourseList should contain only 1 row per course, otherwise you'll end up with various primary keys for the same course and the resulting nigthmare in querying them. Differentiating-details like date started, memo notes or whatever, should be in a seperate table, maybe your tblStudent_LinkCourse. You could do some catching up on database design basics, normalization etc. Northwind.mdb provides some excellent examples for your level.

Once this is solved the problem at hand becomes much easier to solve and u don't even need an append query: just put cboCourseList in your subform and go to a new record in your subform if you want to add a new course for the student in the mainform. I made some changes to your db and attached it for u to see how it works. Notice that cboCourselist select statement has the DISTINCT keyword. This is exactly because your course table has mutiple rows with the same course name. Once it has unique names, this keyword won't be necessary. In the mainform you just select a student and his corresponding courses will show up in the subform: jo blogg has 2 courses and jo smith 1. Adding a new course is just a matter of selecting the new record button.

Opening frmStudentCourse directly will load all students and their corresponding courses. Openeing it from frmStudentSearch will only load the selected student's record with his/her corresponding courses; choose either approach depending on what u need.

2. Don't create unnecessary tables. A table to just hold yes/no is really not necessary. You'll see that I eliminated most of the lookup tables and set the combos in the subform to look their values up in a list of values. Only create lookup tables for large amounts of data or data that's frequently changing

3. Always think of avoiding unnecessary steps for the user. In your original frmstudentsearch, the user had to click and select last name, click and select first name, then finally click the search button. that's 5 steps. In my adapted version it takes only 2 steps and there's also no need to check for null entries.

4. Avoid cryptic labels like strLastName: just Last Name. Spend some time on design, trying always to align controls in some orderly fashion. Always use entry masks, where possible (like I did for your Course Date textbox) for it makes data entry much easier.

I could go on, but this should do for now. Be aware that you're barely scratching the surface of app design in Access for the time being. You need to be willing to spend some time and efforts to get a reasonable understanding of DB design, VBA and SQL, if u want to start creating relatively solid and userfriendly Access applications. To console u I can assure you that other dev platforms like for delphi, java, vb.net, c# etc. would take even more efforts and time.

Good Luck,
Premy

 

[CrostonScottish]

Thanks some fantastic, sound advice. I always try to run before i can walk. Your suggestions are great and i will certainly get back to the drawing board. As a complete novice you pick up bad habits and realise your mistakes only when you get stuck and some one points out the holes.

Thanks again.