Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2008
    Posts
    21

    Unanswered: Preventing New Record When Searched Item Is Not Found -Ms Access

    I have an unbound form "SearchForm" for search purposes with one text box "txtSearch" and one command button. This form is used to search the "Products" table and if search item is found it is displayed in the "ProductsParticulars" form which is bound to the Products table. Ususally the user fills in the product ID in the text box on SearchForm and then click the search button. I am using the following code for the search button.

    Dim strSearch As String
    strSearch = "([ID] = """& Me.txtSearch & """)"
    DoCmd.OpenForm "ProductsParticulars"
    Forms!ProductsParticulars.Filter = strSearch
    Forms!ProductsParticulars.FilterOn = True

    The thing is; this code works fine as long as the searched item is found, if no product with the matching ID, the form opens in add record mode. I do not like this because it adds empty records in my database.

    I wan a code which will prevent addition of new records when the searched item is not found, that is whenever access fails to get the searched record error message should be displayed. Anyone with the helping hand? Your assistance will be highly appreciated

  2. #2
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    for solving the add new records you should set your form Allow Additions to No even by code or in design view.
    if you want to have an error mesage when the form has no record use the following line in your form open event:
    Code:
    If Me.Form.Recordset.RecordCount = 0 Then
    'your error mesage or do whatever you need
    End If
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I suggest a DLookup to check whether the search item exists or not prior to opening your form.

    Alternatively there should be a form property that you can set to disable new records; but without a copy of access handy I can't tell you!

    But of course, my favourite method, unbound forms! (but this is high level stuff, hence the two previous suggestions first )
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    AllowAdditions! That's the badger
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    I talk about the second form which display the search resault and as Kass mentioned is bound form
    Last edited by Aran1; 05-26-08 at 07:25.
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  6. #6
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57

    Prevent records from being entered after search

    Another thing you could if you didn’t want the form to even be visible if no records are found, is to hide the form when you first open it. If the record count = 0 then just close the form. If the record count is > than 0 then just show the form. This way the user only has to click the ok button on a message box.

    Here is the code
    Code:
    Dim Frm As Form
    Dim strSearch As String
    strSearch = "([ID] = """ & Me.txtSearch & """)"
    DoCmd.OpenForm "ProductsParticulars", , , strSearch, , acHidden
    Set Frm = Forms("ProductsParticulars")
    If Frm.Recordset.recourdcount >  0 Then
        Frm.Visible = True
        Else
        MsgBox ("Record not found")
        DoCmd.Close acForm, "ProductsParticulars"
    End If

  7. #7
    Join Date
    Apr 2008
    Posts
    21
    Where do I place the code

    If Me.Form.Recordset.RecordCount = 0 Then
    'your error mesage or do whatever you need
    End If

    and Form property Forms!ProductsParticulars.AllowAdditions = False

    Is it just after "DoCmd.OpenForm "ProductsParticulars"" or after "Forms!ProductsParticulars.FilterOn = True"?

    Thanks in advance.

  8. #8
    Join Date
    Apr 2008
    Posts
    21
    Thanks gem1204 the code you provided solved my problem. Regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •