Multi combo box problem

[g4b3TehDalek]

Hello AF,

I'm fairly new to Access and have been mostly learning on the job (aside from buying a couple books and a training program from TrainSignal which is amazing btw). I was reluctantly promoted to DB Designer even though I pleaded to my boss that I have never created a DB on my own...but he has faith in me so I don't want to let him down.

He has had a lot of requests that I've been able to deliver on thus far, but now there's one that I can't seem to figure out. I've found similar problems in other threads here but nothing that works for me bc the posters needed to take it a step further. And were much more advanced than me...lol.

Sry...I'm a chatter box (online...not irl )...onto my problem. I'm not 100% on the Access lingo, so plz...be kind

I have a form that needs to show the BaseSalary based on to other criteria. First, they need to select a value from CB1, then that will populate CB2, which will display the BaseSalary value.



I've created a query (and a table if that helps) that has 3 columns: Step, Column, and Base. So I need the users to select a Step value, then a Column value, and then it displays Base.

Any help is much appreciated. Thx.

[pbaldy]

Is this what you're after?

Baldy-Cascading Combos

[g4b3TehDalek]

Yes!! That is what I need. This is a perfect springboard for me. Next I just need it to return the value from a third column. I'm gonna study the expression for a bit and hopefully I'll be able to figure that part out. If not hopefully I can bug u guys a bit more...lol. Ty very very much for the help (I've bookmarked ur page too) and for the clarification on the terminology

[pbaldy]

No problem, and welcome to the site by the way! Post back if you get stuck.

[g4b3TehDalek]

Right on bro. Thx for the warm welcome and all of the help. They're both very much appreciated

[g4b3TehDalek]

Paul,

Good news and bad news. The good news is after a day not only did I get it to work, I was also able to impress my boss!! And the bad news is....it turns out that it wasn't the solution I needed Lulz. It turns out I need to figure out how to do a DLookUp based on 2 combo box choices. I'll definitely be posting again if (when?) I get stuck.

But I just wanted to close this thread and thank you once more for your help.

One more favor tho....how do you mark a thread as Solved? <--n00b Alert!!! lol

[pbaldy]

Happy to help. If this is what you were trying to do, this method is more efficient than a DLookup (though you might not see the difference):

BaldyWeb - Autofill

To mark a thread solved, click on Thread Tools and then "Mark this thread as solved".

[g4b3TehDalek]

Wow...u r the man Paul!!

That's an amazing option I didn't even know existed. It is also along the lines of what I need my form to do, but I think my problem is lying in the way the data is stored.

The source data I'm using is from an old Excel spreadsheet that I had to reformat in a way that I'd be able to retrieve the Base Salary in Access. Now I have several records that start with the X & Y values (1, 2, 3..., I, II, III...etc.) and it's causing me so many problems with every solution I've tried so far.

I've attached a mostly-stripped down copy of the DB I'm making in order to add clarification. Please be kind, as this is the first DB I've ever created on my own

[pbaldy]

I'm not clear on what I'm looking for, but perhaps you want both methods. The first combo they select a step, which narrows down the options in the second combo. That combo would use the second technique above to populate salary once the column was chosen. Is that about right?

[g4b3TehDalek]

I'm sorry. I was very tired last night when I wrote that. I should've clarified. The Step and Column combo boxes will already be populated. What I need is when someone selects a Step and Column, the Base value will populate the BaseSalary text box. It will be pulling the information from qrySalaryScheduleCertificated (or the table if that is easier).

For example:
Step: 11 & Column I = $50,170
Step: 5 & Column II = $47,332

Hopefully that helps. Thx.

[pbaldy]

I'm confused: "The Step and Column combo boxes will already be populated" vs "when someone selects a Step and Column". Are we doing this after the fact or as they select from the combos?

[g4b3TehDalek]

Sorry. I'm having trouble with my words today Lol. The reason I said they will already be populated is because they will already have the values 1-35 in the Step cb, and I-VI in the Column cb. But yes, I would like it to display the Base after the first 2 values are selected.

Heres the expressions I've been using in the BaseSalary Control Source.

DLookup("[Base]", "qrySalaryScheduleCertificated", "[StepCert]='" & Forms![frmEmployeeInformation].Form.[StepCert] & "' AND [Column]='" & Forms![frmEmployeeInformation].Form.[Column] & "'")

With the above expression, the salary fields are blank until I've selected both a Step and a Column, just like I want. However they populate with "Error" and "Type" in the salary fields afterwards.

DLookup("[Base]", "qrySalaryScheduleCertificated", "[StepCert]=" & Forms![frmEmployeeInformation].Form.[StepCert] & "AND [Column]=" & Forms![frmEmployeeInformation].Form.[Column])

This expression has given me the most common result in my testing. The salary fields are prepoplulated with "Error" and "Type". And when a Step and Column are selected the BaseSalary field begins to flash infinitely. As if it's looking for something but can't quite find it.

Ty very much for being so patient with me.

[pbaldy]

I'd probably go with the cascading combos, but you know your situation better than I do. The problem with your DLookup's is that you are either treating both fields as text or both as numeric. In your case, you have one of each. Try:

DLookup("[Base]", "qrySalaryScheduleCertificated", "[StepCert]=" & Forms![frmEmployeeInformation].[StepCert] & " AND [Column]='" & Forms![frmEmployeeInformation].[Column] & "'")

By the way, "Column" isn't a great field name because Access could confuse it with the Column property. Bracketing it will probably work around that, but I'm just throwing it out there.

[RayMilhon]

The other problem with Column as a field name is next year when they want some upgrades. You'll look at that and say what the ?ell is that field. Been there done that. My column names typically look like this mnyBaseSalary which means it's a currency field for base salary.

[g4b3TehDalek]

Mr. pbaldy...I've said it before and I'll say it again. U R THE MAN!!! That works absolutely perfect!! I don't even know how to begin thanking you for your help.

Thanks to both of you for the advice on the naming conventions. I'll absolutely change my field names. The only reason I used the name Column is because it was on the old spread sheet. I'll be using the "mny" prefix here on out.

Again, my sincerest thanks to you and AF.