Link to home
Start Free TrialLog in
Avatar of James
JamesFlag for Canada

asked on

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
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

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
Avatar of James

ASKER

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
Well, as I wrote:

> Adjust 4 to the count of digits required.

So try with 3.

/gustav
Avatar of James

ASKER

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
Couldn't you provide your sample, please?

/gustav
Avatar of James

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of James

ASKER

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
You can try setting:

    Response = acDataErrContinue

/gustav
Avatar of James

ASKER

Hi Gustav,

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

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

James
Can you clearly define:
"doesn't work."
Avatar of James

ASKER

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
> "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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Jeff, spot on.

/gustav
Avatar of James

ASKER

Thanks, for the input, Gustav & Jeff.
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