List Boxes Parent/Child

[CharlesWhiteman]

I have a main table 'Companies' and a Table 'Contacts' Linked by CompaniyID

I have created a list box which looks up the contacts table and displays them as a list box in the main 'Company' Form.

How to I get the list box to only show contact names where the companyID matches?

I have been looking at SQL WHERE in my statement but can't get it to work; example below:

SELECT QryContacts.ContactName, QryContacts.JobTitle, QryContacts.EmailAddress, QryContacts.CompanyID FROM QryContacts WHERE QryContacts.ID equals "[Forms]![FrmQryCompaniesold]![CompanyID]

 

[macca the hacke]

Try amending to:

SELECT QryContacts.ContactName, QryContacts.JobTitle, QryContacts.EmailAddress, QryContacts.CompanyID FROM QryContacts WHERE (((QryContacts.ID)=[Forms]![FrmQryCompaniesold]![CompanyID]))

 

[CharlesWhiteman]

Thanks Macca, thats great, the only thing is that when i try top open the form I get a box "Enter Parameter Value" QrtContacts.ID

 

[CharlesWhiteman]

Found a couple of bits to change but the main thing is that when moving to a different company record the contact record only updated after an F9

SELECT QryContacts.ContactName, QryContacts.JobTitle, QryContacts.EmailAddress, QryContacts.CompanyID FROM QryContacts WHERE (((CompanyID)=Forms!FrmQryCompaniesold!CompanyID));

 

[macca the hacke]

to get to refresh programatically you can use:

me.requery

 

[CharlesWhiteman]

Thanks, i've been looking at a macro to requery but I know thaqts not the bext way

how/where would I ue the me.requery?

 

[macca the hacke]

Insert an On Current Event Procedure for the parent form, and insert it in there.

 

[CharlesWhiteman]

Thanks, did that, but can see another table flickering and i can use the record selector to move to a different company record

 

[macca the hacke]

?? Sorry, don;t understand - can you save a screenshot so I can see what your issue is

 

[CharlesWhiteman]

Reply With Screenshot

Ok, shee the attachment: the SQL which we've been writing deals with teh contact list box top right of the form.

After going to properties oncurrent and adding me.requery to the code and saving and going back to the form

when trying to use the record selectors the table (lower half of the screen) flickers and I am unable to move to another record. the tables flicker and blink and nothing happens.

(I'm going to change the lower subform to a list box too at some point. side issue.

 

[macca the hacke]

Ah OK, sorry. Putting me.requery on the parent form is going to cause the subform below to try and update its values all the time.
Take the me.requery out of the parent form - although we are now back to original problem of not updating.
Does it work if you add the me.requery to the OnGotFocus of the list box?

 

[CharlesWhiteman]

no, all that happens is that the main form scrolls though its available companies, when clickign the listbox the main form reverts back to the company name relative to the contact name which never changed

 

[CharlesWhiteman]

DONE IT!!! YIPPPEEEEEE! WHO'S THE DADDY NOW
I used the following code in the code for the main form and it worked where contactlist is the name of the list box

Private Sub Form_Current()
[Forms]![FrmQryCompanies]![contactlist].Requery
End Sub

 

[CharlesWhiteman]

couldnt have found it without your pointing though!

 

[macca the hacke]

How about going back to the parent form, and on current having:

me.lstname.requery (where lstname is the name of your list box)?

Looks like I am too late you have already figured out....