View only records from cbo result -

[Guest]

Hi,
I've got a form based on one table (tblMoves) with 4 fields - autonum, base
name, action and movements. I'm using a combo box (cboName) that looks up
the base name in the form header. For a majority of bases, multiple actions
and movements exist.

The frm detail section has the action and movements. I'm trying to limit
what is viewed so that when a base name is selected in the cboName box in the
frm header, only the actions and movements for that base are seen.

Is this possible being that all the data is in one table?

Thx!

chip

 

[sebt]

Hi chip

yep, you can do it like this, by changing the form's Recordsource in
the combo's After Update event:

Sub CboName_AfterUpdate()

If Me.CboName="" THen
'Show all records
Me.Recordsource="SELECT * FROM tblMoves (ORDER BY something if you
want)"
ELSE
Me.Recordsource="SELECT * FROM TblMoves WHERE [base name]=""" &
Me.CboName & """ (ORDER BY something if you want)"
End If

the condition for the blank combo box is so that you can blank it out
and show everything - you may have to set the combo's LimitToList
property to False to allow you to blank it out (can't remember: it may
be this is allowed whatever the LimitToList setting is).

cheers

Seb

 

[Guest]

Thx Seb - I've incorporated your code and have an additional question. My
sub cboName AfterUpdate already had the code that looked up the base names
and passed the value to the detail section. For clarification, my issues was
that the selected cboName value sort of pointed to the first record in the
detail section of the form - all other records were either above or below
this pointer.

My intent is to have the cboName value pull only those records for the
specific value. I may be close on combining your code with the existing
lookup - here's what I have - problem is that when a cboName value is
selected, the detail sections does not change -

Private Sub CboName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Name] = '" & Me![CboName] & "'"
Me.Bookmark = rs.Bookmark

If Me.CboName = "" Then
'Show all records
Me.RecordSource = "SELECT * FROM [tbl BRAC Actions Matrix] ORDER BY
'Name'"

Else
Me.RecordSource = "SELECT * FROM [tbl BRAC Actions Matrix] WHERE 'Name=
Me!CboName'"

End If
End Sub


Tnx Again -
chip

 

[sebt]

Hi Chip

I'm not sure why you're doing this bit:

Set rs = Me.Recordset.Clone
rs.FindFirst "[Name] = '" & Me![CboName] & "'"
Me.Bookmark = rs.Bookmark

on its own, this should just move the form through its existing
recordset, to the first record whose Name matches the value selected in
the combo. It's not necessary to do this to restrict the set to only
records matching a certain name.

The next bit of code

If Me.CboName = "" Then
etc etc

will override this operation, by changing the set of records shown by
the form (rather than just moving through an existing set), and moving
to the first record in that set. Because the form has been requeried,
the position of the form (which you set to the first matching record)
will be lost.

I'm stumped as to why, when you select something in the combo, nothing
changes in the form. Setting the form's Recordsource property always
results in a requery. Do you have the record movement buttons at the
bottom of the form (with the "1" of "450" records indication?) You
should see a difference there when you select a value in the combo -
less records.

cheers


Seb