A Complete Example of Simple One To Many Relationship Wanted

[gismofx]

I'm struggling to make a database work for me. I'm new to access, yet very comfortable with VBA, database design/architecture, etc and come from a FileMaker background. The Access scripts are a little confusing, so I'm opting for a VBA approach to manipulate and navigate my database.

I believe I'm using the MS Office 14.0 ADO Library and seem to be able to have some basic CRUD functionality. I've tried to make my own database and have done my research and fiddling around and I'm not getting the results that I quite want in terms of overall functionality.

I'm looking for an example of a simple database with two Tables. Orders and Line Items where I can do at least the following:

I want to create an Orders Form view and have a list box in the Order view that shows all of the related Line Item records. The order view should show an order total. I want to be able to add Line items in the form view via a button and have the form view update to show the newly added line item in the list box. Also, to be able to click on a line item and open a new form to edit the line item(can list boxes have buttons in them?). I've attempted this with my exmaple and seem to be having some trouble getting it to work how I want.

Can someone provide an un-bloated example that would show this functionality and also some best practices along way?

If this is too much to ask, I could post the file I am messing with and see if someone can correct it.

Thanks you.

[Micron]

I would not use a listbox. One was is to have two forms; frmOrderMain and frmOrderDtls (datasheet), which you link to a subform control on main. A control (combo?) on main header drives the display of records in the subform. This is done by requerying the sub form with the order# as criteria. Main has a textbox with a DCount function to get the sum of your dollar field (you do not store this total in a table) or any other aggregate function you need, such as line count, total item count, etc. If adding a record to the order details subform, you requery the sum textbox or refresh the entire form. However, I don't know if you want to add the line items via main form controls or directly in the subform, but either way, you have the ability to lock the subform so that direct editing is not possible if that's what you need. The only other thing I can think of at the moment that you'd need if you take this route is the syntax for requerying a subform: Forms("MainFormName").Controls("subformControlName ").Form.Requery (subformcontrolname is the name of the control that contains the subform, not the name of the subform itself).

Edit:

Also, to be able to click on a line item and open a new form to edit the line item(can list boxes have buttons in them?)

I say no. Anyway, if you use the above method, you don't need an editing form.