add value via drop down list

[deepbreath]

can we add a new value in a drop down list to its table ,without opening the table. i.e when you type a new value it asks to add or not, and on yes just adds it
thankz

 

[Susilowati]

Yes, you can by using the OnNotInList event. I found this code for another website as well. This code adds the new entry to the underlying table and refreshes the available list in the combo box.

Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As Database, rs As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available AE Name"
strMsg = strMsg & "@Do you want to associate the new Name to the current DLSAF?"
strMsg = strMsg & "@Click Yes to link or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub

 

[AJohanson]

Hi, there - I am a new user trying to do the same thing, and I would like to know how to use the information in this solution. I don't understand how to use the complicated syntax or where to type it in to accomplish the solution. Is there any way to do it without typing all that in? I was trying to do it by setting the "Limit to List?" property to "No" as Access 97 helpfile said to do...(but then when I was searching through here for answers, I find a mention of setting the "Limit to list" property to "True" - so which one is correct?) So then I skipped that dilemma thinking I would just try to do the next step, which led to getting stuck on trying to figure out how to set the "OnNotOnList" property, since the helpfile even said that it wasn't on the "lookup tab of the property sheet in table design mode", which it's not and evidently nowhere else either, or at least Access makes it darn good and hard to find. If this makes any sense at all, I hope someone can point me to a way to do this, or at least to understand what I'm doing wrong and maybe comprehend Access and how it works a little better. Thanks.

 

[David R]

The Limit to List property has nothing to do with adding new entries to the dropdown list when they come up. It simply means you can hand-type in new entries that don't fit the pattern. For instance, in my database the dropdown list has "KS" and "MO" in the state field, but you can put another state in on the rare occurrence that one comes up.

To actually add something to the list, yes, you're going to have to use code or (possibly) a macro. Look closely at the code above, and change the few references to specific fields or tables to match yours. Give it a try in a test database, without any rela data in it; you may be surprised by what you can accomplish.
You'll want to change the name of the field: cbxAEName, the strMsg bit to something that makes sense for your field (the @'s create new lines in the message), and the db.OpenRecordset("tblAE" ... needs your table name in it (the one with the lookup list). If you're using a Value List that you hand typed in, you'll have to search the archives as the answer is different.

Good luck. Please post back if you run into specific problems trying to implement this, or start a new topic of your own so people don't think it's an old issue.

David R

 

[AJohanson]

OK, well thanks for responding, but obviously I'm in WAYYYYY over my head here - I can't even figure out where that code stuff is supposed to be "pasted", let alone what parts to change that are specific to my database...I just thought it was something I could be sort of "talked through" but obviously since I don't even know how to get where this stuff goes, I probably shouldn't even mess with it. I was just hoping it was a simpler thing like "open X property sheet and change the blank setting to whatever", but I'm starting to get the idea that Access is a lot less user-friendly than I was hoping...oh well.

 

[David R]

Access is undoubtedly the most complicated of the Office programs. But overall it is more or less understandable once you know the right questions to ask. It doesn't help that the Access Help files are arcane at best and confusing at worst.

Don't get completely discouraged. If you want to post exact fieldnames and table names and such perhaps someone will be able to give you a piece of code to plug into your database.
As far as 'where it goes', when you open a form in design view and click Properties, there is a tab for 'Events'. Under Events, for combo boxes, is an event called "OnNotInList". Clicking the [...] at the end of the line prompts you for the Expression Builder, Macro Builder, or Code Builder. To input code, you'd choose Code Builder and fill in between the open and close of the event:

Private Sub comboBox_OnNotInList (working from memory here)
<new code goes here, either typed or copied>
End Sub

Patience, grasshopper. You can do it, though a decent book on Access or a class would certainly help. Did your boss promote you over your head here? (happened to me a year ago; had never seen an Access file before)

Good luck,
David R

 

[AJohanson]

Thank you so much...that helped clue me in enough to probably get into all KINDS of trouble now! ;-) I'll keep on experimenting, now that you've freed me from being stuck at that dead end...since I know I saw the code for this somewhere else on this site, I'll take another look and see if I can figure out how to revise it for my situation. Thanks a BUNCH for your help!!!