Make form go to record after selecting radio button

[BJF]

Hi All,

I have a form that contains a listbox which is used to select a product and the form navigates to the selected record.
I also put 2 radio buttons on the form called Active and disactive. These buttons toggle what the listbox contains:

here is the code for one of the buttons:

Private Sub ActiveProducts_AfterUpdate()

Me.InactiveProducts = False
List324.RowSource = "qryStdCostListBoxActive"
Me.Requery

End Sub

When selected it changes the row source for the listbox to contain only active products.
Its very straightforward.

My problem is that the form doesnt navigate to any record of the active set. My listbox changes to active products and works when i select a record, however I
would like the form to go to say the first record of the active set. I thought the requery in my code would do it but im wrong!

What do i need to add to the code to get the form to go to the first record of the set?

Thanks for any advice,

BJF

 

[theDBguy]

What is the record source of your form? Can you match it with your listbox?

 

[Minty]

What is the code / method used to make the form go to the selected listbox record?

You would need to select the first listbox item (in code) and then fire the code from the listbox selection.

 

[BJF]

What is the record source of your form? Can you match it with your listbox?

Not sure how to go about that??
My record source for the form is just tblStandardCost, no query just the table

 

[BJF]

What is the code / method used to make the form go to the selected listbox record?

You would need to select the first listbox item (in code) and then fire the code from the listbox selection.

Hi,
The code is in an embedded macro

it uses a where condition:
="[ProductNum] = " & "'" & [Screen].[ActiveControl] & "'"

 

[Gasman]

I thought the requery in my code would do it but im wrong!

How are the two linked?, listbox source and form source?

 

[theDBguy]

Not sure how to go about that??
My record source for the form is just tblStandardCost, no query just the table

What is the SQL statement for qryStdCostListBoxActive? I was wondering if you could simply amend your code to something like this.

Private Sub ActiveProducts_AfterUpdate()

Me.InactiveProducts = False
List324.RowSource = "qryStdCostListBoxActive"
Me.RecordSource = "qryStdCostListBoxActive"
Me.Requery

End Sub

 

[BJF]

How are the two linked?, listbox source and form source?

Hi,

Control source of listbox is blank, I added a listbox from design view and the wiazrd runs and i choose navigate to a record on my form.
It auto sets up a macro to do such,

The record source for my form is just the table:
tblStandardCost

 

[BJF]

What is the SQL statement for qryStdCostListBoxActive? I was wondering if you could simply amend your code to something like this.

Private Sub ActiveProducts_AfterUpdate()

Me.InactiveProducts = False
List324.RowSource = "qryStdCostListBoxActive"
Me.RecordSource = "qryStdCostListBoxActive"
Me.Requery

End Sub

Hi,
Thanks, but this didnt work, i really thought it would too, it made perfect sense to me but it made all fields invalid with type## errors
I will keep trying at it.

 

[BJF]

Hi,
Thanks, but this didnt work, i really thought it would too, it made perfect sense to me but it made all fields invalid with type## errors
I will keep trying at it.

Hi DBguy,
if this helps,
the sql for the listbox is :

SELECT tblStandardCost.ProductNum AS [Product#], tblStandardCost.Inactive AS Active, tblStandardCost.FinishedWideWidth AS W, tblStandardCost.Description
FROM tblStandardCost
WHERE (((tblStandardCost.Inactive)=False));

 

[BJF]

Hi,
Thanks, but this didnt work, i really thought it would too, it made perfect sense to me but it made all fields invalid with type## errors
I will keep trying at it.

What is the SQL statement for qryStdCostListBoxActive? I was wondering if you could simply amend your code to something like this.

Private Sub ActiveProducts_AfterUpdate()

Me.InactiveProducts = False
List324.RowSource = "qryStdCostListBoxActive"
Me.RecordSource = "qryStdCostListBoxActive"
Me.Requery

End Sub

Hi DBguy,
if this helps,
the sql for the listbox is :

SELECT tblStandardCost.ProductNum AS [Product#], tblStandardCost.Inactive AS Active, tblStandardCost.FinishedWideWidth AS W, tblStandardCost.Description
FROM tblStandardCost
WHERE (((tblStandardCost.Inactive)=False));