Use selected record in dlookup query

[MICKI0220]

I have a form that is used as a master contact lookup. Therefore it is set to continuous and shows all the records. I want another form to open up based on which record is selected in the detail sections. This selection will be what the query uses for a dlookup.. It will lookup all entries where the field is equal to the company that was chosen on the first form. And then that second form I mentioned will use that info in its form. How do code which field criteria has been selected. I hope I made sense


Thank you in advance
Micki

 

[Remou]

You can refer to the selected record as just that, for example, if there was button to open a form on the continuous form, you could have code:

DoCmd.OpenForm "SomeForm",,,"ID=" & Me.ID

http://lessthandot.com

 

[MICKI0220]

I couldn't get this to work. I searched and found some code. I think it just needs expanding on.


Private Sub Company_DblClick(Cancel As Integer)
Dim rst As Recordset
Set rst = Me.RecordsetClone


Let me try to explain it a little better. A user opens this form (which is set to continuous). Therefore instead of seeing one record in the details section he sees all of them as he scrolls down. As he/she scrolls down, Access puts the curser at each record as it moves to the next one, (say it was the third record down). I want the user to double click on a record, and then create a variable of the companies name, so I can create a query that takes that company as its criteria, and opens another form, that has all contacts with the same company name.

I hope this makes more sense. Basically it should take the current record and then parse out the company name as a variable or dlookup. Anything that would give me the company variable to use as criteria.

 

[Remou]

The suggestion I made is the common solution and something I have used very regularly over a number of years to give the user extra information from a continuous form. There is no need to use a recordset, however, if the item is text, as suggested by company, then you must use quotes. In addition, the form to be opened must have matching data, company, say.

Perhaps you could post details of how it did not work, such as error messages?

http://lessthandot.com