Error in Input Box

[Guest]

Hi, If I have an Input box with this code below in, what I want to know is
that if the value that is input into this box is not in my lookup table how
can I put a message saying that this person is not in the database?

Jez

Private Sub btnCreateNewInput_Click()
Dim rs As DAO.Recordset
Dim NewID As Long
varInput = InputBox("Enter the RPSGB Reg No", "Add new Data")
If varInput = "" Then Exit Sub
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblData WHERE
False")
rs.AddNew
NewID = rs.Fields![FormNumber]
rs.Fields![RPSGBRegNo] = varInput
rs.Update
rs.Close
Dim sQRY As String
sQRY = "SELECT tblData.* FROM tblData WHERE tblData.FormNumber =
" & NewID
Set rs = CurrentDb.OpenRecordset(sQRY)
rs.Close
Set rs = Nothing
Me.RecordSource = sQRY
Me.txtDummy.SetFocus
Me.Requery
End Sub

 

[Scott McDaniel]

Hi, If I have an Input box with this code below in, what I want to know is
that if the value that is input into this box is not in my lookup table how
can I put a message saying that this person is not in the database?

Jez

Assuming that your lookup table is tblData, and that varInput would be the person you're looking for:

'/Dlookup returns a Null, so we use the Nz function to convert it to a zero length string
If Nz(DLookup("FormNumber","tblData","NameField='" & varInput & "'"),"") ="" Then
Msgbox varInput & " is not in this database."
Exit Sub
End If

You'd need to change the name of your table and columns to match those in your application. I'd do this immediately
after your "If varInput="" Then Exit Sub" line.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Guest]

Scott, Thanks for the reply, there is one bit I dont understand...

" You'd need to change the name of your table and columns to match those in
your application. I'd do this immediately
after your "If varInput="" Then Exit Sub" line." How do you mean?

 

[Scott McDaniel]

Scott, Thanks for the reply, there is one bit I dont understand...

" You'd need to change the name of your table and columns to match those in
your application. I'd do this immediately
after your "If varInput="" Then Exit Sub" line." How do you mean?

If you're referring to my comment about your table and columns, then I simply meant that the names I used for your
objects - "FormNumber", "tblData", "NameField" - are probably NOT the same as those in your application, so you'd have
to review the code and make the necessary changes

If you're referring to the comment about putting this immediately after the "If varInput ..." line, then what I mean is
to enter the code block I suggested AFTER that line in your code ... so the code would first determine if varInput has a
value ... if not, the code would exit, but if so, then it would run through the DLookup to determine if varInput is
already in the database.

For more info on DLookup and the syntax used, check online help or check here: http://support.microsoft.com/kb/208786

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com