How a subform can get combobox information on the main form. (1 Viewer)

[brucemc777]

Probably very simple, but i've been stumbling for a little over a couple hours now due to my absence from Access for 12 years-

We have a single record parent form, lets call it frmParent (innovative, no?) and on it is a continuous subform - frmChild.

On frmParent is an unBound combobox - cbxSelect - a four field combobox, which gets it's records from filtering a table; OK, tblEquipment. THis was a poor choice of naming since it would have been more appropriate for the library of items of equipment, but i have enough issues with which to deal at this time-

All four fields of cbxEquipment are the four fields (including key) in tblEquipment. The filtering is to only present items that are a part of the current order ("IDOrders" in the attached pictures).

The purpose of the two forms is that frmParent has the metadata of the order and frmChild is to have line-item equipment records added from cbxSelect to populate an order for items of equipment. Incidentally, following cbxSelect and also unbound on frmParent follow a textbox for quantity (tbQuantity) to be ordered, then a command button (cbAdd) whose intent is that once an item is selected in cbxSelect and a quantity is entered in tbQuantity, cbAdd will cause a new record to be populated in frmChild.

(Incidental: Does it matter if cbAdd enters the info from cbxEquipment into the record display in frmChild vs entering the information directly into tblEquipment?)

My issue is that i need to learn how to make cbAdd do what it is supposed to do, as presented above. This should be pretty simple if i only understood the syntax, but i just can't hit on the right words in my web searches to find it, for either extracting each field from the current cbxEquipment choice, nor how to then either put it into frmChild to then create a new record in tblEquipment, or conversely, enter the cbxEquipment data into tblEquipment to be displayed in frmChild (with perhaps some form of refresh command).

In that a picture (or two) might save you from my going on for a thousand more words, attached are "Form.png" and "Table.png". The entry selection controls, cbxEquipment, tbQuantity and cbAdd, are just above the continuous subform frmChild.. You will note that in frmChild the type of equipment is spelled out; this is only because i made that field into a lookup - for its underlying basis is a long integer that corresponds to an equipment library key in another table.

As always, thank you to you folks who have excellent memories and skills, and are willing to help - your kindness IS appreciated.

 

[June7]

Could you provide db for analysis?

 

[brucemc777]

I guess two reasons prevent me from doing so:

1. I am embarrassed to expose my newbie hack method of putting it together - this issue i can overcome, but for the next item
2. I have no clue as to how to provide you with that for which you have asked-

 

[GaP42]

The cbxEquipment - where you choose an item to add to the list of items for the "Parent" - has no effect until you/user press Add, whereupon it add the item to the Child Form, with the Quantity (=1 by default?).
Your ADD button needs vba code for the On Click event where a SQL statement is constructed to insert (add) the record to the OrderItems table with the data for the Equipment item ID (from cbxEquipment) and the Quantity.

BTW why not use the cbo in the child form to add the item?

 

[The_Doc_Man]

The syntax you are seeking has to do with crossing boundaries of things that are structurally associated.

To see a control on the parent from the sub-form, use: Me.Parent.parent's-control-name - and in this case, Me refers to the sub-form.

To see a control on a child from the parent form, use: Me.subform-control-name.Form.child's-control-name - and in this case, Me refers to the parent form.

 

[brucemc777]

The cbxEquipment - where you choose an item to add to the list of items for the "Parent" - has no effect until you/user press Add, whereupon it add the item to the Child Form, with the Quantity (=1 by default?).
Your ADD button needs vba code for the On Click event where a SQL statement is constructed to insert (add) the record to the OrderItems table with the data for the Equipment item ID (from cbxEquipment) and the Quantity.

BTW why not use the cbo in the child form to add the item?

Thank you! I will work on that. As to not using the child form, because it is a continuous form and that seemed weird to me until i thought "Well, why not put the combobox, quantity textbox and command button in the form header??? I'm going to have to give that a try and if it works, we will just add to my embarrassment cache...

 

[brucemc777]

The syntax you are seeking has to do with crossing boundaries of things that are structurally associated.

To see a control on the parent from the sub-form, use: Me.Parent.parent's-control-name - and in this case, Me refers to the sub-form.

To see a control on a child from the parent form, use: Me.subform-control-name.Form.child's-control-name - and in this case, Me refers to the parent form.

Many thanks! I will "fiddle around with that" (that's how i learn) to see what i can craft. Displaying and being embarrassed by my lack of accumulated and/or retained knowledge has become a common method to learning-

 

[brucemc777]

May i ask, how do i address each of the fields in the combobox? In Excel i could do that in a heartbeat, but i think i've found out that this ain't Excel-

 

[brucemc777]

Aha! Got it!

Now i "just" have to dig up the proper syntax for an append and i should be good. THANK YOU EVERYONE!!! (And yes, i am shouting that, y'all deserve a big thanks for reading through my long-winded request-)

 

[GaP42]

Thank you! I will work on that. As to not using the child form, because it is a continuous form and that seemed weird to me until i thought "Well, why not put the combobox, quantity textbox and command button in the form header??? I'm going to have to give that a try and if it works, we will just add to my embarrassment cache...

Well - I was meaning in relation to using the combo on the child form - what you already have on each line item - it shows the combo and quantity. So just use the combo on the new record line - no code, no SQL, bound to equipment ID.

 

[June7]

To attach files, follow instructions at bottom of my post.

I also wonder why you need VBA and SQL to select and save equipment ID. Bind combobox to field and value will be saved. Saving associated equipment attributes is duplication of data between tables and is most likely not necessary.

 

[The_Doc_Man]

You said you got it, but just to clarify: Combo boxes with multiple fields are addressed via the .Column property

ComboBox.Column property (Access)

Office VBA reference topic

learn.microsoft.com

And remember, typically Access will index things as zero-based rather than one-based, because they use -1 to indicate no selection. The Column number is one such case.

This link will get you to the page where you can explore all of the many properties of a combo box.

ComboBox object (Access)

Office VBA reference topic

learn.microsoft.com

 

[Pat Hartman]

@brucemc777 As others have mentioned, this is not the best method -- unless of course you have a great desire to write unnecessary code.

Typically, the subform would have a combo that lists the items that can be ordered, Picking an item this way is a NO CODE solution. You pick the item and then you tab to the quantity and enter the quantity. Usually, that is all you need to do and NO code is required. Except of course validation code in the BeforeUpdate event of the subform to ensure that both columns are not blank and quantity is >0 since 0 or a negative number make no sense for a quantity value.

Look at any order entry application to see how they work. Also, any many-many (which is what the orderdetails table actually is) will work this way.

The ONLY field you need from the combo's table is the PK which becomes the FK in the child table.

Here's a m-m example that will show you how this works.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk