Help incrementing a number by 1 3

[kat17]

I have a table called tblStudent & a matching form with the following fields:
idClass(Long), idSchool(String), rollNo(Long), barcode(String), fName(String), lName(String)

I would like to increment the field "rollNo" by 1 depending on a WHERE clause.

My problem is getting my form to work properly.
How do you insert SQL & variables into Visual Basic syntax?

This is my attempt.. but its not working!


SQL = "SELECT Last(tblStudent.rollNo)" & _
" FROM tblClass INNER JOIN tblStudent ON tblClass.idClass = tblStudent.idClass" & _
" GROUP BY tblClass.rowClass, tblStudent.idSchool" & _
" HAVING (((tblClass.rowClass)=" & Me.Combo8.Value & _
") AND ((tblStudent.idSchool)='" & Trim$(Me.Combo10.Value) & _
"'))"

I'm only new to Visual Basic but have used other programming languages before. I'm not sure how I have to go about declaring this statement and where should I put it??

I've seen on the net somewhere to use the trim() function to help with reading the String properly. Now I'm just lost and dont know what to do!!
Is there a simpler solution??

 

[kat17]

OK... here is a new attempt. I am just a bit stuck with getting the String to become an Integer.

I feel like I need to do something like:

Me![rollNo] = sql + 1


How and where would this go???




Private Sub Combo10_AfterUpdate()
Const cQuote = """"
Dim sql As String

sql = "SELECT Last([tblStudent].[rollNo])" & _
" FROM tblClass INNER JOIN tblStudent ON [tblClass].[idClass]=[tblStudent].[idClass] GROUP BY [tblClass].[rowClass], [tblStudent].[idSchool] HAVING (((tblClass.rowClass)= " & _
Me!Combo10 & " AND ((tblStudent.idSchool)=" & cQuote & _
Me!Combo8 & cQuote & _
"))"


End Sub

 

[ZmrAbdulla]

Did you try DMax Function to check the highest number?
[tt]
DMax("MyNumber", "MyTable")+1
[/tt]
You can even use criteria in it.. See help file for more..


________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

 

[kat17]

I have looked at the DMax function but did not know it could use criteria. I will research more and let you know... Thanks!

 

[ZmrAbdulla]

[tt]
=DMax("[Freight]", "Orders", "[ShipRegion] = 'CA'")
[/tt]
See more in the help file with topic
"DMin, DMax Functions Example"

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

 

[kat17]

Thanks heaps Zameer. It works beautifully!

Just in case anybody is interested what I did, here is the code below:

Private Sub Combo10_AfterUpdate()
'after update of CLASS FIELD, a new student roll number is generated
Me![rollNo] = newRollNo()
End Sub
---

Public Function newRollNo() As Long
On Error GoTo NextID_Err

Dim lngNextID As Long
'Find the highest student roll number for the selected class and add 1
lngNextID = DMax("[rollNo]", "tblStudent", "idSchool=[Combo8] AND idClass=[Combo10]") + 1
newRollNo = lngNextID
'Exit function now after successful incrementing or after error message
Exit_newRollNo:
Exit Function

'If an error occurred, display a message, then go to Exit statement
NextID_Err:
MsgBox "Could not update Roll No. Please enter Roll No. manually "

Resume Exit_newRollNo

End Function

 

[ZmrAbdulla]

You have done it well!!
But you need to add something in it..
Now your function checks the Max Number and adds 1 to it. What if the table has no records? It will complain as invalid use of NULL. So you need to wrap the DMax with NZ
[tt]
=Nz(DMax("[rollNo]", "tblStudent", "idSchool=[Combo8] AND idClass=[Combo10]") + 1,1)[/tt]
You can replace it with any number that you wish. See more in the help
Also IIF Function will do the trick...

Regards

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

 

[kat17]

Yes, I noticed that error.. hence, the error message to ask the user to enter the info manually! (nice try, eh?! LOL)

Thank you so much for your time & for giving me this valuable information.

 

[ZmrAbdulla]

You could use an InputBox to add the number manually
Something like...
Note: code not tested....

Sub AskNumber()
Dim x As Integer
Dim strInput As String, strMsg As String
x = DMax("[rollNo]", "tblStudent", "idSchool=[Combo8] AND idClass=[Combo10]")
         If IsNull(x) Then
    strMsg = "Enter the number."
    strInput = InputBox(Prompt:=strMsg, Title:="Missing number")
         x = strInput
        Me.txtBox = x
Else
x = DMax("[rollNo]", "tblStudent", "idSchool=[Combo8] AND idClass=[Combo10]") + 1
         Me.txtBox = x
End If
End Sub

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

 

[kat17]

Thanks again. You help has exceeded my expectations! I hope I can repay the favour one day!

 

[PHV]

Just a note:
Dim x As Integer
...
If IsNull(x) Then

For me only a Variant can be null ...

Sub AskNumber()
Dim x
Dim strInput As String, strMsg As String
x = DMax("[rollNo]", "tblStudent", "idSchool=[Combo8] AND idClass=[Combo10]")
If IsNull(x) Then
    strMsg = "Enter the number."
    strInput = InputBox(Prompt:=strMsg, Title:="Missing number")
    Me.txtBox = strInput + 0
Else
    Me.txtBox = x + 1
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ZmrAbdulla]

Coding become magic when a real expert comes into the scene..

Thanks PHV for that greate work...

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

 

[kat17]

Yes.. thanks for jumping in there PHV!

It works like a dream now! VERY USER FRIENDLY ;-)

Just one question in regards to the Input Box.... Can I get rid of the CANCEL button?
(if you press cancel I get a run-time error '13' "Type Mismatch")
It works great when the user types a value and clicks OK. How can I get them to NOT PRESS CANCEL???

 

[ZmrAbdulla]

See

http://www.tek-tips.com/viewthread.cfm?qid=1109744

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

 

[ZmrAbdulla]

Or trap the error number here itself.

Sub AskNumber()
On Error GoTo ErrorHandler
Dim x
Dim strInput As String, strMsg As String
x = DMax("[rollNo]", "tblStudent", "idSchool=[Combo8] AND idClass=[Combo10]")
If IsNull(x) Then
    strMsg = "Enter the number."
    strInput = InputBox(Prompt:=strMsg, Title:="Missing number")
    Me.txtBox = strInput + 0
Else
    Me.txtBox = x + 1
End If
ErrorHandlerExit:
    Exit Sub
ErrorHandler:
    If Err = 13 Then    'user pressed cancel
    MsgBox "User pressed cancel"
        Resume Next
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume ErrorHandlerExit
    End If
End Sub

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

 

[earthandfire]

Zameer, wouldn't this do the trick:

Sub AskNumber()
Dim x
Dim strInput As String, strMsg As String
x = DMax("[rollNo]", "tblStudent", "idSchool=[Combo8] AND idClass=[Combo10]")
If IsNull(x) Then
    strMsg = "Enter the number."
    strInput = InputBox(Prompt:=strMsg, Title:="Missing number")
    If strInput = "" Then strInput = 0
    'or ... If StrInput = "" Then strInput = "0" 
    Me.txtBox = strInput + 0
Else
    Me.txtBox = x + 1
End If
End Sub

 

[ZmrAbdulla]

It will..
But there is a doubt..
will it add a record assigning the x=0 ?


________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

 

[earthandfire]

The textbox will be assigned strInput + 0, so whatever is done later with the textbox will still be done.

One other point that I've just noticed, there doesn't appear to be a check anywhere for the user typing "text", i.e. a non numeric value in the InputBox. Perhaps IsNumeric should also be part of the validation of the InputBox return value.

 

[ZmrAbdulla]

So what do you think change it like this?

Sub AskNumber()
On Error GoTo ErrorHandler
Dim x
Dim strInput As String, strMsg As String
x = DMax("[rollNo]", "tblStudent", "idSchool=[Combo8] AND idClass=[Combo10]")
If IsNull(x) Then
    strMsg = "Enter the number."
    strInput = InputBox(Prompt:=strMsg, Title:="Missing number")[b]
            If Not IsNumeric(strInput) Then
                MsgBox "You must enter a Number"
            Else
    Me.txtBox = strInput
 End If[/b]
Else
    Me.txtBox = x + 1
End If
ErrorHandlerExit:
    Exit Sub
ErrorHandler:
    If Err = 13 Then    'user pressed cancel
    MsgBox "User pressed cancel"
        Resume Next
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume ErrorHandlerExit
    End If
End Sub

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

 

[earthandfire]

That's pretty much what I had in mind, and with the If IsNumeric(string) and If IsNull(Variant - for number), I don't think the On Error error handling is necessary.


Hope this helps.