Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Posts
    56

    Red face Unanswered: best solution for fields to automatically complete based on whats typed in 1 field

    Before i say anything, i had a similar post on here, but didnt quite get the result i was looking for due to a picky Manager i have, and i'd like to give full Credit, to the user "siindho" who was absolutely brilliant in helping me so much. however i now need something similar but different, siindho, if your out there, i need you! lol

    right, im designing a database to manage all files and documents for a company i work for, my database consists of:

    Tables
    Stock Controller (main)
    Exceptions
    ISOS
    Waste Disposal
    Driver Ctrl Forms
    Postcodes and Boroughs

    Forms:
    Stock Control
    Exceptions
    ISOS
    Waste Disposal
    Driver Ctrl Forms

    now, the problem i have, in the stock control form, it has these fields:
    ID
    Collection Number
    Date
    Postcode
    Town
    Borough
    Item
    Weight
    Qty
    Comments

    what i want to acheive is.. when i type in a postcode, say "SE18" on the stock control form/table i want it to recognise that as a postcode from my [postcodes and boroughs] table, and so, as a result, recognise that "SE18" from [postcodes and boroughs] belongs with "Woolwich" from [postcodes and boroughs] and "Greenwich" from [postcodes and boroughs] and then automatically completes the "town" and "borough" fields on my form as soon as ive completed the "Postcode" field,

    BUT!!

    the tricky part is,
    1. i want it as a typing autocomplete, not a dropdown box
    2. i want to be able to type "SE18" then it completes the rest, and then i can continue to write "7NB" to finalise the postcode..
    3. i want to be able to link this with the [exceptions] Form/Table, and [Waste Disposal] Form/table so that it autocompletes on all Tables/Forms with a "postcode" "town" and "borough" field..

    Screenshots:

    Stock Control Form:
    Imageshack - formv.jpg

    Postcodes and Boroughs Table
    Imageshack - postcodes.jpg

    and the Stock Control Table:
    Imageshack - stockm.jpg


    Any Help or suggestion is GREATLY! appreciated.. thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution but I must warn you that it is quite complex and needs some skills in VB/BVA and in Object-oriented programming techniques.

    1. You first need to create a class module in your database. To do so, open the VBA Editor (ALT+F11) and select "Class Module" in the Insert Menu.


    2. Once the New (blank) class module in open, copy the following code and paste it into it.
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_strTableName As String
    Private m_strColumnSearch As String
    Private m_strColumnDefault As String
    Private m_ctl As TextBox
    Private m_rst As DAO.Recordset
    Private m_strCriteria As String
    Private m_lngColumnSearch As Long
    Private m_lngColumnDefault As Long
    Private m_booReady As Boolean
    Private m_booMatch As Boolean
    
    Private Sub Class_Initialize()
    
        m_lngColumnSearch = -1
        m_lngColumnDefault = -1
        
    End Sub
    
    Private Sub Class_Terminate()
    
        If Not m_rst Is Nothing Then
            m_rst.Close
            Set m_rst = Nothing
        End If
        Set m_ctl = Nothing
    
    End Sub
    
    Private Sub BuildCriteria()
    
        Dim fld As DAO.Field
        
        If Not m_rst Is Nothing Then
            If Len(m_strColumnSearch) > 0 Then
                For Each fld In m_rst.Fields
                    If fld.Name = m_strColumnSearch Then
                        If fld.Type = dbText Then m_strCriteria = fld.Name & " Like '@T*'"
                        CheckReady
                        Exit For
                    End If
                Next fld
            End If
        End If
                       
    End Sub
    
    Private Function CheckReady() As Boolean
    
        m_booReady = False
        If Not m_rst Is Nothing Then
            If Len(m_strCriteria) > 0 Then
                If m_lngColumnSearch > -1 Then
                    If m_lngColumnDefault > -1 Then
                        If Not m_ctl Is Nothing Then m_booReady = True
                    End If
                End If
            End If
        End If
        CheckReady = m_booReady
        
    End Function
    
    Public Property Get ColumnDefault() As String
    
        ColumnDefault = m_strColumnDefault
    
    End Property
    
    Public Property Let ColumnDefault(ByVal ColumnName As String)
    
        If m_strColumnDefault <> ColumnName Then
            m_lngColumnDefault = ColumnIndex(ColumnName)
            If m_lngColumnDefault > -1 Then
                m_strColumnDefault = ColumnName
                CheckReady
            End If
        End If
    
    End Property
    
    Private Function ColumnIndex(ByVal ColumnName As String) As Long
    
        Dim fld As DAO.Field
        
        ColumnIndex = -1
        If Not m_rst Is Nothing Then
            For Each fld In m_rst.Fields
                If fld.Name = ColumnName Then
                    ColumnIndex = fld.OrdinalPosition
                    Exit For
                End If
            Next fld
        End If
        
    End Function
    
    Public Property Get ColumnSearch() As String
    
        ColumnSearch = m_strColumnSearch
    
    End Property
    
    Public Property Let ColumnSearch(ByVal ColumnName As String)
    
        If m_strColumnSearch <> ColumnName Or Len(m_strCriteria) = 0 Then
            m_lngColumnSearch = ColumnIndex(ColumnName)
            If m_lngColumnSearch > -1 Then
                m_strColumnSearch = ColumnName
                If Len(m_strColumnDefault) = 0 Then
                    m_strColumnDefault = m_lngColumnSearch
                    m_lngColumnDefault = m_lngColumnSearch
                End If
                BuildCriteria
            End If
        End If
    
    End Property
    
    Public Sub KeyPressed(ByRef KeyValue As Integer)
    
        Dim strBuffer As String
        Dim lngPos As Long
        Dim strLeft As String
        Dim strRight As String
        
        If KeyValue <> 0 And KeyValue <> 8 And KeyValue < 31 Then Exit Sub
        If m_booReady = True Then
            With m_ctl
                m_booMatch = False
                If KeyValue = 8 Then
                    If .SelStart > 1 Then strLeft = Left(.Text, .SelStart - 1)
                    If Len(.Text) > .SelStart Then strRight = Mid(.Text, .SelStart + 1)
                    .Text = strLeft & strRight
                    .SelStart = Len(strLeft)
                End If
                strBuffer = Left(.Text, .SelStart) & IIf(KeyValue > 31, Chr(KeyValue), "")
                If Len(strBuffer) > 0 Then
                    lngPos = Len(strBuffer)
                    m_rst.FindFirst Replace(m_strCriteria, "@T", strBuffer)
                    m_booMatch = Not m_rst.NoMatch
                    If m_booMatch = True Then
                        .Text = m_rst.Fields(m_lngColumnSearch)
                        .SelStart = lngPos
                        .SelLength = Len(Mid(.Text, .SelStart))
                        KeyValue = 0
                    End If
                End If
            End With
        End If
        
    End Sub
    
    Public Property Set LinkedControl(ctlLinkedControl As Control)
    
        Set m_ctl = ctlLinkedControl
        CheckReady
    
    End Property
    
    Public Property Get Match() As Boolean
    
        Match = m_booMatch
        
    End Property
    
    Public Property Get TableName() As String
    
        TableName = m_strTableName
    
    End Property
    
    Public Property Let TableName(ByVal strTableName As String)
    
        m_strTableName = strTableName
        Set m_rst = CurrentDb.OpenRecordset(m_strTableName, dbOpenSnapshot)
        BuildCriteria
    
    End Property
    
    Public Property Get Value(Optional ByVal ColumnName As String = "") As Variant
    
        Dim lngIndex As Long
        
        If m_booReady = True Then
            lngIndex = -1
            If Len(ColumnName) > 0 Then
                lngIndex = ColumnIndex(ColumnName)
            Else
                lngIndex = m_lngColumnDefault
            End If
            If lngIndex > -1 Then
                If m_booMatch = True Then Value = m_rst.Fields(lngIndex).Value
            End If
        End If
                
    End Property
    Note: If the two lines:
    Code:
    Option Compare Database
    Option Explicit
    appear twice after the copy, remove one set of them.


    3. Select "Save" from the File menu and when asked for the name of the new class, type "Cls_Search" (without the quotes).


    4. To test the Cls_Search class, create a new form and add 3 unbound textboxes to it named: Text_PostCode, Text_Town and Text_Borough.


    5. Open the Module of the form then copy and paste this code into it.
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_clsSearch As Cls_Search
    
    Private Sub Form_Open(Cancel As Integer)
    
        SearchInitialize
        
    End Sub
    
    Private Sub Text_PostCode_KeyPress(KeyAscii As Integer)
    
        If Not m_clsSearch Is Nothing Then
            m_clsSearch.KeyPressed KeyAscii
            If m_clsSearch.Match = True Then
                Me.Text_Town.Value = m_clsSearch.Value
                Me.Text_Borough.Value = m_clsSearch.Value("Borough")
            End If
        End If
    
    End Sub
    
    Private Sub SearchInitialize()
    
        Set m_clsSearch = New Cls_Search
        With m_clsSearch
        
            ' Name of the table (or query) where the search will be performed.
            '
            .TableName = "PostCodes and Boroughs"
            
            ' Name of the column (in table <.TableName>) in which the search will be performed.
            '
            .ColumnSearch = "PostCode"
            
            ' Change the name of the column (in table <.TableName>) the value of
            ' which will be returned by the Value property of the Cls_Search class.
            '
            .ColumnDefault = "Town"
            
            ' Textbox Control used to input the search string.
            '
            Set .LinkedControl = Me.Controls("Text_PostCode")
        End With
    
    End Sub
    Note: If the two lines:
    Code:
    Option Compare Database
    Option Explicit
    appear twice after the copy, remove one set of them.


    6. Open the form and try typing something into the Text_PostCode textbox.

    The class Cls_Search can be linked to any textbox in any form in the database by declaring a private instance of it in a form module, then by initializing this instance, assigning the proper values to the TableName, ColumnSearch and LinkedControl "mandatory" properties (assigning a value to the ColumnDefault property is optional; if you don't, the Value property (without parameter) will return the value of the ColumnSearch column.

    The class won't work if the 3 "mandatory" properties (TableName, ColumnSearch and LinkedControl ) are not correctly initialized and it only works for searching in columns of Text type.
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    56

    hey man

    hey again siindho...

    mate... how long did that fuckin take you? bless ya heart!

    now im a little confused, i did everything you said, except i got all confused towards the end bit when the big programmy words came into play,

    i can see in the code for example:

    Code:
    Private Sub SearchInitialize()
    
        Set m_clsSearch = New Cls_Search
        With m_clsSearch
        
            ' Name of the table (or query) where the search will be performed.
            '
            .TableName = "PostCodes and Boroughs"
            
            ' Name of the column (in table <.TableName>) in which the search will be performed.
            '
            .ColumnSearch = "PostCode"
            
            ' Change the name of the column (in table <.TableName>) the value of
            ' which will be returned by the Value property of the Cls_Search class.
            '
            .ColumnDefault = "Town"
            
            ' Textbox Control used to input the search string.
            '
            Set .LinkedControl = Me.Controls("Text_PostCode")
        End With
    
    End Sub
    the bits with 'blah blah blah' are secret instructions that wont interfere with the coding? that correct? i hope so... but... i aint quite sure how to follow em? what do i change the column <table name> to? the values and private instances bit went straight over me mate, im terrible at interpretation.

    anyway if you can simplify that down? if you want to/dont mind...

    Thanks,

    Your retarded Friend Jack.

    PS!
    ive just obtained 2 e-books, Access 2007 for dummies, and VBA programming for Access, so hopefully after a couple of weeks i wont be so dumb!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by jackjsmith88 View Post
    ... how long did that fuckin take you?
    A little more than 2 hours yesterday, late in the evening, if you want to know.

    Quote Originally Posted by jackjsmith88 View Post
    the bits with 'blah blah blah' are secret instructions that wont interfere with the coding? that correct? i hope so... but... i aint quite sure how to follow em?
    If you mean the lines beginning with a single quote (') they are not secret instructions, they're just comments. And you're right they are ignored by the compiler and then do not interfere with the execution of the program.

    What's worth to understand and to remember is that the class performing the search must be initialized. It must be provided with 3 pieces of information before it can do anything useful:

    1. The name of the table where to search for data matching what's typed in the form (that table name is "PostCodes and Boroughs" in my example as well as in your database, but the class Cls_Search is able to perform a search in any table residing in the current database). That's what's done with the instruction:
    Code:
    .TableName = "PostCodes and Boroughs"
    2. The name of the column (in the table specified in 1.) where the search must be performed. In my example, the class searches for a match in a column named "PostCode". Once again, it could be any column, provided that is was defined a Text data type in the table definition. That's what's done with the instruction:
    Code:
    .ColumnSearch = "PostCode"
    3. A reference to a TextBox control (on the form) from where it will receive the bits of information that must be used to find a match (it's where the user will type something, if you prefer). That's what's done with the instruction (Text_Poscode being the name of that TextBox control):
    Code:
    Set .LinkedControl = Me.Controls("Text_PostCode")
    With that the class is ready to work. You can use the Match property to determine wheter a matching value was found (Match = True) or not (Match = False), while the matching data (if any) can be obtained from its Value property. This Value property can be used with 2 different syntactic forms:

    a) You simply fetch what's returned by default:
    Code:
    MyValue = m_clsSearch.Value
    or:
    Code:
    MyControl = m_clsSearch.Value
    b) You specify the value from a specific column to be returned. If I want to fetch the value returned from the "Borough" column, I pass the name of this column as argument:
    Code:
    MyValue = m_clsSearch.Value("Borough")
    or:
    Code:
    MyControl = m_clsSearch.Value("Borough")

    4. By default and unless specified otherwise, the Value property returns a value from the column where the search for a match was performed ("PostCode" in my example), and we saw just above that if you want to retrieve the value of another column, you must explicitly specify its name.

    The fourth property that you can initialize is optional. The class does not need it but it can be used to modify the column in which the data returned by the Value property will be fetched.

    In my example, the column where the search is performed is "PostCode". By default, the Value property will return data from the same "PostCode" column. If you initialize the ColumnDefault property when initializing the class:
    Code:
    .ColumnDefault = "Town"
    The Value property (without any specification of column name -syntax (a) here above-) will now return data from the "Town" column.

    The way the class works is not totally satisfactory and some bugs remain, namely related to the use of the BackSpace key (and probably others too), but I'm working on a big project at the moment and cannot afford spending more time correcting and improving it.
    Have a nice day!

  5. #5
    Join Date
    Oct 2011
    Posts
    56
    am extremely grateful for all your time and effort man, your a legend..but you should get back to your big project and i wish you the best with it.

    have a few questions..

    1. id like to remain in contact with you, wondering if you have skype? facebook? email?
    2. where did you learn all this stuff? any helpful places/books you can point me too?
    3. if theres anything you can think of that i can do to return the favourable time you've donated to me? i'd be more than happy to help.. i have my uses (basic) lol

    thanks alot dude.. theres not enough people like yourself out there

    thanks

    your retarded friend jack

    P.S just for my satisfaction, can we remain with "secret instructions" as opposed to "comments"? it sounds better, lol

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by jackjsmith88 View Post
    1. id like to remain in contact with you, wondering if you have skype? facebook? email?
    My working on a project does not mean that I'll stop visiting dBforums. You can contact me here or, if you prefer, click on my name in the header of of this message and select "Send private message" or "Send email" in the dropdown list.

    Quote Originally Posted by jackjsmith88 View Post
    2. where did you learn all this stuff? any helpful places/books you can point me too?
    I cannot actually recommend any good book (this does not mean that they do not exist, quite the contrary). It mainly depends on which aspect of Access, and of programming in general, you want to learn (the VB/VBA language, relational model, object-oriented programming, algorithms...

    As far as I'm concerned, I have a grade in Computer Sciences from a Canadian university. I learned working with Access from scratch, though I already had a heavy background and experience in databases management and in programming languages in general. That was almost 20 years ago, and at that time MS products such as Access came with a full set of books. I went on from there, attending many presentations and conventions that were organized at each new release of the product.

    Quote Originally Posted by jackjsmith88 View Post
    3. if theres anything you can think of that i can do to return the favourable time you've donated to me? i'd be more than happy to help.. i have my uses (basic) lol
    Can I have one of your kidneys?

    Quote Originally Posted by jackjsmith88 View Post
    P.S just for my satisfaction, can we remain with "secret instructions" as opposed to "comments"? it sounds better, lol
    I'll send your suggestion to the ANSI and ISO committees
    Have a nice day!

  7. #7
    Join Date
    Oct 2011
    Posts
    56
    fair enough, still see you on here then! yey!

    you have a remarkable sense of humour for a guy with a computer science degree

Posting Permissions

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