Listbox

[Nicholas Loo]

Dear All,

I m new comer and need help from your expertise.

I have created a form from a table call Company which contain the CompanyName,
CompanyID (Primary Key), CompanyAddress, CompanyTel, CompanyWebpage and so On.
Inside the Company Form, i have add in the Listbox to show the record of the
Person in charge in Company from another table call PersonIncharge. The table
of PersonIncharge is also contain the CompanyID.

The problem is, the Listbox is showing everything even it not related to the
current record.

How can i make the Listbox to show the record which is only related to the
current record (CompanyID) in the form?

For example, if the CompanyID is ABC then the listbox will show the only
Company ABC person incharge.

Please help.


Thanks and best regard from Nicholas

 

[Brian Bastl]

Nicholas,

Assuming the row source type for the list box is "Table/Query", the row
source looks something like:
SELECT PersonInChargeID, PersonName FROM PersonInCharge; ....

drag the CompanyID from PersonInCharge table into an empty column in the
query designer. Then in the criteria row of this column, you'll need to
reference the CompanyID on your main form.

Your new rowsource would look like the following:

SELECT PersonInChargeID, PersonName FROM PersonInCharge
WHERE (((PersonInCharge.CompanyID) = Forms!MyForm!CompanyID));

HTH,
Brian

 

[Nicholas Loo via AccessMonster.com]

Dear Mr. Brian

Thanks for your kind reply.

It works. Thanks.

But, when i scroll to the next record, the record in the listbox still
maintain not change. In another word, When i open the form, it show the
record of the Company ABC and the List of the PersonInCharge in the Listbox
for example Mr. AAA and Mr.BBB. When i Schroll to another record of the
Company (Company cde), the listbox still list the same record which is
showing Mr. AAA and Mr.BBB. How to do make it refresh the record of the
listbox accordingly to CompanyID? Would you mind to help me?


Thanks in advance.

Nicholas,

Assuming the row source type for the list box is "Table/Query", the row
source looks something like:
SELECT PersonInChargeID, PersonName FROM PersonInCharge; ....

drag the CompanyID from PersonInCharge table into an empty column in the
query designer. Then in the criteria row of this column, you'll need to
reference the CompanyID on your main form.

Your new rowsource would look like the following:

SELECT PersonInChargeID, PersonName FROM PersonInCharge
WHERE (((PersonInCharge.CompanyID) = Forms!MyForm!CompanyID));

HTH,
Brian

Dear All,

[quoted text clipped - 18 lines]

Thanks and best regard from Nicholas

 

[Brian Bastl]

Nicholas,

you'll need to requery the listbox using the After Update event procedure
for your CompanyID, and even perhaps the main form's On Current event.

HTH,
Brian

Dear Mr. Brian

Thanks for your kind reply.

It works. Thanks.

But, when i scroll to the next record, the record in the listbox still
maintain not change. In another word, When i open the form, it show the
record of the Company ABC and the List of the PersonInCharge in the Listbox
for example Mr. AAA and Mr.BBB. When i Schroll to another record of the
Company (Company cde), the listbox still list the same record which is
showing Mr. AAA and Mr.BBB. How to do make it refresh the record of the
listbox accordingly to CompanyID? Would you mind to help me?


Thanks in advance.

Nicholas,

Assuming the row source type for the list box is "Table/Query", the row
source looks something like:
SELECT PersonInChargeID, PersonName FROM PersonInCharge; ....

drag the CompanyID from PersonInCharge table into an empty column in the
query designer. Then in the criteria row of this column, you'll need to
reference the CompanyID on your main form.

Your new rowsource would look like the following:

SELECT PersonInChargeID, PersonName FROM PersonInCharge
WHERE (((PersonInCharge.CompanyID) = Forms!MyForm!CompanyID));

HTH,
Brian

Dear All,

[quoted text clipped - 18 lines]

Thanks and best regard from Nicholas

 

[Nicholas Loo via AccessMonster.com]

Dear Brian

I know i m a little big dumb in access. Since im a new comer, i would like to
request your sincere help in this.

I still cant solve that problem. Actually i dont know what is commend to put
in to the current event. I have try Me.Requery, It dont work. I have try Me.
ListBox.Requery, it not working. Please guide me what is the correct way
maybe in more details. Hope you can help for that.


Thanks in advance

Nicholas,

you'll need to requery the listbox using the After Update event procedure
for your CompanyID, and even perhaps the main form's On Current event.

HTH,
Brian

Dear Mr. Brian

[quoted text clipped - 35 lines]

 

[Brian Bastl]

Hi Nicholas,

Open your form in design view, and on the menu bar select View | Properties.
This will bring up the form's property sheet.

Across the top, there will be a tab named "Events". Click that tab.

The first item on that page will say "On Current". Click on these words.
You'll notice a dropdown arrow to the far right. Click it, and select [Event
Procedure].

Then next to it on the right, there will be a button with three dots [...].
Click this to launch the vba editor.

Your cursor should now be on a blank line between:
Private Sub Form_Current()
'here is your blinking cursor. This is where you issue the Requery.
End Sub

So your code should look like this:
Private Sub Form_Current()
Me.NameOfMyListBox.Requery
End Sub

Do exactly the same as above in the After Update procedure of your combo box
with the CompanyID.

Your code would then look like:
Private Sub MyComboBox_AfterUpdate()
Me.NameOfMyListBox.Requery
End Sub

You obviously need to change the names of the example combobox and listbox
to the names of YOUR combobox and listbox on your form.

HTH,
Brian

Dear Brian

I know i m a little big dumb in access. Since im a new comer, i would like to
request your sincere help in this.

I still cant solve that problem. Actually i dont know what is commend to put
in to the current event. I have try Me.Requery, It dont work. I have try Me.
ListBox.Requery, it not working. Please guide me what is the correct way
maybe in more details. Hope you can help for that.


Thanks in advance

Nicholas,

you'll need to requery the listbox using the After Update event procedure
for your CompanyID, and even perhaps the main form's On Current event.

HTH,
Brian

Dear Mr. Brian

[quoted text clipped - 35 lines]

Thanks and best regard from Nicholas

 

[Nicholas Loo via AccessMonster.com]

Dear Brian

thanks alot of your help


Thanks

Hi Nicholas,

Open your form in design view, and on the menu bar select View | Properties.
This will bring up the form's property sheet.

Across the top, there will be a tab named "Events". Click that tab.

The first item on that page will say "On Current". Click on these words.
You'll notice a dropdown arrow to the far right. Click it, and select [Event
Procedure].

Then next to it on the right, there will be a button with three dots [...].
Click this to launch the vba editor.

Your cursor should now be on a blank line between:
Private Sub Form_Current()
'here is your blinking cursor. This is where you issue the Requery.
End Sub

So your code should look like this:
Private Sub Form_Current()
Me.NameOfMyListBox.Requery
End Sub

Do exactly the same as above in the After Update procedure of your combo box
with the CompanyID.

Your code would then look like:
Private Sub MyComboBox_AfterUpdate()
Me.NameOfMyListBox.Requery
End Sub

You obviously need to change the names of the example combobox and listbox
to the names of YOUR combobox and listbox on your form.

HTH,
Brian

Dear Brian

[quoted text clipped - 21 lines]