Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set field data by OnClick (Access 2003)

    I currently have a table T_AgileData_Parsed, which has the fields Item, LDesc, MFR, MPN (and a few others that I don't think I need to mention). The MFR and MPN fields are currently blank. I would like to fill the MFR in by selecting a string from the LDesc field where the first click would be the beginning integer of the string and the second click would be the ending integer of the string; using these 2 variables to obtain the MFR.

    I tried to setup this script, but had a few problems. I used the OnClick event to come up with the start integer, but couldn't figure out how to come up with the end integer (second click). The problem that I'm having is how to remember what click I'm on. I could probably solve this by entering another field into the table to store what click I was on, but I don't want the table to grow any more than necessary and I'm thinking their is a more efficient way to handle this.

    Any help is greatly appreciated.
    Regards,
    Drew

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Set field data by OnClick (Access 2003)

    I'm completely lost on what you are trying to do. You say:
    >>selecting a string from the LDesc field where the first click would be the beginning integer of the string and the second click would be the ending integer of the string;<<
    What exactly do you mean by this?

    What do you mean by first click?

    Are you in a form when you do this?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Set field data by OnClick (Access 2003)

    You select text by either double clicking to select a word and by clicking and holding down the mouse button while you drag the mouse over the portion of text you want to select. However, I don't see why you're doing this at all. It would be faster and simpler to use an update query, assuming you are using this table to parse data from one field into several others and also assuming that there is some logic to the parsing. In any event, it doesn't take a "script" to do this if you are in a table, query or even a form, so please explain exactly what it is you are trying to do and where.
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set field data by OnClick (Access 2003)

    Keeping track of first and second clicks leaves no room for correcting mistakes. It might be better to use a custom toolbar button and the SelStart and SelEnd properties of the text box bound to to the field. The On Action code of the toolbar button would extract the text between SelStart and SelEnd and put it in MFR. You'd have to do this for every record.

    But as the otheres noted, it might help if you provided more information.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set field data by OnClick (Access 2003)

    To clarify further...
    I'm in a datasheet form when I do this (sorry, not a table).

    What I have is a manufacture name embedding into the LDesc field. My end goal is to get that manufacture name (string) into the MFR field. Since their is a large amount of records to go through, I was thinking that the most effient way to extract this would be by first and second mouse clicks in the LDesc field. Then setting the MFR field equal to the text string in between the clicks.
    After the second click, it resets and if a mistake was made, just redo first click and second click.

    I was thinking that this would be the fastest way to extract a string because we wouldn't have to go up to the toolbar and click or wouldn't have to select the text string, copy, then paste. If any of you have other suggestions, please suggest.

    Regards,
    Drew

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set field data by OnClick (Access 2003)

    I still think this is a counter-intuitive way of doing things, but you could try the following:

    Private lngStart As Long
    Private lngEnd As Long
    Private blnSecond As Boolean

    Private Sub Form_Current()
    blnSecond = False
    End Sub

    Private Sub LDesc_Click()
    If blnSecond = True Then
    If Me.LDesc.SelStart < lngStart Then
    lngEnd = lngStart
    lngStart = Me.LDesc.SelStart
    Else
    lngEnd = Me.LDesc.SelStart
    End If
    Me.MFR = Mid(Me.LDesc.Text, lngStart + 1, lngEnd - lngStart)
    Else
    lngStart = Me.LDesc.SelStart
    End If
    blnSecond = Not blnSecond
    End Sub

    The declarations at the beginning MUST be at the top of the form module, below Option Compare Database and Option Explicit, but before all subs and functions.

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set field data by OnClick (Access 2003)

    Hans,

    Works great. I've added a checkbox to the form to enable and disable.

    I could agree that this is counter intuitive, but since their will only be 2 of us using this it won't be a big deal.
    We aren't able to use logic to extract this data until we build an alias table, so I was thinking this would the fastest. If you have another suggestion, I could try it.

    Thanks,
    Drew

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set field data by OnClick (Access 2003)

    If there is no "logic" to extract the manufacturer names, I would probably select the manufacturer name any which way (click and drag, click and shift+click, click and shift+right arrow), and a keyboard shortcut to transfer the selected text (determined by SelStart and SelEnd) to the MFR field.

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set field data by OnClick (Access 2003)

    I tried your suggestion (amazingly I was able to set it up myself). I now have the F11 and F12 keys setup to extract the MFR and MPN (manufacture part number) text respectfully.
    Much similar and faster; thanks for the suggestion.

Posting Permissions

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