navigating through records using combobox list

[davyre]

hi, I got this form


where I got three combo boxes : OrderID, UnitID, and PartID. The form's data source was populated from a table using a query.

What I want is to navigate through records based on OrderID or UnitID or PartID, which I can choose from the combobox, otherwise I have to click next record button to move to next record (which means pain if the records are thousands).

How can I do that? I have tried to use the Property Sheet for each combo boxes to populate the value list but it failed (it doesnt show the value that I wanted). Any help will be much appreciated. Thanks!

 

[davyre]

Update: In the RowSource property, I put the Query

SELECT DISTINCT OrderID FROM TmpTblOrderUnitPartStatus;

Now it is showing the values that I want, but still I cant navigate through the records by selecting different values in that combobox. What I am afraid is that when I select different value in the Order ID combo box, it overwrites the OrderID value in the source table. How can I ensure that does not happen? Or it wont happen? Thanks

 

[PHV]

For me, a ComboBox used as a navigation tool shouldn't be bound.
A common way is to place it in the header section of the form.
Tip: use the wizard to create it.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

Thanks PHV, I used the wizard and it helps me a lot. But I got this tiny problem,
for example when I choose Order ID 1, it should have populated the Unit ID and Part ID for the OrderID=1. But it seems I have to manually click the refresh button to refresh the list. How can I skip the refresh button?
here's the Row Source for UnitID combobox:

SELECT DISTINCT UnitID FROM TmpTblOrderUnitPartStatus WHERE TmpTblOrderUnitPartStatus.OrderID=OrderID.Value;

 

[PHV]

In the AfterUpdate event procedure of the OrderID combobox:
Me![name of UnitID combobox].RowSource = Me![name of UnitID combobox].RowSource

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

still it does not solve my problem, as depicted below:


What I did was first selecting Order ID 1, which has Unit ID 1 and 51. I chose Order ID 1 and Unit ID 1 and doing stuffs there, and when I finished, I selecting Order ID 2, which has Unit ID 51 and 149, but not 1. But in my case, it still showing 1 (from previous OrderID 1) which would messes up with the table's content. How can I solve this?
In the AfterUpdate event in the OrderID combobox I already put

Me![UnitID].RowSource=Me![UnitID].RowSource

but still no avail.

 

[PHV]

Use this instead:
Me!UnitID.RowSource = "SELECT DISTINCT UnitID FROM TmpTblOrderUnitPartStatus WHERE OrderID=" & Me!OrderID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

That code is supposed to be in the OrderID's AfterUpdate() right?
Still showing unwanted value:

I suspect that I have to do something with ControlSource? But that will make the combobox become bounded right?

 

[PHV]

And this (in the OrderID's AfterUpdate()) ?
With Me!UnitID
.RowSource = "SELECT DISTINCT UnitID FROM TmpTblOrderUnitPartStatus WHERE OrderID=" & Me!OrderID
.DropDown
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

It shows error 2185 You can't reference a property or a method for a control unless the control has focus.

 

[PHV]

OOps, sorry:
With Me!UnitID
.RowSource = "SELECT DISTINCT UnitID FROM TmpTblOrderUnitPartStatus WHERE OrderID=" & Me!OrderID
.SetFocus
.DropDown
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

It only shows dropdown of combobox list but then I have to actually manually click the value in the list.
But I found the solution using

Me!UnitID = Me!UnitID.ItemData(0)

to select first value of the combobox.
Thanks!