Results 1 to 2 of 2
  1. #1
    bglaugh is offline Novice
    Windows XP Access 2002
    Join Date
    Sep 2010
    Posts
    1

    Partial Limit

    This is going to sound like a ridiculous question (probably), but is it possible to partially limit what people can enter into a field? I know you can limit entirely what can be entered, but is it possible (in any way) to, say, ensure that in a field people can only enter certain codes before a space, then anything after the space?



    My problem is that I have inherited an enormous database, that has grown exponentially over the past 10 years, and that is incredibly convoluted. I don't want to start adding new fields as there are myriad reports, queries and forms that all relate to one another and adding fields would be more trouble than it's worth (the person who initially created it didn't really know what he was doing, and it's full of redundant tables and the like that all seems to have been created solely for the purpose of getting in my way).

    My manager wants a new yearly report creating and, as it's a new year for us (we work by a school year. Don't ask me why) it is the ideal opportunity to make amendments.

    The field that the report needs to pull info from is a free type field. The tasks we do are generally the same, however extra information needs adding to ensure all details are captured, so I can't simply limit the field entirely to a pull down list. The problem is the info is always entered differently by different people, i.e. if the task is water pipe maintenance some people list it as WPM while others list it as Water PM. Then there are those who don't list it as anything resembling either. We've never been asked for a report on this field before (every other field is a restricted, choose from a list, deal) and it's proving tricky to police as we have 25 different people who enter the information at one time or another and they do what they want.

    Can I limit people to a list of codes before a space then allow them to type whatever they want after the space?

  2. #2
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    you could add an unbound combobox with a list of the allowed prefixes. Then add code that resembles the following (presuming cboPrefix is the name of the unbound combo box, the prefix is always 3 chars long and the name of the text field is txtText):
    ======================
    Private Sub cboPrefix_AfterUpdate()
    If Len(Me.cboPrefix) > 0 Then
    If Len(Me.txtText) > 3 Then
    Me.txtText = Me.cboPrefix & "-" & Right(Me.txtText, Len(Me.txtText) - 4)
    Else
    Me.txtText = Me.cboPrefix
    End If
    End If
    End Sub
    ======================
    Private Sub txtText_BeforeUpdate(Cancel As Integer)
    If Len(Nz(Me.cboPrefix, "")) = 0 Then
    MsgBox "Please choose a code first"
    Me.Undo
    End If
    End Sub
    ===============

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

Similar Threads

  1. is there a Size Limit to reports???
    By ghodges in forum Reports
    Replies: 5
    Last Post: 08-17-2010, 07:16 AM
  2. Partial SUM of values on a query.
    By jrvf in forum Queries
    Replies: 2
    Last Post: 08-16-2010, 01:33 PM
  3. Limit Records
    By EHittner in forum Forms
    Replies: 1
    Last Post: 05-03-2010, 10:37 AM
  4. Replies: 1
    Last Post: 03-22-2010, 03:37 PM
  5. Partial Matching based Queries
    By Yatesb in forum Queries
    Replies: 0
    Last Post: 02-23-2009, 01:06 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