Unbound Form and Editable Subform

[tndinnc]

I need help in figuring out how to accomplish this in access 2010 or 2007

I have an unbound form with a text box on it. The text box is "Order Number". When someone enters an order number and clicks "search" I need to populate a subform or grid or something with the existing details of what products were on the that order. Users will need to then input the extra information and save all of the information to a table. My problem is when I populate the subform with query results it's not editable. All my tables are in SQL server. I'm familiar with VBA - is there a way to populate this subform with the results of a stored procedure maybe without actually binding it to anything?

Ref #	Product Name	Product ID	Gross	Net	Supplier	Delivery Date
111	product 1	prod1
2222	product 2	prod2

Thanks to anyone who can point me in the right direction or give a comment to help!

[June7]

Why not bound form?

How are you populating the subform - show code.

[tndinnc]

The reason I was shying away from a bound form for the main form is because the order table has 100000s of records in it and I thought it would be better performance to not have that form open up with all those records in there. I am not currently populating the subform as my question is "how" to populate it I have no code to show.

[June7]

The form could probably be bound but set to open filtered with criteria that results in empty recordset. Then use code behind the form to requery form based on textbox value.

Options:

1. parameterized query as the form RecordSource, parameter would be reference to the unbound textbox, since the form opens with nothing in the box, the form will filter so no record retrieved, code in textbox AfterUpdate event Me.Requery would retrieve the record matching the input

2. set the Filter and FilterOn properties of form to filter to an non-existent ID, code in textbox AfterUpdate would reset the Filter property to input value

Set the Master/Child links properties of the subform container and the records will synchronize.

That leaves the question of why the subform records are not editable.

Your post stated 'when I populate the subform with query results it's not editable' - how does this population happen? Did you mean when the subform is bound to a query? Post the SQL statement of the subform RecordSource.

[tndinnc]

Here is the sql for my subform query. I want to populate VPSBOL table. But if there is already something in there matching my order number I want to show it so they can edit it. SELECT dbo_freightdetail.fgt_refnum, dbo_freightdetail.cmd_code, dbo_stops.cmp_id, dbo_orderheader.ord_number
FROM dbo_VPSBOL RIGHT JOIN ((dbo_freightdetail INNER JOIN dbo_stops ON dbo_freightdetail.stp_number = dbo_stops.stp_number) INNER JOIN dbo_orderheader ON dbo_stops.ord_hdrnumber = dbo_orderheader.ord_hdrnumber) ON dbo_VPSBOL.LoadPay = dbo_orderheader.ord_number
WHERE (((dbo_orderheader.ord_number)="1235799") AND ((dbo_stops.stp_type)="drp"));

[June7]

A form can do data entry/edit to only one table. I see 4 datasets in that sql:
dbo_freightdetail
dbo_stops
dbo_orderheader
dbo_VPSBOL

No fields are pulled from dbo_VPSBOL. How can you enter/edit data of VPSBOL if fields are not pulled?

What are the relationships of these tables?

If you want to provide db for analysis, follow instructions at bottom of my post (make copy, import relevant tables).

[tndinnc]

my apologies I removed the fields from VPSBOL as I have been working on it and did not readd them before sending. I am aware I can't edit/save without them and that you can only update/save to one table. However it takes all 4 tables in that one query to pull the information I want to show.

FreightDetail joins Stops on Stop#
Stops joins OrderHeader on OrderHeaderNumber

That will give me the original order information

then I added
OrderHeader joins VPSBOL on OrderNumber

to get any previously entered extra information.

I know I can't edit the data using that query as the source. but what I want to know is how to show that information on the form, and just update the VPSBOL fields if I click save.

I will clean up my db and attach. thanks.