Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-08-2019, 07:14 AM   #1
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Add new item to Combo Box list from table

I am wanting to allow the user to update (add) a category. There is a tblCategory that has three field: ID (primary), VendorName and Category. the Combo box opens only the categories associated with that vendor. Through the help of these threads, we got that to work. this is a Row Source query that allows that to work. I found some code to update tblCategory when the user adds Category. It is 'working' in that the category is added to the tblCategory. the problem is that the new record in tblCategory needs to include the VendorName as well. This is available on the form. Actually I was able to get to two consecutive operations which adds the Category and then adds the VendorName but it made two record entries so I have a record with ID, blank VendorName, Category then ID,VendorName, blank Category. So this doesn't work. Next I found the INSERT INTO, which was used semi successfully, also has another version which inserts the entire record at once but I must have something wrong in the syntax because I get errors. Run-time error 3061, too few parameters. Expected 2. The highlighted line is DBEngine(0)(0).Execute strTmp, dbFailOnError. the code for "not on list' is below.

Private Sub Combo53_NotInList(NewData As String, Response As Integer)
'Allow user to save non-list items.
Dim strTmp As String
'Get confirmation that this is not just a spelling error.
strTmp = "Add '" & NewData & "' as a new category?"
If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
' These two work but they each start a new record so that one record has a blank for VendorName_
'And the other has the correct VendorName but a blank for CAtegory_
'Append the NewData as a record in the Categories table.
' strTmp = "INSERT INTO tblCategory ( Category ) " & _
' "SELECT """ & NewData & """ AS Category;"

' DBEngine(0)(0).Execute strTmp, dbFailOnError

' 'Add the SupplierName in the record
' strTmp = "INSERT INTO tblCategory ( VendorName ) " & _
' "SELECT """ & Forms!frmMRRLog!SupplierName & """ AS VendorName;"
' MsgBox "Gets to Here" & Forms!frmMRRLog!SupplierName
'DBEngine(0)(0).Execute strTmp, dbFailOnError
'Notify Access about the new record, so it requeries the combo.
'Response = acDataErrAdded
'End If
' Try the 'VALUES' vesion of INSERT INTO
strTmp = " INSERT INTO tblCategory " _
& "(ID,VendorName, Category) VALUES " _
& "(500,Forms!frmMRRLog!SupplierName, NewData);"
DBEngine(0)(0).Execute strTmp, dbFailOnError
Response = acDataErrAdded
End If
End Sub

gakiss2 is offline   Reply With Quote
Old 05-08-2019, 07:16 AM   #2
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Add new item to Combo Box list from table

Wanted to add that I tried getting rid of the primary key to add simplicity but that caused other issues so I have abandonded. And I don't even think it would solve it anyway.
gakiss2 is offline   Reply With Quote
Old 05-08-2019, 07:22 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,848
Thanks: 57
Thanked 1,280 Times in 1,261 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Add new item to Combo Box list from table

Hi. Have you considered simply using the List Items Edit Form property?

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-08-2019, 07:55 AM   #4
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Add new item to Combo Box list from table

Quote:
Originally Posted by theDBguy View Post
Hi. Have you considered simply using the List Items Edit Form property?
Do you mean change the property to allow additions? I thought that in that case the underlying table. TblCategory would not get updated.
gakiss2 is offline   Reply With Quote
Old 05-08-2019, 08:05 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,848
Thanks: 57
Thanked 1,280 Times in 1,261 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Add new item to Combo Box list from table

Quote:
Originally Posted by gakiss2 View Post
Do you mean change the property to allow additions? I thought that in that case the underlying table. TblCategory would not get updated.
No. I mean, if you go to the design view of the combobox, go to the properties window and look for the "List Items Edit Form" property and select your data entry from for Category from the dropdown list.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-08-2019, 08:38 AM   #6
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Add new item to Combo Box list from table

Quote:
Originally Posted by theDBguy View Post
No. I mean, if you go to the design view of the combobox, go to the properties window and look for the "List Items Edit Form" property and select your data entry from for Category from the dropdown list.
OK I found the place in the properties you are talking about. Right now I only have the one form which actually contains the Combobox so that doesn't make sense (at least to amateur me). Instead do you mean for me to make a Form whose purpose is to add records to tblCategory? In fact I think that even the Access Form wizard could handle that task. I can see how that would be simpler.... I can try it and see how it works

Sorry if I'm not tracking with you yet. thanks for your patience.
gakiss2 is offline   Reply With Quote
Old 05-08-2019, 08:55 AM   #7
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Add new item to Combo Box list from table

Quote:
Originally Posted by gakiss2 View Post
OK I found the place in the properties you are talking about. Right now I only have the one form which actually contains the Combobox so that doesn't make sense (at least to amateur me). Instead do you mean for me to make a Form whose purpose is to add records to tblCategory? In fact I think that even the Access Form wizard could handle that task. I can see how that would be simpler.... I can try it and see how it works

Sorry if I'm not tracking with you yet. thanks for your patience.
Not sure if I'm just not getting what you are saying but that seems very clunky and doesn't seem to be working. the data entry form I created does launch. the very first time it even launched with the right Vendor name in the VendorName field and the text I had typed in the Category field. so I closed the form and went to the frmMRRLog and it would still say the item was not in the list. I looked at the tblCategory and it had not been updated. and besides it seems like a lot of extra busywork just to add a category that is already typed in. Sorry, I don't mean to criticize your help. And it may well be that I am just not understanding your advice.

gakiss2 is offline   Reply With Quote
Old 05-08-2019, 08:56 AM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,848
Thanks: 57
Thanked 1,280 Times in 1,261 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Add new item to Combo Box list from table

Quote:
Originally Posted by gakiss2 View Post
OK I found the place in the properties you are talking about. Right now I only have the one form which actually contains the Combobox so that doesn't make sense (at least to amateur me). Instead do you mean for me to make a Form whose purpose is to add records to tblCategory? In fact I think that even the Access Form wizard could handle that task. I can see how that would be simpler.... I can try it and see how it works

Sorry if I'm not tracking with you yet. thanks for your patience.
Hi. I can see you're getting there though. Let us know how it goes. Just make sure the form to enter new categories is set as a Data Entry form. Cheers!

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Tags
combo box , dbengine(0)(0) , form , insert into

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Store Value List Item from Combo Box in a Table Field? Jason1971 Forms 2 07-23-2009 10:13 PM
Adding a combo box list item to a table PRodgers4284 Forms 11 02-24-2009 05:28 PM
COMBO BOX : Specifying Item not in List Will04 Forms 3 06-25-2006 01:57 PM
COMBO BOX : Specifying Item not in List Will04 Forms 0 06-22-2006 06:57 AM
**Add Item to Value list in Combo Box** John M Forms 5 04-22-2003 09:15 AM




All times are GMT -8. The time now is 04:34 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World