Results 1 to 6 of 6
  1. #1
    Nima4444 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    8

    Autocomplete combobox match any part of string not only beginning string


    Hi
    I have a form "contractor" and it has a subform.In subform I have a field named "ProductID_FK" which is the IDs of the products.But I've changed its row source to enter the name of products that are in the table "product" instead of their IDs.Now I want to search through the names of products but not just the begining part of the name.for example if one of the products is named "jeyhoon morvarid" I want to type "morvarid" and the combobox suggest "jeyhoon morvarid"
    Attached Files Attached Files

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    The persian text values referred to in english make it really difficult to assist you. The "Like" statement in the contractor form looks to be correct for what you are describing, but I am unable to test.

  3. #3
    Nima4444 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    8
    I hope I get it right.You mean the name of products makes it hard to assist?I've changed them and now I think it's easier for you to understand
    Attached Files Attached Files

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is in response to your PM. I am using the dB from your Post #3.

    Open table "J2" in design view.
    Click on the field "Contractor ID_FK". The Default Value is 0 (zero). Delete the zero.
    Click on the field "ProductID_FK". The Default Value is 0 (zero). Delete the zero.
    Close and save.

    Open the form "J2_Subform_Contractor" in design view.
    In the purple menu header, click on Design, then on "Add Existing Fields".
    Drag "Contractor ID_FK" to the detail section to add the field to the form.
    Double click on the control to open the Properties dialog box. Click on the "Other" tab. Set the "Tab Index" to 11.
    Save and close the form.

    Open the form "Contractor" in design view.
    Double click on the control "ContractorID_PK".
    In the properties dialog box, click on the FORMAT tab.
    In the VISIBLE property, set it to NO. (the control "ContractorID_PK" is an autonumber and should NEVER be visible on a form)
    Save and close the form.

    Open the table "J2". If you see a record with the "Contractor ID_FK" = 0, change the 0 to a 4.
    Close the table.

    Open the form "Contractor". Try adding a product.
    Change to s different contractor. Add products.
    Go back to the first contractor. Is the product now being displayed?



    "J2_Subform_Contractor" is a subform. The linking fields are "ContractorID_Pk" (master field) and "Contractor ID_FK" (child field).
    BUT YOU DID NOT HAVE A CONTROL BOUND TO "Contractor ID_FK" on the sub form. So there was no way to enter the "ContractorID_Pk" into the "Contractor ID_FK" field on the sub form.

    Because you are using the sub form in Datasheet view, you will have to find the "ContractorID_Pk" field (on the far right of the sub form) and move the right border to the left border to hid the field. You do NOT want this field visible!


    I never use datasheet view. Did you notice that the sub form "J2_Subform_Contractor" has green colors in design view, but no color (white) when displayed (when form "Contractor" is open)?

  5. #5
    Nima4444 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    8
    Thank u so much for mentioning that datasheet view doesn't work well.Yes I notices that the color doesn't change but I didn't know why.I changed it and now the combo box works well!Was it my problem that i didn't know working with datasheet view or it has generally some problems?

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think your problem is that you don't understand how a main form/sub form works.

    When you have a main form/sub form, you set the linking fields (master and child) in the sub form control (not the sub form source object).
    In your case, the Master Link field is "ContractorID_Pk" (main form) and the child Link field is "Contractor ID_FK" (the sub form).
    So there MUST be a control on the main form bound to the field "ContractorID_Pk". Likewise, on the sub form, there MUST be a control bound to the field "Contractor ID_FK". You DID NOT have a control on the sub form bound to the field "Contractor ID_FK".

    When you set the Master/Child link fields, Access knows that it is supposed to take the value of the Master Link field (the PK field) and insert it into the Child Link field on the sub form (the FK field). With no control for the FK field, where was Access supposed to put the PK value? Thus the record in the table "J2" was "lost" (not linked to the main form record).


    There is nothing wrong with using a form in datasheet view, but for me, there is less control on how the form is displayed (one of which is the background color).


    One other item.
    On EVERY code page, the top two lines should be
    Code:
    Option Compare Database
    Option Explicit
    Some of the code pages do not have the 2nd line.
    In the IDE, click on TOOLS/OPTIONS.
    Put a check for the property "Require Variable Declaration".
    This will add the line to NEW code modules.

    You have to go to existing code pages and manually add the line.

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

Similar Threads

  1. if statement for part of string
    By fishhead in forum Macros
    Replies: 4
    Last Post: 04-23-2015, 02:50 PM
  2. Extract part of string
    By Fais in forum Access
    Replies: 5
    Last Post: 08-06-2014, 04:46 PM
  3. Replies: 6
    Last Post: 09-10-2013, 08:37 AM
  4. Returning part of a string
    By dr4ke in forum Queries
    Replies: 7
    Last Post: 01-15-2013, 11:21 AM
  5. matching part of the string from two columns
    By hoachen in forum Queries
    Replies: 4
    Last Post: 12-20-2011, 01:54 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