Insert to table after user input on Form

[JaredSangco]

All,
this is my first thread and I don’tknow where else to go. I am a non-experience Access user and VBA programmer. I have done all the minor things to my Access Programs (simple reports and forms), but now I have to do something a bit more complex.

What I have done so far. I created a form where I linked multiple tables to gather my data, however, the hard part now is to have the functionality to insert a new record to the tables when using a option group button or even a list box.

I have a parts table (tblparts [prtID ('automated number'), partname, categoryId] )with a 1 to many relationship with the Part Category Table (tblPartCat[categoryID, CategoryName, LocationID]). I also have a Location table (tblLocation [locationId, LocationName])which has a relationship to the TblPartCat.

Tblparts.PartName is an unbound text box that is free form. As far as the tblparts.CategoryName is concerned (there are 75), I would like to list them on the form individually as a label, but next to the tblparts.CategoryName label, I want to have a OptionGroup button with values from tblLocation.LocationName.

After the user completes inputing the tblparts.partname (textbox) and selecting each .tblLocation.LocationName with each corresponding tblparts.CategoryName label, the records are basically inserted to TblParts and TblpartCat.

Your guidance is appreciated. Again, I have no prior VBA experience and I have tried utilizing the standard code wizards as well as the templates, to no avail. I just can’t piece it all together.

If you feel this is too complex, your recommendations are welcome. This is basically how I initially visualized this form

 

[JaredSangco]

Bob,

yes, Category and Class are NOT stored in tblParts, only in tblPartCat.

I don't quite get what u mean here.

If you want to display them you can include them in the combo box source that uses tblParts (with a query that identifies the text for category and class) and place them in two other controls you would put on your form, using a statement like me.cboPartID.Column(2)

 

[JaredSangco]

Bob,

Forgive me. I don't know if you missed what I mentioned. I really want to individually list each Category on the form with a corresponding Radio Button or the like next to each Category.

I know listing each Category will be cumbersome, but i really want to treat this form almost like a survey type.

 

[BSman]

tblPartCat contains the category and the class for each part. Category and class are not stored in tblParts, so they only thing you need to do is display on the form the text of the category and class associated with the selected part. This is nothing fancy like radio buttons, combo boxes, or the like. Just simple text boxes that are populated with the results from the parts combo box. Or use a dLookup function to look up the category or class based on the PartsID in cboPartsID.

If you insist on showing all of the possible categories/classes, just put them in a list box on the form that shows all possible ones. Users will become bored very quickly with seeing the useless data. It's on the form for entering/editing data for tblPartCat where the combo boxes are needed so the users can select the category and class associated with each part.

 

[JaredSangco]

Bob,

that is the thing. This form is not intended to be used as a query form, just a "NEW" parts entry form where the user will input a part, select from a location, and for each category listed below, assign it a class.

Is this still possible without using VBA?

 

[BSman]

So why aren't you entering the data into tblPartsCat? You could make a subform that would use tblParts and only have the use enter a separate record for each location where the part is located. Link the main form to the subform with PartID.

 

[JaredSangco]

Bob,

In essence, I am adding records to tblPartsCat, however, the only records being added are the the PartId, ClassID, and Category ID.

This is what I have done, per your recommendation.

On the form, I have now 3 comboboxes that a user can select upon entering a PartNumber. 1) location name 2) Part Category (there are 95) and 3) Classification Name.

Issues:
1) When entering a new record, tblParts and tblPartCat are being populated, however, its only for the first record. Since there are Many Categories to One Part, I want the user to continue to select a differect Category and Assign its appropriate classification without having to enter a new Record with the same part.

2) Since there are 95 Categories, the ComboList box is extremely long thus, is there anyway that once the user selects a Category it is not visible anymore so it will not be entered twice? Will a sub-report be best here?

 

[BSman]

You should not be trying to enter on one form unless you also use a subform for the locations. Something like this:

Main form source tblPartsCat

Partnumber plus combo boxes for Part category and Class.

Subform source tblParts. Linked to main form by the PartID.
Just one combo box for the location. You could hide a control (text box) for the PartID and set it to the value of PartID in the control (txtPartID) (which could be hidden) on the main form. Refer to it from the subform as:
me.Parent.txtPartID

The main and subforms reflect your relationship of the tables. Think of it like a purchase order, but in this case the heading is the part (rather than the vendor) and the detail area contains all of the locations where that part is/will be (instead of the items being ordered on the purchase order).

 

[JaredSangco]

Bob,

First, thanks for taking the time to assist.

Let me give it a shot. Altough, I'm not 100% clear, i'll try and follow your direction.

Give me a few..

 

[JaredSangco]

Bob...

I think I've done the first part.

created a form using tblPartsCat and added two combo boxes for Part category and Class.

I'm confused with creating a subform. When I select the 'subform' button and select PartId, PartName and Location, it looks like a table. On the subform, where would I put the LocationName combobox?

[/code]

 

[BSman]

Create your subform separately as a form and make it continuous instead of single record. Then when you put the subform control on your main form you can select the form you just created.

I usually name subforms starting with "sfrm" instead of "frm".

 

[JaredSangco]

Let me give it shot. I actually didn't create a new form, but rather inserted a subform on the tblPartCat form

 

[JaredSangco]

Bob.

Ok..Done. What id did though was put the subForm on Top so the user will be able to enter the part number.

Question 1..How do I remove all the borders of the subform?

Question 2.. How do I accomplish this..

You could hide a control (text box) for the PartID and set it to the value of PartID in the control (txtPartID) (which could be hidden) on the main form. Refer to it from the subform as:
me.Parent.txtPartID

 

[JaredSangco]

You Still there, Bob?

 

[JaredSangco]

Another issue..

1) When entering new parts and selecting a location id on the subform, the PartsId on the mainform does not get populated. This should happen.

2) How can I make both SubForms and Mainform work in sync. Right now, it seems as if they are independent of each other since the subform looks like a separate form.