Change text input (1 Viewer)

access17401

Registered User.
Local time
Yesterday, 22:02
Joined
Aug 24, 2017
Messages
33
I think I am having a rough morning, searched the web and haven't found anything (but to be honest drawing a blank on the wording of what to search)

We have a data entry form, its high use hundreds of records a day. I am simplifying some aspects and would like some guidance on one.

The data entry people for each record inputs a category (drop down/combo box didn't suit). This starts with either WMD or CMD and is followed by a date aka 10102017.

Is there a way for that field to mask it so we could enter for WDM = 01 and CDM = 02.

So to speed up the process it will be 0110102017 instead of WMD10102017?

So I want to be able to type 0110102017 but as i tab to the next field WMD10102017 appears and this is the text that saves into our table.

Does this make sense? Can someone point me into the write direction please?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:02
Joined
Feb 19, 2002
Messages
42,981
I'm confused. Do you want to store what they typed or do you want to store the letters instead?
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:02
Joined
Aug 30, 2003
Messages
36,118
You could use an unbound textbox. In the before or after update event of that textbox, do your testing and validation and then populate a bound textbox with the actual desired data. Normally data is validated in the before update event, but since this isn't where the data will end up, it probably doesn't matter.
 

isladogs

MVP / VIP
Local time
Today, 05:02
Joined
Jan 14, 2017
Messages
18,186
WMD = Weapons of mass destruction?

The combobox or listbox idea is MUCH better. Why didn't it suit?
If the date is always the current date, you can just use e.g.

Me.Category=Me.Listbox & Format(Date,"mm/dd/yyyy") if US based
or
Me.Category=Me.Listbox & Format(Date,"dd/mm/yyyy") if UK based

All done in one click!

If the date can vary, use a calendar control & then
Me.Category=Me.Listbox & Format(Me.CalendarControl,"dd/mm/yyyy") if UK based

This is just 2 clicks
MUch quicker than typing 0110102017 instead of WMD10102017
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:02
Joined
Aug 30, 2003
Messages
36,118
I work a lot with "head down" data entry people. This is exactly the type of thing they would ask for. They hate taking their hands off the keyboard. And they'll whine to high heaven about 2 extra clicks. ;)
 

Mark_

Longboard on the internet
Local time
Yesterday, 22:02
Joined
Sep 12, 2017
Messages
2,111
It sounds like After you Update the field you want to change it.

First place to look would be the AfterUpdate event.

I would look at the RIGHT function and LEFT.

LEFT(String,2) would give you the first two characters.
Right(String(Len(String)-2) would give you the rest.

Code in your fields .AfterUpdate would look similar to the following:
Code:
IF LEFT(Me.TxtString,2) = "01" Then
   Me.TxtString = "WMD" & RIGHT(Me.TxtString,(LEN(Me.TxtString)-2)
Else
   Me.TxtString = "CDM" & RIGHT(Me.TxtString,(LEN(Me.TxtString)-2)
End If

pbaldy, as a touch typist I can relate to your users. Often I can type an entire entry quicker than using a mouse to select from a drop down.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 01:02
Joined
Jan 23, 2006
Messages
15,364
What else has to be entered? Seems you have a C or M followed by DDMMYYYY.
Perhaps if we knew a little more of your business context, readers may have some focused options/advice.
 

isladogs

MVP / VIP
Local time
Today, 05:02
Joined
Jan 14, 2017
Messages
18,186
I work a lot with "head down" data entry people. This is exactly the type of thing they would ask for. They hate taking their hands off the keyboard. And they'll whine to high heaven about 2 extra clicks.

OK so they can just type in the textbox
Anyone else could use the listbox & calendar control instead.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:02
Joined
Aug 30, 2003
Messages
36,118
OK so they can just type in the textbox
Anyone else could use the listbox & calendar control instead.

Okay, but keep those 2 out of the tab order. ;)
 

access17401

Registered User.
Local time
Yesterday, 22:02
Joined
Aug 24, 2017
Messages
33
Wow thanks for the response, i really did have one of those moments.

Unfortunately ridders nothing that exciting, basically warm and cold. Unfortunately the last couple (really my first couple) projects at my new job have had these kind of restrictions, having never used access before its been interesting to sort some of the issues. Essentially it just came down to speed of entry, the drop down/list would be somewhere near 30 at the moment with the different date fields.

haha definitely pbaldy, this new database has improved the process and processing time by a great margin over the old system but there has been a couple small things like this that have been complained about.

Thats exactly what i was looking for Mark cheers, I will implement tonight
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:02
Joined
May 7, 2009
Messages
19,169
you can further make the entry faster
by entering "W" or "C" followed by Month and
Date (in MMDD format) if it is current
year. otherwise "W" or "C" plus full date
if not current year.

then on AfterUpdate Event of the textbox:

Code:
Private Sub yourField_AfterUpdate()
    Dim strField As String
    strField = Me.yourField & ""
    ' check if W or C is entered plus MMDD
    ' if so, add the year
    If Len(strField) = 5 Then strField = strField & Year(Date)
    ' check if we are editing and WMD or WMC is already
    ' present in the field, if not add it
    If InStr(1, "WMD/CMD", Left(strField, 3), vbTextCompare) = 0 Then _
        strField = IIf(Left(strField, 1) = "W", "WMD", "CMD") & Mid(strField, 2)
    If Len(strField) <> 0 Then Me.yourField = strField
    
End Sub
 
Last edited:

access17401

Registered User.
Local time
Yesterday, 22:02
Joined
Aug 24, 2017
Messages
33
Hi guys can the answer provided by Mark be altered. To change text in the MIDDLE of a field IF the only constant in the field is the number after the text that needs changing

eg.

Could be *****01****
Could be ***01****

There is ALWAYS four number trailing the text i want to change, but can be a different amount before the code

IF LEFT(Me.TxtString,2) = "01" Then
Me.TxtString = "WMD" & RIGHT(Me.TxtString,(LEN(Me.TxtString)-2)
Else
Me.TxtString = "CDM" & RIGHT(Me.TxtString,(LEN(Me.TxtString)-2)
End If
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:02
Joined
May 7, 2009
Messages
19,169
Dim strToken As String
Dim strReplacement As String
strToken = Left(Right(Me.txtString, 6), 2)
If strToken = "01" Then
strReplacement = "WDM"
ElseIf strToken = "02" Then
strReplacement = "CDM"
End If
If strReplacement <> "" Then
Me.txtString = Mid(Me.txtString, 1, Len(Me.txtString) - 6) & strReplacement & Right(Me.txtString, 4)
End If
 

access17401

Registered User.
Local time
Yesterday, 22:02
Joined
Aug 24, 2017
Messages
33
Thanks arnelgp, I was close. So I understand why is the 'Right' in the line 'strToken = Left(Right(Me.txtString, 6), 2)' is that what changes it to read from the right?

I think when I was trying to do it I was trying something like 'strToken = Left(Me.txtString, - 6), 2)'
 

isladogs

MVP / VIP
Local time
Today, 05:02
Joined
Jan 14, 2017
Messages
18,186
Excuse me for butting in but arnel is offline ATM

Left(Right(Me.txtString, 6), 2) means
a) you are reading the last 6 characters in a string
b) then you are reading the first 2 characters in that 6 character string

So "Happy Christmas" becomes "istmas" becomes "is"
 

access17401

Registered User.
Local time
Yesterday, 22:02
Joined
Aug 24, 2017
Messages
33
Ow ok that's perfect thanks ridders.

Though no point in asking for help, if I don't learn and understand why.

Appreciate the explanation
 

Users who are viewing this thread

Top Bottom