Listbox question, please help!

[ychousa]

Hi. I'm spending days to figure out this. Please help!
I have a frmOrder as a subform inside a frmDetail. frmOrder gets values such as DetID automatically from frmDetail. DetID is an autonumber and a primary key in tblDetail which is a base for the frmDetail. What I want to is to dynamically change the list in the listbox on frmOrder depending on the change of DetID on frmOrder.

Right now frmDetail doesn't have a DetID textbox on it. It is just generated and transfered to frmOrder depending on other inputs.

Thank you for your comments in advance.

John

 

[EstuardoSierra]

John:
You don't give details of the listbox, so i'm going to give a general answer.
to sichronize the listbox with any other field the common procedure is:

Me.lbxAny = Me.tboxAny

Or

Me.lbxAny.Column(0) = Me.tboxAny <-- note that lbx's and cbox's are zero based

With this is just a matter settings to get it to work.
In the lbx properties the data tab
Bound columns 1 <--- Default

In the general tab
Column count = 2

It means that if the bound column is a primary key having, let's say a product id and the second column displays the product description, To sinchronize the listbox you do

Me.lbxAny = Me.tboxAny

Where Me.tboxAny is the field you have in the parent form.

i hope it helps

good luck

Estuardo

 

[ychousa]

Thanks for your reply, Estuardo. Sorry I should have given more details about the situation.

I have 2 tables-tblDetail and tblOrder.
tblDetail has DetID as a primary key(autonumber)and tblOrder has DetID as a foreign key.

tblOrder has many fields for order details such as sales order number and customer name.

The listbox shows the salesorder number and customer name so that a user can look up and select one of them to retrieve the record in frmOrder, which is a subform in frmDetail.

So, typically a user select DetID by performing some action on frmDetail, then the order records on the specific DetID are also retrieved on frmOrder.

Currently I tried the rowsource of the list box as following: SELECT [tblOrder]![SalesOrderNo], [tblOrder]![CName]
FROM [tblOrder]
WHERE ((([tblOrder]![DetID])=[Forms]![frmOrder]![DetID]));

But this doesn't work. Some people suggest I need to use form_current event, but I don't know how.

This is the situation and tell me if you need more info.

thanks much.

John

 

[EstuardoSierra]

Hello John:
People might be right. if in the current event of your main form (Form details) you include this line:

Me.frmOrder![listboxnamehere] = Me. DetID

the both fields will be sinchronized when you move from one record to other.
So, you probalbly want to &quot;force&quot; sinchrony after the DetID is manually changed: so you must repeat the line in the DetID's after update envent, and that most do what you need.


Just a couple of points:
1st. Usually subforms does not sichronize their parent form
2nd. If forms are linked by any common field you should not break that link.
3rd. if the subform is not linked with the main form, it problably not will be recognized by the intellisense in the main form so it might not work

I hope you solve your problemm, please let me know.

good luck

Estuardo