Access subform lookup problem. 1

[JonMa]

Hello, can anybody help or give me a hint.
I have a main form and two subforms.
In the last subform a field called "Member" should select from a lookup table (combine many-to-many) with the following recordsource SQL:

SELECT Person.Medlemsnr, Person.Etternavn, Person.Fornavn
FROM Person
WHERE (((Person.KorpsID)=[Forms]![Reg05-Tilhorighet]![KorpsID]));

This works great the first time, but when the key on the main form changes it keeps the old lookup data, so I have to go in and out of the main form in order for it to work.
Any suggestions will be much appreciated. I am a beginner in access, so I know very little of how to manipulate properties and subroutines.
Regards Jon.

 

[judgehopkins]

Look at this: thread705-604756 and also go to

http://www.mvps.org/access/

While you are there, run a search on lookup; you will find all kinds of articles to help you.

"I have never killed a man, but I have read many obituaries with great pleasure." - Clarence Darrow

 

[JonMa]

Hello judgehopkins.
This was very good, I am getting much closer now.
However I still can't make it work and I have tried so many things.
Is NotInList the right place ?
Here's what I have done looking at your example:

Private Sub Medlemsnr_NotInList(NewData As String, Response As Integer)
ComboboxCleanUp = ""
Me.Refresh
Me.Requery
Me.Medlemsnr.RowSource = Me.Medlemsnr.RowSource
End Sub

Regards Jon.

 

[JonMa]

Hello again.
Thanks very much judgehopkins. Good suggestion.
It is working beautifully now.
Here's the code who did the trick.

Private Sub Medlemsnr_Enter()
Me.Refresh
End Sub

Thanks.