Update Combo based on integer field

Hibba

Registered User.
Local time
Today, 03:45
Joined
Jan 6, 2005
Messages
23
How can I update a combo box on my form to show one of two tables, depending on what number the user enters for the first field (integer)

So something like an IF statement for the combo box:
If number field <10, show lower table in combo box.
If number field >10, show upper table in combo box.


Thanks!
 
Hibba,

Use the AfterUpdate event of the NumberField control:

Code:
If Me.NumberField < 10 Then
   Me.Combo.RowSource = LowerTable
Else
   Me.Combo.RowSource = UpperTable
End If

Wayne
 
If it's really that easy, I'm going to cry...
 
Ok, not too familiar with access syntax, so would I chane what you wrote to something like this:

If Me.[Cabin] < 10 Then
Me.[Skill Code].RowSource = [Lower Skills]![Skill_ID]
Else
Me.[Skill Code].RowSource = [UpperTable]!{Skill_ID]
End If

So [Cabin] is the name of the integer box, and [Skill Code] is the name of the combo...
 
Hibba,

Don't start crying yet ...

Code:
If Me.NumberField < 10 Then
   Me.Combo.RowSource = "Select SkillID From LowerTable Order By SkillID"
Else
   Me.Combo.RowSource = "Select SkillID From UpperTable Order By SkillID"
End If

Wayne
 
Sorry, I'm pretty new to coding in Access, so this doesn't make clear sense to me.

I am assuming that I use the code you have to derive my answer, and not just copy paste to the expression builder...

Let's pretend I'm stupid (pretend, ha)
 
Hibba,

Get your form in Design View. Right-Click on the NumberField control and
select Properties. Go to the Event Tab. Look for AfterUpdate. Use the
Dropdown list and select [Event Function]. Just to the right of that click
on the three dots and put the code there.

The code will run and reassign the RowSource whenever the NumberField
is updated.

Wayne
 
Ok, I understand that, put I will need to change things, correct?

Example:

NumberField should actually say what the integer field is called.
Also, do I leave "Select SkillID From LowerTable Order By SkillID" just like it is or do I need to do some adjustment, like brackets and stuff..

Thank you!
 
Hibba,

"Select SkillID From LowerTable Order By SkillID"

SkillID should be the column in your table that is displayed. If it is not
SkillID, change it.

LowerTable should be the table that has the list of lower skills, substitute
the names of your tables for LowerTable & UpperTable.

You only need brackets if you have names with Spaces (not a good idea).
Try to stay away from spaces and special characters like "#", "/" etc.

Wayne
 
Ok, cool, I was right on with that one. Now, would I do the same for numberfield and combo? Replacing numberfield with the actual name of that field?

Thanks, you deserve a raise!
 
Ok, GREAT, this is starting to work.

Now, when I click in the Skill ID, it does not select it in the combo box, it just goes back to being blank. Also, I am going to do this action 6 times in my form, so do I need to put in different code for all 6 combo boxes?
 
Scratch that, bad stuff leftover from doing something before...

Now, what I would really like to do is have the combo box to show the skill name after I select the skill id and also have the skill name be sent to the table
 
Hibba,

Can you post a sample?

Just remove unwanted stuff,
Then Tools --> Database Utilities --> Compact/Repair
Then ZIP
Then attach.

Wayne
 
Attached is what I have.

the skills 1-6 are what I had, and the skill_id at the bottom is what I am working with. I have already found a few bugs I need to work out. For instance if you lookup to a specific record, then it will show all the skills (because i have that set as the default value) So would I add some similiar code to the onenter of the combo? Also, the way it is setup now, I would see the skill codeon the form, but it would send the skill name value to the table. However, it would be nice to see the skill name in the form after I have entered it.

Thanks.
 

Attachments

Users who are viewing this thread

Back
Top Bottom