Selecting a record in subform when control can’t be edited, its bound to Autonumber field

[Paul1]

Hello,



I have a bound form with a subform. The subform is linked to the mainform on “Workspace_ID” through the Master Fields and Child Fields links.
The “Reports” subform is a listbox based on query whose bound value is also “Workspace_ID “.

I would like to be able to click on one of the rows in the subform and do something based on the non bound column. However I have 2 problems.

(i). When I click on a row in the subform a message in the task bar reads “Control can’t be edited, its bound to Autonumber field “Workspace_ID”. Therefore I cannot even select a record in the subform.

(ii). Even if I could select this value, I want to get the value of “Reference” which is the non bound value. BTW the bound value “Workspace_ID” (a hidden field in the subform listbox query) is the same for every record which complicates the problem further.

I would appreciate if someone could indicate a way of doing this if it is possible.

Many thanks.

Paul

[June7]

I don't see 'rows' in subform, only the listbox items.

I would need a better understanding of data structure and relationships and form design to analyze this issue. If you want to provide db for analysis, follow instructions at bottom of my post.

[Paul1]

Thanks for your reply.


I will upload the db as soon as I am able to. In the meantime I will try to make the problem clearer.


You are right I meant listbox items and not rows. The listbox (which is within a subform) is bound to a query on field workspace_id.
The main form and the subform are linked via the same field workspace_id. There is a 1:M relationship between Workspaces and Reports.


I would like to click on a listbox item (what I call a row) and then be able to capture the "Reference" for that list box item.
If I can capture the "Reference" for a specific listbox item then together with the workspace_ID, I can archive a "Report-Workspace" item in another table.


Only because the bound field of the listbox query is an Autonumber field, this means that I can't select a listbox item at all because I get the error message "control can’t be edited, its bound to Autonumber field" which is the first problem that I would like to solve.

[June7]

What is RecordSource of main form and RecordSource of subform? What field is listbox bound to?

Refer to combobox or listbox columns by index. If Reference is in column 2 its index is 1: Me.listboxname.Column(1)

Why isn't subform bound to the table you want to save the listbox selection into?

[Paul1]

Hello,

Please find attached the relevant parts of the db.

When I click on a list item in the list box, I would like to archive that unique "report-workspace" line in the table tblReportsWorkspaces.
That is I would like to be able to run an update query in code something like this

strSQL = "UPDATE tblReportsWorkspaces SET tblReportsWorkspaces.Workspace_Live_YN = False " _
& "WHERE tblReportsWorkspaces.[Workspace ID] = " & Me.txtWorkspaceID & " " _
& "AND tblReportsWorkspaces.Reference = " & strReference

However this is not currently possible because of the reason I have outlined above.

Many thanks.

Paul

[davegri]

Instead of a navigation subform, try using a tab control on the main form and put the listbox on the tab. Do away with the subform altogether.

[Paul1]

Thanks davegri for your reply.
What is the advantage of doing away with the subform and putting the listbox in a tab on the main form?
Also how would I link the workspace to the reports with a workspace which is a 1:M relationship? Without the subform I can't use the Link Master - Child feature?

[June7]

The subform and listbox use the same data source (qryWorkspaceReports). Not showing any data on the subform. The subform is not necessary. I suspected this when I asked questions in post 4. If purpose of these two listboxes (lstReports and lstUsers) is to select record to set the Workspace_Live_YN field, then they should not be bound because this changes WorkspaceID value in record.

I don't like and never use Navigation form because of complications in referencing subform and subform controls.

I agree with davegri. Put the listboxes (UNBOUND) on main form, with or without a tab control. Or don't use listboxes and simply display records on subform and let users directly edit the Workspace_Live_YN field - eliminate the VBA running UPDATE action.

Why use VBA to set listbox RowSource?

There is no qryWorkspaceUsers object.

[davegri]

Like this:
davegri 01 Reports Log Database AF.zip
Click on a listbox row and you'll see the result in a msgbox.

[Paul1]

Thanks very much June7 for your reply. I will need some time to digest your response.

The qryWorkspaceUsers object was deliberately not included because I didn't want to display personal information and it wasn't essential to display this for the question.
Also, it is necessary to display the information in the two list boxes but it is also necessary as well to be able to set the Workspace_Live_YN field from the reports listbox.

[Paul1]

Thank you davegri for your example. This is really helpful. I believe that I have enough information to solve the problem now.