Results 1 to 9 of 9
  1. #1
    viper is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    26

    Post "No Record Found" Function

    I have a database that serves as a visitor check in and check out with ID badges. I have two tables one with visitor info with an visitor ID number that auto generates. The other is an event table to enter information when a visitor comes in. I have a lookup query to auto fill in the fields related to the visitor info table, after entering just the visitor ID number.



    What I want to do is, if some one enters a visitor ID number that does not exist in the system a mesg box will display stating the error then open a second form to enter the visitor info, thus generating a ID number for the visitor.

    I have some knowledge of VB in access but I’m un-sure on how to go about writing it up. Any help would be greatly appreciated.

  2. #2
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Are you familiar with recordsets? One method would be this....

    Set up a recordset with the table that holds your visitor IDs. Set a variable equal to your visitor ID text box value and use the rs.FindFirst method to look up the visitor ID. Use an IF statement something like...

    Code:
    IF rs.NoMatch then
         Msgbox "No ID Match.", vbOkOnly
    End If
    If you need more clarification on this let me know.

  3. #3
    viper is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    26
    Yes please any additional clarification would be great, I haven't experimented with recordssets yet. Would i need a 3rd table to perform the lookup alone?

  4. #4
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    No, you would set up a recordset as follows:

    Code:
    Dim db as DAO.Database
    Dim rs as DAO.Recordset
    Dim vInput
    
    set db = CurrentDb
    set rs = db.OpenRecordset("tablename", dbOpenDynaset)
    rs.movelast
    rs.movefirst
    
    vInput = TextBoxWithID.Value
    rs.FindFirst "[TableFieldNameWithID] = " & vInput
    
    If rs.NoMatch then
         MsgBox "ID Not Found.", vbOkOnly
    End If
    Let me know if this makes sense.

  5. #5
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    a fast way to know if the recordset is empty: then both the properties BOF (Begin Of File) and EOF (End of File) are together true.
    Example:

    Code:
    Dim rst as new ADODB.recordset
    
    rst.Open "select * from tblA", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
        With rst
            If .BOF And .EOF Then
                MsgBox "Empty recordset"
            Else
                'do whatever needed
                '..........
            End If
            .Close
        End With
    set rst = nothing
    success
    NG

  6. #6
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    If you are checking for an empty table (recordset) then you most definitely should use NoellaG's example. However, if you are going to have visitor IDs already in there you'll need to use something like my example.

  7. #7
    viper is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    26
    Quote Originally Posted by jgelpi16 View Post
    If you are checking for an empty table (recordset) then you most definitely should use NoellaG's example. However, if you are going to have visitor IDs already in there you'll need to use something like my example.
    Yes I will have an existing table that contains a list of visitors, so I would want to run a check that if the Visitor ID is not listed in that table it would return with an error mesg where i can redirect them to a different form to enter a new visitor informaiton.

    On the code that you listed jgelpi, where would I enter that code? would I place it in the "Beforeupdate" on the visitor ID feild on my entry form?

  8. #8
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    That depends. If I understand your form correctly your users are just selecting/searching for a visitor ID. They are not entering the data at this time (The text box where they are entering the visitor ID is unbound). IF that is the case I would do it either on the lost focus event of the text box or the click event of the button they click.

    IF they are actually updating a record when they enter that visitor ID (Your text box is bound to a field in a table) then you are correct you would want to do it on the before update event of the text box to keep them from updating the table with an invalid ID.

  9. #9
    viper is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    26
    Quote Originally Posted by jgelpi16 View Post
    That depends. If I understand your form correctly your users are just selecting/searching for a visitor ID. They are not entering the data at this time (The text box where they are entering the visitor ID is unbound). IF that is the case I would do it either on the lost focus event of the text box or the click event of the button they click.

    IF they are actually updating a record when they enter that visitor ID (Your text box is bound to a field in a table) then you are correct you would want to do it on the before update event of the text box to keep them from updating the table with an invalid ID.
    I set up the code in the Before Update in the text feild that contained the visitor ID on my entry form. It works perffictly thankyou for helping me on this. Below I am posting my final code for anyone that could benifit from this.

    Code:
    Private Sub JACS_Number_BeforeUpdate(Cancel As Integer)
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim vInput
     
        Set db = CurrentDb
        Set rs = db.OpenRecordset("PROVIS_Name_List", dbOpenDynaset)
        rs.MoveLast
        rs.MoveFirst
        vInput = JACS_Number.Value
        rs.FindFirst "[JACS_Number] = " & vInput
        If rs.NoMatch Then
        Me.Undo
        MsgBox "The JACS ID you entered does not exist in the system please exit this form and add this Visitor to the system", vbOKOnly
        End If
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access 2003 Date() Function returns "#Name?"
    By smartel@soprema.ca in forum Programming
    Replies: 5
    Last Post: 01-18-2012, 05:52 AM
  2. Replies: 3
    Last Post: 04-10-2010, 10:22 AM
  3. Replies: 4
    Last Post: 02-11-2010, 02:21 AM
  4. Creating "Edit Record" link in table column
    By joshearl in forum Forms
    Replies: 1
    Last Post: 12-25-2009, 11:17 AM
  5. Replies: 0
    Last Post: 11-03-2009, 11:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums