Could You Check this code, please

[Nadia]

Dim StrSQL as string
Dim strSQL1 as integer

strSQL = "SELECT Pre_crsID from Pre_course where Cid= "C1";"

IF IsNull(StrSQL) Or StrSQL = "" Then 
DoCmd.RunSQL = "insert into enrollment (Crs_id,Ehour , Eday ,room_No,sec_id,instructor_id,sid ,sem_no) select '" & Me!cid & "',Atime,Aday,Room_no,secid,instructor_id," & Me!sid & ",Sem_no FROM available_courses;" 

else

strSQL1 = "SELECT grade from st_Grade where Cid='" & strSQL &"' ;"
IF strSQL1 >60 
then DoCmd.RunSQL = "insert into enrollment (Crs_id,Ehour , Eday ,room_No,sec_id,instructor_id,sid ,sem_no) select '" & Me!cid & "',

else 
 MsgBox "The Course has a Previous Course that you must take first!"

End IF
End IF

 

[Alansidman]

What are we checking for? Does it work? Are you getting errors? If so, what do they say?

 

[VilaRestal]

A couple of things stand out:

IF IsNull(StrSQL) Or StrSQL = "" Then

A string variable can't be null so there's no need to check for that. When declared it gets a value "". And you've assigned it a value so it won't be. I think what you mean is If the value of the field it's SQL would select.

strSQL1 = "SELECT grade from st_Grade where Cid='" & strSQL &"' ;"

Assigning a string to a number. I think you'll get an error there. What you should be doing is getting the value of the field the SQL would select.

So I think the code should be:

If Nz(DLookup("Pre_crsID", "Pre_course","Cid = 'C1'"),"") = "" Then 
    DoCmd.RunSQL "insert into enrollment (Crs_id,Ehour , Eday ,room_No,sec_id,instructor_id,sid ,sem_no) select '" & Me!cid & "',Atime,Aday,Room_no,secid,instructor_id," & Me!sid & ",Sem_no FROM available_courses;" 
Else
    If Nz(DLookup("grade","st_Grade","Cid=[COLOR="Red"]'C1'[/COLOR]"),0) > 60 Then 
        DoCmd.RunSQL "insert into enrollment (Crs_id,Ehour , Eday ,room_No,sec_id,instructor_id,sid ,sem_no) select '" & Me!cid & "',Atime,Aday,Room_no,secid,instructor_id," & Me!sid & ",Sem_no FROM available_courses;" 
    Else 
        MsgBox "The Course has a Previous Course that you must take first!"
    End If
End If

I'm unsure about the bit in red. Perhaps it should be '" & Me!cid & "'

 

[VilaRestal]

Or an improved version:

Dim b As Boolean
b = (Nz(DLookup("Pre_crsID", "Pre_course","Cid = 'C1'"),"") = "")
If Not b Then b = (Nz(DLookup("grade","st_Grade","Cid=[COLOR="Red"]'C1'[/COLOR]"),0) > 60)
If b Then
    DoCmd.RunSQL "insert into enrollment (Crs_id,Ehour , Eday ,room_No,sec_id,instructor_id,sid ,sem_no) select '" & Me!cid & "',Atime,Aday,Room_no,secid,instructor_id," & Me!sid & ",Sem_no FROM available_courses;" 
Else
    MsgBox "The Course has a Previous Course that you must take first!"
End If

 

[VilaRestal]

Ah I think I've worked out what the bit in red should be:

Dim b As Boolean
Dim s As String
s = Nz(DLookup("Pre_crsID", "Pre_course","Cid = 'C1'"),"")
b = (s = "")
If Not b Then b = (Nz(DLookup("grade","st_Grade","Cid='" & s & "'"),0) > 60)
If b Then
    DoCmd.RunSQL "insert into enrollment (Crs_id,Ehour , Eday ,room_No,sec_id,instructor_id,sid ,sem_no) select '" & Me!cid & "',Atime,Aday,Room_no,secid,instructor_id," & Me!sid & ",Sem_no FROM available_courses;" 
Else
    MsgBox "The Course has a Previous Course that you must take first!"
End If

 

[Nadia]

the first one Vila didn't work
i get: Compile Error argument not Optional ( @ !Sid)

and yes C1 i use it to test the Sql statement and forget to change it,

and about comparing two values , isn't right to Dim StrSQL as integer?

Now i'll test the other ones ..

 

[Nadia]

What are we checking for? Does it work? Are you getting errors? If so, what do they say?

No, i get red lines in the code

 

[VilaRestal]

and yes C1 i use it to test the Sql statement and forget to change it

Ok so I guess it should be:

Dim b As Boolean
Dim s As String
s = Nz(DLookup("Pre_crsID", "Pre_course","Cid = '" & Me!cid & "'"),"")
b = (s = "")
If Not b Then b = (Nz(DLookup("grade","st_Grade","Cid='" & s & "'"),0) > 60)
If b Then
    DoCmd.RunSQL "insert into enrollment (Crs_id,Ehour , Eday ,room_No,sec_id,instructor_id,sid ,sem_no) select '" & Me!cid & "',Atime,Aday,Room_no,secid,instructor_id," & Me!sid & ",Sem_no FROM available_courses;" 
Else
    MsgBox "The Course has a Previous Course that you must take first!"
End If

 

[Nadia]

i chose a course which it's Previous course is taken, but i get the message box

 

[VilaRestal]

Well at least it compiles now. It's just a matter of sorting out the logic behind it and I'm not sure what it should be.

Perhaps

If Not b Then b = (Nz(DLookup("grade","st_Grade","Cid='" & s & "'"),0) > 60)

should be

If Not b Then b = (Nz(DLookup("grade","st_Grade","Cid='" & Me!cid & "'"),0) > 60)

 

[Nadia]

You always find the solution!

- i got the same old problem about adding null values! will check the tables to find the reason of that , and hope that it will work well then

thanks Vila

 

[VilaRestal]

I'm happy to help.

I've got say, it looks a bit like this teacher of yours getting you to build him a database for his admin work. Everyone's a winner I suppose

 

[Nadia]

hhhhhhhhhhhhhhhhhhhh hope that my teacher won't come here!
he is my lecturer in university, and almost 30 student should do this project but using different applications, and no i don't think that he would sail all the projects

 

[Nadia]

and yes i guess so , Everyone's a winner

 

[Nadia]

Well at least it compiles now. It's just a matter of sorting out the logic behind it and I'm not sure what it should be.

Perhaps

If Not b Then b = (Nz(DLookup("grade","st_Grade","Cid='" & s & "'"),0) > 60)

should be

If Not b Then b = (Nz(DLookup("grade","st_Grade","Cid='" & Me!cid & "'"),0) > 60)

Now he want to check the grade of previous course, so it should be the first one but it still not working (get the message box)
and when trying the other one i still have the NULL values problem

 

[Nadia]

A string variable can't be null so there's no need to check for that. When declared it gets a value "". And you've assigned it a value so it won't be.

Not exactly null, i mean that sometimes a course doesn't have a previous course,
the idea is checking in Pre_course table to see if it has a previous course, if it hasn't, then register the course
if it has ,we have two options, 1. the student take the previous course and get a mark >= 60,then register the course
2. the student didn't take it, or take it but get mark <60
then Message Box " the course has a Previous Course that You must take first"