Page 3 of 3 FirstFirst 123
Results 31 to 36 of 36
  1. #31
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Update: look at the expression for Default Value of [Part Number] on the subform. If you copied the form from a posted solution db, it might be referring to "Copy of sfrm_parts" when it probably should be sfrm_parts. My suggestion to those who modify OP posted db's would be to copy any objects you wish to play with, and play with the originals rather than the copies. Control references, code, expressions - all sorts of references to the original object name can be broken unless you modify them to point to your copy. Don't see the point in that 'cause it's just unnecessary work if you play with the original. Here's one example of where an expression is no longer valid because the form name was changed and the original is gone.


    Code:
    =[Forms]![frm_component]![txtComponentPrefix] & (DMax("[NumericPartNumber]","[qryNumericPartNumbers]")+1)
    Gotta take a break for now, so I can't say if the expression will work if I fix the reference. I found what I think is another error in the code for the user_id combo event.

  2. #32
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Micron,

    With all due respect to you and your expertise, but rather then wasting your time bashing my post you could have look at the attachment(s) and maybe you would have understood why the expression looks like that. First of all, the OP posted the sample db with the forms named "copy of ..", so I used those as provided.

    And now to the expression: IIf(Nz([Part_Number],"0")="0",0,CLng(Mid([Part_Number],3,Len([Part_Number])-2))) AS NumericPartNumber

    A refresher about the MID function: https://www.techonthenet.com/access/...string/mid.php

    So if the [Part_Number] field contains SP5001 (as mentioned by the OP in post # 11) then Mid([Part_Number],3,Len([Part_Number])-2)=Mid([Part_Number],3,6-2)=Mid([Part_Number],3,4)="5001" the[Part_Number] field is set to text and in the sample OP provided in post #19 there were no values like that, only Nulls and "0"s therefore the Nz([Part_Number],"0")="0",0 part of the expression. And finally because we want to increment the numeric value of the part_number as suggested by the name of the field (in red above) I wrapped the Mid function in a CLng.

    Click image for larger version. 

Name:	Micron1.png 
Views:	12 
Size:	298.4 KB 
ID:	34073 Click image for larger version. 

Name:	Micron2.png 
Views:	13 
Size:	57.6 KB 
ID:	34074


    Hopefully this should clarify this issue. Beanie_d83, please note that in my latest upload I misnamed the two queries, hopefully you've figured it out (the one with the"_old" is the one with the Replace function that you want to use).

    Cheers,
    Vlad

  3. #33
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Apologies; no bashing intended. I will be honest and admit that I presumed you were the one who modified the form name (then again, why would I upload older copies from the OP that have been modified later on?). But I'm not going to engage in a tit-for-tat behavior regarding the things I take exception to in your latest post. That's what pm's are for, rather than chastising in a public manner.

    As for my "expertise" if I was so smart in this stuff, I would have my own website. Your contributions are just as valid.
    BTW, did you try choosing a Created By name for the new record with a new calculated part number?

  4. #34
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I was just trying to explain the expression and the reasons behind some of its "weirdness" (it wasn't copied from some internet site as somebody else suggested). Sorry if I sounded too harsh!
    Cheers,
    Vlad

  5. #35
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was just trying to explain the expression
    Really? Didn't sound like it:
    rather then wasting your time bashing my post
    Suggest we not sidetrack this thread any more than we have. I consider the matter closed and will focus on the thread and its solution from here on.

  6. #36
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Beanie_d83,

    Would you please download the attached (revised) file and have a look at the form again? I have moved the calculation from the default value of the textbox to the Form_BeforeInsert where you had it originally and it seems to work better there (you might want to add some error handling in there).

    Cheers,
    Vlad
    Attached Files Attached Files

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. DLookup Help Needed
    By krakalackin in forum Programming
    Replies: 4
    Last Post: 11-07-2014, 04:07 PM
  2. Help needed : Library catalogue in access format is needed
    By dealers in forum Sample Databases
    Replies: 3
    Last Post: 01-16-2014, 02:03 PM
  3. DLookUP Help Needed
    By Kirsti in forum Programming
    Replies: 2
    Last Post: 11-06-2012, 01:19 PM
  4. *URGENT HELP NEEDED* DLookUp Formula
    By iProRyan in forum Forms
    Replies: 1
    Last Post: 03-28-2012, 11:55 AM
  5. Dlookup help needed
    By mkb_cma in forum Access
    Replies: 12
    Last Post: 11-28-2011, 11:37 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