HELP! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

HELP!

Author

Topic

kongyong92
Starting Member

2 Posts

Posted - 2014-11-11 : 09:08:50

Private Sub Command170_Click()
strSQL2="SELECT * FROM tblRegister WHERE (StudentID="&me.combo119.Column(0)&" "AND" SectionID="&me.SectionIDFK&")"

Set rs = CurrentDb.OpenRecordset(strSQL2)

If Not rs.EOF Then
MsgBox "You have already registered for this course!"

Else
strSQL3 = "SELECT COUNT(*) As countNum FROM tblRegister WHERE(StudentID=" & Me.Combo119.Column(0) & ") "
Set rs2 = CurrentDb.OpenRecordset(strSQL3)
a = rs2.Fields("countNum")

If a > 5 Then
MsgBox "You cannot register for more than 6 courses!"

Else

strSQL1 = "INSERT INTO tblRegister(StudentID,SectionID)VALUES("","&Me.Combo119.column(0)&","&Me.SectionIDFK&","")"

Set db = CurrentDb
db.Execute strSQL1, dbFailOnError
MsgBox "Successful Registration!"
End If
End If

End Sub

I have tried to include this inside Access VBA. The two lines highlighted in red are the errors.

Could anyone help to review and give me suggestions on how it can be changed??

Thanks in advance!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-11-11 : 09:43:04

Try these

strSQL2="SELECT * FROM tblRegister WHERE (StudentID='" & me.combo119.Column(0) & "' AND SectionID='" & me.SectionIDFK & "')"
strSQL1 = "INSERT INTO tblRegister(StudentID,SectionID)VALUES('" & Me.Combo119.column(0) & "','" & Me.SectionIDFK &"')"

If that does not work, add a DEBUG.Print strSQL2 and a similar one for strSQL1 and look at what it prints out or post the results.

kongyong92
Starting Member

2 Posts

Posted - 2014-11-11 : 09:48:32

thanks! now the 2 statements worked fine.

However, there was a bug with

Set rs = CurrentDb.OpenRecordSet(strSQL2)

the error prompted was "too few parameters".

is there anything else that is wrong with the code?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources