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.

Code works on one form, but not another

Discussion in 'Business Applications' started by terabacktyl, Jan 11, 2012.

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

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    Hello!

    I have two forms in my database that auto increment a record number (primary key). The format is a 2 digit year, followed by a 4 digit number. i.e. 12-0001. I have seen posts about how to get this to work, and this is the code I use:

    Dim strMax As String
    strMax = DMax("[NCIR#]", "New Fields")
    Me!HiddenCtl = Format(Date, "yy") & "-" & Format(Right(strMax, 4) + 1, "0000")

    This works for one form, but when I try to use it (referenceing different tables and such, of course) in another form, the two digit year will not show up. i.e. "-3001" is all that shows up. Slightly different format, but here's the code:

    Dim strMax As String
    strMax = DMax("[LIR #]", "tbl_LIR")
    Me!HiddenCtl2 = Format(Date, "yy") & "-3" & Format(Right(strMax, 3) + 1, "000")

    Ideas?
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    What is the HiddenCtl2 field, is it taken from a table or is it an unbound field on the form?
    If it is from a table, what type of field setting does it have?
    What do you get if you just use Me!HiddenCtl2 = Format(Date, "yy")
     
  3. terabacktyl

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    HiddenCtl2 is a text box with a control source in a table.

    What do you mean by field property?

    Changing the code to only format(date(), "yy") returns nothing.
     
  4. terabacktyl

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    Sorry, changing to Me!HiddenCtl2 = Format(Date, "yy") returns nothing.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    Are you sure that it is not a Number field.
    What do you get if you use
    msgbox format(date(), "yy")
     
  6. terabacktyl

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    It is a text field in the table, and I don't see a place to specify that in the properties sheet of the field in the form.

    The msg box comes up empty, too.
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    It looks like it is not recognising the Date(), which I have seen before, try format(Now(), "yy") and see if that works.
    You may need to set a VBA Library reference to get the date working.
     
  8. terabacktyl

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    That worked! I think I will change the other code to Now() instead of Date, to make sure it doesn't decide to quit working.

    Thanks so much!
     
  9. 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!

Loading...
Thread Status:
Not open for further replies.

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

  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