Fliter a subform based on PK on main form

[chrisguk]

Firstly I want to say, how do you guys know so much about access. I am trying so hard to learn and pick things up but its real slow.

Are you just naturally gifted or what?

Anyway, this is another one of my issues:

I have a main form called "frmsites", its is controlled by a PK of sitesid which is like an account number so its not an autonumber.

On that form I have a subform called subfrmelec. The PK for that form is electricid and the FK is sitesid.

The other fields on this subform are:

accountnumber
MPAN
MSN
date
install date

I have a combo box on the subform. Which has the account numbers in it. When I select the drop down all the account numbers are available for all siteid's and not just the account numbers for the particular siteid in focus on the main form.

I have tried using a filter method and a combination of vba provided across the internet but cant seem to get it to work.

Is anyone willing to help me out here and maybe give me some solid guidance to help me learn.

Remember I am a pure beginner but Im trying........

 

[jzwp22]

I've attached an example database that illustrates what you want to do. Basically you want to filter the combo box based on whatever the current site record is in the main form, so you will need code in the on current event of the main form. What I have done in the attached DB is to create a query in code that uses the key value of the current record to filter the query. I then assign that query to the row source of the combo box in the subform.

Now you also have to take care of the case when you have a new record and someone enters the siteID (or selects it from a combo box) in the main form. So you will need to repeat the code in the after update event of that control in the main form.

 

[chrisguk]

Hi,

Any chance you can downgrade the file to 2000 version?

 

[boblarson]

Hi,

Any chance you can downgrade the file to 2000 version?

Here you go - changed to 2000 format and also the reference error from Access 2010 fixed.

 

[chrisguk]

Thanks Bob,

Is his solution the right one by the way?

 

[jzwp22]

Thanks for the assist Bob, I got caught up in a meeting.

 

[chrisguk]

Thanks for the assist Bob, I got caught up in a meeting.

Having trouble working out the mysql

 

[boblarson]

Thanks Bob,

Is his solution the right one by the way?

I haven't looked but based on what I know about him, I trust that it is.