Form not displaying data properly

[azalea]

Dear All,

I am brand new to MS Access 2013 & VBA.

I have a report that I need to develop. The setup is as follows
There is a form that collects the data and writes it to a SQL database. The form is a certificate order form for a school. There are two tables that data is written to. dbo.CertificateMaster & dbo.CertificationModules. There is a relationship between the two tables. A person will request the certificate and included in the request are the subject(modules) that the person has studied.
As part of the request the college/school needs a printed copy of the request ( I know its old fashioned, but it is a legal requirement per our licence).

As the report opens I need it populate certain fields with their full names. To do this I use the following script

Private Sub Report_Load()
Dim strSQL1 As String, db As DAO.Database, rs As DAO.Recordset
Dim strSQL2 As String, db2 As DAO.Database, rs2 As DAO.Recordset
Dim strSQL3 As String, db3 As DAO.Database, rs3 As DAO.Recordset

Set db = CurrentDb
strSQL1 = "SELECT [RequestDetails]" & _
"FROM [dbo_CertificateRequestType]" & _
"Where RequestTypeID = " & Me.RequestType & ""
Set rs = db.OpenRecordset(strSQL1)
Me.Req = Trim(rs.Fields(0)) & " Request"

Set db2 = CurrentDb
strSQL2 = "SELECT [Course],[NQFLevel],[Credits],[SAQAID]" & _
"FROM [dbo_tblCourse_Department]" & _
"Where ID = " & Me.ProgrammeTitle
Set rs2 = db.OpenRecordset(strSQL2)

Me.PgmTitle = rs2.Fields(0)
Me.NQFLevel = rs2.Fields(1)
Me.Credits = rs.Fields(2)
Me.SAQAID = rs.Fields(3)

If Me.ProgrammeType = 1 Then
Me.pgmType = "Higher Education"
Else
If Me.ProgrammeType = 2 Then
Me.pgmType = "Occasional"
Else
Me.pgmType = "Occupational"
End If
End If

If Me.Completion = 1 Then
Me.pgmComplete = "Completed"
Else
If Me.Completion = 2 Then
Me.pgmComplete = "No Result"
Else
Me.pgmComplete = "Partially Completed"
End If
End If

Set db3 = CurrentDb
strSQL3 = "SELECT [ID],[ModuleName]" & _
"FROM [dbo_tblCourses]" & _
"where ID = " & Me.ModuleID
Set rs3 = db.OpenRecordset(strSQL3)
Me.mod = rs3.Fields(1)

End Sub

I am sure that the code is shocking (n00b here), I am stuck with 2 problems that I have spent the past 2 days trying to resolve

Problem 1
When drawing the data from dbo_tblCourse_Department I need to populate 4 textboxes on the report. I am able to populate PgmTitle &NQFLevel, but I keep getting "Item not found in this collection error for Credits & SAQAID.

Problem 2
I need to populate the various courses with their name which is extracted from dbo_tblCourses. However only the first course name is visible in the report. I have tried all sorts of different iterations with IF Then or Do While to get the names in the correct line of the report.

Please help :banghead::banghead::banghead:

Azalea

 

[namliam]

Why wouldnt you simply join the required tables into your report's rowsource and go from there?

 

[arnelgp]

is it possible for you to upload your db?

 

[azalea]

namliam - you are my Hero. Thanks I have got it almost 100%.

I think that it is a case of not seeing the wood for the trees.

My issue no is that there are so many records (See the attached) when i open the report.

I suspect that there is an issue with one of the relationships between the tables.

arnelgp - I have included a copy of the DB as requested. I am not sure if you will be able to view it as it has tables that are linked to the SQL server.

Thanks All

 

[namliam]

Well you are getting what you are asking for....
Most likely you are seeing each module instead of only each course.

Now I dont know your design or anything but having a circular relationship usually is a bad idea .... in your case going from master > Modules > Courses > Course_Module > Department > Master
Most likely you will want a seperate Course_Module for each of the Courses and Department table.

 

[azalea]

namliam,

thank you so much for the feedback. I did have a circular reference.

Azalea