Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

find record code needs a 'null' record line

Posted on 2000-04-10
9
Medium Priority
?
286 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 240 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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