Padding a value before adding it to a table via a combo box

I have a table of classes that are all three-digit numbers (padded with leading zeros).

I want to allow the user to select a value via a combo box but also to type it in. The complication is that the user is likely to not type the leading zeros. So I want to pad their entry with leading zeros before validating it against the table. I also want the user to be able to add values to the underlying table through the combo box. But again, their entry needs to be padded with leading zeros first.

If I don't care about padding it with leading zeros, then I can use this code and it works perfectly:

----------------------------------------------

Private Sub Class_NotInList(NewData As String, Response As Integer)
   
Dim strTmp As String
   
    'Confirm that this is not just a typing error.
    strTmp = "Add '" & NewData & "' as a new class?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
   
        'Append the NewData as a record in the classes table.
        strTmp = "INSERT INTO tblClasses ( Class ) SELECT """ & NewData & """ AS Class;"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
       
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    End If

End Sub

----------------------------------------------

Any idea how to modify this to pad their entry with zeros both before validating it against the table and also when they're entering a new value?

Thanks in advance!

James
jrmcanada2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Try this:

Dim strTmp As String
   
    'Confirm that this is not just a typing error.
    ' Pad NewData with leading zeroes for a total of 4 digits.
    NewData = Format(Val(NewData), "0000")
    strTmp = "Add '" & NewData & "' as a new class?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
   
        'Append the NewData as a record in the classes table.
        strTmp = "INSERT INTO tblClasses ( Class ) SELECT """ & NewData & """ AS Class;"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
       
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    End If

End Sub

Adjust 4 to the count of digits required.

/gustav
0
jrmcanada2Author Commented:
Hi,

I've tried that but it has a few problems. For instance, if they enter 4, it asks them if they want to add it to the list even though 004 is already in the list.

James
0
Gustav BrockCIOCommented:
Well, as I wrote:

> Adjust 4 to the count of digits required.

So try with 3.

/gustav
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jrmcanada2Author Commented:
Hi Gustav,

I did try it with three before responding. It gives the error. If you make a quick sample form and try the above code, you'll see what I mean.

James
0
Gustav BrockCIOCommented:
Couldn't you provide your sample, please?

/gustav
0
jrmcanada2Author Commented:
Sure! Here's a sample. Just enter a few characters for the Job Number and then press tab to get to the class field. Type 5 and try tabbing out of the field. It will ask you if you want to add "005" to the classes table even though "005" is already in the classes table.

Thanks again for your help!
sample.accdb
0
Gustav BrockCIOCommented:
I can see that.
You could work around like this:

Private Sub Class_NotInList(NewData As String, Response As Integer)

    Dim strTmp As String
   
    'Confirm that this is not just a typing error.
    ' Pad NewData with leading zeroes for a total of 4 digits.
    NewData = Format(Val(NewData), "000")
    If IsNull(DLookup("Class", "tblClasses", "Val(Class) = " & Val(NewData) & "")) Then
        strTmp = "Add '" & NewData & "' as a new class?"
        If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
            'Append the NewData as a record in the classes table.
            strTmp = "INSERT INTO tblClasses ( Class ) SELECT """ & NewData & """ AS Class;"
            DBEngine(0)(0).Execute strTmp, dbFailOnError
        End If
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    Else
        Me!Class.Text = NewData
        Me!Description.SetFocus
        Response = acDataErrContinue
    End If

End Sub

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jrmcanada2Author Commented:
Hi Gustav,

That is excellent! Thank you!

One last little touch. When they use that routine to create a new list item, it does update the underlying table but it still gives them a "Not in list" error and they have to then go to the list and select the item they just created. Any idea how I can get around that?

James
0
Gustav BrockCIOCommented:
You can try setting:

    Response = acDataErrContinue

/gustav
0
jrmcanada2Author Commented:
Hi Gustav,

Sorry for the delay, but I've been away.

Regrettably, that doesn't work. I'll keep tinkering.

James
0
Jeffrey CoachmanMIS LiasonCommented:
Can you clearly define:
"doesn't work."
0
jrmcanada2Author Commented:
Sure ... if I enter an item that is already in the list, it works fine (as did the code you supplied me with a few steps back when you listed the whole routine). But if I enter a new item, the following things happen:

1. It asks me if I want to add the padded value to the list. (Which is correct ... it should do that.)

2. When I click "Yes", it opens the Class drop-down list but the new item isn't in it.

3. If I then try going to the next field, I get a pop-up error that says: "Run-time error 2057. There isn't enough stack memory left to perform the operation."

4. It then tells me again that the item isn't in the list.

5. If I go back into the list and select an existing code, I can then save the record.

6. The next time I pull up the list, the new item is there.

It's possible I put "Response = acDataErrContinue" in the wrong place. What I did was take your code (Posted on 2013-10-03 at 08:25:36) and changed "Response = acDataErrAdded" to "Response = acDataErrContinue" since there was already a "Response = acDataErrContinue" in the Else section.

James
0
Gustav BrockCIOCommented:
> "Run-time error 2057. There isn't enough stack memory left to perform the operation."

I can confirm this. Looks like a bug.

That said, I've never liked nor used the option to just add values to a combobox. I always use some other method - like a popup or another form - to add, delete, or change such options.

/gustav
0
Jeffrey CoachmanMIS LiasonCommented:
jrmcanada2,

Typically if cactus_data cant resolve something then it generally cannot be done.
;-)

But lets go back....

You may have to clearly define "padding"
Technically "Padding" means actually inserting these characters.
So we need to be clear that "Formatting" means storing one value, but displaying it in a different way.
This is not the same as "padding"

The other thing here is that these values are stored as "Text", ...not numbers.

Thirdly, you want the user to enter a one or two digit *Number*, then pad the stored *text* with leading zeros.

This is all too much work IMHO.

<The complication is that the user is likely to not type the leading zeros. >
Then this is the issue.
Then your only option, would be to pad the value as soon as they enter it, ...then ask them if they want to ad this new "padded" value.
(But systems like this always leave you vulnerable to other user mistakes)


If you really want to go through all this work, then create a system to detect what the user typed in, then force the leading zeros in..., then add the values after a confirmation.

So, set the "Limit to list" property of the control to *No* (again, possibly very dangerous), ...and do this all manually...
On the After Update event of the control do something like this:

'Declarations
Dim strTmp As String
Dim strYourVal As String

'Load with the typed in value
strYourVal = Me.Class

'Validate for numbers
    If Not IsNumeric(strYourVal) Then
        MsgBox "You must enter a numeric value."
        Me.Class = ""
        Exit Sub
    End If
       
'Pad the Value
    If Len(strYourVal) = 1 Then
        strYourVal = "00" & strYourVal
    ElseIf Len(strYourVal) = 2 Then
        strYourVal = "0" & strYourVal
    ElseIf Len(strYourVal) = 3 Then
        strYourVal = strYourVal
    Else
        MsgBox "Invalid value length"
        Exit Sub
    End If

'Ask to add the value if it does not exist
If DCount("Class", "tblClasses", "Class=" & "'" & strYourVal & "'") = 0 Then
    If MsgBox("Add " & strYourVal & " as a new class?", vbYesNo + vbQuestion, "Not in List") = vbYes Then
        'Append the NewData as a record in the classes table.
        strTmp = "INSERT INTO tblClasses ( Class ) VALUES(" & "'" & strYourVal & "'" & ")"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
        Me.Class.Requery
        Me.Class = strYourVal
    Else
        Me.Undo
    End If
End If

But again, I am not a big fan of doing things like this...
My feeling is that you should educate your users on the the correct data entry procedure.  Either that or simple Don't let them enter new values...
I mean if they don't know what value to type in, then how would they know if a value *should* be added?
For example what if they typed in a "R"
They could actually run this code an have a new class of "00R"
Now you have to create *even more* code to validate number only...
...Now what happens when they type in 5412???
See where I am going with this...?
;-)


Again this is all way too complicated IMHO.
If I did not trust users to enter the correct values, then I would not trust them enter new values...

Hope this all helps

JeffCoachman
sample.accdb
0
Gustav BrockCIOCommented:
Thanks Jeff, spot on.

/gustav
0
jrmcanada2Author Commented:
Thanks, for the input, Gustav & Jeff.
0
Jeffrey CoachmanMIS LiasonCommented:
Just notes my comments on the hazards of doing this...

I present this  not as a true "solution", but rather as an example of how complicated and dangerous this can become if users cannot be trusted to enter new values correctly, but somehow are intrusted with entering new data nonetheless.

Jeffcoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.