Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005

    Red face Unanswered: searching records

    can anybody help out here?

    i have put together a muti user database split front/back end.

    due to managers request, they have requested that i disable fields until a person presses a button to enable them to be edited (I then have written a lock flag to indicate that the record is being edited(i had to do this because 2 or more people were accessing a record - when i tried to use the lock edited record options within access - it seemed to lock more than 1 record at a time!).

    I had included a search button(using the wizard) which uses the find option from the menu but unfortantley because the controls are disabled the find option will not work.

    is there a way of searching a database (any field to be searched on) where the fields have been disabled?

    appreciate any help on the above!



  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    assuming you have numeric boxes box1, box2 and text boxes box3, box4

    dim strSQL as string
    strSQL = "SELECT * FROM tblBlah WHERE ((1=1)"
    if not isnull(box1) then strSQL = strSQL & " And (fld1 = " & box1 & ")"
    if not isnull(box2) then strSQL = strSQL & " And (fld2 = " & box2 & ")"
    if not isnull(box3) then strSQL = strSQL & " And (fld3 = '" & box3 & "')"
    if not isnull(box4) then strSQL = strSQL & " And (fld4 = '" & box4 & "')"
    strSQL = strSQL & ") ORDER BY fldX;

    SELECT * is sloppy as always
    1=1 fixes zero criteria and the excess-And problem
    the parenthesis are pedantic (but so am i)
    isnull() may not be an adequate test, consider also len(nz(fldX, ""))=0 or similar, perhaps in conjunction with isnumeric(), isdate() etc
    depending on your circumstances, you might want to block searches if zero criteria - simplest way is by len(strSQL)

    then you use that as .recordsource for a form or .rowsource for a list etc

    but remind me - how is the user entering his search criteria while the fields are locked?

    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2005
    thanks for your reply.
    for the user to search i had to enable the fields, the button is the same as ctrl-f option from the menu. The field that would be searched on the most is a notes field, for which the find next works fine, but only if it is enabled. what I need is a separate search field to be displayed.. the user types in what word they are searching for and the word is searched in the whole field and then moves onto the next record until they find the one they are looking for.

    is there a way of programming the ctrl f -so that records are searched but when the user finds the one she wants the field is then disabled with the focus moved elsewhere?

Posting Permissions

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