Referernce to control in Subform from Query

[kilburfi]

I have a main form "KZ_SEARCH" and within this I have a subform which is loaded when a button is pressed and is populated with query results (based on text entered in a text box). The subform is called "KZ NOTICES Query subform". That all works fine and the query modifies based on the text entered every time the button is pressed. The next stage is that when the subform is loaded I want the user to be able to select a line from the subform and to jump to that record in another form (which also needs to be opened from that selection). The reference for the record to select in the newly opened form would be the first column of the selected line highlighted in the subform (control name "KZ NUMBER"). Any suggestions?

 

[pbaldy]

Is this what you're looking for?

http://www.baldyweb.com/wherecondition.htm

 

[kilburfi]

Thanks for the quick response (your next one might not be as quick!). I can open the form and get the right record to display the problem is getting the correct reference from the subform. I can't seem to get the subform to tell me what the first column of the selected records value is. I have used code like:

MyKZRecord = Me.KZ_NOTICES_Query_subform.Form.KZ_NUMBER

but MyKZRecord variable appears "empty". KZ_NUMBER is the name of the field in the subform.

 

[pbaldy]

This is in the main form? It should work, but why not populate it in the same code that opens the other form?

 

[kilburfi]

Paul - sorry I may be confusing you. I load a form with a search box and the user enters, for example "printer" the subform then populates with all records from a table that include the word "printer" somewhere in the description. I then want the user to be able to select a record from the subform and, as part of the event for that selection, it stores the "KZ Number" and opens another form and locates the records with the same "KZ Number". My problem is getting the "KZ Number" from the subform so that I can use this as a variable. Opening forms and finding records is fine (I can just about manage that) but I am struggling with getting the subform to tell me what it's controls are and then referencing them in code. Thanks. Fiona.

 

[pbaldy]

I live in a virtually constant state of confusion.

The same reference that works to open another form to that record should work to populate a variable. Perhaps this helps?

http://www.mvps.org/access/forms/frm0031.htm

If not, can you post the db here?

 

[kilburfi]

Hi - I have attached the DB here because I am not having much luck. If you go to KZ search and type "price" in the text search there will be 2 results displayed in the subform. They are both the same record but are displaying as 2 but I understand why that is happening (because they have two options selected against them and reported on from the main record). What I want to do is click on the first line of the subform and for it to go to the KZ-Notices form and display the corresponding record from that sub-form (so using the sub-form as a dynamic link to the record in the main form).

 

[pbaldy]

I don't see where you've tried the technique in the link. I just did and it opened the second form to the record on the subform.

 

[kilburfi]

Sorry Paul - I removed most of the code I tried. I will try again and see what happens. If I still can't get it to work I will post the code I am using.

 

[kilburfi]

I am a dumbo Paul! - it works a treat (after hours of complex code it was the stupidest of things I was requesting the 'value' rather than 'text' which was what was wrong). Believe me I have chastised myself enough but feel free to jump on the bandwagon!

 

[pbaldy]

Happy to help!