populate child form by record from parent form

[eugzl]

Hi All.
I created parent form with ListBox. Also I created child form with the fields related to columns in ListBox. I would like to create code that give me ability open and populate child form by record data that I will double click in ListBox of the parent form. I will appreciate if someone will show how it to do.

Thanks

[mike60smart]

Hi
The Normal layout is to have a Main Form bound to fields in a table.
Then you have a Subform bound to a table with related Child fields.

[Minty]

Have you tried linking the subform to the ID from the list box? In the master/Child properties of the subform.

[eugzl]

Hi mike60smart. Thanks for reply.
That query is source to populate ListBox in the parent form:

SELECT tblRequests.RequestID, tblRequests.RequestDate AS [Date], [tblRequests].[FirstName] & " " & [tblRequests].[LastName] AS [Employee Name], tblRequests.TicketNo, tblRequests.Amount, tblDepartments.Department, [qDevices].DeviceType & " " & [qDevices].Brand & " " & [qDevices].Model AS Device, IIf(Len([qLocations].[Facility] & " " & [qLocations].[Building] & " " & [qLocations].[Wing] & [qLocations].[Floor] & "-" & [qLocations].[Room])>3,[qLocations].[Facility] & " " & [qLocations].[Building] & " " & [qLocations].[Wing] & [qLocations].[Floor] & "-" & [qLocations].[Room],"") AS Location, tblRequests.DeviceID, tblRequests.LocationID
FROM tblDepartments 
RIGHT JOIN ((tblRequests 
LEFT JOIN qDevices ON tblRequests.DeviceID = qDevices.DeviceID) 
LEFT JOIN qLocations ON tblRequests.LocationID = qLocations.LocationID) 
ON tblDepartments.DepartmentID = tblRequests.DepartmentID;

That query is source of child form:

SELECT tblRequests.RequestID, tblRequests.RequestDate, tblRequests.Department, tblRequests.FirstName, tblRequests.LastName, tblRequests.TicketNo, tblRequests.Amount, tblRequests.DeviceID, tblRequests.LocationID, tblRequests.DepartmentID
FROM tblRequests;

For double click event I created such code:

Public Sub MaintainList(vNew As Integer)
    Dim stDocName As String
    Dim FormArg As String
    Dim stLinkCriteria As String
    
    stDocName = "fRequest"
    
    FormArg = lstRequest.Column(0) + "," + lstRequest.Column(6) + " - " + lstRequest.Column(7)
    If vNew = 1 Then
        stLinkCriteria = "RequestID= " & lstRequest.Column(0)
        DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, , , FormArg
    End If
End Sub


Private Sub lstRequest_DblClick(Cancel As Integer)
    MaintainList 1
End Sub

But unfortunately when I double click record in ListBox of parent form the child form is opening empty. I will appreciate if you will show where is my wrong.

Thanks.

[mike60smart]

Can you upload a zipped copy of the database?

You would normally link the Main Form to the Subform using the Link Master Fields/Link Child Fields.

In your case it would be linked on RequestID

Why do you need to open the related Form in a new window?

[eugzl]

The attached file is my last version
AddNewCascadeCB.zip

Thanks

[June7]

Which form is the issue - fInventory?

You want to open child form filtered to record selected in listbox?

fRequest has UNBOUND controls. Bind to fields and data will show.

None of these forms have bound controls - why?

[eugzl]

Hi June7. Thanks for reply.
That is my first project in Access. And I don't know if I will bound controls in fRequest form how it will effect on source of the form and controls, VBA code and all other functionality. Now I met problem to display data on this form when parent form pass data to it. So I have some questions:
1. Is it possible to show data in fRequest form if controls are unbound? If yes. How it to do?
2. If it is not possible. Can you show how to modify VBA code, source of the form and controls, and keep the same functionality by modify my attached file?
I will appreciate for help,
Thanks.,

[June7]

1. with a bunch of vba - most common approach would be to open recordset and populate each textbox with value from recordset; another could pass data as a comma separated string with form OpenArgs and parse the string to fill into controls; another would directly reference controls on first firm to populate second form controls

2. as said in 1, it is possible, so not relevant but if you want to use bound form/controls, should edit forms yourself to do that, regardless, I am not going to build your db

I have NEVER used UNBOUND forms/controls for data entry/edit. I know some here have. Why would you want to?

[mike60smart]

I agree with June.

Data Entry should always be bound to tables.

You currently have no method of editing Devices to add Brands & Models.

I am assuming you do this manually via the tables?

[eugzl]

My fRequest form populated from tblRequest table but that table doesn't have BrandID and DeviceTypeID fields. In that case how to bound cboBrand and cboDeviceType combo boxes. I will appreciate if you will show how it to do.
Thanks

[June7]

You don't. You save DeviceID of tblDevices into tblRequests. When you need to see the brand and device type and model, build a query that joins tables, and build a report to present this output.

Why is BrandID in tblDeviceTypes and why is DeviceTypeID in tblModels? Similar data scheme with facilities/buildings/wings/floors. Only save LocationID.

[eugzl]

If I understood correct. To populate fRequest form I must create query like this:



Is that correct? Thanks.

[June7]

No, I am not saying you should do that, although it might be okay. Just don't allow any of the fields from tblDevices, tblDepartments, tblLocations to be edited. Those tables would be included only to display information from those tables. Set those textboxes as Locked Yes and TabStop No.

[eugzl]

I would like to give an user option to update record in case if data is not correct. If I will lock the form field user cannot modify a record. That why I would like to keep all field in child form active. But if you are saying the query which I try to create to populate fRequest form will work that is good sign. I just will substitute source of fRequest form from tbkRequest table to new qRequest query. If I will call the fRequest form and a new record will save correct or call this form by double click record in the ListBox and will save modifications correct then that piece of project will be done. What do you think my idea will work this query that present in the previous post?
Thanks