Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Oct 2011
    Posts
    56

    Red face Unanswered: want a field to autocomplete based on a record being entered?

    hi thanks for viewing, i really need some help

    ok, im creating a stock control database, so far, i have several tables and forms, and its looking very good, however i need to start implemting features which make it easy and quick to use, so.....

    i have a table called stock control:
    ID (primary key) Collection Number, Date Collected, Postcode, Town, borough, Catergory, Item

    and another table called Postcodes and Boroughs:
    ID (primary Key) Postcodes, Towns, Boroughs
    1.........SE18.........WOOLWICH............GREENWI CH

    now what i need to do, is when im on the stock control table/form, and i go to the post code field and type in "SE18" the remaining "town" and "borough" fields will automatically update. regardless of whether i type in "SE18 7NN" or "SE18 5QP" the town is WOOLWICH and the borough is GREENWICH, i have 120 postcodes, towns and boroughs.. and need them all to autocomplete, whats the easiest way to implement this please?


    Also, same sort of problem,

    when i type in "C" in catergory, i want the items dropdown box to only display items beggining with "C", is that possible?

    if you can help, then you are a legend in my books and ill be forever grateful.. thanks!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could try this:

    1. Add a ComboBox (what you call "dropdown box") to the form with the following properties (unmentionned properties keep their default values):

    Format Tab:
    -----------
    Column Count: 3
    Column Width: 3cm;3cm;3cm (can be in inches, adjust as needed)
    List Width: 9cm (see above)

    Data Tab:
    ---------
    Row Source Type: Table/Query
    Bound Column: 1
    Limit To List: Yes
    Auto Expand: Yes
    Row Source: (adapt to the actual names in your database)
    Code:
    SELECT PostCodes_And_Boroughs.PostCode, PostCodes_And_Boroughs.Town, PostCodes_And_Boroughs.Borough FROM PostCodes_And_Boroughs ORDER BY PostCodes_And_Boroughs.PostCode;
    Event Tab:
    ----------
    After Update: [Event Procedure]
    On Enter: [Event Procedure]

    Other Tab:
    ----------
    Name: Combo_PostCodes

    In the Form Module, add these procedures (adapt to the actual names in your database):
    Code:
    Private Sub Combo_PostCodes_AfterUpdate()
    
        Me.Text_PostCode = Me.Combo_PostCodes.Column(0)
        Me.Text_Town = Me.Combo_PostCodes.Column(1)
        Me.Text_Borough = Me.Combo_PostCodes.Column(2)
        
    End Sub
    
    Private Sub Combo_PostCodes_Enter()
    
        Me.Combo_PostCodes.Value = Null
        Me.Combo_PostCodes.Dropdown
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    56

    Unhappy Hey thanks for your reply,

    cant tell you how grateful i am that somebody replied with useful information unlike on other forums, however im having a few issues with the info you told me..? maybe im not writing the code right? because im a noob.. lol, maybe screenshots would help?

    Heres my form
    Imageshack - formv.jpg

    the postcodes table
    Imageshack - postcodes.jpg

    and the stock table
    Imageshack - stockm.jpg



    whe i type SE18 into postcode, i want the town and the borough fields to complete automatically? is there anyway i can do that without complication? if there isnt and i do need to write code, can you write it as if you were talking to a retard? so that i could understand.. :-) thanks bro! really appreciate ur help..
    Last edited by jackjsmith88; 10-31-11 at 20:15.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    jackjsmith88,

    Re-read what Sinndho wrote because he gave you exactly what you will need to accomplish what you want.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Oct 2011
    Posts
    56

    i cant understand it? sorry for being a retard

    i dont mean to retarded but i dont understand it? i get everything but as soon as i get to the code part, i cant make sense of what is written?

    so, to access the forms module i just go into visual basic, i copy and past the code, but which parts do i need to adapt? and also, whats me.text_postcode mean? whats the me mean? does an underscore represent a "space"? im really sorry,

    i have no experience in programming what soever..

    thank you for your pateince

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    "me" is the the form on which the code resides.

    The underscore doesn't represent anything--it's just a way to separate the words in the name of the control without using a blank space (which would have caused other problems).

    So, me.text_postcode refers to the text_postcode control on the form on which the code has been placed.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Oct 2011
    Posts
    56

    changed something and now get a different error...

    the record source '~sq_cProgramTest~sq~sq_Combo_postcodes~ does not exist.. so what havent i done right? lmfao.. am so sorry, yous must be sick of me...



    Btw ken thank you for taking the time to explain stuff to me, appreciated mate.. but i didnt get it? lmfao!

    "Me" is where the code resides? like where the codevlives/belongs? im sorry for being a retard, blame my parents? haha

    your a good man sharing your knowledge tho! your both legends in my book :-)


    Just clicked! understood! so, do i need to change the "me" to the name of my form? "programtest"?
    Last edited by jackjsmith88; 11-01-11 at 13:57.

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    if your form is named FORM1, and you have a control on your form named CONTROL1, then you can refer to that control like this:

    forms!FORM1!CONTROL1

    or, you can refer to that control as:

    me!CONTROL1

    "me" is a shortcut referring to the form on-which the control resides.

    BTW, the capitalization of the names has no significance.

    So, forms!FORM1!CONTROL1 is equal to FORMS!form1!control1 which is equal to FoRmS!FoRm1!CoNtRoL1.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Oct 2011
    Posts
    56

    ok, i still cant figure this out and its killing me,

    i still cant get it right!!! see the error message.

    the record source '~sq_cProgramTest~sq~sq_Combo_postcodes~ does not exist.. (may of been mispelled blah blah blah)

    the screenshots,

    Form
    Imageshack - formv.jpg

    Postcodes Table
    Imageshack - postcodes.jpg

    and the stock table
    Imageshack - stockm.jpg


    ive copied the code.. Me.text.PostCode ETC ETC into the VB thing.. and it still wont work :-( do you know why please?

    thanks mate..

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't see any combobox for the post code (Combo_PostCodes in my example) on your form. The code cannot work without it.

    As far as the part you need to adapt is concerned, in my example the table name is "PostCodes_And_Boroughs" and its columns are "PostCode", "Town", "Borough", while the TextBox controls on the form are named "Text_PostCode", "Text_Town" and "Text_Borough" (plus the combobox I mentionned above). If these objects have different names in your database, you must change the code and use the proper names of the objects in your database instead of the names I used.
    Have a nice day!

  11. #11
    Join Date
    Oct 2011
    Posts
    56

    welcome back siinndho

    hey man, i took them screenshots before your 1st reply... and it does now have a combobox..? still cant get this to work, tried renaming the row sources etc still wont work,

    ive hosted the database on a website, maybe you could take a look for me?

    http://www.freefilehosting.net/mydatabase

    i would appreaciate this so much.. its doing my brain in...

    if you do thanks mate, also, could you tell me what i did wrong/didnt do? so i can learn from this and never have to hassle your poor and very kind soul again? lol cheers man

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I cannot succeed in downloading your file from the site you mentionned. Please upload a zipped version here.
    Have a nice day!

  13. #13
    Join Date
    Oct 2011
    Posts
    56

    attempted upload

    lol, i didnt know i could do that! thanks for pointin that out

    My database.zip

    hope this works

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. You named the combobox "Combo PostCodes" (with a space) and the table name is "PostCodes and Boroughs" (also with spaces) so the RowSource property of the combobox becomes (notice the square brackets):
    Code:
    SELECT [PostCodes and Boroughs].Postcode, [PostCodes and Boroughs].Town, [PostCodes and Boroughs].Borough FROM [PostCodes and Boroughs];
    2.Two textboxes on the form are named "Town" and "Borough" (they were "Text_Town" and "Text_Borough" in my example) but I don't see any that should dislay the postcode. I guess you can go without as the postcode will be displayed in the combobox anyway.

    3. When adapted to the names of the objects in your database, the code becomes:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Combo_Postcodes_AfterUpdate()
    
        Me.Postcode = Me.[Combo Postcodes].Column(0)
        Me.Town = Me.[Combo Postcodes].Column(1)
        Me.Borough = Me.[Combo Postcodes].Column(2)
        
    End Sub
    
    Private Sub Combo_Postcodes_Enter()
    
        Me.[Combo Postcodes].Value = Null
        Me.[Combo Postcodes].Dropdown
        
    End Sub
    You'll also have problems with the definition of some tables which is incoherent: In the table "Stock Tracker" you defined the column "Borough" as Text(12) while in the table "PostCodes and Boroughs" you can have longer values such as "Kensington And Chelsea", "Hammersmith and Fulham", "Richmond Upon Thames", etc. This will cause an error if one of the corresponding postcode is selected.

    In general, you should avoid using spaces or other non-alphanumeric characters in the names of the objects.

    When such characters are used, this forces Access (and you) to perform some kind of translation/substitution process: Notice how "Combo PostCodes" becomes "[Combo Postcodes]" inside the procedures while "Combo_Postcodes" must be used in their declarations (square brackets are not allowed there).

    You end up with an object that has 3 different names, according to the place where it is referenced, which can be quite confusing.

    On the same path of thoughts, you should also stay clear from reserved words (names of functions, properties and methods), such as "Date", "Year", "Name", Type", etc. Using such words can lead to very subtle errors that are hard to detect and yields a program that is quite complex to maintain.

    Last but not least: Always activate the explicit declaration of variables (that's the line "Option Explicit" in the Declaration section (the header) of the module. You can force Access to automatically insert that line in every module: open the VBA Editor (ALT+F11), then in the Tools menu, select Options. In the Options window select the Editor tab and check the option box "Require Variable Declaration". This will save you hours of trouble in the future.
    Have a nice day!

  15. #15
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Sinndho,

    You are a saint for having done so much analysis on this DB.

    To emphasis what you have mentioned:

    Jacksmith88, you should NEVER use spaces in an object name.

    NEVER!

    It is the worst of beginner programming practices.

    And refrain from any other special characters. Limit yourself to 26 characters, upper and lower case.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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