Edit Junction Table - Unbound - VBA

[CJ.B]

Hello,

New to access (and this forum) and working on a small db that includes the following tables:

• tbl_Orders
• tbl_Junction (Order Details)
• tbl_Products

While the form itself is bound to tbl_Orders I decided to use unbound text boxes to enter the data (knowing I was in for some work!). Reason is that if textboxes were bound would need to use a subform with multiple combo boxes and that UI did not seem suitable for the application … although I have seen many examples where its just fine.

Anyway, I’ve completed the code to add new records to tbl_Orders and tbl_Junction from the unbound textboxes. I'm using the VBA .AddNew method and is working well. Now I need to integrate code that will allow users to edit existing records while they are viewing them on the form.

I have the code to look up the record set I want to edit in tbl_Junction and am familiar with the .Edit method but the issue is that the required changes MAY include not just editing existing records but also deleting and adding new records. For example, the initial order may have been for Apples and Oranges and the revised order may change the number of Apples, eliminate Oranges entirely, and add some Bananas. So, it seems I need to .Edit Apples, .Delete Oranges, and .AddNew Bananas?

Not sure best way to approach. Should I try to write code to determine where I need to .Edit/.AddNew/.Delete? -- not looking forward to that. Thought maybe could delete the entire junction record set and then just .AddNew for everything per the revised order. That should work as the display on the form would be correct for the whole order. But maybe would cause a problem as would remove the FK in tbl_Junction that corresponds to the PK in tbl_Orders? Anyway, wondering if could get some guidance on best way to approach etc. Thanks!

 

[plog]

Still lost on why you didn't use a subform. I mean, all these issues go away with that method. What's the multiple combo box issue?

 

[CJ.B]

Didn't use the subform as I really didn't like the multiple combobox UI -- for this particular application -- I think it can be great if lots of options etc. I will have about 8 options and users will usually choose about 3. I know the subform is preferred, but I really wanted the unbound UI ... and now I pay the price ... but learning/interesting so that's OK!

 

[plog]

8 options of what? That's what a drop down is for, to house options.

Can you post the structure of tbl_Junction?

 

[spikepl]

I too am very curious: dumping bound forms is a lot of pain, and I'd like to see how display/availability of options influences the selection of unbound forms.

 

[CJ.B]

I cant seem to insert an image but Form display is very basic -- example is below. The Fruit item chosen and the quantity are stored in tbl_junction. Again, if I had more items I would gladly use combos.

Fruit No. Total
Apples 4 $1.50
Oranges
Bananas
Cherries 2 $2.00
Pears
Grapes 4 $3.00
Mangos

 

[plog]

That certainly can't be your junction table layout. How does it create the junction? There needs to be foreign keys to tbl_Products and tbl_Orders in there.

I don't really care about sample data, I care about the fields. Post like this:

tbl_Junction
Junction_ID, autonumber primary key
Product_ID, number, foreign key to tbl_Products
etc.

Give me field names, data types and purpose of each field.

 

[CJ.B]

OK, tbl_junction only has 3 fields, the primary key is a composite of the two ID/Primary key fields shown below:

tbl_junction:
- ID_Orders: Number field, primary key for tbl_Orders
- ID_Product: Number field, primary key tbl_Products
- Quantity: Number field, records number of products chosen

I have the code to populate all tables when adding new records and working OK ... issue is the editing.

 

[plog]

That's correct, which only confuses me more as to why you are against a normal subform.

The subform would have only 2 inputs on it:

A drop down to choose the product (e.g. Bananas, Kiwi, Apples, etc.)
An input for the Quantity

Additionally, it would be connected to your main form in a parent/child relationship on ID_Orders. When you want to edit how many or which type of product, you simply edit the subform. Honestly, I think you've over-thought this thing.

 

[CJ.B]

I hear you, its not that I'm against subforms I just think this is a situation where an unbound structure may make sense as: options are few, the UI is especially important, and the db is very basic so hopefully coding will not be too complex. Its fair to say a significant personal preference in involved as well but nonetheless I think the unbound option has its place. Appreciate your all input -- thanks!

 

[jdraw]

CJ.B,

Do you have a clear description of what you are trying to automate?
Any sort of specification you could share with readers?

 

[vbaInet]

I cant seem to insert an image but Form display is very basic -- example is below.

Zip the screenshots and upload the zipped file. And include a screenshot of your form as well, both in design and form view. Here's how (read from step 3):

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

You're new to Access, perhaps not new to programming, but you want to do something that's meant for experts. Working with an unbound form isn't just about managing the edit, delete, update and insert processes, there are other much more difficult areas to handle; editing a record is the easy part. That's why you're being advised to use a bound subform, learn advanced VBA (not just basic/intermediate VBA) then move on to unbound forms.

 

[CJ.B]

OK, I will try to make clearer, please also refer to original post.


Looking for the best way to edit existing records in tbl_junction.

As noted I am able to enter data to add new records from a form via VBA, like this:

With rst
.AddNew
!Quantity = frmMain.txtQuantity
.... etc ........
.Update
End With


​

I now need to integrate code using the .Edit method. The editing per se should not be a problem, the problem is described below.

While each Order will have only 1 record in tbl_Orders , the related number of records in tbl_junction will vary between 1 and 8 per the products chosen in the original Order. I can’t simply edit the existing records in tbl_junction as only records where the quantity has changed can be edited. Other records need to be added where products not in the original order are in the revised order. And records need to be deleted where products in the original order are not in the revised order.

So, seems I need a test of some sort to determine where to AddNew or Edit or Delete tbl-Junction records based on a comparison of the existing records there (from the original order) vs the new revised order showing on the form at this point.

Or maybe can delete the entire tbl_junction record set and add all records per the revised order.

Its easy enough for me to edit ALL existing records, but again the issue is may need to add or delete or edit and identify when to do so.

 

[spikepl]

The easiest thing is to use a disconnected ADO recordset. You can hardly make it more complicated. Then you can add, edit or delete and then stuff everything back into the db. Good luck with this - you'll need plenty.

http://www.access-programmers.co.uk/forums/showthread.php?t=269186

 

[CJ.B]

Thanks for the link, I will review -- though using ADO is not going to happen! That would kill me. And thanks for warning that this is difficult - that helps.

I was thinking if I add a field and tag the existing tbl_junction record set for deletion, then add the complete new order from the form, and then go back and delete the original tagged rst maybe could work ... will see.

 

[vbaInet]

It's not about personal preferences here, we're trying to tell you what is logically sound. And considering that you're new to Access, you may not be aware of the full capabilities of a subform. The order details table will be the Record Source of the subform and new items can be added, unwanted items deleted, quantity of existing items changed all within the same subform which is linked to the main form containing the order. All of this can be done at the user's will.

An order can have multiple order details, so how do you represent multiple order details with your current setup? By dropping 16 textboxes and 16 command buttons (delete and save)? Whereas in a subform you only drop 4 controls (textboxes, combo boxes and command buttons).

If you wanted a situation where any changes (additions, deletes, edits etc) do not become part of an order until the user explicitly hits a save button, then I can understand why you're thinking that a subform won't work. Well, a subform will still work, an extra table or a change to your existing table may be required. Otherwise, if you still want to keep things unbound - even after all the advice - then disconnected recordsets is the best way. Other workarounds would involve Types or Classes.

Can we see a screenshot of your form as previously requested.

 

[CJ.B]

OK, I will post in a day or so, I want to work with this a bit first - thanks for the feed back and sorry for the delay.

 

[jdraw]

CJ.B,

You seem a little hesitant on specifics and detail. Have you done any work/training in database or Access?
Do you have a response to my questions in post 11?

 

[CJ.B]

My post 13 was in response to your post 11. That's as clear as I can write it out and I will upload some stuff to illustrate but will take me a day or so. No training and first db ... its very basic but junction table has led to complications ... Thanks

 

[jdraw]

It isn't a junction table that has led to complications. You seem to have a Many to Many relationship, and a junction table is a tried and true way of dealing with M:M.

I recommend you watch some tutorials, and work through some simpler databases. You will learn concepts of database that will be useful in subsequent projects.

For database design:
RogersAccessLibrary Entity Relationship Diagramming

Normalization:
RogersBlog

For Access:
Richard Rost 599CD has a number of free Access 2010 videos on youtube

ManytoMany:

TrainSignal