updateable subform how?

Question

I am a bit frustrated on this. It seems I am missing something very obvious.

I have several databases with need for this.      What I have is

tblVendor            a table of Vendor contact info   using autoID for a ID # for each Vendor called ID

tblItems       a table of one record for each item with descp of item. Has an autoID called ItemID.

tblItemsCarriedByVendor     a table of records, each with  3 fields per record, autoID, VendorId, ItemID.

frmVendor            a form based on tblVendor

what I am trying to do is add some type of control

so I can NOT only see what items vendor carries per tblItemsCarriedByVendor      I HAVE DONE THAT

SEVERAL WAYS, Using Access 2013 win7 64bit "SubForm" Button on Ribbon, from scratch, personally setting parent child fields in property sheets.  

basing main form on table or a qry

BUT what I haven't been able to do of figure out is how to add or set up that subControl or subForm so I am able to add New data to the base tables. 

from the subControl          being able to see what item by the descp.  I am adding to tblItemsCarriedByVendor     

any ideas?

---

Mark J

Answer 1

your post is not entirely clear;

table ItemsCarried is definitely the child table (many) to the parent Vendor table

You should not have any problem making ItemsCarried into a sub form where you can add info

Since you are having difficulty - you might consider in your ItemsCarried table to alter the Items field to being a LookUp field type, and the wizard will step you thru linking that to the Items table.  This will then automatically provide to you the plain language Item info visually while still linking to its key field.

 

Answer 2

Hi Mark,

I know you said you have tried many ways to do it but since we're not there to see what you're doing, would you please verify that you have tried the following:

1. Create a single-view form based on tblVendor (this will be your main form)
2. Create a continuous view form based on tblItemsCarriedByVendor (this will be your subform)
3. Modify the subform to change the ItemID textbox into a combobox
4. For the Row Source of the combobox, select tblItems
5. Set the Column Count to 2 and Column Widths to 0"
6. Modify the main form by adding the subform to it (either by dragging the subform or using the Wizard or doing it manually) but make sure the link fields are set up correctly (Master Link Fields = ID and Child Link Fields = ItemID)
7. Test the main form in Form view

Remember, you won't be able to add any items that don't exist in tblItems, and you won't be able to add any items in the subform if the main form is empty.

Hope that helps...

Answer 3

DB guy,

Thanks.

I have done most of what you suggested. 

What I am now going to do is see if I missed or had a typo on any of the suggestions.

Sorry I was not as clear as I should be.  Looking at code for so long, too easy to forget others not seeing what I am seeing.  Doesn't  help when rushing to leave and too sleepy. 

your line below gives me one answer was hopping to not hear.

"Remember, you won't be able to add any items that don't exist in tblItems"

if that is how it must be, so be it.   It did look like I had "*" to indicate I could add a new item but kept getting item not in list.  So you line above explains that.   I will respond with results.

---

Mark J

Answer 4

"Remember, you won't be able to add any items that don't exist in tblItems"

That's easily catered for.

You have a conventional binary many-to-many relationship type between tblVendor and tblItems, modelled by tblItemsCarriedByVendor.  The conventional interface for this would be a vendors form in single form view, bound to a query which orders the records in whatever order you wish, e.g. by the vendor name;  within the parent form would be a subform based on tblItemsCarriedByVendor, linked on the ID keys, and containing a combo box bound to the ItemID column, but set up to show the item names.

Inserting data into tblVendor and tblItemsCarriedByVendor is merely a case of entering records into the form and subform.  To insert a new row into tblItems, i.e a new item not currently included in the combo box's list, put code in the ItemID combo box's NotInList event procedure.  If, other than the key, the only value to be inserted is the item name then the code can do this transparently; if additional columns in tblItems  need to have data inserted then the code can open a form in dialogue mode to do this, passing the new value typed into the combo box to it via the OpenArgs mechanism.  You'll find examples of both in NotInList.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

There are other interfaces you could use to represent a many-to-many relationship type like this, which you'll find examples of in the StudentCourses demo in the same OneDrive folder.  I'd recommend a conventional form/subform, however, which is far simpler to implement than the other methods.

To enable quick navigation to a vendor in the parent form you can include an unbound combo box to select a vendor by name.  You'll find an example in the FindRecord demo in my OneDrive folder.

---

Ken Sheridan, Stafford, England

Answer 5

Thank you this is one of the missing pieces I needed to finish this project.

Mark.

---

Mark J

Answer 6

Ken Sheridan,

Thanks,

After thinking over your reply for a bit of time, I realized that

Your info was the last info I needed.  Now I can update item vendor carries while still on form.  Almost, to have SubForms show updated info,

I still need to move the frmVendor forward or backwards by one vendor and then back to original vendor. 

I think I need to Refresh, Re-query or Repaint the SubForms, some forms are based on a qry others a table. 

So all NOW works as I needed.

1. I have a form frmVendor  (single vendor shown) with all vendor contact info and notes about vendor.

2. I have a subForm based on a qry based on a table of ItemsID and ItemsDescp, a table of Vendors,
         and the table  tblVendorCarries   

That ONLY shows what the current Vendor carries.  frmVendor ONLY shows one vendor at a time.

3. I added a combo box with record source of a qry based on tblItems, bound col is field of qry with ID# of item I wanted to add to the current vendor.  on after update it posted value to a textbox on frmVendor. 

Private Sub cboSelectWanted_AfterUpdate()

Me.txtCboResult = Me.cboSelectWanted
Exit Sub

Added a cmdAddResultToTblVendorCarries to frmVendor.  

yes, I think, I can use a Append Query instead of a recordset. I have been working on this too long. So I finished project very quickly using code I knew would work in this project and several others with same need. 

I decided to put on my to "search on how to do list",  Learn IF I can, and How I can do this using and Append query and not using a DAO.recordset   I am aware of possibility of DAO code might be no longer usable some time in the future.

Private Sub cmdAddResultToTblVendorCarries_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblVendorCarries", dbOpenDynaset, dbSeeChanges)

    With rst
        .AddNew
     '   .Fields("ID") = 0
        .Fields("VendorIS") = Me.ID  ' Me.ID holds the current Vendor ID #
        .Fields("ItemCarriesIS") = Me.txtCboResult     ' holds the ID# of what item I want to add to Vendor.
        .Update
        .Close
    End With

    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

---

Mark J

Answer 7

Edward Z,

Want to make sure you got my thanks.

your info in this post was more than just helpful.

it was extremely helpful,

your post had info I needed to finish this project.

Mark J.

---

Mark J