Results 1 to 5 of 5
  1. #1
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318

    Filter vs Find ?

    Reading other posts I've got bits and pieces of what I want to do but can't figure out the easiest most user friendly way to do it.

    I have a primary field named blgd#. When a user is entering data the building number may already be listed in which case they would get the "change not successful because it would create a duplicate" message. They will have no idea what that means.

    So I'm not sure if I could


    • put a filter on the form so as they type the building number in the field the record will come up in a list, (I think would be most user friendly but by other posts it sound very complicated to do)
    • or have a pop up form where they enter the building number which runs a query that would look to see if the number exists, (I would think with this you would have to have an if statement so if there is no record it opens a blank but if there is it opens that record)
    • or when there is a duplicate have the Find option pop up,
    • or have Find pop up when the form is open.
    There would be too many records to have them scroll through the whole list to see if the building number exists already.

    Any suggestions on what would be best or if there is a better way to do it?

  2. #2
    dsmacs is offline Advanced Beginner
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2009
    Location
    Perth Western Australia
    Posts
    52
    If you include a combo box with the building numbers as the underlying query, as the building number is entered it will narrow down to the number. If the number is not in the list you can use the NotinList event to advise the user "No such building number, do you want to add Y/N?".

    Private Sub CboBuildNo_NotInList(NewData As String, Response As Integer)

    Dim Db As DAO.Database
    Dim Rs As DAO.Recordset
    Dim Msg As String

    On Error GoTo Err_BuildID_NotInList
    ' Exit this subroutine if the combo box was cleared.

    If NewData = "" Then Exit Sub

    ' Confirm that the user wants to add the new building.
    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
    ' If the user chose not to add a message, set the Response argument to suppress an error message and undo changes.
    Response = acDataErrContinue
    ' Display a customized message.
    MsgBox "Please try again."
    Else
    ' If the user chose to add a new Building, open a recordset using the Building table.

    Set Db = CurrentDb
    Set Rs = Db.OpenRecordset("tblBuilding", dbOpenDynaset)

    ' Create a new record.
    Rs.AddNew
    ' Assign the NewData argument to the OutletMessage field.
    Rs![blgd#] = NewData
    ' Save the record.
    Rs.Update
    ' Set Response argument to indicate that new data is being added.
    Response = acDataErrAdded

    End If


    Exit_BuildID_NotInList:
    Exit Sub
    Err_BuildID_NotInList:

    ' An unexpected error occurred, display the normal error message.
    MsgBox Err.Description
    ' Set the Response argument to suppress an error message and undo
    ' changes.
    Response = acDataErrContinue
    End Sub



    You then would open a Building Form at the Desired new record and have the user enter additional details required about the building?



    Hope this helps,
    Cheers
    Darren

  3. #3
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    This seems to be what I would need, however I've never worked with code before only formulas and micros.

    If there is a similar way to accomplish this without code can you direct me. If not I will see if I can learn how to do this.

    Thanks so much.

  4. #4
    dsmacs is offline Advanced Beginner
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2009
    Location
    Perth Western Australia
    Posts
    52
    Email me a sample of your DB with some dummy data and I'll place the code in and provide info on what the code is doing?

    d [dot] mcdonald @ ozemail [dot] com [dot] au

  5. #5
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    I have attached the dummy database you requested. Let me know if you need any addtional information.

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

Similar Threads

  1. cant find the object
    By AccessLes in forum Forms
    Replies: 1
    Last Post: 06-30-2010, 12:41 PM
  2. Looping through a tbl to find a certain value
    By cwf in forum Programming
    Replies: 1
    Last Post: 05-17-2010, 04:02 PM
  3. can't find language DLL
    By tequila in forum Access
    Replies: 0
    Last Post: 05-01-2008, 09:31 PM
  4. How do I find the serial value of Now()?
    By JoeG in forum Queries
    Replies: 0
    Last Post: 03-13-2007, 08:16 AM
  5. How to Find a Record
    By lschlitt in forum Programming
    Replies: 2
    Last Post: 09-09-2006, 06:24 PM

Tags for this Thread

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