Using a combo box to switch to and add new records

[cocacrispy]

I've read many forum posts and articles and haven't made any headway with this. Let me describe my tables and when I'm trying to do and see if any one can help me.

I have an items table, a vendors table, and a vendorItems table. The primary keys are ItemID, (ItemID, VendorID), and VendorID respectively. vendorItems is a join table, facilitating a many-to-many relationship between vendors and items. I've built a form for entering data on items. I want to include a subform on this form that includes information on the relevant vendorItems records. This is displayed as a single form, not a continuous form, because the data is complicated and best presented one at a time.

I would like a combo box (it can be in the items form or the vendorItems subform) to do the following:
- get it's rowsource from the vendors table, displaying the name and storing the vendorID
- after the combo box is updated, look for a record in vendorItems where the vendorID = the updated combo box's value and the itemID = the current itemID of the items form.
- If said record is found, move the subform to this record for editing
-If said record is not found, create a new record in vendorItems where vendorID = the updated combo box's value and itemID = the current itemID of the items form.

Here is a picture of the form. The combo box labeled vendor is the one I want to do all of this work. As I've said before if it makes more sense for it to be in the items form itself then that is no problem.

Thanks in advance for any help.

[NeoPa]

Let me first try to organise the metadata into understandable form :

Table Name=Items
ItemID; Autonumber; PK
Name; String
Description; String
SerialNo; String
Location; String

Table Name=Vendors
VendorID; Autonumber; PK
Name; String
StockNo; String
ItemsPerUnit; Numeric

Table Name=VendorItems
ItemID; Numeric; FK
VendorID; Numeric; FK

Please repost with more accurate info if you can.

[NeoPa]

You would need in the ComboBox RowSource something like :

SELECT V.VendorID,V.Name
FROM VendorItems AS VI INNER JOIN Vendors AS V
ON VI.VendorID=V.VendorID
WHERE VI.ItemID=txtItemID

This could all be entered on a single line if preferred with spaces replacing new lines.
BTW this assumes the the name of the TextBox control holding the ItemID is txtItemID.
Test this out first as I'm not sure if the ComboBox being in the SubForm will cause any problems.

[NeoPa]

There will be a procedure created for the AfterUpdate event of the Vendor ComboBox (We'll call cboVendor) but we don't know all the details of what's required in there until you post back what you find when testing out the earlier posts.

Private Sub cboVendor_AfterUpdate
'... Some dependant code here
    Call Me.SubFormControlName.Form.ReQuery
End Sub

[cocacrispy]

NeoPa,
Your summary of my schema is spot on. In the vendorItems table, the two ID domains are set as primary keys as well as serving as primary keys. I could just as easily have included a 'vendorItemID' and set that as the primary key, but the former solution seemed best.

The Row Source you've given seems like it will work but I'm unsure if a join is needed. I would like all records in the vendor table to be selectable in the combo box regardless of wether they have corresponding records already in the vendorItems table. So, for example, even if the item with itemID 1 has records only for vendors with vendorID 2, 4, and 5 (in the vendorItems table, this would be the primary key pairs (1,2), (1,4), and (1,5)), I would still like the combo box to have a row source with, say, 1,2,3,4 and 5 as available selections, assuming there are 5 vendors total.
Preferably, the afterUpdate event will then determine if the selected vendorID has an entry in the vendorItems table where itemID = 1 (in keeping with the previous example). Once determined it will switch to that record if it exists, or create it if it does not.

I'm assuming you already know all this and your reason for using the inner join will become apparent later on. Thanks for your interest and I look forward to your solution.

[NeoPa]

NeoPa,
Your summary of my schema is spot on. In the vendorItems table, the two ID domains are set as primary keys as well as serving as primary keys. I could just as easily have included a 'vendorItemID' and set that as the primary key, but the former solution seemed best.

If you'd had a VendorItemID then you would still have needed the VendorID & ItemID fields as well.

The Row Source you've given seems like it will work but I'm unsure if a join is needed.

My reason for a join here is that I felt the Name field would be required to show in the ComboBox. If that is not required then, certainly, no JOIN would be. You would simply use the [VendorItems] table.

I would like all records in the vendor table to be selectable in the combo box regardless of wether they have corresponding records already in the vendorItems table. So, for example, even if the item with itemID 1 has records only for vendors with vendorID 2, 4, and 5 (in the vendorItems table, this would be the primary key pairs (1,2), (1,4), and (1,5)), I would still like the combo box to have a row source with, say, 1,2,3,4 and 5 as available selections, assuming there are 5 vendors total.

OK, so now I understand better ;) In that case :

SELECT VendorID,Name
FROM Vendors

will do you.

Preferably, the afterUpdate event will then determine if the selected vendorID has an entry in the VendorItems table where itemID = 1 (in keeping with the previous example). Once determined it will switch to that record if it exists, or create it if it does not.

Does that mean that the data in the SubForm is from the VendorItems table as you haven't supplied me with any fields in there other than the PK itself. This implies it is simply a connecting table and not one for data. Please clarify here. If there is more info in the schema that is required please post it. I'm stuck at this point.

I'm assuming you already know all this and your reason for using the inner join will become apparent later on. Thanks for your interest and I look forward to your solution.

No, that wasn't clear before.
That reminds me - I'm not trying to post solutions - simply to help you to go in the right direction. I forget that sometimes :(

[cocacrispy]

Let's use filler data instead of speaking in abstracts all the time. Here are some records for each of the tables:

Items table:

ItemID Name
1      lead pipe fitting
2      electrical tape (single roll)
3      measuring tape
4      12ft. ladder

Vendors table:

VendorID Name
1        Barett
2        Nolan
3        CostCo

VendorItems table:

ItemID VendorID Price
1      1        $1.67
2      3        $3.09
1      2        $1.42
1      3        $1.50

The rowsource for our special combo box will be

SELECT vendors.vendorID, vendors.name FROM vendors;

and some relevant access properties:

BOUND COLUMN = 1
COLUMN WIDTHS = 0";2";

So, the cboVendor combo box will display the names of the vendors, and 'store' the selected vendorID.

If our items form were currently on the item with itemID=1, then cboVendor would simply act as the standard combo box that you can get from the wizard that lets you switch among records. This is mostly coincidental because item 1 happens to already have entries for all of the vendors.

If our items form were instead on the item with itemID=2, then cboVendor would act differntly. if the user selects 'CostCo', then '3' would be stored in cboVendor and code in the afterUpdate event would switch the VendorItems subform from whatever record it is currently displaying to the vendorItems record where itemID = 2 and vendorID = 3.

if the user alternatively selects 'Nolan' then something different should happen. a vendorItems record with itemID = 2 and vendorID = 2 will be searched for and not found. When this happens a new record is added to vendorItems (the same one we failed to find in our search: itemID=2 and vendorID=2). the subform will then display the new record.


If it helps to conceptiualize the problem, you can think of it this way. If not, please ignore it because it probably just confuses things. An alternative solution to this magic combo box, would be to automatically create a record for each vendor the vendors table in the vendorItems table whenever a new item is added to the items table, and automatically create a record for each item in the items table in the vendorItems table whenever a new vendor is added to the vendors table. If this were done then the combo box could be created using the access wizard for switching among records. This approach is of course impractical and creates a lot of empty records.

Thanks for your interest and I look forward to you teaching me how to fish ;)

[NeoPa]

Example is good, but I find it easier to work from definitions.
Your examples are quite clear though and provide more info which is good.
To start then, I will repost the updated MetaData.

Table Name=Items
ItemID; Autonumber; PK
Name; String
Description; String
SerialNo; String
Location; String

Table Name=Vendors
VendorID; Autonumber; PK
Name; String
StockNo; String
ItemsPerUnit; Numeric

Table Name=VendorItems
ItemID; Numeric; FK
VendorID; Numeric; FK
Price; Currency

This will help anyone else reviewing the thread later. I did consider updating the earlier post but realised that would confuse someone reading it later as the conversation wouldn't have made proper sense.

[NeoPa]

The rowsource for our special combo box will be

SELECT vendors.vendorID, vendors.name FROM vendors;

Personally, I don't bother with the table (RecordSet) qualification when only one RecordSet is specified in the FROM clause. It makes it longer and there's more to read and understand.

SELECT VendorID,Name FROM Vendors;

...Stuff about how it should work...

This bit I get. I'll share what I know but it's limited I'm afraid.
When the Vendor is selected from cboVendor, the code (In the AfterUpdate event procedure) should set the RecordSource of the SubForm's Form object to SQL querying the VendorItems table but matching only the record (if it exists) which matches both the ItemID & the VendorID selected. When this is done, like any updatable query, if no records match, then the query will select the New record at the end. The problem here is to ensure that, if data is entered and saved away, the ItemID & VendorID are correctly set even if there are no ItemID & VendorID controls on the form. Let's try doing it this way and see if Access fills the other data in automatically. If it doesn't we'll look again.

Private Sub cboVendor_AfterUpdate
    With Me.SubFormControlName.Form
        .RecordSource = _
            "SELECT ItemID,VendorID,Price " & _
            "FROM VendorItems " & _
            "((ItemID=" & Me.txtItemID & ") AND " & _
            "(VendorID=" & Me.cboVendor & "))"
        Call .ReQuery
    End With
End Sub

We need to know the name of your SubForm control (as opposed to the name of the form used within that).

Thanks for your interest and I look forward to you teaching me how to fish ;)

I like the reference - very well put.

[NeoPa]

Firstly, I updated your original post to show the image rather than to link to it.
Secondly, don't forget to read my other replies since you last posted. There may be questions and there will certainly things I need you to read.