Limit list in subform to selected record in main form 1

[GrahamYSA]

This is probably dumb, but I am new to Access and learning fast (but not fast enough)

I am trying to set up a 3 part form: the main form selects a company from a list, the first sub form selects a contact from a list from the selected company and the third form selects an action from teh selected contact.

I have created the first subform using a wizard and the contacts are limited to those in the related comany but when I add a List or Combo box all of the records in the contacts file are on view (I use the option *Find a record on my form based on the value I selected in my list box*)

How can I limit this so I only get the contacts that belong to the related company?

Thanks

 

[Edski]

It's not a dumb question.

One way would be to add an Event procedure when a record in the subform receives the focus, making it the current record.

The Event procedure would define the Row Source for the Combo Box (the Row Source Type should be Table/Query).

[blue]Private Sub Form_Current()[/blue]
  Dim strQry as String
  strQry = "SELECT [red]*[/red] FROM [red]tblContacts[/red] WHERE [red]CompanyID[/red] = " & [red]Me.CompanyID[/red]
  [red]cboContact[/red].RowSource = strQry
[blue]End Sub[/blue]

Chage the words in red to the names of your tables/fields.
Hope that helps.

 

[TheAceMan1]

GrahamYSA . . . . .

The [blue]ComboBox Wizard[/blue] always come up with an [blue]SQL statement[/blue] for the [blue]RowSource[/blue]. Its a matter of [blue]adding criteria[/blue] to the SQL and setting the Current Event of the mainform to [blue]Requery the combobox[/blue]. In this way, [purple]everytime you change record in the mainform, the listbox is automatically updated[/purple].

So, call-up the [blue]properties[/blue] for the combobox and put the cursor on the [blue]RowSource Property[/blue] line. Click the three elipses just to the right, then click as necessary to get into query design view. In the criteria for the [blue]PrimaryKey[/blue], copy/paste the following code ([blue]you[/blue] substitute proper names in [purple]purple[/purple]):

[blue]Forms![[purple][b]YourMainFormName[/b][/purple]]![[purple][b]YourPrimaryKeyName[/b][/purple]][/blue]

Use the close button (X) to get back to form design view.

Next, in the [blue]Current Event[/blue] of the mainform, copy/paste the following code:

[blue]   Me!YourSubFormName.Form.Requery[/blue]

[purple]Thats it . . . give it a whirl and let me know . . . .[/purple]

See Ya! . . . . . .

 

[GrahamYSA]

Thanks - I'll give it a whirl!

 

[GrahamYSA]

Hi all

It is an eternity since I made the last post. I have been trying on and off to get this to work but I just can't do it. I have tried both your suggestions. I couldn't make Edskis work at all (probably becasue I couldn't find the right place to put the code) and AceMan1's suggestion works the first time when I go from design to form view but then stops working.

I have the Main form and two sub forms set up and they work in that each sub form shows only the data they should, I just can't make the combo or list boxes 1) show only the items belonging to the ID or 2) make the data on the subform move to the particular record I select in the list.

Is it possible for me to post the databses somewhere so someone can look at it or do I ned to go down the road of engaging a consutlant.

Thanks

 

[TheAceMan1]

GrahamYSA . . . . .

Did you put the List/Combobox on the [blue]Main Form[/blue] or [blue]subForm1[/blue]?

You want to go to a record on the subform . . . yes?

What are the names of the mainform/subform/listbox?

See Ya! . . . . . .

 

[GrahamYSA]

TheAceMan1: Thanks for getting back so soon

The list box is on the sub form which is named Contacts and yes I do want to go to a particular record in the CONTACTS table based on choices made in the list box which is called List45. The records in List45 should be limited to records in CONTACTS that on the field NUMBER to the table (SCHOOLS) on which the Main form (Schools1)is based.

The relationship for these tables has been set with the Relationships wizard.

Hope that is enough info.

 

[TheAceMan1]

GrahamYSA . . . . .

Sorry to get back so late (I somehow deleted your last post).

Using the code I supplied,when you navigate records on the mainform, does the listbox follow along (showing the contacts on the Contacts form?

See Ya! . . . . . .