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

 

[maxhugen]

A little unclear on your design, is it:

[Location] 1 --- ? [Category] 1 --- ? [Parts]

Max Hugen
Australia

 

[JaredSangco]

Hi Max,

THanks for responding..


[location] 1 to Many [category] 1 to many [parts]

if you need any clarification, please let me know.

 

[TheAceMan1]

How are ya JaredSangco . . .

If my read is correct, you need bound forms in the following manner:
[ol][li]Bound MainForm [blue]Location[/blue] with bound subForm [blue]Category[/blue].[/li]
[li]Bound subForm [blue]Category[/blue] with bound subForm [blue]Parts[/blue].[/li][/ol]
This arangement accesses and saves to the tables directly, with no need for insert. If you have problems constructing this let us know . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JaredSangco]

Max, Correction. I apologize. I've been overwhelmed with this task. I missed some information- basically another table. Let me clarify and give u some table values at the same time.

[tbllocation]
locationid locationName
1 Los Angeles
2 Canada
3 NY

[tblparts]
PartID (autonumber) PartsName LocationID
AutoNum 1 Brakes 1
AutoNum 2 Shocks 2
AutoNum 3 Tower 1

[tblPartCat]
PartId "from [tblparts]" CatID ClassID
1 1 1
1 2 2
2 1 3
2 4 3
3 3 3

[tblCategory]
CatId CatName
1 Outer
2 Frame
3 Cross
4 Inner


[tblClass]
ClassId ClassName
1 Small
2 Mid
3 Large



1) Tblparts.PartName is an unbound text box that is free form that the user will input

2) User will then select a Location from a ListBox or Option Group with Values populated from [tblLocation.LocationName]

** After Step 1 and 2, [tblparts] will be populated

3) Now, for every part from tblParts.PartName, I will have a "label" corresponding to each [tblCategory.CatName] and next to the "label", I would like to have a Option Group with Values from [tblClass.ClassName] that the user selects.

NOTE: IT is NOT required for the user to select a[tbl.ClassName for each [tblCategory.CatName] if it does not apply. IF NULL, a record will not be inserted to the [tblPartCart] table

A "submit" button will be on the bottom of the form and upon clicking the "submit" button, the tables[tblParts] and [tblPartCart] would essentially be the ones populated.

Does this make more sense. I wish there was an edit feature, i would have edited my initial posting. I apologize from the confusion.

Thank you in advance for your patience and assistance.

 

[JaredSangco]

TheAceMan1,

Thanks for your assistance. I was in the midst of clarifying my request and didn't read your response till now.

I apologize for my lack of knowledge, but I am not familiar with your recomendation. I need 'Very detailed' explanation as my experience is 2 out of 10 when it comes to VBA and Access in general.

To be frank, the only reason I was tasked to complete this program was because the person in charge is on medical leave from 2 months and will not be able to complete it. I volunteered since I'm always willing to learn something new. However, this has come to be overwhelming. What I thought would only be simple reports and forms has now come to more requests that I have no knowledge of. arghhh

 

[JaredSangco]

Good Morning All,

I thought about it more last night and I guess the best approach would be tackle this task in two parts. I'll worry about the first then the second. This way, I'll have a better idea.

First Part..

1) Tblparts.PartName is an unbound text box [TxtPartName] that is free form that the user will input

2) User will then select a Location from a ListBox [ListLocationName] or Option Group with Values populated from [tblLocation.LocationName]. NOTE: tblLocation.LocationId = tblParts.LocationId

** After Step 1 and 2, [tblparts] will be populated.

I have a 'Save' button with the following code..

here is my code..

Private Sub cmdSave_Click()
   Dim SQL As String
   
   SQL = "INSERT INTO tblParts ( PartName, LocationID) " & _
         "SELECT textPartName, " & _
                "tblLocation.LocationName, " & _
         "FROM tblLocation " & _
         "WHERE (tblLocation.LocationId=[Forms]![PartEntry]![ListLocationName]);"
   DoCmd.RunSQL SQL
End Sub

I get the following error.
Runtime error 3134.
Syntax error on insert statement.

I believe my issue is in the "Where"

 

[JaredSangco]

Still trying here.

I think one of my issues is also because of the "From" clause since I am linking both tblparts.LocationID with tblLocation.LocationID, I forgot to do this..

Private Sub cmdSave_Click()
   Dim SQL As String
   
   SQL = "INSERT INTO tblParts ( PartName, LocationID) " & _
         "SELECT textPartName, " & _
                "tblLocation.LocationName, " & _
                  "FROM tblLocation INNER JOIN tblParts ON tbllocation.locationId = tblParts.locationId " & _
         "WHERE (tblLocation.LocationId=[Forms]![PartEntry]![ListLocationName]);"
   DoCmd.RunSQL SQL
End Sub

But I am still getting an error. On the debug screen, the following is highlighted

DoCmd.RunSQL SQL

 

[JaredSangco]

All,

I'm trying here...arghh. I modified the above code to

Private Sub cmdSave_Click()
   Dim SQL As String
   
   SQL = "INSERT INTO tblParts ( PartName, LocationID) " & _
         "SELECT & Me.textPartName, " & _
                "& Me.tblLocation.LocationName, " & _
         "FROM tblLocation " & _
         "WHERE (tblLocation.LocationId=[Forms]![PartEntry]![& Me.ListLocationName]);"
   DoCmd.RunSQL SQL
End Sub

But now, i get the following error

Run-Time error '3075'
Syntax error (missing operator) in query expression '& Me.TextPartName'

 

[JaredSangco]

Assistance anyone? I think I just confused myself even more.

 

[BSman]

I think it would be good to step back and describe what this application is basically supposed to accomplish. Looking at your sample tables and data, it would appear to be an inventory management situation where tblParts is the main data table, showing what part and classication is where (and I presume there is also data about quantity, etc., in this table). The other tables all appear to actually be lookup tables that will be used by tblParts.

If my guess is reasonably correct, then you should have a main menu where the user can go to a form for each lookup table to maintain the data in those tables, which shouldn't change that often (relative to tblParts). Also from the main menu form the user should be able to go to the form for tblParts, which will include combo boxes for each column so it records the ID from each lookup table, but actually displays the text associated with the ID value.

Bob

 

[JaredSangco]

Bob,

You are precisely correct. I'm talking it one step at a time and to tell you the truth, if I can get this step going, it'll make for a good start.

I just can't seem to get this 'simple insert' statement.

Private Sub cmdSave_Click()
   Dim SQL As String
   
   SQL = "INSERT INTO tblParts ( PartName, LocationID) " & _
         "SELECT & Me.textPartName, " & _
                "tblLocation.LocationId, " & _
                  "FROM tblLocation INNER JOIN tblParts ON tbllocation.locationId = tblParts.locationId " & _
         "WHERE (tblLocation.LocationId=[Forms]![PartEntry]![ListLocationName]);"
   DoCmd.RunSQL SQL
End Sub

 

[BSman]

Run your form with one table as the source (tblParts). Use the combo boxes like I described for the lookup table data. Then you won't need any insert statement, since the data editing and adding will occur right from the form.

Bob

 

[JaredSangco]

Bob,

Hmmm...Not sure to accomplish this. Would you be so kind to explain.

Currently instead of ComboBoxes, I am using a list box. I'm uncertain how to use this as a lookup on the form without 'insert' occuring behind a cmdbutton.

 

[JaredSangco]

Still trying to figure out this 'Insert' statement. What i did find through the boards is I didn't need the '&' in
Me.textPartName

so my code now reads...

Private Sub cmdSave_Click()
   Dim SQL As String
   
   SQL = "INSERT INTO tblParts ( PartName, LocationID) " & _
         "SELECT Me.textPartName, " & _
                "tblLocation.LocationId, " & _
                  "FROM tblLocation INNER JOIN tblParts ON tbllocation.locationId = tblParts.locationId " & _
         "WHERE (tblLocation.LocationId=[Forms]![PartEntry]![ListLocationName]);"
   DoCmd.RunSQL SQL
End Sub

Unfortunately, i still get an error of..

Runtime error 3134.
Syntax error on insert statement.

Please help

 

[BSman]

A combo box uses a table or query as its data source, so each combo box would use the appropriate lookup table as its data source, returning the ID column and the description. You set the column width display property to 0" for the first column (the ID) and an appropriate width for the text, so only the text will display. But the column returned as the value should be set to column 1 (the ID column).

When an existing record is displayed, the text is automatically displayed in the combo box.

Since you don't need the multiple table query any more as a data source (to display the text associated with each lookup ID column), the form will automatically write the new/changed data to the table that is the data source for the form. No insert is needed.

The one thing to be aware of is that all of the sources for the combo boxes on the form are read when you open the form. So if you popped up a form to add more records to a lookup table, when you closed the pop up form and were back on your main data form, the combo box for the lookup table where you added (or edited) data will not reflect any changes/additions you made to the lookup table unless you force the combo box to requery its data source.

Bob

 

[JaredSangco]

Bob...Thanks for your response. I'm not 80% sure what you mean, so let me try to follow your direction and give it a shot. If I have any additional questions, I'll post. Wish me luck

 

[BSman]

Why don't you try setting up a new form that uses tblParts as it's record source, and put text boxes and combo boxes on the form using what I've described. Then try adding and editing data using that form. You should be pleasantly surprised.

Bob

 

[JaredSangco]

Bob...

Ok...My first piece was done with your recommendation. Thank You..I did not have to use an insert statement.

Now to my second task - to assign each part a category topopulate table [tblPartCat], using [tblCategory] and [tblClass] as the lookup tables.

"[tblPartCat]
PartId "from [tblparts]"       CatID           ClassID
1                              1              1
1                              2              2
2                              1              3
2                              4              3
3                              3              3

[tblCategory]
CatId                CatName   
1                    Outer
2                    Frame
3                    Cross
4                    Inner


[tblClass]
ClassId              ClassName
1                    Small
2                    Mid
3                    Large

currently, the [tblCategory] has ~approx 85 unique entries (above only shows 4). I want to list every Category name on the form and next to each of the names have a radio button for the user to select a ClassName from the [tblClass].

sample

Outer [X]small []mid []large
Frame []small [X]mid []large
Cross []small [X]mid []large
Inner []small []mid []large

After completion, [tblPartCat] will be poplulated linking each part to a corresponding Cateogory and Class. NOTE Above: Each Part does not have to have a Category and Class assign. (see INNER above - it was not assign to a class)

Any ideas? Bob, I ONLY want this form to input NEW Records. Currenlty, when I open this form, it pulls data from the tables, therefore, each part will display a Category only if it was assigned a Class. Maybe this helps.

Does this makes sense?

 

[BSman]

You should not store the category and class in tblParts, since they are already in tblPartCat. 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)

Just remember that when you refer to the columns using this syntax, column 1 (as referred to in the properties of the combo box) becomes column 0, so the third column (category, for example) in the source is referred to as column 2.

Bob