Error in Input Box

J

Jez

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
 
S

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
 
J

Jez

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?
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top