no data in query => no data in form

[chrisbirley]

Hi I hope someone can help me!!

I am creating a small database to aid the company I work for in collating student’s information for the broadband service that we offer.

I have created a students table with their details and a room number. I have created a history table that stores this data minus the room no, but includes the end of the academic year. A student might do more than one year in the place of residence.

My problem is checking to see if a room no has been taken.

I type in the room no and get the query to check this against the data in the students table. If the value is matched then fine - it returns a message saying that room is not free please choose again. The problem is that if the room has not been taken, then I get no response from the query - i.e. not even a box for a record - and I cannot get my code to read this and return with the returning student in the room.

If you need any further details then do not hesitate to ask.

Thanks in advance

Chris

 

[lupins46]

You need to post your code.

 

[chrisbirley]

the SQL is as follows, (stored as a query):

SELECT Students.Room
FROM Students
WHERE (((Students.Room)=[forms]![frmReturning]![txtRoom]));


the code is:

Private Sub cmdAddStudent_Click()

DoCmd.OpenForm "frmCheckRoom"
If Forms!frmcheckroom!Room = "" Then
DoCmd.SetWarnings False
DoCmd.Close acForm, "frmCheckRoom"
DoCmd.OpenQuery "apndqryreturningstudents"
MsgBox ("This may take a while whilst the student's information is being transfered across.")
DoCmd.SetWarnings True
ElseIf Forms!frmcheckroom!Room = Forms!frmReturning!txtRoom Then
MsgBox "Sorry room is not available. Please make another choice."
DoCmd.Close acForm, "frmcheckroom"
End If

End Sub

I hope that this makes a little more sense, again if any more info is needed to solve the problem, then just post back

thanks in advance

Chris

 

[lupins46]

My guess is that this is failing :

If Forms!frmcheckroom!Room = "" Then

I suspect that the returned value is null, not "".

try:
If nz(Forms!frmcheckroom!Room) = "" Then

 

[chrisbirley]

have tried that and now have the error message:

Run-time error '-2147352567 (80020009)':
You entered an expression that has no value

I have tried to use both "" and is null statements but neither work.

thanks in advance

chris