How to program Subform as select list

[larryb43 via AccessMonster.com]

I've built a very simple Bill of Material database that has a many-to-many
relationship between two main tables: tblQWMaster and tblPartNo. There is an
intersecting table called tblQWParts. The intesecting table also has a field
called QtyUsed and some price fields that are calculated. The concept is
that every component (Part Number) could be in many Assemblies (QWMaster) and
many Assemblies could have many Part Numbers. This DB uses tables that are
already populated with data (you don't enter a line of information to match
to another record other thanQtyUsed). I've tested the relationships and
everything works as it's supposed to when mannually connected by putting the
ID (Primary Key) numbers of one the PartNumbers into more than one of the
QWParts intersecting table and entering a QtyUsed. I've built queries that
list both matched (linked) and unmatched records and they work great showing
all the info I need.

Here's the question: I have a Mainform called frmQWMasterEdit with two
subforms. The top of the main form shows one Assembly record and it's
description and ID number. The first subform below that shows matched
(linked) parts that are already linked to the Assembly at the top of the form.

Below this subform is another subform that is not linked to the Assembly at
the top and displays all available (unmatched or not linked) Part Numbers.
This second subform gets it's information from a query that has both
PartNumbers and QWParts with both ID fields and QtyUsed in this query (and
subform) What I want to do is enter a quantity in the QtyUsed field on the
second subform and click a button I've added that says Add and have the ID
number (PartNoID) of the PartNumber line populate the field in the
intersecting table and enter the quantity I've entered into the QtyUsed field
in the intersecting table then requery. Hopefully the item will show up in
the first subform since it now matches (linked) and the number selected
dissappears from the second subform since it's now matched. I'm not good at
VBA yet. I'm trying to learn but not sure how to make this happen.

 

[Barry A&P]

Larry
I am reasonably new at access also so i sometimes dont use the most elegant
methods to complete tasks but here is how i did the exact same thing..

What i do is i have a temp table (on the Front end if you have a split
database)
the temp table has TempID (PK), TempPartNoID, & TempQTY
then set this in a 1-1 relationship to your "NotLinked" Subform on
TempPartNoID and add the TempQTY field to your "Not Linked Subform"
now everytime you put a number in the tempQTY field on your subform a record
is created in the Temp table with the TempPartNoID populated. (i hope i
described this corectly its been a while..

Lets add A Clear Temp Button so the user can manually clear the list

Private Sub ButtonClearList_Click()
'DoCmd.SetWarnings False ' please leave this out for testing.....
DoCmd.RunSQL "DELETE T_TempAdd.TempAddID, T_TempAdd.TempPartNoID, _
T_TempAdd.TempQTY"
DoCmd.Requery "YourUnlinkedListName"
DoCmd.SetWarnings True
End Sub

Now when your masterEditForm is opened lets make sure there are no old
records in the temp table. we will delete them in the onload event of the
master edit form with

Private Sub Form_Load()
ButtonClearList_Click
End Sub

Now for your Update Button

Private Sub ButtonLinkItems_Click()
'DoCmd.SetWarnings False 'Please Comment this line out for testing...
DoCmd.RunSQL "INSERT INTO tblQWParts ( AssemblyNoID, PartNoID, Quantity) _
SELECT Me!AssyIDCombo, T_TempAdd.PartNumberID, T_TempAdd.Quantity _
FROM T_TempAdd _
WHERE (((T_TempAdd.Quantity) Is Not Null))"
ButtonClearList_Click
DoCmd.Requery "YourLinkedListName"
DoCmd.SetWarnings True
End Sub


I Hope This helps get you there.. please backup your database as i may have
mistyped something in this post..
if helpfull please check this post as such
Thanks
Barry

 

[larryb43 via AccessMonster.com]

Hey Thanks for the response Barry. I had already begun testing my structure
again prior to seeing your post and found an error in my ways. The biggest
problem was that my supporting queries were flawed in their approach. I knew
that the relationships were working correctly, I just did it wrong! I
literally built two seperate new projects till I discovered what i was doing
wrong. Once I fixed the query, then rebuilt my subform to use a combo box to
select and store the ID in the correct field, all started working.

I appreciate your input

Larry

Larry
I am reasonably new at access also so i sometimes dont use the most elegant
methods to complete tasks but here is how i did the exact same thing..

What i do is i have a temp table (on the Front end if you have a split
database)
the temp table has TempID (PK), TempPartNoID, & TempQTY
then set this in a 1-1 relationship to your "NotLinked" Subform on
TempPartNoID and add the TempQTY field to your "Not Linked Subform"
now everytime you put a number in the tempQTY field on your subform a record
is created in the Temp table with the TempPartNoID populated. (i hope i
described this corectly its been a while..

Lets add A Clear Temp Button so the user can manually clear the list

Private Sub ButtonClearList_Click()
'DoCmd.SetWarnings False ' please leave this out for testing.....
DoCmd.RunSQL "DELETE T_TempAdd.TempAddID, T_TempAdd.TempPartNoID, _
T_TempAdd.TempQTY"
DoCmd.Requery "YourUnlinkedListName"
DoCmd.SetWarnings True
End Sub

Now when your masterEditForm is opened lets make sure there are no old
records in the temp table. we will delete them in the onload event of the
master edit form with

Private Sub Form_Load()
ButtonClearList_Click
End Sub

Now for your Update Button

Private Sub ButtonLinkItems_Click()
'DoCmd.SetWarnings False 'Please Comment this line out for testing...
DoCmd.RunSQL "INSERT INTO tblQWParts ( AssemblyNoID, PartNoID, Quantity) _
SELECT Me!AssyIDCombo, T_TempAdd.PartNumberID, T_TempAdd.Quantity _
FROM T_TempAdd _
WHERE (((T_TempAdd.Quantity) Is Not Null))"
ButtonClearList_Click
DoCmd.Requery "YourLinkedListName"
DoCmd.SetWarnings True
End Sub

I Hope This helps get you there.. please backup your database as i may have
mistyped something in this post..
if helpfull please check this post as such
Thanks
Barry

I've built a very simple Bill of Material database that has a many-to-many
relationship between two main tables: tblQWMaster and tblPartNo. There is an

[quoted text clipped - 27 lines]

dissappears from the second subform since it's now matched. I'm not good at
VBA yet. I'm trying to learn but not sure how to make this happen.