Getting a blank screen...

[willhyde]

I have a form frmLostID on which there is a single text box, txtLostID. A query qryLostID searches a table for the value typed into txtLostID and returns some related fields.

The AfterUpdate function of txtLostID opens another form, frmLostIDShow which displays the value of txtLostID, the other fields returned by qryLostID, and a button to go back to a main menu form.

This all works fine until the user puts enters a value into txtLostID which doesn't exist in the table used by qryLostID. frmLostIDShow still opens but is totally blank, not even the button shows. I need a message saying the value does not exist and a way of getting back to the main menu. Any guesses?

 

[nickjar2]

The way I would do it as follows:

When the user clicks the button to run the query, add the query to the code behind the button (as SQL) and then check for the number of records returned. If no records are returned to can inform the user and NOT open the next form.

set rs = db.openrecordset("select * from table where field = " & txtfield)
rs.movelast
rs.movefirst
if rs.recordcount <1 then
magbox &quot;No records match etc etc&quot;

Nick

 

[willhyde]

The table is called tblMain, the field is CatalogueID, so now I have

Private Sub txtLostID_AfterUpdate()
Set rs = db.OpenRecordset(&quot;select * from tblMain where CatalogueID = &quot;txtLostID&quot
rs.MoveLast
rs.MoveFirst
If rs.RecordCount < 1 Then
MsgBox &quot;No records match etc etc&quot;
End If

DoCmd.OpenForm &quot;frmLostIDShow&quot;

End Sub

as the AfterUpdate of txtLostID, but I'm getting an error in the code editor

Compile Error:
Expected: list separator or )

 

[nickjar2]

replace the =&quot;txtlostid&quot; with :

= &quot; & txtlostid

and also include an exit sub ie:

If rs.RecordCount < 1 Then
MsgBox &quot;No records match etc etc&quot;
exit sub
End If

 

[willhyde]

Ok, it was happy with the code but when i run the form with an incorrect value, i get a 'variable not defined' error, and it takes me back to the code editor and highlights rs. Ddo I need a 'Dim rs As...' in there?

 

[nickjar2]

here is the full code. Sorry, when I first posted it, it was meant as example but here goes.

dim db as database
dim rs as dao.recordset

set db = currentdb
set rs = db.openrecordset(&quot;select * from Tblmain where catalogieid = &quot; & txtLostId)

PS. if txtlostid holds a string value as opposed to a numeric use this instead

set rs = db.openrecordset(&quot;select * from Tblmain where catalogieid = '&quot; & txtLostId & &quot;'&quot

If rs.RecordCount < 1 Then
MsgBox &quot;No records match etc etc&quot;
exit sub
End If

docmd.openform &quot;frmLostIDShow&quot;

Nick

 

[willhyde]

Perfect, thank you very much (again!)