• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

find record code needs a 'null' record line

What code do I need to add to have a msgbox appear if there is no such control no in the underlying table
(When a control no [struserinput] is entered that doesn't exist - the form opens to the first record)

Public Sub FindControlNo()
'THIS FORM FINDS CONTROL NOS ON VARIOUS FORMS
    Dim struserinput As String
    'user input string
    On Error GoTo err_box
    struserinput = InputBox("Enter Control Number", "Find Case")
    DoCmd.GoToControl "Control No"
    'go to control no field
    DoCmd.FindRecord struserinput
    'search field with user input string
   
err_box:
    Select Case err
        Case 2142
            MsgBox ("Operation canceled")
            Exit Sub
        Case Else
            Exit Sub
    'error box
    End Select
End Sub
0
ksk
Asked:
ksk
1 Solution
 
berg1375Commented:
Add this:

Dim dbs as database
dim rst as recordset

set dbs = current db
set rst = dbs.openrecordset("YourTableName")

with rst
..movefirst
do while not .eof
   If struserinput = ![FieldSearchingInTable] then
msgbox "The record already exist.....blah blah"
    End if
..movenext
loop
end with

set rst = nothing
set dbs = nothing


This will search your control source and pop a msgbox if a match is made


HTH
berg
0
 
berg1375Commented:
For some reason it keeps putting ".." in front of my code, so just delete one of the periods.
0
 
RonaldBiemansCommented:

DoCmd.FindRecord struserinput

if me![Control No] <>  struserinput
   msgbox "not found"
end if
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
berg1375Commented:
Or you can do something simple like that!

:)
0
 
kskAuthor Commented:
I added this:

If "Control No" <> struserinput Then
        MsgBox ("No Such Control Number")
End If

the only problem is that the msgbox pops up no matter if the control no exists or not.

The me![Control No] part doesn't work

this code is beneath a button that opens a form with the control no
0
 
gcs001Commented:
Change the name of the control "Control No" (I take it it's probably a TextBox or Label) to something like txtControlNo.

Then you can test the struserinput value against this as in:
If txtControlNo.Text <> struserinput Then
   MsgBox ("No Such Control Number")
End If


Regards,
Grant.
0
 
BrianWrenCommented:
I'm making a lot of presumptions here, which you will have to correct if I'm wrong...

I'm presuming that:

 •    there is a field on a form called "Control No".  Seems an odd name, but that's how it appears from your question.

 •   this code is behind the form, so that the 'Me' reference will work;  if that's not true, you'll need to substitute the actual form.

 •   the InputBox() statement is returning a literal string that equals exactly what is to be found in the control named "Control No", and that that control contains a string.

Anyway, if all that holds, this should get the results that you want:

_______________________________________________________________________


Public Sub FindControlNo()

        ' This form finds control nos on various forms.

        Dim struserinput As String
        Dim r As RecordSet

        On Error GoTo err_box

        ' User input string.
        strUserInput = InputBox("Enter Control Number", "Find Case")
   
        Set r = Me!RecordsetClone
        r.FindFirst "[Control No] = '" & strUserInput  & "'"
        If r.NoMatch
                MsgBox "Record Not Found"
        Else
                Me.BookMark = r.BookMark
        End If

ResumePoint:
        Exit Sub

err_box:
        If Err.Number = 2142 Then _
                MsgBox ("Operation canceled")

        Resume ResumePoint

End Sub

__________________________________________________________________

    If "Control No" <> strUserInput Then

compares the literal characters 'C', 'o', 'n', 't', 'r', 'o', 'l', ' ', 'N', 'o' against the contents of the variable, 'strUserInput.'  If the user didn't type in "Control No", the test will always show that the 2 are not equal.  I think what was intended should have been:

     If <frmName>![Control No] <> strUserInput Then

'Me' only functions when it is in the module behind the form that contains the referenced control.  For instance, if a form has a control named 'text5', then 'Me!text5' only works in the module behind the form that has the control named 'text5';   you cannot put that code in a module in the Modules tab of the DB window.  

Your comment, "'THIS FORM FINDS CONTROL NOS ON VARIOUS FORMS," coupled with the use of 'Public' leads me to believe that you are trying to put the code into a regular module.

(I admit, I'm not really following the interface here...)

If I can help more, let me know.

Brian
0
 
kskAuthor Commented:
Adjusted points from 50 to 60
0
 
kskAuthor Commented:
thanks to everyone
I used:
If <frmName>![Control No] <> strUserInput Then
and formatted them for the individual forms
ksk
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now