Pop-up form to display current datasheet record

[Ashleyjp1985]

I have a sub form in datasheet view that displays all of a customers related records.

I have set a double click event on one of the subforms fields thats opens the selected record in a popup form. I currently have this working using openargs (recordID)

I want the user to be able to use the record selectors to navigate through the customers other records. However at the moment users can navigate through all the customer records including records for different customers

How do i fix it so the pop up opens at the selected record but also allows the users to view other related records for the same customer only???

main form is based on customers table (custID) subform is linked on custID and has its own recordID.

 

[jdraw]

You are confusing us with customers vs customer's and subforms vs subform's.
It appears you have the traditional form/subform

Customer ([COLOR="Red"]main form[/COLOR])
  DetailRecordsforCustomerOnMainForm ( related on CustID) ([COLOR="DarkOrange"]subform[/COLOR])

You double click on a subform record and then what happens exactly?
And what exactly do you want to happen?

 

[Ashleyjp1985]

I thanks for replying. To clarify:

I have a main form based on customers table (PK custID). This form then has a sub form based on customer records (PK recordID). The subform is linked to main form on custID. When a user selects a customer on the main form the subform updates showing all related records in datasheet view.

When a user then double clicks on a recordid in the subform it opens a popup form displaying the record. However the popup form does not restrict the user to the specific customer as they are able to cycle through all the records in the customer records table.

I want to open the popup at the selected record and allow the user to cycle through the customers related records only.

 

[jdraw]

Please show the code for the double click event.
We need to see the code for opening the pop up. You will need some Openargs parameters based on your subform and custid (as I see it at the moment).

 

[Ashleyjp1985]

double click event:

DoCmd.OpenForm FormName:="popformQCustomerBP", OpenArgs:=Me.BPID

form load:

Dim rst As DAO.Recordset
If Len(Me.OpenArgs & "") > 0 Then
Set rst = Me.RecordsetClone
With rst
.FindFirst "BPID = " & Me.OpenArgs
If Not .EOF Then Me.Bookmark = .Bookmark

End With
End If

the above will display the selected record but i need it to restrict the user to the same customer record aswell.

 

[jdraw]

Something like (untested)

if you want to restrict it to records for this Customer, then

openargs: =me.custid

 

[Ashleyjp1985]

I firstly want it to restrict to the record (which it does) and secondly restrict to the customer so a user can view the rest of that customers records. This is what I can't get to work.

 

[jdraw]

Well, your findfirst would limit things to the record on which you double click. But because there is nothing, as far as I can see, that says and limit things to this customer, seems you need an additional restriction.

You could just close the popup and let the user choose another detail record, or select another Customer.

Anyway, if you want to limit the user to this Customer, you have to get that CustId into the constraint.

Thinking as I type -- could you say in the double click event something along

where customer = CustId and
recordId = findfirst this recordId

seems that would reduce all recordIds to only those for this Customer, but you klnow the data much better than I do.

good luck

 

[Ashleyjp1985]

I have come up with a solution not sure if its the best way but it works

i have set the reocrd source of the pop up form as the customer table and added the customer records as a subform.

the popup form is opened from the main form by double clicking on a selected record and the recordid is passed using Openargs. The popup form filters on a combo to restrict the record to selected customer it then pass the Openargs recordid to the sub form using parent.openargs.

the pop up is now restricted to one customer, displays the selected record but still allows the user to navigate through the rest of that specific customers records.

it works but if someone comes up with a better way im all ears.

 

[jdraw]

Sounds like a plan. Good stuff.