Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156

    Continuous search form won't open selected record in view form.

    Hi, all!



    In my database, I'm using continuous forms to create search forms. I have a button that appears at the end of each row, and SHOULD allow the user to click and bring up the selected record in the full view form. However, when I click the button, one of two things happens:

    1) There is one record in particular that will always pull up, without fail. If I click that one, it goes right in.

    2) If I click ANY of the other records, it pulls the view form up as if to add a new record. The auto# primary key is even "(New)".

    I've tried things I found online, and this is what's in it currently:

    Code:
    Private Sub btnView_Click()
    On Error GoTo btnView_Click_Err
         'Open policy view form to correct record on click.
         'Declare variable.
        Dim strWhere As String
        
        strWhere = "[PolID] =" & Me.[ctlPolID]
        
        DoCmd.OpenForm "frmPolicy3rd", , , strWhere
        
    btnView_Click_Exit:
        Exit Sub
        
    btnView_Click_Err:
        MsgBox Error$
        Resume btnView_Click_Exit
    End Sub
    The "PolID" is an auto# primary key.

    Can anyone tell why it might be behaving this way?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    When you say search form, normally it would be a single view form with some field(s) to supply a search term and then a "search" button. On clicking the button, the record (s) meeting your criteria would be displayed on a new form , or subform, if nothing was found -then an info message for the user.

  3. #3
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    This is what I'm trying to do. (The odd column of numbers right after the policy numbers are the PolID; it's normally invisible, but I have it visible while I'm trying to figure this out.)

    Click image for larger version. 

Name:	Untitled.jpg 
Views:	14 
Size:	101.7 KB 
ID:	20110

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I wouldn't set it up that way, but i don't know your environment.

    Take a look at this approach and download the database to see how it works.

  5. #5
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    I do like that style, but with the way this is setup, at least for now, I think the style I'm using is probably my safest bet. It's just a matter of being able to open these in the viewing page that's giving me fits; everything else on it works perfectly.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Database Copy.zip
    Fortunately, it was mostly dummy data anyway. Not live until the end of this month. The one I posted an image of was frmPolSearch, but there are other search forms I haven't installed the buttons on yet.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I just looked at the database. I do not understand the relationships, but that could be just because of the large number of tables involved.
    I Looked at your form frmPolSearch

    I selected Cancelled in the combo upper right click filter and No records appear.
    I selected Expired in the combo upper right click filter and No records appear.
    I selected Active in the combo upper right click filter and 3 records appear.

    Can you describe how the form is suppose to work? A couple of examples of what your search term(s) is and what the result should be would be helpful.

  9. #9
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    It also may be because this is my very first database that I'm building as I'm learning. Sorry, it's probably a tangled web, compared to what you're used too.

    In the case of the policy status search, that's happening because I just don't have status codes on each policy record. Since they're just dummy records for testing, I didn't fill out all fields. There aren't any with Cancelled or Expired in tblPolicy. Sorry, I didn't even think about that!

  10. #10
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    It's meant for us to search by a policy number if we have that, or a client ID or name (name field can use a portion of the name, and find everything that includes user's entry), insurance company (carrier's) ID, date ranges for the effective and expiration dates of the policy, and all of a set status. Multiple criteria can be used at once, as well.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    That's ok.
    I just tried Policy Number Fil* and modified the code to allow for Like

    Code:
      'Text field example. Use quotes around the value in the string.
        If Not IsNull(Me.ctlPol) Then
            strWhere = strWhere & "([PolNum] Like """ & Me.ctlPol & """) AND "
        End If
    It allows me to see all records beginning "Fil".

    What exactly do you want the user to enter before the search? Do you want them to enter the full number or just the first few characters, or any characters anywhere in the string?
    You are using AND between fields that means records have to match ALL search.

  12. #12
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Exact match on policy numbers is ok; I allowed like for the client name field because that's something that one of us might remember part of ("Oh, it was First State Bank of something-or-other..."), but if one of the users is searching by policy number, they have the full policy number.

    So far, the searching is working exactly as I want. I didn't really hit a snag until I started trying to DO something with the results. That magnifying glass button out to the side is what I want users to be able to click to access that record in the policy view form, but I can't get it to work right. If you click it out next to the record for "Policy1", it works. If you click it next to any of the others, it opens the form as if to add a new record. I don't understand why it will work for the one, but no others.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    For your magnifier try this code (I just moved strWhere to be the filter--I think it works but test it!!!)
    Code:
    Dim strWhere As String
        
        strWhere = "[PolID] = " & Me.[ctlPolID]
        Debug.Print "Clicked Magnifying Glass on Policy id " & Me.[ctlPolID] & "  with where = " & strWhere
        DoCmd.OpenForm "frmPolicy3rd", , strWhere
    After a little more "testing" I'm not sure what is working. I seem to be getting the same record even though the where clause is showing a different Number????

    I don't know where you are getting the ClientId on the form-- You show 20, but if I go to the policy table looking for CltEntId 20 there is none?????

    PolId is an autonumber (numeric) no quotes required.

    No matter which magnifier I click it doesn't open to the proper record (PolID = 20) etc.
    If I add an event to dblclick the Policy, it constantly opens the first Policy record??
    I can set the PolID to open but it does not use that PolID???

    You should make use of autonumber PKs. You have many that are text -- not a show stopper but ..

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I downloaded your database and created a form called frmJEDPolicy. It is a single form view of some Policy info.

    I then used your frmPolSearch, and put a double click event on Policy number to open the new form using the PolID of the record dblclicked on.
    Here's the double click code
    Code:
    Private Sub ctlPolNum_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmJedPolicy", acNormal, , "Polid=" & Me.PolID
    End Sub
    It works as expected. I think you have some code in some event that is preventing your form (magnifier button) from using the PolID???
    Here's a jpg of what I have.
    Attached Thumbnails Attached Thumbnails policydblClick.jpg  

  15. #15
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    I see what you mean about getting the same record. The Immediate window shows the PolID I'm clicking on, but I keep getting the same record on the form. I'm not sure where that 20 is coming from either, though; I get 123, which is one of my test clients.

    I wonder what would be blocking the PolID from use. Could it be because it's a primary key that I added to the tables later, and had to splice into forms? The PK for policies WAS originally policy number, but then I found out that some of these policy numbers never change at all, so I could end up with a 2012-13, 2013-14, and 2014-15 and so on series of renewals for a policy all with the same number. I thought using the auto# would fix that.

    If I need to use the Policy Number to search by, I can write a longer where in a variable to look up the record with that number AND the dates, right?

    I have started to use more auto# PK's, but when I started this database, I just didn't know any better. I've literally been building this AS I'm reading the books. There are some things that do (at least for how it works in my brain) need to be text, like the Policy Sequence ID (PSqID); for certain policies, it has to be 3 digits, and I tack a "U" onto the end to distinguish policies we underwrite in-house (there will be special authority attached to that), so I was only able to do it via text.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 21
    Last Post: 08-05-2013, 06:23 AM
  2. Replies: 5
    Last Post: 06-19-2013, 08:01 AM
  3. Replies: 3
    Last Post: 09-20-2012, 11:09 AM
  4. Replies: 7
    Last Post: 05-01-2012, 11:43 AM
  5. Replies: 1
    Last Post: 03-31-2011, 12:18 PM

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