problem with database project

[abu taher]

at 1st: class name entry in a text box (by add new button). access file is store it. then in a another form (when a student admitt) enter all data of this student (in few text box) without class name. when I want to entry the class name then I press enter button and then a form show with msflexgrid and the msflexgrid show that data what I entry at 1st.(I doit for save the time).
however, here I want, a student admitt in class nine. his roll is 01
I entry it manualy. and class name entry by that way. second time another student admitt in class ten. roll is 04. I want when I enter class name the roll no. will entry automatically. I mean I enter class ten the roll will 05. (the last roll will show, cause I don't know what was the last roll in every class). How can I do it. help me any body please.

[vb5prgrmr]

By roll, I take it you mean enrolled in such class whatever number it may be...

So, you have a TableOfClasses table and a StudentsTable table and you want to know how many students have been enrolled in whatever class. So lets add another table, TableEnrollment.

The fields of this table will be (in very simplistic terms)...

ForeignKeyToClassesTable
ForeignKeyToStudentsTable

Then with this information we can then do a simple count query against the EnrollmentTable...

strSQL = "SELECT COUNT(*) AS THECOUNT FROM tblEnrollment WHERE ForeignKeyToClassesTable = " & 10 'class # 10
....
CountOfStudents = adoRs.Fields("THECOUNT") 'this ='s the number of students already enrolled. Then if we did a +1 we would have the count of how many students are enrolled in this class now that we added this student.

Now that is only one way to accomplish this and depending upon your table structure we may be able figure out a different way of accomplishing what you need.

Good Luck

[abu taher]

If don't mind can you modify my attachment. please..............

[vb5prgrmr]

Okay, looking at project now and I have some suggestions...

Under the add new button you should add a check to see if there is information to add...

You should also check to see if the class exists...

Private Sub cmdaddnew_Click()

On Error GoTo cmdaddnew_ClickError

Dim daoRs00 As DAO.Recordset, S As String

If Trim(Text1.Text) = "" Then
  MsgBox "Please Add Class Name", vbOKOnly + vbInformation, "Class Name"
  Text1.SetFocus
  Exit Sub
Else
  S = "SELECT COUNT(*) AS THECOUNT FROM Table1 WHERE class = '" & Text1.Text & "'"
  Set daoRs00 = db.OpenRecordset(S, dbOpenDynaset, dbConsistent, dbOptimistic)
  If daoRs00.RecordCount <> 0 And daoRs00.EOF = False And daoRs00.BOF = False Then
    
    'have a resultset
    If daoRs00.Fields("THECOUNT") > 0 Then
      
      'okay, if count is greater than zero then class already exists
      MsgBox "Class exists!", vbOKOnly + vbInformation, "Duplicate record"
      
      'close rs, exit
      daoRs00.Close
      Set daoRs00 = Nothing
      Exit Sub
      
    Else
      
      rs.AddNew
      rs("class") = Text1.Text
      rs.Update
      
    End If
    
  Else
    
    'if we are here for some reason then the query failed for some reason
    daoRs00.Close
    Set daoRs00 = Nothing
    
  End If
  
End If

Exit Sub
cmdaddnew_ClickError:

MsgBox "cmdaddnew_Click " & Err.Number & ":" & Err.Description

End Sub

Then when the form unloads, you need to take care of your objects...

Private Sub Form_Unload(Cancel As Integer)

On Error GoTo Form_UnloadError

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Exit Sub
Form_UnloadError:

MsgBox "Form_Unload " & Err.Number & ":" & Err.Description

End Sub

Oh yeah, as you can see in the code examples, I added some error handling, which you should add everywhere. Also, while it was only two variables, you should remove unused variables and for future reference you should comment everything. So then your modified form would look something like this...

'********************************************
'Author       :
'Contact Info :
'Creation Date:20090611
'Procedure(s) :
'Description  :
'********************************************
Option Explicit

'holds the connection to the database, used for reading/adding info to the class table
Dim db As dao.Database, rs As dao.Recordset

'********************************************
'Author       :
'Creation Date:20090611
'Proceedure   :Form_Load
'Arguments    :
'Returns      :
'Description  :See in code comments
'********************************************
Private Sub Form_Load()

On Error GoTo Form_LoadError

'open the database
Set db = OpenDatabase(App.Path & "\db1.mdb")

'open the table
Set rs = db.OpenRecordset("table1", dbOpenTable)

Exit Sub
Form_LoadError:

MsgBox "Form_Load " & Err.Number & ":" & Err.Description

End Sub

'********************************************
'Author       :
'Creation Date:20090611
'Proceedure   :cmdaddnew_Click
'Arguments    :
'Returns      :
'Description  :See in code comments
'********************************************
Private Sub cmdaddnew_Click()

On Error GoTo cmdaddnew_ClickError

'declare procedural variables
'used for testing the information in Table1, used to hold the query string
Dim daoRs00 As dao.Recordset, S As String

'test text1 to see if user has entered any information
If Trim(Text1.Text) = "" Then
  
  'user had not entered any information so notify user
  MsgBox "Please Add Class Name", vbOKOnly + vbInformation, "Class Name"
  
  'now, make it easy for user to just enter information and then exit this sub
  Text1.SetFocus
  Exit Sub
  
Else
  
  'set string variable with query
  S = "SELECT COUNT(*) AS THECOUNT FROM Table1 WHERE class = '" & Trim(Text1.Text) & "'"
  
  'execute query and test
  Set daoRs00 = db.OpenRecordset(S, dbOpenDynaset, dbConsistent, dbOptimistic)
  If daoRs00.RecordCount <> 0 And daoRs00.EOF = False And daoRs00.BOF = False Then
    
    'have a resultset so test the information returned
    If daoRs00.Fields("THECOUNT") > 0 Then
      
      'okay, if count is greater than zero then class already exists
      MsgBox "Class exists!", vbOKOnly + vbInformation, "Duplicate record"
      
      'close recordset, and exit
      daoRs00.Close
      Set daoRs00 = Nothing
      Exit Sub
      
    Else
      
      'okay, this class does not exist so use form level recordset to add a new record
      rs.AddNew
      rs("class") = Text1.Text
      rs.Update
      
      'then close procedure level recordset
      daoRs00.Close
      Set daoRs00 = Nothing
      
    End If
    
  Else 'If daoRs00.RecordCount <> 0 And daoRs00.EOF = False And daoRs00.BOF = False Then
    
    'if we are here for some reason then the query failed for some reason
    daoRs00.Close
    Set daoRs00 = Nothing
    
  End If
  
End If

Exit Sub
cmdaddnew_ClickError:

MsgBox "cmdaddnew_Click " & Err.Number & ":" & Err.Description

End Sub

'********************************************
'Author       :
'Creation Date:20090611
'Proceedure   :cmdform1_Click
'Arguments    :
'Returns      :
'Description  :See in code comments
'********************************************
Private Sub cmdform1_Click()

On Error GoTo cmdform1_ClickError

'show form1 (which is the student form)
Form1.Show

Exit Sub
cmdform1_ClickError:

MsgBox "cmdform1_Click " & Err.Number & ":" & Err.Description

End Sub

'********************************************
'Author       :
'Creation Date:20090611
'Proceedure   :cmdexit_Click
'Arguments    :
'Returns      :
'Description  :See in code comments
'********************************************
Private Sub cmdexit_Click()

On Error GoTo cmdexit_ClickError

'time to exit
Unload Me

Exit Sub
cmdexit_ClickError:

MsgBox "cmdexit_Click " & Err.Number & ":" & Err.Description

End Sub

'********************************************
'Author       :
'Creation Date:20090611
'Proceedure   :Form_Unload
'Arguments    :Cancel As Integer
'Returns      :
'Description  :See in code comments
'********************************************
Private Sub Form_Unload(Cancel As Integer)

On Error GoTo Form_UnloadError

'clean up our objects as this form closes
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Exit Sub
Form_UnloadError:

MsgBox "Form_Unload " & Err.Number & ":" & Err.Description

End Sub

Oh yeah! The use of End is not recommended so I also removed it...

[vb5prgrmr]

Now, I know that does not solve your problem but you have the hints in the add new button on how to find out how many students are enrolled. If you need any more help just ask.

Good Luck

[abu taher]

Thanks for details help. Its helpful for me.
>>Now, I know that does not solve your problem but you have the hints in the add new button on how to find out how many students are enrolled.
u right. but how i find out how many stdent..........please help me. fristly you told me to make a query. so please

[vb5prgrmr]

Okay, this was the query I was pointing you too earlier...

S = "SELECT COUNT(*) AS THECOUNT FROM Table1 WHERE class = '" & Trim(Text1.Text) & "'"

You just need to adjust which table it points to (table1 vs. table2) and which field it points to.

Good Luck

[abu taher]

I don't understand.

[vb5prgrmr]

Okay, don't need to change field name (sorry forgot how you had your tables designed), just change Table1 to Table2 and you will have you count of how many records in Table2 = whatever class name. Then on your roll field when you do your addnew you would use THECOUNT + 1, or you could use a different query if you wanted.

S = "SELECT MAX(roll) AS THEMAX FROM Table2 WHERE class = '" classname & "'"

Good Luck

[abu taher]

I use this code. but it not work. the last roll not show in text box automatically.

[vb5prgrmr]

That query is not for showing the last roll, it was showing the max value roll. If you want the last roll then...

SELECT TOP 1 * FROM tablename ORDER BY field DESC

Good Luck

[abu taher]

you mean I make a query where have only the roll field. and I use this code against that code?

[vb5prgrmr]

Yes

[abu taher]

another thing , I use this code in that query?

[vb5prgrmr]

Okay, these last posts have made me thought that you are unsure of what I am trying to say.

The select top 1 * means give me everything, each column from tablename. Then the order by field name is meant to be ordered by autonumber field. Then desc part means descending so you auto number field is numbered 1,2,3,4,5 and by order that field descending its order is 5,4,3,2,1 and with the select top 1 you get the record with the autonumber of 5 and thus you get the last record entered.

Now the max query even further above says for this field (your roll field) give me the largest number. So if in this column/field you had the values 1,5,3,2,5,4,3,2,1 then it would return the value of the 5.

Good Luck

[abu taher]

1 problem. here only show the last roll. but I want when I enter ten then show the last roll of class ten.

[vb5prgrmr]

Last = top 1 desc
highest = max

Good Luck

[abu taher]

please write the full code

[vb5prgrmr]

NO! That is for you to do and for you to learn how to do!

[abu taher]

I don't understand this. help me.

[vb5prgrmr]

Okay, lets say we have this table...

Table1
iID (autonumber unique ID)
iValue (Number Long)

and data has been entered like so...
iID iValue
1 4
2 8
3 1
4 3
5 9
6 2

Now to get the LAST record entered we would use...

SELECT TOP 1 iValue FROM Table1 ORDER BY iID DESC

To get the greatest number entered into the iValue field we would use

SELECT MAX(iValue) AS TheMax FROM Table1

and reference it via the alias TheMax (Rs.Fields("TheMax")

Good Luck

[abu taher]

here it show the last iValue order by iID. but it not similar with me. I mean iID may be various. Like:

class roll
nine 2
ten 5
five 7
nine 3
ten 6

I mean it sorting by class. it show the last roll of every class. like:

class roll
nine 3 ' the last roll of nine
ten 6
five 7

in vb6 I want i write ten (in a textbox) and the roll will come (other textbox) automatically.

[vb5prgrmr]

SELECT MAX(roll) AS THEMAX FROM TableName WHERE class = 'nine'

Good Luck

[abu taher]

SELECT MAX(roll) AS THEMAX FROM TableName WHERE class = 'nine'

yeah this code work well. but a few thing. here you write class = nine, but it will be change by my vb project. if you fixed then how I get result by changing class?

[vb5prgrmr]

class is the name of the field, at least that is what you showed above. If your field name is different then use that name...

Good Luck

[abu taher]

I don't mean that. I have no problem with field name. I mean the 'nine'. you fixed it. If I write 'ten' then it show the last roll of class ten.
I want in vb project I write nine then show last roll of nine or I write ten then show the last roll of class ten.

[vb5prgrmr]

so replace it with your textbox.text... I could for money but will not do it for you for free. You must learn how to connect the dots yourself sir. You have all the information so please use the right (?) side of your brain. (Right side is logical side right???)

Good Luck

[abu taher]

I tried It many way and then solve. thanks