Changing combo list based on button press

[shabbaranks]

Hi all..

Im trying to get my code so that when a button is pressed it will tick a checkbox which runs some code to display table values in a combo box. And then if its pressed again it reverts to the original options as per below:

Private Sub MinutesCheck_Click()
Dim strSQL As String
If Me.MinutesCheck = True Then
strSQL = "SELECT MinutesTable.MinutesInWords, MinutesTable.MinutesInFractions " & _
"FROM MinutesTable;"
Me.MinutesCombo.RowSourceType = "table/query"
Me.MinutesCombo.RowSource = strSQL
ElseIf Me.MinutesCheck = False Then
strSQL = "SELECT MinutesTable.MinutesInWords, MinutesTable.MinutesInFractions " & _
"FROM NegativeMinutesTable;"
End If
Me.MinutesCombo.Requery
End Sub

Private Sub NegativeMinutes_btn_Click()
If Me.MinutesCheck = False Then
Me.MinutesCheck = True
ElseIf Me.MinutesCheck = True Then
Me.MinutesCheck = False
End If
End Sub

So based on if the checkbox is ticked the relevant code should run. At the moment Im getting a blank combo box - any ideas why?

Thanks

 

[jdraw]

For your first snippet: THESE ARE UNTESTED

Private Sub MinutesCheck_Click()
Dim strSQL As String
If Me.MinutesCheck = True Then
	strSQL = "SELECT MinutesTable.MinutesInWords, MinutesTable.MinutesInFractions " & _
	"FROM MinutesTable;"
Else
	strSQL = "SELECT MinutesTable.MinutesInWords, MinutesTable.MinutesInFractions " & _
	"FROM NegativeMinutesTable;"
End If

'You have to set up the rowsource when you change the sql

Me.MinutesCombo.RowSourceType = "table/query"
Me.MinutesCombo.RowSource = strSQL

' and you requery the combo regardless of the rowsource

Me.MinutesCombo.Requery
End Sub

For the second (you seem to "switch True-> or False->True" so a simpler set up might be):

Private Sub NegativeMinutes_btn_Click()
 Me.MinutesCheck = Not Me.MinutesCheck
End Sub

 

[JANR]

If you want the code under MinutesCheck_Click() sub to run after you click on the button you also need to explicitly call it or els it would not run.

Private Sub NegativeMinutes_btn_Click()
 Me.MinutesCheck = Not Me.MinutesCheck
[COLOR="Red"]Call MinutesCheck_Click[/COLOR]
End Sub

However I think using the AfterUpdate_Event for a checkbox would be better.

BTW.

Setting the rowsource in code requeries the combobox so I don't think you need to do it twice.

JR