Alternative approach for unbound Option Group - 2007 1

[JaredSangco]

All,

after reading and searching the forums, most of my findings indicate that you can't utilize unbound Option Groups for continous forms. Is there an alternate solution?

Please advice.

 

[JaredSangco]

Majp...

HAHAHA...Simply amazing. I'm speechless. For someone like me who is a TRUE Newbie, I must say this is GREAT.

Is there anyway that I can actually copy this specific form to my current .mdb file - if you don't mind!!!

 

[MajP]

Yes from your database select "file" "get external data" "import". And you can import the form right in to your db. I imagine you will have a lot of names that need to be changed to get you tables to match up.

 

[JaredSangco]

Majp...

I've been working on this all day and there's a small functionlity that is not working seemlesly.

Using the Category List Box, when I first select a new category, it will not populate the 'CatName' text box. This only occurs when inserting a new category.

However, if I utilize the the ComboBox (cmbCatId) to insert the first record, then using the Category list box to insert a new category, it works fine.

So it's basically only the first Record. Any setting I failed to include? Assistance please.

 

[JaredSangco]

Majp..

I think i figured out the problem. Problem is I need to save my MainForm [partInformation] prior to selecting a new category.

Is there a setting i missed or something when finished enter a part?

 

[MajP]

It is not a setting, but has to do with the order of operations. In my demo I have to close out the new part form. This forces the record to get saved. In your case I think you open the category form before the record is saved. Somewhere on your new part form prior to opening the category form
Try adding this to save the record
If Me.Dirty Then Me.Dirty = False

 

[JaredSangco]

Majp,

If Me.Dirty Then Me.Dirty = False

Where exaclty on the form do i put this?

 

[MajP]

I assume in you new part form you do something like

docmd.open acform, theCategoryform

to open the category form. Do it before that. I would need to see your code to go from the new part form to the category form.

 

[JaredSangco]

Majp,

What's weird is that my current form is very similiar to your version 1, PartCatClass.

I don't know why yours works flawlessly. The combobox works fine, but the txtbox doesn't.

 

[MajP]

If you are interested you can post it on one of the free file sharing sites like I did and I can take a look. I used 4shared and it works fine, but there are several out there. It will probably take me a second once I see it, but I am certain that it has to do with ensuring that the record is saved first. If you can post it then future issues, problems, and enhancements I can help much faster.

 

[JaredSangco]

Majp...just to let you know ahead of time, it's a mess, but I will give it a shot and upload 2morrow.

Thanks for your assistance.

 

[JaredSangco]

Majp...

After looking through a bit more, i realized that your tables do not have relatshionships defined. Could this be the case?

 

[JaredSangco]

Majp...

Yep that was it. I removed my table relationships and now works like a charm? Isn't it best practice to define relationships with your entities in your DB?

 

[MajP]

Without seeing the issue I am Not sure why that would fix the problem. I think maybe something else is going on. I was more concerned about demo-ing the controls, not on a good db design. So I was just lazy on relationships. So you should define your relationships, and I would try to find the real cause of the problem. Here is why. If I define my relationship I can enforce referential integrity, cause cascade deletes, and primary key updates. In your case this is real important.

Referential integrity ensures you can not add a record to tblPartCats that is not related to a record in tblPart. This would be an orphan record.

Cascade deletes means if you delete a Part then all of the records in tblPartCat that relate to the part are deleted so that no orphans exist in tblPartCat. You definately are going to want to ensure this happens.

The update primary key names means if the primary key was "Lg" and you later decide you want "LRG" then it will update all foriegn keys with "LRG".

 

[JaredSangco]

Majp...you are absolutely correct.

Let me look into it further...

in the meantime...I want to put a 'tab control' box on the form since I will be adding yet another set of Categories. Is there any way that I can keep the 'lst box' in tact to the the Tab control. the subform is fine, however, the 'list box' of categories is present in all tabs?

 

[MajP]

If you drag a control onto a tab it floats over the tab control and appear on every page. You need to ensure that you select the page not the entire tab control and then paste it in the page. This will embed it in the page.

 

[JaredSangco]

Majp,

Just establishe relationship with your sample DB and the results are the same issue I am encountering. If you remove the relationship..it works flawlessley

 

[JaredSangco]

Majp...

Issue is when you link PartId with tables tblParts and tblPartCat. You can link them, but you can't enforce Referential integrity. Is this correct?

 

[MajP]

Sounds like you are fixing the symptom but not curing the problem. I will take a look at it later. Posting your db would still probably make it easier for me to see the problem. Here is my thought why this makes a difference. Often what you see on a form is not what is in the underlying table. Changes and additions are made on a form and then get committed to the database when the control or record is updated. So you may see a part ID or part name on the form, but that only exists on the form not yet in the table. These queries refernce partIDs from the form, but if they only exist on the form and not the table the queries will return no records. With the relationships set the upate happens later than without a relationship because of verifying referential integrity. So the queries run before the part id is commited to the table. So the fix is either to do a record save or change the event.

 

[JaredSangco]

Majp,

I enforced referential integrity on the 2 tables. A record save button prior to assigning a category is the solution.

Now, regarding the tab control. I've done what you did, but the "'Choose Categories to Add' list box, and the two additional cmdbuttons do not work once inside the tab control. Y is that?

BTW..i'm using your sample 1 DB [partcatclass]

 

[MajP]

an extra button is clunky, you may just want to add the save command prior to calling the query in the listbox event. The code should be something like.
Docmd.runcommand acCmdRecordSave

There are a lot of controls referenced in the queries and the code. If you did a copy and paste a control called "lstCategories" will no longer have that name because you can not have two controls with the same name so you will need to check all of the names and ensure they match. Ensure the event procedures still exist.

If you move a control from a form to a tab control on the form there is not a problem in how you would reference it. If you moved a control with code from the main form to the subform or vice versa there is a lot of issues. The code would remain in the main form.

So here is what to do. Go to you new control and find the event you want to verify. Click on that event and see if there is still matching code with it. You may want to go to the code in the events put something like
msgbox "Click event for lstCategory"
You can comment it out later. This way you can tell if the event is even firing or if the problem is in your code. If you get a message at least you know the event fires.