1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Input Mask Doesn't Help

Discussion in 'Business Applications' started by dab7222, Jan 5, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. dab7222

    dab7222 Thread Starter

    Joined:
    Dec 5, 2004
    Messages:
    20
    I'm not an Access expert, but I can use it fairly well. My problem is that I have an HR data base and I want to use an Input Mask for the last name field so that the user won't have to use the upper case. Well, of course, the input mask isn't good for that because we have many with two last names and suffixes and the mask can't address that situation. I've found some code to address this that was given out on the internet, but I don't know where to put it. Can you please help me with this? Thanks a lot.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If the code looks like this
    Me.describe = StrConv(Me.describe, vbProperCase)
    then you can put it in the Form's "On Current" event procedure for updating old records and in the Field's "After Update" event procedure to handle current records.
     
  3. dab7222

    dab7222 Thread Starter

    Joined:
    Dec 5, 2004
    Messages:
    20
    Thank you, OBP. That is the code, and I've tried to enter it a bunch of times, but I keep getting told that Access 2007 can't find the Object Name. Also, I don't have an On Current option. I've tried to enter it in the After Update field, though. Also, will this setting, if I ever get it working, apply to the table as well or only the form? Thanks!
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    dab, it applies to the table as long as the table or a query based on it is used as the record source of the form.
    The On Current is a Form event so you need to click on the form's background to get the form results or open the form in design mode.
    If Access can't find the object name you need to carefully check the name of the Field you are trying to work with.
    What is the name of your field on the form?
     
  5. dab7222

    dab7222 Thread Starter

    Joined:
    Dec 5, 2004
    Messages:
    20
    Okay. Thanks so much for your help. I thought I read somewhere that the controls on the form didn't apply to the table.

    I found On Current, thanks. I was setting the properties for the individual field in the form.

    I am entering this: Last Name = StrConv(Last Name, vbProperCase) I have tried this: Last_Name = StrConv(Last_Name, vbProperCase)

    The field in the table is set up as Last Name. It shows as that in the fields in Queries, Forms and Reports.

    Do I need to create a Function or Macro first? When I click on Help in the Error Message I get this information:


    Access was unable to locate the macro or VBA function. If you are trying to call a macro, make sure that the name of the macro and the name of the macro group are spelled correctly.
    If you are trying to call a user-defined VBA function, be sure to use the following syntax:
    =FunctionName() or =FunctionName(argument1,argument2,...)
    Make sure that the function is either:
    Defined in the code for the form or report.
    - or -
    A public function that is in a module (not a class module).
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The code does not go in the event line.
    The VBA code doesn't go in the Line that says event procedure, what you need to do is Cut that piece of code from the event line.
    Then click the drop down and Select "Event Procedure", 3 small dots will appear on the right hand side, click on the dots and it will open the VBA Editor at the Combo's "After Update" event procedure.
    paste the code between the 2 lines of code that are already there.

    You will need to use

    me.[Last Name] = StrConv(me.[Last Name], vbProperCase)
     
  7. dab7222

    dab7222 Thread Starter

    Joined:
    Dec 5, 2004
    Messages:
    20
    Doh! I got it. At least, sort of.

    Here's what's happening now. I create the Event, and pasted just like you said. First I just did On Current on the Property Sheet with Form as the Selection Type. It works as soon as I move to the next record when I enter in the Form. If I enter it in the Table it doesn't do it. I move to the next record and it doesn't update. I close the database and re-open it, and it's all lowercase. However, if I open up the Form and look at that specific record there, it's properly showing the proper case and then when I go back to the Table it is just the way I want it. The same was true if I chose Last Name under Selection Type on the Property Sheet.

    The other situation I had was that when I created two Events, one for On Current and the other for After Update, after I saved and closed and then opened the form for entry, I could only access the first record. Upon closing I received the error message saying:

    You can't save this record at this time. Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway?

    I really appreciate your help, I hope I'm not being completely dense.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The VBA only works on the Form, which then updates the table, it does not work if you are in the table.
    I would suggest that you get the Field's "After Update" event working and discard the "On Current" version.
    If you have lots of records I will provide you with a solution to update them all in one go.
     
  9. dab7222

    dab7222 Thread Starter

    Joined:
    Dec 5, 2004
    Messages:
    20
    Thanks a lot. I did choose to go with the After Update option since that worked best in part, but my database has 5,600 records and another 2,500 ready to be entered, so if you can help me get it working in the Table, that will be really helpful as the user finds that to be the fastest way to get the entry done.
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    dab, I am concerned that the user finds it fastest to enter data directly in to the table. It strongly suggests that the data and form are not optimised for data entry.
    What is the name of the Table that contains the names?
     
  11. dab7222

    dab7222 Thread Starter

    Joined:
    Dec 5, 2004
    Messages:
    20
    OBP, the table is called Hiring Data Base. It is a civil engineering company and they take about 30-40 new applications a day, most of which don't ever get hired. They need the records of who applies for purposes relating to union and government inquiries. The user is a friend who had a guy create a basic database for him, but it didn't serve his needs. He dislikes entering in the form because he likes to have the ability to look at all the data in the table as the entries go in. Says he catches a lot of mistakes that way and as his key is a number that is sequential but is not generated automatically, he likes to be able to see the last number entered in the table and the form disallows that. (Every applicant gets a number and although they are sequential, entry is not necessarily done sequentially. Time constraints sometimes cause him to enter only those whose skills are most urgently needed so that he can query for those he considers to be the best candidates.) I've encouraged him to use the form which I have updated for him, but he likes the table and feels as though entry is faster when he works in it as opposed to the form. Everything about the database is predicated on speed of entry. That's why he wants, if possible, the automatic capitalization of last names as the company is involved in government programs that allow them to hire skilled laborers from outside the country if need demands. Many of those individuals who come in to work the job are Hispanic and use two last names. I think he likes the table entry best because originally he began keeping this data in Excel and he's comfortable with that format.
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I understand ex Excel users wanting to work in the table, but then they might just as well use Excel.
    Can you send me a copy of the database less any data?

    It is not possible to have the VBA work from the Table, but I would like to try and get a form that they would find acceptable.
    How many tables does the database have?

    In the "table is called Hiring Data Base" what are the names of the fields that need Capitalising, (5,600 records) or are they already capitals and you only need it for the other 2500 waiting to go in?

    It is possible to capitalise them all after entry if required.
     
  13. dab7222

    dab7222 Thread Starter

    Joined:
    Dec 5, 2004
    Messages:
    20
    OBP, I can send you a blank version of the table, but I don't think it's necessary to go that far. He'll have to live with using upper case where appropriate. But, is there any way to do this with an Update Query? He only has one table.
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What do you want the query to do?
     
  15. dab7222

    dab7222 Thread Starter

    Joined:
    Dec 5, 2004
    Messages:
    20
    I thought that maybe if he did his entry in the table and only used the lower case, after entry was done he could run an update query to change the case in the Last Name field. I don't know if that's possible.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/972872

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice