Solved

find record code needs a 'null' record line

Posted on 2000-04-10
9
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 4

Expert Comment

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

:)
0
 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

710 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