Combo Box which is linked to a textbox

[jdwazere]

Hi,

I was trying to build an access form which is similar to something I've already made on excel.

It was an automatic pricing program which works out the cost of a water tank once you enter the dimensions.

What I am trying to do is have a combo box which has 2 options, where the two options are the values that appear in two seperate calculation textboxes in the same form. I tried entering [calccompartments1];[calccompartments2] in the rowsource box but just displays the two names. I got it to work fine on excel, but I'm unsure on access.

 

[Mr. B]

You will need to use some VBA code to do what you want.

Here is a user defined function that first checks to be sure that there are values in the "calccompartments1" text box and the "calccompartments2" text box. If there are values in both text boxes, it gets the value from each text box and makes those values to be the values available in the combo box name "cboOptions". You can add this function to the module of your form. Just copy and paste this code just below the

Option Compare Database
Option Explicit

in your forms code window.

Function SetComboBoxOptions()
Dim strValues As String
If Not IsNull(Me.calccompartments1) And Not IsNull(Me.calccompartments2) Then
    strValues = Me.calccompartments1
    strValues = strValues & ";" & Me.calccompartments2
    With Me.cboOptions
        .RowSource = strValues
        .Enabled = True
    End With
Else
    With Me.cboOptions
        .RowSource = ""
        .Enabled = False
    End With
End If
End Function

You will need to change the "cboOptions" to the actual name of your combo box.

Then in the On Current event of your form, just use the following to call the function:

SetComboBoxOptions

If the values in the two text boxes can be changed in your form, you would need to place the above code in the After Update event of each text box so the values entered in the text boxes would be updated in the combo box.

Hope this helps.

 

[jdwazere]

Thanks for the reply.

I tried that but then it said "Microsoft Office cannot find the object 'SetComboBoxOptions"...

Come to think of it, no VBA code I've entered has ever worked on this database, is there something wrong with my settings?

 

[Mr. B]

Check your Macro settings. That could be the problem. You might also want to check your "Trusted Locations".