Adding records through Subform

[CrostonScottish]

I've got myself a bit mixed up with all my sqls and subforms. I am a bit new to relationship databases.

I have a database designed for booking students on to courses run at our hospice. It is based on three main tables. [tblCourselist], [tblStudentinformation] and a table to link these [tbleLINKStudent_Course].

I have a form [frmStudentCourseRecord] based on [tblStudentinformation] with a subform based on an SQL query which i have listed below. This shows the details of the course(s) the student is registered on or have been on previously:

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.[Booking Reference]
FROM tblCourselist INNER JOIN tblLINKStudent_Course ON tblCourselist.lngCourseID = tblLINKStudent_Course.lngCourseID
ORDER BY tblLINKStudent_Course.DateBooked DESC;

Not sure if this is the best way to display the code.

THE PROBLEM:

I want to be able to enrole a student on a new course so that once registered, the details will be recorded on the subform, which is based on my sql query, for that individual. The table [tblCourseList] contains all the courses run at the hospice, with all the relevant fields [curCourseCost] etc.

WHAT I HAVE TRIED:
I have placed a control button in the subform with a macro to open a new form which is a copy of the subform, remaned as [frmAddCourseForStudent] so it is stll based on the sql query above. I thought changing the field [strCourseTitle] to a combobox would allow me to select the right course and bobs your uncle. My mistake! The control source for this field is set as [tblCourseList] so i removed this and set the record source as a sql query [tblCourseList]SELECT [strCourseTitle]. This brings up list of courses in the field but nothing else.

I want to be able to select the course in this form, which then populates all the other linked fields like [curCourseCost] from [tblCourseList] so that when form [frmStudentCourseRecord] is opened for an individual student it will show all the courses enroled in in the subform with the newley added course.

Sorry for the long winded explanation but can anybody help me untangle my SQLs forms?

Thanks

[NeoPa]

You have (accidentally) posted this question in the Access Articles section. This is NOT an article.
I'm moving this to the main Access questions forum.

ADMIN.

[NeoPa]

I'm not too hot on using forms like this, but (Example Filtering on a Form.) may help you from what I understand of your needs.
PS when posting code, the easy way to do it is to select all your code in a chunk, then click on the hash (#) button. This will add the code tags for you :)

[CrostonScottish]

Thanks for that. I have had a look at the code but will need some time to understand it.

Thanks

[NeoPa]

Take your time.
This is a really useful concept once you get it :)

[CrostonScottish]

I have had a good look at the Form Filtering databased which is zipped on this thread. I have very little knowledge of VBA but could the code be cut down so that it filtered on just one control, e .g. CourseTitle but then place the returned values from the filter in a new form as a new record? If so, how

Not sure that explains my question very well. Would a copy of the database help to see where my question is coming from?

[NeoPa]

I found your other post and was working on it when you reposted it in here. Quite funny really :D
I deleted all remnants of it now so we can continue in here as if it never happened.

[NeoPa]

I have had a good look at the Form Filtering databased which is zipped on this thread. I have very little knowledge of VBA but could the code be cut down so that it filtered on just one control, e .g. CourseTitle but then place the returned values from the filter in a new form as a new record? If so, how

Not sure that explains my question very well. Would a copy of the database help to see where my question is coming from?

In answer to your question, No.
Some of the principles may be the same but you're asking about a separate aspect here really.

Filtering simply selects, from the available records, those that match a particular criteria. It does not set defaults for the controls on a form for when you add a new record. In other words - A filtered form will NOT restrict addition of new records to the filtered criteria.

This can be done, but with some extra code in your form to set defaults on the fly to those matching your criteria. Make sure to set the Control defaults rather than trying to change the defaults for the underlying Field in the table.

[CrostonScottish]

A shame nothing in life is quite so simple.
When you say a little extra code, what are we talking? For a complete novice like myself could you point me in the right direction with the code or do you have any sample code i could tweak?

Cheers

[NeoPa]

Let me see if I can dig something up for you.
I don't know where it is at the moment but I'll see what I can find. Hopefully it's a work database that I did this on.

[CrostonScottish]

That would be great. My knowleddge of databases is slowly growing with each database but with this seems to bring the need to build something more elaborate!

[NeoPa]

Try this :

Private Sub Form_Open(Cancel As Integer)
    Dim strJob As String, strDefault As String
 
    With Me
        strJob = ""
        If .Filter > "" Then strJob = Split(.Filter, "'")(1)
        .Caption = Replace(.Caption, "%J", strJob)
        .lblTitle.Caption = Replace(.lblTitle.Caption, "%J", strJob)
        .txtJob.DefaultValue = "'" & strJob & "'"
        strDefault = Replace("#31 Dec %Y#", "%Y", Year(Date + 5))
        .txtExpiry.DefaultValue = IIf(strJob = "Mgmt", strDefault, "")
        .lblExpiry.Visible = (strJob <> "ElecInv")
        .txtExpiry.Visible = .lblExpiry.Visible
    End With
End Sub

It's original so not doctored for this purpose so you'll see extraneous code in there too. I've underlined the main lines that you're interested in though.
It works from the Me.Filter property and sets up defaults for two fields depending on what it finds there.