Solved

find record code needs a 'null' record line

Posted on 2000-04-10
9
265 Views
Last Modified: 2012-05-04
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
Comment
Question by:ksk
9 Comments
 
LVL 4

Expert Comment

by:berg1375
ID: 2701529
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
 
LVL 4

Expert Comment

by:berg1375
ID: 2701531
For some reason it keeps putting ".." in front of my code, so just delete one of the periods.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 2701549

DoCmd.FindRecord struserinput

if me![Control No] <>  struserinput
   msgbox "not found"
end if
0
 
LVL 4

Expert Comment

by:berg1375
ID: 2701558
Or you can do something simple like that!

:)
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:ksk
ID: 2701988
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
 
LVL 4

Expert Comment

by:gcs001
ID: 2702357
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
 
LVL 9

Accepted Solution

by:
BrianWren earned 60 total points
ID: 2702367
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
 

Author Comment

by:ksk
ID: 2704551
Adjusted points from 50 to 60
0
 

Author Comment

by:ksk
ID: 2704552
thanks to everyone
I used:
If <frmName>![Control No] <> strUserInput Then
and formatted them for the individual forms
ksk
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now