Behind the scenes

[BayerMeister]

Hi, I'm new to access and I am lost in what Access is doing behind the scenes. For instance creating a form for a table is done for you with one click.

I have however 3 tables and want them to display in a single form. The logic is simple. I attach a picture of the tables and how the form might look like (form composed in Visua C# Express). Please don't mind the form is in Czech.



So the logic - at hand - the form should display:
1) the customer, can create a new, traverse through customers
2) one of his/hers delivery note, again creation and traversal possible
3) items of the current delivery note, this table enables creating, editing and deleting rows

I think this is something access is good in, but I can't find the basic principles, how to tell access how to populate the form.
In addition I find it hard to design the form like this in access.

Would someone kindly guide me? Thanks a lot!

[pbaldy]

The most common way of representing a one-to-many relationship is with a form/subform. The form would be bound to the "one" table, the subform to the "many" table. The master/child link properties of the subform control keep them in sync with each other.

[BayerMeister]

Thank you for your quick reply. I'll try and send my experience ASAP.

[pbaldy]

Happy to help, and welcome to the site by the way!

[BayerMeister]

So a short closure for this thread:

I used subforms to achieve the behavior you would expect from the form shown in the first post.

In Access 2007 you get used to the automatic creation of forms. Those are however not good as subforms, because they have a header and footer. Once you have a simple self-made form for one table (say "items" in this case) and one for the other ("delivery_notes") you simply drag one form from the list of forms into the other (drag the "N" into the "1" in a "1:N" relationship).

If the behavior is weired check the master/child link properties as pbaldy proposed.

From now on it got a little tricky – if you want to edit the subform, you can open the original form of it or edit it as an embedded subform. I had a hard time to get a good visual result – placing borders and titles of the subform etc.

When OK, I did the same embedding thing again, this time into the final form which has a header, logo etc.

Further on I did not like the built-in navigation tool at the bottom of the form. When embedding subforms in subforms, two navigation bars appear next to each other at the bottom and that is not usable for a common warehouse-guy. The wizard of creating buttons in Access 2007 made it a breeze to create this by myself and place wherever else.

So and that's it. It works and the result is below. Further I'd like to gray-out navigation buttons that are irrelevant (previous record when viewing the first etc.) and find out how to open the form with a particular record or with a new record (empty).

Read up to here? Damn', you're good!



...and I don't know how to set this topic as SOLVED. Anyone?

[pbaldy]

Here's how to mark it solved:

https://www.accessforums.net/forum-s...lved-1828.html

Here's how to open a form filtered to a particular record (and a link to a method to open it with all records but at a particular record:

BaldyWeb wherecondition

[BayerMeister]

Well I guess I need a VBA syntax tutorial first. All the "[table]![id]" stuff is... new to me. I'm slowly getting used to the & concatenation operator.

What I'm not getting from "BaldyWeb's Open a second form to the record" is: What if "FieldName" is in fact part of a subform? How do a address that?

My case is this:
As shown above, there is a CUSTOMER table bound 1:N to DELIVERY_NOTES bound 1:N to ITEMS. That means I have a customers form with a delivery notes subform and that itself has a subform for items. When I'm going to open this form I know both the customer.ID and delivery_note.ID I want to show. Both of them restrict what is to be show (as a matter of fact one implies the other -- the deliver_note.ID belongs only to one customer -- so delivery_note.ID could suffice).

So, please, would you or someone else :-) tell me how that would be done with this beautiful syntax of VBA?

Thanks thanks thanks. I really appreciate your fast helpful replies.

[RuralGuy]

In the example you have shown in your first post, I would create a Form that displays the DeliveryNotes and Items side by side on SubForms properly linked to each other. Then the Customer Form would display this "Combo form" as a SubForm using the CustomersID as the linking field. All three forms will then remain in sync. I've done this several times in systems.

[BayerMeister]

I see, that would eliminate the need to set any properties on subforms... I'll think about recreating the form, so that the customer becomes a subform (displayed asa combo box). Then I would however need another form for entering customers... I planed that in the beginning, but later found it easier to use this way.

You don't happen to know how to set a filter (limit a property) for a subForm, do you? I'd like to quick-fix this, I should show it to a customer today.

[RuralGuy]

You simply need to control the field on the MainForm used as the LinkMasterField. It does not need to be an actual field but can simply be an invisible TextBox on the MainForm that you are controlling some way.

[RuralGuy]

Here's a link to some examples: http://www.members.shaw.ca/AlbertKal...icles/Grid.htm

[BayerMeister]

I'm not getting it

Lets use the A, B example again...

Tables A and B in a 1:N relation (the B.a_id column exists).
Form A with subform B, subform is linked to A like this:
subform.SourceObject = B
LinkMasterFields = id (A's id)
LinkChildFields = a_id

Now from the outside (from another Form) I know how to set open the Form A and set the form's bookmark to anything:

    'the "aid" is given
    Set rs = Forms!A.RecordsetClone
    rs.FindFirst "id = " & aid
    Forms!A.Bookmark = rs.Bookmark

Further I need to get the right record in the B table (subform) as well:

    'not working hypothetical example
    Set rs = Forms!A.subformB.RecordsetClone
    rs.FindFirst "id = " & bid
    Forms!A.subformB.Bookmark = rs.Bookmark
    
    'error at ".subformB..."

So and back to your proposal:
OK I make and invisible textbox on the A form. Now what? Will it contain the B.id? How do I link that?

[RuralGuy]

You lost me there. To reference a SubForm through the FORMS collection you need to use the SubFormControl (the control that displays the form as a SubForm) name.
FORMS.MainFormName.SubFormControlName.FORM.Bookmark (if that's what you want). The UPPERCASE words are required in the syntax.

[BayerMeister]

I'll give it a try. Thaks

[RuralGuy]

I'll stay with you on this 'til you get it running. Just post back for assistance.