Form Textbox value changes based on entry of another?

[djspod]

Hi,

As user has to enter the product traceability code in of the product he checks and inspects.
The code is always 10 characters long, a mix of letters and numbers.
I want the 5th letter and the last number every time.

In the example picture the code would generate W8 which is Eastwood.

My plan is for the Producing Site to auto populate the producing site after the users enters the code.

Any ideas folks, imagine a DLOOKUP would reference the table of sites, match the code to the site.
And a trim/left or right function to dissect the code to extract the 5th and 10th character?



Essentially do away with the Combo drop down of sites and replace with a textbox.



Sent from my SM-G935F using Tapatalk

[CJ_London]

you would extract the value as

mid(code,5,1)+right(code,1)

[andy49]

Do you always want the 5th and 10th character. I guess not otherwise it's exactly as you suggest solving it.




Sent from my iPhone using Tapatalk

[andy49]

A function should do the job


Function retstring (yourstring as string) as string.
Dim countletter as integer

For I = 1 to 10
If isletter(mid(yourstring,I,1))= true then
If countletter = 5 then retstring = mid(yourstring,I,1)
countletter =countletter+ 1
Endif

Next I
Count = 10
Do while isnumber(mid(yourstring,count,1) = false
Count = count-1
Loop

Retstring =retstring & mid(yourstring,count+1,1)

End function


The isletter function is borrowed from here

https://www.google.co.uk/amp/s/techn...n-for-vba/amp/



Sent from my iPhone using Tapatalk

[djspod]

Thanks for the quick replies.

Yes I do always want the 5th and 10th character. This will always produce a W and a number (W15 = Tuscany, W1 = Bolton, W13 = Wednesbury etc. etc.)

Number 13,14,15,18 would count as the 10th character and only as 1 character not 2 separate digits.

I'm new to VBA, How would I plot the code out? (Any help I would be grateful)

I'm guessing my code is wrong below but am I onto the right lines?

AfterUpdate Function??

DIM SiteCode as string

Sitecode = me.textbox.value = mid(code,5,1)+right(code,1)

Me.ProducingSite.value = DLookup("[Supplying Site]", "tblAllSites", "[SiteSAPNumber]='" Sitecode "'")

[andy49]

The tenth character can't be 15 in access. It's two characters? Do some end in letter 3 to be thee whilst others end 1 3 as thirteen.


Sent from my iPhone using Tapatalk

[djspod]

The codes will end in a number: 3 - Burnley or 8 - Eastwood, 12 - Variety Plant, 13 - Wednesbury, 14 - Enfield, 15 - Tuscany

Each site has a SAP code per site ie: W15 = Tuscany, W14 = Enfield

I hope that helps

[djspod]

Hi Andy 49,

Thanks for the Function retstring option, some of it makes sense.

I'm still fairly new to VBA, How would I program that in?

In the form code or a separate module?

Sorry but I'm not sure how to implement the code.

Thanks for your help

[andy49]

Last question. Can a code ever have 91 or 83 at the end.

And do they mean anything?

Is there a maximum number of places I guess?


Sent from my iPhone using Tapatalk

[djspod]

Hi Andy,

The code does mean something later down the line to our Quality Team and technicians

They have a code cracker in Excel that works out the code and it generates the Site it was produced, Time, Date and machine ID that made that product.

I will generate and Access report that will pickup all today's records and email them direct, the report record will have the full 10 digit (11 digit code if over 10 included)

For the general operative user, my thinking was:

He writes the full code into the textbox from the Product label.

The code will be 9 Characters long with either a single or double digit on the end.

4 of our producing sites have a SAP code of W1, W8, W3, W6 the rest have 2 digits on the end of the code : W13, W14, W15, W18

As the user enters the full code, I would like the Site to auto populate the 2nd textbox (Producing Site) with the matching site by referencing the W and Digit or digits on the end.

My Table AllSites has this information as below:

ProducingSiteID_PK	Supplying Site	SiteSAPNumber
31	Bolton	W1
32	Bristol	W18
33	Burnley	W3
34	Eastwood	W8
35	Enfield	W14
39	Newburn	W6
46	Stockton	W16
49	Tuscany	W15
50	Variety	W12
51	Wednesbury	W13
52	Gluten Free	W19

[djspod]

The code will only ever be the numbers after the W in above table, so no 91 or 83

I help that helps

Thanks

[andy49]

Ok I think I have it now

assuming text0 has the code

A text box (Ive called it text2) with

=Mid([Text0],5,1) & IIf(IsNumeric(Right([Text0],2))=True,Right([Text0],2),Right([Text0],1))

will find the letter and number (even if the number has two digits


Another text with

=DLookUp("[town]","table1","Table1.code= " & Chr(34) & [text2] & Chr(34))

where Table1 is the name of your table (ALLSITES)
[town] is the name of the filed with the places in [supplying site]????

[djspod]

Hi Andy,

Many thanks for your help.

Text2 is now working fine and getting the Sap code - that's brilliant!

I now have new text3 at the side of producing site label.
The Dlookup seems how fail here when working out the value in text2 for some reason?



Sent from my SM-G935F using Tapatalk

[andy49]

What code you using for dlookup



Sent from my iPhone using Tapatalk

[djspod]

Sent from my SM-G935F using Tapatalk