Solved

find record code needs a 'null' record line

Posted on 2000-04-10
9
272 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Suggested Solutions

Title # Comments Views Activity
DateAdd every year from Jun 3 24
Sum in Split Form 17 27
ERROR 3113 MODULEID NOT UPDATABLE 8 18
MS Access How can I utilize 2 list boxes at the same time? 2 22
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

861 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