Select case statement guidance needed

[Pablos]

Hi 'Access' community,

I love MS Access. It's a great tool. I'm self taught in it and use Access 2003.
I've been trying to put together a select case statement in a form unload event. I've tried different scenarios of using the Me keyword, puting control names in [] and I can't seem to escape a 'debug' error.
The VBA I have put together seems to do the task but not in a speedy manner.
Allow me to put the code here:-

Private Sub Form_Unload(Cancel As Integer)
    Select Case Me!txtTrunk_Element
    Case "10060" To "19999"
        Me.txtRoot_Name = "INCOME"
    Case "20000" To "29999"
        Me.txtRoot_Name = "BALANCE"
    Case "10000" To "10059"
        Me.txtRoot_Name = "IC_SALES"
    Case "40000" To "44999"
        Me.txtRoot_Name = "BUSUNIT"
    Case "45000" To "45999"
        Me.txtRoot_Name = "APINT"
    Case "50000" To "59999"
        Me.txtRoot_Name = "KPI"
    Case "60000" To "62999"
        Me.txtRoot_Name = "TAX"
    Case "63000" To "64999"
        Me.txtRoot_Name = "ADDINFO"
    Case "65000" To "69999"
        Me.txtRoot_Name = "STATNOTE"
    Case "70010" To "79999"
        Me.txtRoot_Name = "BUSACQ"
    Case "80000" To "89999"
        Me.txtRoot_Name = "ESTIMATE"
    Case Else
        Me.txtRoot_Name = "CHECK!!"
    End Select
End Sub

Too many cases/statements for a switch function. I've searched far and wide and had a go at putting together a Select Case Statement in VBA. Allow me to give a bit of background. The txtTrunk_Name is the name of the control on the form named "F_Hyperion Codes" and this form populates a text record in the Table "Hyperion Codes".
The txtRoot_Name is the name of the control on the same form. Aim is to use the trunk element to determine the root element.
I hope I'm not too far away with my efforts.
Really value any help/guidance to make it work smarter.
Xie xie Ni ! = Thank You !
I'm also learning Chinese!
Pablos

 

[boblarson]

If txtTrunk_Element is a text box on your form you should use the dot and not the bang:

Me.txtTrunk_Element

And second does txtTrunk_Element return numeric or text values? If numeric you need to get rid of the quotes in the case statements.

 

[ghudson]

If txtTrunk_Element is a text box on your form you should use the dot and not the bang:

Me.txtTrunk_Element

And second does txtTrunk_Element return numeric or text values? If numeric you need to get rid of the quotes in the case statements.

Bob, your back! ;-)

Strange for I have noticed more postings recently with users using the bang ! instead of the dot . in their coding when referencing text boxes.

 

[Pablos]

Thanks for the reply. Both text fields. All the me keywords now dot.

Greeted with a run-time error '-2147352567 (800200009) you can't assign a value to this object.

Both fields are not locked on form properties.

Am I missing something so simple?

 

[PNGBill]

Is TxtRoot_Name bound to a record source? If so, this may be the problem.

 

[boblarson]

Is TxtRoot_Name bound to a record source? If so, this may be the problem.

Yep, if the underlying recordsource is not updateable then that could present a problem.

 

[boblarson]

Bob, your back! ;-)

Took some time away to clear my head (which is hard to do of a stone block)

 

[PNGBill]

Took some time away to clear my head (which is hard to do of a stone block)

Compact and Repair?

 

[Pablos]

txtRoot_Name is the control name, it is bound to Root Name in my table called Hyperion Codes.
I started off with the control name as Root name but learned quickly that in vba it is better not to have spaces so i renamed to txtRoot_Name.

Could it be an issue that txtRootname is a combo box?

The idea behind all this is that the user puts in the info for a new record. If they get the root name wrong a bit of vba magic looks at it replaces it using the logic above. If I can get the vba working I'll lock the field then that way I can be assured all is as i would expect

 

[PNGBill]

Check Allow Edit is Yes on the control property.

 

[Pablos]

Thanks for thread reply PNGBill

Form property control is Yes to allow edit.
Both text boxes are yes to enabled and no to locked.
Am still puzzled.
It's got to be something simple - I'm gonna go away and try and recreate something simpler in a smaller access model...wish me luck! Should you have any other suggestions please throw them into the arena - cheers

 

[boblarson]

Again, if the values being returned by the combo box are numbers then you don't use the quotes in the Select Case Statements. You would use:

    Case 10060 To 19999
        Me.txtRoot_Name = "INCOME"
    Case 20000 To 29999
'...etc.

 

[PNGBill]

Try your code on an unbound text box control.

 

[Pablos]

Hi Bob,

I've tried case without "" on case statements, still greeted with a run-time error, this time says '2448 you can't assign a value to this object.

When I go into debug, the expression I have put together is in yellow, it's like the me.txtRoot_name = " whatever in here" is causing the headache. I'm trying to find some similar examples to compare to....

 

[boblarson]

Oh Crap! I hate it when we miss the little details.

You can't have this code in the form's UNLOAD event. The recordset is blown away by then. You need to have it in the form's BEFORE UPDATE event.

 

[Pablos]

Hi PNGBill,
I don't quite follow your suggestion.
'Try your code on an unbound text box control' Surely, I need them both bound as they populate my table?
My aim is on my Form use unload event take one text box and use it to determine a second text box by a set of rules..I can't quite get it right...

 

[PNGBill]

You don't need a control to be bound in order for the data to populate a table.

Did Bob's post resolve the issue?

 

[Pablos]

Bob - your a TOP man !

Thanks, that's done the trick.

I'm new to this forum - Do I need to close the thread? More importantly do you need me to give you positive feed back? More than happy to do so!
I can now watch the footy with a peaceful mind knowing my code is good. Not bad for a beginner!

 

[boblarson]

Bob - your a TOP man !

Thanks, that's done the trick.

I'm new to this forum - Do I need to close the thread? More importantly do you need me to give you positive feed back? More than happy to do so!
I can now watch the footy with a peaceful mind knowing my code is good. Not bad for a beginner!

No problem. No closing is necessary. And as far as feedback goes, this is sufficient. You can do the add to reputation but it won't really do anything until you have a lot more posts and/or time on the board. So, I wouldn't worry about that. This is fine.

 

[boblarson]

You don't need a control to be bound in order for the data to populate a table.

No, but you would need a bit more code to do that. Binding the control to the recordset is a much more efficient way to do it.