VBA code to set data in combo boxes using raion buttons

[tina hayes]

Good morning everyone
I am new to VBA, but I have come to a bit of problem.
I am using some radio buttons, which hold different lengths of dates one is permanentt and the others are 16yrs 10yrs etc.
I have a combobox, which holds options as to whether it is permanent or not. What I would like to be able to do is when i click on the permanent radio button I would like the combo box to show the word permanent and when I click on the others I would like the combo box to show the word non permanent.
This combo box is populated from a table.
There is no code currently behind these buttons

Please help

 

[Mile-O]

What's the RowSource of the combo?

 

[tina hayes]

row sourse

both the row sourse and the control source is pointing at the reason table which holds the options for combo

 

[Mile-O]

In that case, you have a design flaw over all else. This question about the combobox is the least of your worries.

Are you able to fix this structure, not permitted to, or not willing?

 

[tina hayes]

i am willing to fix this
not sure what you mean though
woudl you mind explaining more

 

[Mile-O]

What's your table structure - all tables?

 

[tina hayes]

table structures

the database is split in two
one database hold all data
the one i am working on holds the forms and code.

the database is linked together using the link tables manager

i believe that the table structure is all tables,
i must apoligise i am almost a novice

 

[Mile-O]

lol, by table structure I meant the fields of each table

i.e.

tblCustomers
CustomerID
Forename
Surname
DepartmentID

etc..

 

[tina hayes]

Table Structures

will this relationship screen shot help

????

 

[Mile-O]

It helps to confirm that you'll need to normalise your tables.

When someone says "main table" it's a fair bet they've made one big table where they store everything and other tables, if any, feed into it. That's what's happening in this case.

Although I don't know what everything does and can't see all the fields in the "main box" table I can see that you'll need a table for RespOfficer, to join your department table.

Can you make a copy of the database, delete all the records from it (maybe put a couple of sample ones in), .zip it up, and upload it?


Saying this, you do realise that normalising your table structure will impact upon everything else (queries then forms the reports and so on). I'd understand if you wouldn't want to go on with this and looked for a workaround to your initial question instead.

 

[tina hayes]

maybe a work around

maybe a workaound would be better for the moment,

if i get things working then i can go and sort the normalisation out later

you wouldnt be able to help me with my inital question would you?????.

i have just tried to zip down the dataase and it is still 7mb so i can upload this

i do appreciate the time you have given me though

 

[Mile-O]

I assume this is what you mean...

 

[tina hayes]

yeh thats what i wanted but two things

1) call me nieve but i can't figure out how you did it could you explain i have looked through the properties and can't find anything and there doesnt seem to be any code

2) i have 4 values inthe list

permanent is the highest catergogories and there ia radio button for permanent

the next highest is non permanent which selction for the rest of the buttons,
there is one more whch is business need
this wont be used much but i would like the option for it to be used under the non permanent radio buttons
so if there is a way that it sets itself to permant but then i could change it to business needed it

 

[Mile-O]

Maybe you have selected the radio buttons individually rather than using the option group object.

I set the default value of the option group to 1 (the first one, permanent).

On the update of the option group the event looks at the option group's value and says if it's one then make the combobox say permanent otherwise make it say non permanent

 

[tina hayes]

an absolute star

WOW thankyou so much it worked

you are a star

would you mind me coming back and asking for more help if i get stuck down the line

i am completely in debt

many thanks ifi ever meet you i will buy you a beer

 

[Mile-O]

would you mind me coming back and asking for more help if i get stuck down the line

That's what this forum is here for.

if i ever meet you i will buy you a beer

Everyone says that and then hides. I should be in a constant state of intoxication by now.

 

[tina hayes]

a new problem added to an old one

I have been looking at setting values in combo boxes by clicking the radio buttons.
MILe –o-pjhile helped me to get to where I am now and thankyou for that

BUT, a small problem has occured

Basically I have a set of combo boxes one which is names permanent and the others which are number of years.
When setting up the record what I wanted was if the permanent raio button was checked that permannet be selected from the combo box. If any other were selected for it to display non permanent.,

This now happened by the only problem I have now is that I have scroll buttons to take me to next record and then last record. Before I made this change what was in the combo box when you saved it the record was displayed in the combo box as you scroll through. But now what ever you selected by using the radio buttons stays with every record.

I have it set up as the option group which, the buttons sit in has a default value of 1.
The combo box has a row source type of list and the row source holds the four values.
The codee behind the option group is as follows
Private Sub Retention_AfterUpdate()

If Me.Retention = 1 Then
Me.CmbReason = Me.CmbReason.ItemData(0)
Else
Me.CmbReason = Me.CmbReason.ItemData(1)
End If
End Sub

Can any one help me this situation please

 

[Mile-O]

Put these lines in the Form_Current event instead:

If Me.Retention = 1 Then
    Me.CmbReason = Me.CmbReason.ItemData(0)
Else
    Me.CmbReason = Me.CmbReason.ItemData(1)
End If

And, where you've removed it from (the frame's afterupdate) put: Call Form_Current

 

[tina hayes]

confused

so in do i put the code something like this (the form_current event??????????)

Public Function form_current()
If Me.Retention = 1 Then
Me.CmbReason = Me.CmbReason.ItemData(0)
Else
Me.CmbReason = Me.CmbReason.ItemData(1)
End If
End Function

just in the gnereal section of the VBA code builder????

 

[Mile-O]

Yes, select the form's properties, find OnCurrent, click Code Builder and put those lines there.

Then all you need is this:

Private Sub Retention_AfterUpdate()
    Call Form_Current
End Subc