Results 1 to 12 of 12

Thread: Leading Zeros

  1. #1
    Join Date
    Apr 2006
    Posts
    5

    Unanswered: Leading Zeros

    I have a Access application that has a form that pulls in from a table a text value that includes 01, 02, 03, 04, etc. The first time I open this form it looks up an id number and the corresponding location *the numbers above and it pulls in correctly. When I hit add new record the 01 drops the leading zero and is 1 and it bombs on me as there is no value of 1 in the table. This used to work just fine but the last programmer we had changed the format from Access 2000 to Access 2003 and that is when this error started showing up. I can't ask him as he is no longer with the company in fact right now we have no programmer. I know a lot about this program just very little about programming. Can anyone help?? I can get the code behind the add new record button or the original opening of the form if that helps. All the tables that this is pulling from are set as text values not numbers so I am confused at why it is now all of a sudden dropping the zero's and casuing the errors.

    Thanks for any help

    Confused :confused:

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Read that through from the perspective of someone who has never, ever seen your application and see if it makes sense...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2006
    Posts
    5

    Leading Zeros

    Sorry If I was not more deatiled in my post. Kind of new at this.

    I have an Access based db that we use for Inventory purposes. We buy and sell live animals. When we purchase these animals they get entered into our inventory table with various information; type code, head, weight, cost, location, etc.
    Each Inventory item receives a groupid (which is the id which identifies a group of animals purchased). We sometimes have to adjust the qty(head) or weight of these animals because they died or they miscounted them originally or the lost or gained weight. In the adjustment acitivity we open up or main form (the Activity Form) and select the type of activity (Adjustment in this case), we select the groupid (group of animals we want to move). We the save this record and go into a detail screen (form), this form goes out and looks at this group id and fills in the following information Groupid, Type Code, and Location (Pen). Then the fields for head and weight are left open and editable so we can enter the positive or negative amount of head or weight we need to adjust off.Then if we want to enter more activities for this same groupid then we click add new record and it should do the same thing.

    The first part of this when we go into the the detail form originally works fine it pulls in the location id as it is stored in the table (09), but when I click on the add new record button it fills all in fine except for location id which it drops the leading zero. Wish cause it to bomb because there is a location table and it verifies that the location is in that table and we do not have a location 9 without the zero.

    Hope this makes more sense.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    ... And it sounds like they changed the TYPE of the column to which you reference ... Ted's next comment is: THis is bad design, blah blah blah ... Displaying leading zeros is a format issue blah blah blah ... How we can help? Give us some more detail. Suggestion: Get yourself another programmer, only this time one who won't dick around with your table structure without approval ... And have them fix this problem for you
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Apr 2006
    Posts
    5
    Unfortunetly I do not believe they have any immediate intentions of getting a new programmer so I get stuck with this for now. Although I love your suggestion thanks.

    The tables in which this data is pulling this information the location table and the activity detail table have the text property. I do not see on the form properties anywhere that I can change that format to text it only shows numeric formats in the box. Here is the code behind the add new record button which seem to be where the problem is if it helps at all. The movement type is 3 or 4 that is having the problem.

    Private Sub btnAddNewRecord_Click()
    'AI-09/10/2003, AI-10/27/2003
    On Error GoTo Err_btnAddNewRecord_Click
    DoCmd.GoToRecord , , acNewRec
    If FormIsOpen("Activity Form") Then
    Me.MOVEMENT = Forms![activity form].MOVEMENT
    Me.EntDateDtl = Forms![activity form].ENTDATE
    Select Case Forms![activity form].MOVEMENT
    Case 1, 5, 6
    Me.TypeCode.SetFocus
    Case 2, 7
    Me.GroupID.SetFocus
    Case 3, 4
    Me.HEAD.SetFocus
    Me.GroupID = Forms![activity form]![GroupID].Column(0)
    Me.TypeCode = Forms![activity form]!GroupID.Column(1)
    Me.Location = Forms![activity form]!GroupID.Column(2)
    End Select
    End If
    Me.Refresh
    Exit Sub
    Err_btnAddNewRecord_Click:
    varX = AI_ErrorMessage("Activity Detail Form.btnAddNewRecord_Click: ", Err.Description, Err.Number)
    End Sub

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    <3 Owen <3


    ....


    Anywho, in leui of actually fixing your database, you could pull a hack and format the number you're passing:

    Me.GroupID = Format(Forms![activity form]![GroupID].Column(0), "0#")
    Last edited by Teddy; 04-18-06 at 11:58.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Maybe....locate the Table Field in question then place 00 (two zeros)into the Format property. Then for the Form Field TextBox (in question) place 00 (two zeros) into the Format property for that field.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  8. #8
    Join Date
    Apr 2006
    Posts
    5
    Thanks for all your help everyone I got it

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by vcurtis
    Thanks for all your help everyone I got it
    And what was it that you did? We're curious ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Apr 2006
    Posts
    5
    Found a line of code on the form open that apparently our great programmer decided to change somehow. I reviewed the code between this version and the previous one where it was working and we found this line.

    If Not IsNull(lcLocation) Then
    Me!Location.DefaultValue = lcLocation
    ' Me!LOCATION.DefaultValue = """" & lcLocation & """"

    I commented this line out as it was not there before and now it works. If it was not for all your help and the idea that he must have messed with the code I would have not went do this path so thanks again.

    I am not sure why he would have been messing with this code at all none of the changes we made were supposed to be on the forms or tables or any of the code associated with the forms or tables we only added a few reports and select statements.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by vcurtis
    Found a line of code on the form open that apparently our great programmer decided to change somehow. I reviewed the code between this version and the previous one where it was working and we found this line.

    If Not IsNull(lcLocation) Then
    Me!Location.DefaultValue = lcLocation
    ' Me!LOCATION.DefaultValue = """" & lcLocation & """"

    I commented this line out as it was not there before and now it works. If it was not for all your help and the idea that he must have messed with the code I would have not went do this path so thanks again.

    I am not sure why he would have been messing with this code at all none of the changes we made were supposed to be on the forms or tables or any of the code associated with the forms or tables we only added a few reports and select statements.
    Perhaps this and the fact he is an ex-programmer is related....

    BTW - Mike - loved your post. Perhaps Teddy can find room in the FAQ. BTW - I've been intending to write FAQ stuff. I started one and it turned into a tutorial\ semi-fascist lecture on appropriate use of Auto Numbers. I am going to try again soon but not get so carried away
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Perhaps this and the fact he is an ex-programmer is related....

    BTW - Mike - loved your post. Perhaps Teddy can find room in the FAQ. BTW - I've been intending to write FAQ stuff. I started one and it turned into a tutorial\ semi-fascist lecture on appropriate use of Auto Numbers. I am going to try again soon but not get so carried away
    That will definitely heat things up around here ... Quite a few STRONG opinions around here on those autonumbers and proper use thereof ...

    Let the flame wars begin
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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