Converting an MACRO to VBA

[Xx_TownDawg_xX]

Originally, for some stupid reason I took the easy way out and took advantage of using Access' new setTempVars feature.

BAD MOVE. It worked great, but today because of some changes to the application I am trying to add additional parameters (if you will) to the original code.

Field name is qDepartment. The event triggers on LostFocus. I used a combination of "SetTempVar" and this code:

deptstr, Switch([Forms]![frmMainMenu]![qDepartment]="Fabrication","[ShipFab]",[Forms]![frmMainMenu]![qDepartment]="Mirrors","[ShipMir]",[Forms]![frmMainMenu]![qDepartment]="Partitions","[ShipTPF]")

But now.. I have more departments.. so after WADING through and updating the original code, it's being truncated now because I exceed the 255 character limit I guess?

deptstr, Switch([Forms]![frmMainMenu]![qDepartment]="Fabrication","[ShipFab]",[Forms]![frmMainMenu]![qDepartment]="Mirrors","[ShipMir]",[Forms]![frmMainMenu]![qDepartment]="Partitions","[ShipTPF]",[Forms]![frmMainMenu]![qDepartment]="Hardware","[ShipHDW]",[Forms]![frmMainMenu]![qDepartment]="Accessories","[ShipACC]",[Forms]![frmMainMenu]![qDepartment]="Dryers","[ShipDRY]",[Forms]![frmMainMenu]![qDepartment]="ChildCare","[ShipCHI]",[Forms]![frmMainMenu]![qDepartment]="GrabBars","[ShipGRA]",[Forms]![frmMainMenu]![qDepartment]="SoapDispensers","[ShipSOA]")

I'm a newbie. I've got a pretty slick application, but trying to modify this and using an event procedure instead would be iffy at best. Don't get me wrong, I can write some "event procedure" VBA code, but I thought I had better leave this request up to the experts.

Would someone take pity on me?

 

[SOS]

I would just use a table to store the Department and what to use and then use a DLookup to get that. At least that's my thought. There may be better ways.

 

[Xx_TownDawg_xX]

Actually?.. that might work fine.. let me ponder that.

And what I like best about that idea, if it would be dynamic instead of hard-coded like it is now..