select syntax return zero (1 Viewer)

[ost3z]

hi every body
i have aform which class module where i make 2 queries the first works fine but the other always return values with zero
my select syntax is in clsschool and the query is

   strSQL = "SELECT tblgrade.Grade, tblClasses.NoOfclasses, tblClasses.NoOfSessions " & _
             "FROM tblgrade INNER JOIN tblClasses ON tblgrade.IntGradeId=tblClasses.IntGradeId " & _
             "WHERE tblClasses.intschoolinfoid = " & intId

my database link is
http://www.mediafire.com/download/rq...upervisior.mdb
can any one help ? thans:banghead:

 

[JHB]

Hmm - the file you link to seems to be deleted.
You can attached file here in the forum, (zip it).

 

[ost3z]

sorry the correct link is
https://www.mediafire.com/download/rqo591f59cdc12r/Supervisior.mdb

 

[JHB]

i have aform which class module where i make 2 queries the first works fine but the other always return values with zero
my select syntax is in clsschool and the query is

   strSQL = "SELECT tblgrade.Grade, tblClasses.NoOfclasses, tblClasses.NoOfSessions " & _
             "FROM tblgrade INNER JOIN tblClasses ON tblgrade.IntGradeId=tblClasses.IntGradeId " & _
             "WHERE tblClasses.intschoolinfoid = " & intId

The query you show here works perfect, if the other does I don't know because I didn't find out which one you were thinking of!
The problem lies somewhere else, you have some customer created property, there never is set, (they never gets any value), a property like the one shown below.
So when you call them you get zero value.

Public Property Let gradeSubform(ByVal value As Integer)
  On Error Resume Next
  StrgradeSubformVal = value
End Property

Ps. I've never seen such bundle of code to provide nothing special, except of doing it difficult to maintain and troubleshoot the database.

 

[ost3z]

The query you show here works perfect, if the other does I don't know because I didn't find out which one you were thinking of!
The problem lies somewhere else, you have some customer created property, there never is set, (they never gets any value), a property like the one shown below.
So when you call them you get zero value.

Public Property Let gradeSubform(ByVal value As Integer)
  On Error Resume Next
  StrgradeSubformVal = value
End Property

Ps. I've never seen such bundle of code to provide nothing special, except of doing it difficult to maintain and troubleshoot the database.

thank you for replying i read your post any read my code and so i find 2 errors the frist is i assigned string and read it as integer in this property

Public Property Get gradeSubform() As String
  On Error Resume Next
  gradeSubform = StrgradeSubformVal
End Property
Public Property Let gradeSubform(ByVal value As String)
  On Error Resume Next
  StrgradeSubformVal = value
End Property

despite that and despite the query is correct it try it many time its still return nothing now than zero where is the error ? thanks for you again

 

[jdraw]

Did you post the updated database?
Can you tell the readers exactly what the problem is? The more info you give, the better your chances of getting a focused response.

 

[JHB]

Place a breakpoint in you code where you are setting (and getting values) from the property gradeSubform, like shown in the picture, to see if you are setting any values. You can also use a message box, (MsgBox).

 

[ost3z]

thanks all for help after foolowing my code i find the problem and solve it but still apart of the problem in the form show pouplate with data for main table id in main form for example in main form i have id =1 in subform should populate with all data from another table for the same id and it done but it appears one by one after click next i need it populate all data for that id ?? my code is

Function BuildSQLSelectSChools(blnAllRecords As Boolean) As String

    On Error GoTo HandleError
    
    'generate SQL command to retrieve projects records
    Dim strSQLRetrieve As String
    
    'if option to display all records is selected in toggle button
    If blnAllRecords Then
    
 strSQLRetrieve = "SELECT tblSchoolInfo.intschoolinfoid, tblSchoolInfo.SchoolName, tblSchoolInfo.intEdaraid, " & _
                  "tblSchoolInfo.TeachersExistsNumber, tblSchoolInfo.TeachersLack, tblSchoolInfo.TeachersIncrease, " & _
                  "tblSchoolInfo.TeachersNeed, tblSchoolInfo.intSchoolkindid, tblgrade.Grade, tblClasses.NoOfclasses, " & _
                  "tblClasses.NoOfSessions FROM tblSchoolInfo INNER JOIN (tblgrade INNER JOIN tblClasses " & _
                  "ON tblgrade.IntGradeId = tblClasses.IntGradeId) ON tblSchoolInfo.intschoolinfoid = tblClasses.intschoolinfoid " & _
                         "ORDER BY tblSchoolInfo.intschoolinfoid"
    Else
        'show only the unclosed projects
        strSQLRetrieve = "SELECT tblSchoolInfo.intschoolinfoid, tblSchoolInfo.SchoolName, tblSchoolInfo.intEdaraid, " & _
                  "tblSchoolInfo.TeachersExistsNumber, tblSchoolInfo.TeachersLack, tblSchoolInfo.TeachersIncrease, " & _
                  "tblSchoolInfo.TeachersNeed, tblSchoolInfo.intSchoolkindid, tblgrade.Grade, tblClasses.NoOfclasses, " & _
                  "tblClasses.NoOfSessions FROM tblSchoolInfo INNER JOIN (tblgrade INNER JOIN tblClasses " & _
                  "ON tblgrade.IntGradeId = tblClasses.IntGradeId) ON tblSchoolInfo.intschoolinfoid = tblClasses.intschoolinfoid WHERE intEdaraid <> 10 " & _
            "ORDER BY tblSchoolInfo.intschoolinfoid "
            
    End If
    
    BuildSQLSelectSChools = strSQLRetrieve
    
    Exit Function

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, DB_LOGIC, "BuildSQLSelectSChools"
    Exit Function

End Function

Sub PopulatePropertiesFromRecordset(rsProj As ADODB.Recordset)
On Error GoTo HandleError
'Populate the object with the current record in the
'recordset
Me.schoolinfoid = rsProj!intschoolinfoid
Me.SchoolName = rsProj!SchoolName
Me.txtEdraName = rsProj!intEdaraid 'rsProj!EdraName '1st ERROR FIELD IS NOT EXIST
Me.Schoolkindtxt = rsProj!intSchoolkindid 'rsProj!Schoolkindtxt ' 2nd ERROR FIELD IS NOT EXIST
Me.TeachersExistsNumber = rsProj!TeachersExistsNumber
Me.TeachersIncrease = rsProj!TeachersIncrease
Me.TeachersLack = rsProj!TeachersLack
Me.TeachersNeed = rsProj!TeachersNeed

Me.IIDSubform = rsProj!intschoolinfoid
Me.gradeSubform = rsProj!Grade
Me.gradenumberSubform = rsProj!NoOfclasses
Me.NoOfSessionsSubform = rsProj!NoOfSessions

Exit Sub
HandleError:
GeneralErrorHandler Err.Number, Err.Description, CLS_PROJECT, _
"PopulatePropertiesFromRecordset"
Exit Sub
End Sub

Sub PopulateSchoolControls()
    
        On Error GoTo HandleError
    
        'Populate the controls on the Contacts form with the values of the
        'current record in the contacts object.
        If Not rsSchool.BOF And Not rsSchool.EOF Then
            Me.txtschoolid = objSchool.schoolinfoid
            Me.txtschoolname = objSchool.SchoolName
            Me.CmbEdra = objSchool.txtEdraName
            Me.Cmbschoolkind = objSchool.Schoolkindtxt
            Me.txtteachersNumber = objSchool.TeachersExistsNumber
            Me.txtteacherIncrease = objSchool.TeachersIncrease
            Me.txtteacherslack = objSchool.TeachersLack
            Me.txtteachersneed = objSchool.TeachersNeed
          
                 Forms.FrmSchoolDetails.QSchoolClassessSub.Form.IID = objSchool.schoolinfoid
                  Forms.FrmSchoolDetails.QSchoolClassessSub.Form.txtgrade = objSchool.gradeSubform
                   Forms.FrmSchoolDetails.QSchoolClassessSub.Form.txtgradenumber = objSchool.gradenumberSubform
                    Forms.FrmSchoolDetails.QSchoolClassessSub.Form.NoOfSessions = objSchool.NoOfSessionsSubform
                       
            'display the record count on the form
            lblRecordNum.Caption = "Record " & intCurrSchoolRecord & " Of " & rsSchool.RecordCount
            
        ElseIf rsContacts.BOF Then
            'past beginning of recordset so move to first record
            Call MoveToFirstRecord(intCurrSchoolRecord, rsSchool, objSchool, blnAddMode)
        ElseIf rsContacts.EOF Then
            'past end of recordset so move back to last record
            Call MoveToLastRecord(intCurrSchoolRecord, rsSchool, objSchool, blnAddMode)
        End If
    
    Exit Sub

HandleError:
    GeneralErrorHandler Err.Number, Err.Description, School_FORM, "PopulateProjectsControls"
    Exit Sub

End Sub

 

[JHB]

Could you think of using as a real subform linked to the main form, because what you have now will not work.
Because you are using unbound controls in you subform, and on this way it can only show one record at a time.

 

[ost3z]

what do you mean by a real sub form linked and can use loop to fill subform in datasheet view ?

 

[ost3z]

thankx for all i load it listbox and now it works good