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.
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
If don't mind can you modify my attachment. please..............
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 SubThen 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 SubOh 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 SubOh yeah! The use of End is not recommended so I also removed it...
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
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
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
I don't understand.
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
I use this code. but it not work. the last roll not show in text box automatically.
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
you mean I make a query where have only the roll field. and I use this code against that code?
Yes
another thing , I use this code in that query?
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
1 problem. here only show the last roll. but I want when I enter ten then show the last roll of class ten.
Last = top 1 desc
highest = max
Good Luck
please write the full code
NO! That is for you to do and for you to learn how to do!
I don't understand this. help me.
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
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.
SELECT MAX(roll) AS THEMAX FROM TableName WHERE class = 'nine'
Good Luck
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?
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
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.
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
I tried It many way and then solve. thanks
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.