Help needed with DLookup

[Gicu]

Please feel free to download my free utility that allows you to setup variable levels of security (http://forestbyte.com/ms-access-util...access-levels/). You import the objects in your database and adapt it to use your existing user table.



Cheers,
Vlad

[Beanie_d83]

Thank you all for your feedback....I hope to put it to good use!

If yes you need to bind the textbox to the PArt_Number field and add code to the Current event of the form to check if new record and if yes make the control

Vlad,

I have tried the above and now the text box displays the number 1 so this to me certainly seems a step in the right direction! Should the On Current Event code be applied to the main form, or the sub form?

Thanks!

[Gicu]

Of the subform, but probably even better try to add it to the Default Value property of the textbox on the subform.

Cheers,
Vlad

[Beanie_d83]

Hi all!

I'm still having difficulties with this whole number increment business!!

My problem is that I'm not understanding the coding that needs to be written and where it needs to go! I've attached a copy of the DB if anyone wants to have look at what I'm trying to do!

Many thanks!

[Gicu]

Have a look at the updated sample. I have edited a couple of parts records for FD to show you how it would look when you populate the part numbers with your existing ones.

Cheers,
Vlad

[Beanie_d83]

Have a look at the updated sample. I have edited a couple of parts records for FD to show you how it would look when you populate the part numbers with your existing ones.

Cheers,
Vlad

This is what I wanted to achieve...only you have done it better as I was just going to settle for the number without the prefix appearing in the text box!

Thank you Vlad

[Gicu]

Great to hear you got it working!

Cheers,
Vlad

[Beanie_d83]

Well it's almost there, however I've noticed that some of the component types won't increment the number but instead displays #Error in the text box.

SELECT tbl_component_type.Component_Prefix, IIf(Nz([Part_Number],"0")="0",0,CLng(Mid([Part_Number],3,Len([Part_Number])-2))) AS NumericPartNumber, tbl_parts.Component_Type_ID_FK
FROM tbl_component_type INNER JOIN tbl_parts ON tbl_component_type.Component_Type_ID = tbl_parts.Component_Type_ID_FK
WHERE (((tbl_parts.Component_Type_ID_FK)=[forms]![Copy Of frm_component]![cboSelect_Component_Type]));

I'm now educating myself further on SQL code as there are some parts of the above that I don't quite understand!

[isladogs]

I haven't read the whole thread.
You seem to have a missing space before FROM though that may be a forum glitch - space added below
If you are using SQL, also try adding delimiters to the WHERE code as mentioned in an earlier reply by gicu

SELECT tbl_component_type.Component_Prefix, IIf(Nz([Part_Number],"0")="0",0,CLng(Mid([Part_Number],3,Len([Part_Number])-2))) AS NumericPartNumber, 
tbl_parts.Component_Type_ID_FK FROM tbl_component_type INNER JOIN tbl_parts ON tbl_component_type.Component_Type_ID = tbl_parts.Component_Type_ID_FK
 WHERE (((tbl_parts.Component_Type_ID_FK)=[forms]![Copy Of frm_component]![cboSelect_Component_Type]));

[Beanie_d83]

Must have been a glitch as checked for the space and it was there.

As I understand it, this query is taking the component prefix and then combining it with the incremented into the text box? What I can see happening is that all the double character prefixes work fine (PR, SP, LS...etc) but the triple character prefixes return #Error?

Is this even relevant to anything that's happening in the query code?

[Gicu]

Hi! Sorry, I haven't noticed you had three character prefixes as well. Have a look at the revised version, it replaces the Mid function with Replace.

Cheers,
Vlad

[Beanie_d83]

No problem at all, thank you kindly for taking the time to revise the code!

I am curious however to know why the "If" statement is so different between the two codes just for an extra prefix character?

2 Character Prefix:

SELECT tbl_component_type.Component_Prefix, IIf(Nz([Part_Number],"0")="0",0,CLng(Mid([Part_Number],3,Len([Part_Number])-2))) AS NumericPartNumber

3 Character Prefix:

SELECT tbl_component_type.Component_Prefix, IIf(Nz([Part_Number],"0")="0",0,CLng(Replace([Part_Number],[forms]![Copy Of frm_component]![txtComponentPrefix],""))) AS NumericPartNumber

[Gicu]

The first one was hard-coded for 2 characters ONLY using the Mid function to strip the first 2 characters:Mid([Part_Number],3,Len([Part_Number])-2).

The second one works with ANY characters using the Replace function to replace the Component Prefix with an empty string:Replace([Part_Number],[forms]![Copy Of frm_component]![txtComponentPrefix],"")

Cheers,
Vlad

[Micron]

I don't like this expression for a couple of reasons

IIf(Nz([Part_Number],"0")="0",0,CLng(Mid([Part_Number],3,Len([Part_Number])-2)))

I don't get the point of 'convert a null to 0 but make 0 a text data type, then compare it to 0 that is also a text data type, and if equal, return 0, which is a number. As for the rest, I skimmed over all the prior posts, but didn't see anything that indicates why you're wanting all of the number value except for the last 2 characters/digits. After composing this part, I see you've replied and I responded to the comment at the end of this post. Seems to me you don't quite grasp what the expression does, but I could be wrong. You might want to review its syntax. However, seems to me that even if that is what you want, let's look at the logic by evaluating the parts. I replaced the field reference with Null for cases where it is Null:

IIf(Nz([This Is Null],"0")="0",0,CLng(Mid([This Is Null],3,Len([This Is Null])-2)))
IIf("0")="0",0,CLng(Mid([This is Null],3,Len([This is Null])-2))) *so IIf will return 0 and Mid will return Null
IIf("0")="0",0,CLng(Null)-2

Pretty sure you cannot convert Null to a Long, which is likely at least 1 reason for #Error. I think the expression is also too complex. Rather than returning a text zero from Nz then comparing it to "0" just to arrive at using 0, just IIf(Is Null([Part_Number]),0...

The first one was hard-coded for 2 characters ONLY using the Mid function to strip the first 2 characters:Mid([Part_Number],3,Len([Part_Number])-2).

No, that says start at position 3, go to the right and return all the characters exept for the last 2.

I haven't kept up with the various db attachments, so I shouldn't suggest a fix or any other possible approach at this point. Maybe you'll fix it before I have the chance to try anything.

[Minty]

@Micron

Nz([ValueBeingChecked],"0")="0"

Weirdly I have seen this style of code on a number of answers on Access websites recently, and didn't really like the methodology employed.

I wonder if it's come from some example code somewhere, or someone has come up with some bizarre proof that this is super efficient ?