Results 1 to 7 of 7
  1. #1
    grant.smalley is offline Novice
    Windows Vista Access 2000
    Join Date
    Jan 2010
    Posts
    8

    Create combo search form in subform

    Hi,

    i have a main form called MainForm with FraudRef as the primary key. On the form I have a sub form called PolicyForm that is linked by FraudRef. On the subform there may be more than one record linked to the FraudRef and the primary key on the subform is PolicyNumber.



    I would like to create a combo search field in the header of the subform to find a record based on PolicyNumber. I would also like the combo field to contain PolicyNumber, CustomerName, 1stLineofAdress and Postcode when searching.

    Any thoughts on how I can acheive this?

    kind regards,

    Grant

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931

    Combo Search

    Let me get it straight you have a main table with a primary key FraudRef which is linkked to a Slave Table with a Primary key as policy number. Now you want to create a Combobox on the main form data source of the same will be your Slave table and you want to filtre the main form using the policy number (which is a field in the subform).

    If this what you want here is my solution:

    create a combo box on the main form. select data source Slave table and select the fields that you want to display. Now make sure that you make the following changes to the property of the combobox:
    Name: cboSearchMain
    The Combo must be bound to the PolicyNumber field. How to do this make PolicyNumber The first Field in the combo query.

    Now on the UpdateEvent Type the following VBA code:

    Private Sub cboSearchMain_AfterUpdate()
    strSQL = "SELECT DISTINCTROW Main.* FROM Main " & _
    "INNER JOIN Slave ON " & _
    "Main.FraudRef = Slave.Link_id " & _
    "WHERE Slave.PolicyNumber = " & Me.cboSearchMain & ";"
    Me.RecordSource = strSQL
    End Sub

    The lines highlighted are there by default so make sure you don't copy them again.


    Let me explain what actually happens here. We are basically changing the datasource of the main form using SQL query. The query basically Selects all the data on the main table which has a corresponding link in the slave table and the criteria is that the policy number on the slave table be = to the number selected using the combo box.

    if this solves your problem please mark this thread solved.

  3. #3
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    I am just curious were you able to solve your problem if yes plase mark this thread solved.

  4. #4
    grant.smalley is offline Novice
    Windows Vista Access 2000
    Join Date
    Jan 2010
    Posts
    8
    Hi,

    Sorry, I don't think I made my request clear.

    To confirm, I would like to add a combo box on the subform to be able to filter the subform. The problem I am currently having is that the combo on the subform displays all records rather than just the records associated with the existing fraud Ref (Primary key).

    Any ideas??

    Grant

  5. #5
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931

    Solution

    Sorry friend my mistake

    Here is what I would like you to do.

    Create a combobox on the header of your subform. The name of which as before is cboSearchup. Now in its After event type the following code. Refer to my previous post regarding the data source of the cboSearchup Combobox

    strSQL = "SELECT DISTINCTROW Slave.* FROM Slave " & _
    "WHERE Slave.PolicyNumber = " & Me.cboSearhcup & ";"
    Me.RecordSource = strSQL

    Result the subform is filtred showing all entries with the policy number that you select Linked to your fraudref ID in your main form. Provided you have linked your subfrom to the main form (fraudref ID on the main form with the link_id in the subform).

    I think you should have figured this out yourself anyways. U r online I see try this out and tell me if this solves your problem

  6. #6
    grant.smalley is offline Novice
    Windows Vista Access 2000
    Join Date
    Jan 2010
    Posts
    8
    Hi,

    I can get the combo form to find a record on the subform, but the problem still remains that it contains all records, not just the ones associated with the specific fraud ref.

    Is there any way to filter the combo field before update to just show the records associated with the fraud ref??

    I am a novice at Access as you have probably gathered by now!

    Kind regards,

    Grant

  7. #7
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931

    Link_tables

    I anticipated that you will have a problem similar to this and thus I had included this following statement

    Result the subform is filtred showing all entries with the policy number that you select Linked to your fraudref ID in your main form. Provided you have linked your subfrom to the main form (fraudref ID on the main form with the link_id in the subform).

    Now here are some questions that I would like you to answer:

    1. Have you linked your subform to the main form.
    2. if Yes Is the Parent Key in the main Form FraudRef

    Now let me explain how the set up works. Suppose I have a Main Table with Primary Key FraudRef (Auto number) and it is linked to a Slave table Field called Link_id (which is a number Field). The Relation set up is One to Many Relationship which means that you can have one or more than one entries in the Slave Table related to an entry in the Main table linked to the Field FraudRef.


    Now I create a Form with Main table as its record source. Then I create a Slave form with the Table Slave as the Record Source. Then I open the main form and insert a subform from the Toolbox. The subform wizard starts and you select the Slave from. Then the wizard prompts to select the links. DON'T opt for automatic selection link the FraudRef field in the main form with The Link_id of the subform.

    Now Suppose you make the following entry in Main Form and you have a FraudRef 123 you make 6 entries realted to the FraudRef id in your subfrom and the Policy number for each is 568,789,456,453,856,366.

    What should have been the result that you should have got with the second solution I had written assuming you had done all this:

    At any time when you open the form for FradRef 123 the
    568,789,456,453,856,366 wil be displayed in the subfrom and not all the entries in the slave table.

    When the FraudRef is 123 in the main form, selecting 568 in the combobox on the subform will only show 568 and not the other numbers in the subform because the Form relationship will filter only the data related to the FraudRef in the mainform i.e.
    568,789,456,453,856,366 and the SQL in that we have used in the combobox will further filter 568.

    Now you have to understand that the record source of the cboSearchUp is the Field PolicyNumber in the slave table. Thus it will show all the policy numbers that are entered there. e.g. suppose you have made another entry for FradRef 124 PolicyNumbers in the Subform is 989, 787,666. So the cboSearchUp will show all the ppolicy numbers always:
    568,789,456,453,856,366,989, 787,666.

    Suppose you open the form FradRef id 123 and in the subform you will have the following policynumbers 568,789,456,453,856,366. You Go to next record FradRef 124 Subform will display following policynumbers 989, 787,666. (If you want this to happen link your forms). Now if the FraudRef is 123 you select 568 in your cboSearchUp only 586 is displayed, if you select 666 no entries in the subform will be displayed as this policy number is related to FradRef 124 and not to 123.

    Just Link your subform to main form as far as I understand your problem you won't need this combobox trick after that.

    Please mark the thread to be solved if this post helps you if it does't write to me at silverback_bats@yahoo.co.in






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

Similar Threads

  1. Replies: 4
    Last Post: 01-11-2010, 11:41 PM
  2. How do I create a name search ?
    By nightviperdark in forum Access
    Replies: 1
    Last Post: 11-23-2009, 09:53 AM
  3. Replies: 1
    Last Post: 08-03-2009, 08:24 AM
  4. Replies: 1
    Last Post: 03-02-2009, 11:54 AM
  5. Replies: 0
    Last Post: 08-17-2008, 12:19 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