Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unanswered: Search from Front end problem

    Search the forum for several hours and can't find any help. Hope someone out there can at least help me to form the correct question to ask in hope that I can get some help.

    My application have ONE fronend databases and TWO backend databases

    I create a search form. Like search by Change control form number,Title, description, outcome and etc. They are all good. Here is a sample of my select statement

    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE title Like '*" & LN & "*';")

    Well Change control form number, title, description and outcome are all REAL fields in my main table -- tblChangeControlFormDetails. Now, I have a field called OtherRequestor which is a link table called tblCobbCountyContact. Both tblChangeControlFormDetails and tblCobbCountyContact are in the SAME BackEnd database.

    When I use the this select statement to search for Mary for example

    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE OtherRequestor Like '*" & LN & "*';")

    It returned "NO Match"

    Well when I put the Requestor ID number (8 for Mary for example) in search string using which is in tblCobbCountyContact table, I have the correct return.

    The linked table in my FrontEnd database does show that Mary Jones and my REAL Backend database also show Mary Jones even the field is a Select statement from the tblCobbCountyContact.

    Here is a full example of my Title Search:

    **********************************************
    Private Sub cmdTitle_Click()
    Dim varInfo As Variant
    varInfo = ""
    Dim LN As Variant
    LN = InputBox("Enter Change Control Form Title:")
    Const ADS_SCOPE_SUBTREE = 3
    Dim strSQL As String

    'Set all record's print report field = false
    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = False;")

    'Find our all the Revise code{s} for this Change Control Form
    Dim dbRevise As Database
    Dim rsRevise As DAO.Recordset
    Set dbRevise = CurrentDb()

    'Open the tblChangeControlFormDetails table
    Set rsRevise = dbRevise.OpenRecordset("Select * FROM tblChangeControlFormDetails ")

    rsRevise.MoveFirst

    If Not rsRevise.EOF Then

    Do
    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE Title Like '*" & LN & "*';")

    rsRevise.MoveNext
    Loop While rsRevise.EOF = False
    End If

    rsRevise.Close

    'Check if there is any matching report

    strRecCount = DCount("*", "tblChangeControlFormDetails", "PrintReport = True")

    If strRecCount = 0 Then
    MsgBox ("no match")
    Exit Sub
    End If

    ' release the memory back to the system

    Set rsRevise = Nothing

    'Minimize the Search form
    DoCmd.Minimize

    Dim stDocName As String

    stDocName = "rptChangeControlForm"
    DoCmd.OpenReport stDocName, acPreview

    End Sub

    ************************************************

    I use this code for my OtherRequestor seach too. I replace all "Title" to "OtherRequestor"

    Thanks in advance
    Last edited by sweetmail; 04-28-08 at 17:33.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by sweetmail
    Here is a sample of my select statement

    CurrentDb.Execute ("UPDATE
    Are you sure about that?

    You appear to be looping through a recordset and undoing the update you applied previously; can you exlpain what you are trying to achieve as I am sure there is a better method.

    Lastly, is "LN" text? Just to be sure
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2007
    Posts
    148
    Hey Georgev, thanks for the reply.

    I decided to loop throught the linked table first (it is tblOtherContact in this case) in order to capture the corresponding ID for the input name before it passes the value into the main form (it is tblChangeControlFormDetails in this case). Here is the code and it is working. Kind of!!!

    ************************************************** ******************************

    Private Sub cmdOtherRequestor_Click()
    Dim varInfo As Variant
    varInfo = ""
    Dim LN As Variant
    LN = InputBox("Enter Change Control Form OtherRequestor:")
    Const ADS_SCOPE_SUBTREE = 3
    Dim NewLN As Integer

    'Do nothing if user provide no input

    If LN = "" Then
    Exit Sub
    End If

    'Set all record's print report field = false
    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = False;")

    'Find our all the Revise code{s} for this Change Control Form
    Dim dbRevise As Database
    Dim rsRevise As DAO.Recordset
    Set dbRevise = CurrentDb()

    'Open the tblChangeControlFormDetails table
    Set rsRevise = dbRevise.OpenRecordset("Select * FROM tblChangeControlFormDetails ")

    'open the tblOtherContact table
    Set rsotherrequestor = dbRevise.OpenRecordset("select * from tblOtherContact")

    rsotherrequestor.MoveFirst

    If Not rsotherrequestor.EOF Then
    Do
    If rsotherrequestor!LastName = LN Or rsotherrequestor!FirstName = LN Or rsotherrequestor!MiddleInitial = LN Then
    NewLN = rsotherrequestor!OtherContactID
    rsRevise.MoveFirst

    If Not rsRevise.EOF Then

    Do
    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE OtherRequestor Like '*" & NewLN & "*';")

    rsRevise.MoveNext
    Loop While rsRevise.EOF = False
    End If
    End If
    rsotherrequestor.MoveNext
    Loop While rsotherrequestor.EOF = False

    End If

    rsotherrequestor.Close

    rsRevise.Close

    'Check if there is any matching report

    strRecCount = DCount("*", "tblChangeControlFormDetails", "PrintReport = True")

    If strRecCount = 0 Then
    MsgBox ("no match")
    Exit Sub
    End If

    ' release the memory back to the system

    Set rsRevise = Nothing
    Set rsotherrequestor = Nothing

    'Minimize the Search form
    DoCmd.Minimize

    Dim stDocName As String

    stDocName = "rptChangeControlForm"
    DoCmd.OpenReport stDocName, acPreview

    End Sub

    *******************************************
    The only problem that I have is that the input string has to be a real name, like Mary. If you only type in ma, the search will not find anything as I only have mary in the database for the moment.

    I don't know how to make the If statement to check the "like" condition, any idea?

    Thanks
    Last edited by sweetmail; 04-29-08 at 14:25.

  4. #4
    Join Date
    Sep 2007
    Posts
    148
    OK, I think I have just solved my own problem. I changed the If statement as follow

    *********************************************

    If InStr(rsotherrequestor!LastName, LN) <> 0 Or InStr(rsotherrequestor!FirstName, LN) <> 0 Or InStr(rsotherrequestor!MiddleInitial, LN) <> 0 Then
    NewLN = rsotherrequestor!OtherContactID
    rsRevise.MoveFirst

    If Not rsRevise.EOF Then

    Do
    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE OtherRequestor Like '*" & NewLN & "*';")

    rsRevise.MoveNext
    Loop While rsRevise.EOF = False
    End If
    End If

    ********************************************

    Now, it allows me to search any partial strings now.
    Thanks

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I still don't understand your prolem and really, really dislike your method
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2007
    Posts
    148
    georgev,
    I am a self learner in access database and this is my very first accessdatabase project and I am sure my method is totally not the best.


    What I was trying to do is to have a command button in my search form. When user click that button, they are given a pop up box to input the data for searching.

    If the field that they search is a text field in my main table and the table is in my main BackupEnd database (I have 2 BackEnd databases), I have no problem searching. The problem arises when the field they search is in the table which is sitting in the second Backend Database.

    For example,

    Mary Lee has a recordID 8 in the table sitting in the second Backend Database. The field that hold Mary Lee in the table which is in the Main Backend Database is "8". When user input Mary, no match will be found. But when the user input "8", matches found. That is why I have to loop through the table in the second Backend database to get the corresponding number.

    Any better method is mostly welcomed.

    Thanks

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So here's the killer question; if you are searching, why on earth are you updating?
    George
    Home | Blog

  8. #8
    Join Date
    Sep 2007
    Posts
    148
    The result of the search is to produce a report. This report is base on a query and this query is based on the 'PrintReport' checkbox on the main table. If the record was choosen, the 'PrintReport' chechbox would be checked so that it will be one of the record(s) in the query. So that the proper report would be generated. Does that make sense.

    My report is based on a main table and the main table has serveral linked tables base on the primary key of that main table.

    The is why I need the update statement.

    Thanks

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    But presumably all the records you want can be identified by a set of rules (e.g. all peolpe whose names begin with 'G' who are less than 25 years old and have brown eyes); so in reality you could do away with all this complexity and looping and put a single query together.

    I appreciate your logic; heck I did the same thing a few years ago, but I think you are over-complicating something that ultimately has caused you more problems than you expected.

    Next time you consider this method, come back here and explain your requirements and ask for advice; I'm sure everyone would be happy to help
    George
    Home | Blog

Posting Permissions

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