Limit record navigation buttons 1

[SeadnaS]

I want my navigation button to only be able to change the record to records listed in a bound combo box. Is this possible?

 

[PWise]

change your forms recordsource to be limited to the records in the combo box

 

[Gammachaser]

There are two ways to interpret what you are asking...

Do you want to:
1) Make the RecordSource of the Form the same as the RowSource of your ComboBox?
OR
2) Change the RecordSource of the Form based on what is selected in the ComboBox? Or, maybe just apply a Filter based on the Selection?

Both are possible (even easy, probably) but will provide signifcantly different results.

 

[SeadnaS]

I tried making the rowsource the same as the record source but it doesn't work.

How would I apply a filter based on whats listed in my combo box?

 

[SeadnaS]

When I use this:


Me.RecordSource = "SELECT LOT_NO FROM T_MASTER WHERE [Size_Type] = '" & Me.sizecombo & "' AND [TP] = '" & Me.typecombo & "' ORDER BY [LOT_NO] DESC"


All my other bound txt boxes and combo boxes stop working.

 

[TheAceMan1]

How are ya SeadnaS . . .

Whats the origional [blue]Record Source[/blue] of the form?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SeadnaS]

T_MASTER

 

[Gammachaser]

When you changed the RecordSource to:

Me.RecordSource = "SELECT LOT_NO FROM T_MASTER WHERE [Size_Type] = '" & Me.sizecombo & "' AND [TP] = '" & Me.typecombo & "'  ORDER BY [LOT_NO] DESC"

your other controls lost their bound field because you limited the return set to just LOT_NO.

Try this:

Me.RecordSource = "SELECT * FROM T_MASTER WHERE [Size_Type] = '" & Me.sizecombo & "' AND [TP] = '" & Me.typecombo & "'  ORDER BY [LOT_NO] DESC"

Or... you could use

Me.Filter = "[Size_Type] = '" & Me.sizecombo & "' AND [TP] = '" & Me.typecombo & "'"
Me.FilterOn = True
Me.OrderBy = "LOT_NO DESC"
Me.OrderByOn = True

That assumes that sizecombo and typecombo BoundColumns are text.

 

[SeadnaS]

Where do I put the the Me.Filter code?

 

[Gammachaser]

You would put it in the AfterUpdate Event procedure for BOTH ComboBoxes.

Private Sub SizeCombo_AfterUpdate
Me.Filter = "[Size_Type] = '" & Me.sizecombo & "' AND [TP] = '" & Me.typecombo & "'"
Me.FilterOn = True
Me.OrderBy = "LOT_NO DESC"
Me.OrderByOn = True
End Sub

Private Sub TypeCombo_AfterUpdate
Me.Filter = "[Size_Type] = '" & Me.sizecombo & "' AND [TP] = '" & Me.typecombo & "'"
Me.FilterOn = True
Me.OrderBy = "LOT_NO DESC"
Me.OrderByOn = True
End Sub

That way a change of either one will change the recordset.

A little better, but slightly more complex is to make the changes in a subroutine called from both events:

Private Sub SizeCombo_AfterUpdate
ChangeRecordset
End Sub

Private Sub TypeCombo_AfterUpdate
ChangeRecordset
End Sub

Sub ChangeRecordset
Me.Filter = "[Size_Type] = '" & Me.sizecombo & "' AND [TP] = '" & Me.typecombo & "'"
Me.FilterOn = True
Me.OrderBy = "LOT_NO DESC"
Me.OrderByOn = True
End Sub

Either way will work. The second is best if you have to make changes later.

 

[TheAceMan1]

SeadnaS . . .

Realize ... if you had a [blue]Record Source[/blue] for the form that matched that of the [blue]RowSource[/blue] of the combobox, you wouldn't need the combo! If you could do what you want (that is filter the forms recordsource same as the combo), then what is the combobox for?

By standards the combo is probably meant to filter the entire table, according to selection ... so I'm suggesting you do just that ... get the combo to filter properly, instead of concentrating on setting a new [blue]recordsource[/blue] for the form.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SeadnaS]

Its ok now aceman, i dont really understand what ur saying, but i got gammachasers code to work perfectly so all is good.