Adding "ALL" to My Combo Box

[sahaitu]

Greetings:

I have scoured the internet and gotten several useful tips from the experts here, though, I still have the same problem.

I have a form with a combo box with a rowsource connected to tblState which is a simple state lookup table. The rowsource is as follows:

SELECT tblState.idnStateID, tblState.StateLong FROM tblState UNION SELECT "*", "<All>" FROM tblState ORDER BY tblState.StateLong;

The bound column is "1". This combo box is linked to a subform by idnStateID that displays cases from tblcases with a foreign key of idnStateID.

Each time I select <All>, the subform only shows cases from the state of Alabama, which is idnStateID "1".

I also have the following code behind the form in VBA:

Private Sub cmdClearFilter_Click()
Me.FilterOn = False
End Sub

Private Sub Form_AfterUpdate()
Dim strMyData As String
Dim strMyFilter As String
strMyData = Me![Combo9]
strMyFilter = "[idnStateID] = '" & strMyData & "'"
Me.DataEntry = False
Me.Filter = strMyFilter
Me.FilterOn = True

End Sub

Private Sub Form_Close()
Me.Filter = vb
End Sub
Private Sub Combo9_AfterUpdate()
If Nz(Me!Combo9, "*") = "*" Then
Me.FilterOn = False
Else
Me.Filter = "idnStateID=" & Me!Combo9
Me.FilterOn = True
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
End Sub

Private Sub Form_Unload(Cancel As Integer)
Me.Filter = vbNullString
Me.FilterOn = False
Me.DataEntry = True
End Sub

I would truly appreciate any help on this. My deadline is Friday.

Thanks in advance. Sahaitu

 

[KenReay]

Hi

Would it not be easier to dump the use of Filter, and instead set the recordsource of the sub form to be a query with a WHERE clause depending on the Combo

eg WHERE StateId Like Forms!MyMainFormName!cboStateId

In the OnLoad event of the main form put code like so

If cboStateId.ListCount > 0 Then
cboStateId = cboStateid.Column(0,0)
Me.MySubFormControlName.requery
End If

in the after update event of cboStateId put:

Me.MySubFormControlName.requery



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sahaitu]

This sounds great. Quick Question: How will I include the selection "All" in the combo box?

Thanks

 

[KenReay]

Hi

You have almost given the answer to this yourself in the original question

SELECT tblState.idnStateID, tblState.StateLong FROM tblState
UNION
SELECT "*" As A, "<All>" As B FROM tblState ORDER BY tblState.StateLong;


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pavla]

Does not the SELECT statement:
SELECT tblState.idnStateID, tblState.StateLong FROM tblState UNION SELECT "*", "<All>" FROM tblState ORDER BY tblState.StateLong;

generate a list of all your states from the tblState and then as many lines of “<All>” as there are records in the tblState?

To add only one *, <All> line to your combo box I would create a new table, tblOneRecord, in your database (even if the rest of your tables are linked, keep this one with the structure part of the database) with two fields, Link and Description. Enter * in the first field and <All> in the second field in the one record this table contains.
Then your SELECT statement for the RowSource of the combo would read:
SELECT tblState.idnStateID, tblState.StateLong FROM tblState
UNION
SELECT tblOneRecord.Link, tblOneRecord.Description FROM tblOneRecord ORDER BY tblState.StateLong;

Then, in the AfterUpdate event of the combo box:
If combo = “*” then
Me.SubformName.FilterOn = False
Else
Me.SubformName.Filter = “idnStateID = “ & combo.Column(0)
End if
Me.SubformName.Requery

Pavla

 

[KenReay]

Hi

"Does not the SELECT statement:
SELECT tblState.idnStateID, tblState.StateLong FROM tblState UNION SELECT "*", "<All>" FROM tblState ORDER BY tblState.StateLong;

generate a list of all your states from the tblState and then as many lines of “<All>” as there are records in the tblState?"

No it produces a line for each row in tblState, plus one row containing *,<All>


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK