Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

not on list - add record to combo box help

Status
Not open for further replies.

keiserc

Technical User
Joined
Feb 24, 2004
Messages
16
Location
US
my main form "datasheets"
has combo box "model" - based on table "modeltable"
second form (for entry of new model to "modeltable" = "ADDmodelform"

I need to allow the user to add a new model to the list (combobox).

I tried faq702-4283 and can't get it to work.

I set up a commond button to open my "ADD modelform" - a simplier way to add to the "modeltable" - only the newly added item is not available in the main form untill I exit ACCESS and re-enter. what code do I need to actually update the table for immediate use?
 
What I do is to create a 'not in list' code:

Private Sub model_NotInList(NewData As String, Response As Integer)
R = MsgBox("'" & NewData & "' does not exist." & vbCrLf & _
"Would you like to Add it?", vbYesNo + vbQuestion, _
"Confirm Addition")

If R = vbYes Then
Combo0.Undo
CurrentDb.Execute "Insert Into modeltable (field) Values ('" & NewData & "')"
Combo0.Requery
Combo0.Text = NewData
End If
Response = 0
End Sub

And replace field with your modeltable field in brackets
 
Sorry, replace Combo0 with model
 
How are ya keiserc . . . . .

Have a look at faq702-5205

Calvin.gif
See Ya! . . . . . .
 
thank you, but I'm coming up with an error on this line -

CurrentDb.Execute "Insert Into ModelTable (Model) Values (" ' & NewData & "')"

something missing perhaps?
 
sorry, also

Model.Undo is coming up error???
 
Check your references in vb. I think it uses Microsoft Active Data Objects 2.5 upwards, depending on your version of Office.
 
No probs, glad I could help someone else for a change!
 
Thank you Hfnet for your help, unfortunately my lack of traning is shining brightly.

I have asked for, received, and copied VB before - with the help of all you great folks on Tek-tips.

But this time, I'm afraid I do not have a clue what you mean with your response "check your references in vb....etc". I'm assuming I may not have a current enough version of something for your code to work. I don't know how to check it.

If you'd care to walk me thru it? (I'll understand if this would be to timely) or if code can be written another way? I have windows 2000 professional and Access 2003.
 
Check out Hfnet's original reply, the placements of the quotes/single quotes:

[tt]CurrentDb.Execute "Insert Into ModelTable (Model) Values ('" & NewData & "')"[/tt]

This shouldn't need any additional references, the currentdb method is a member of the application object, as is the later currentproject. Well, when mentioned, firing off the same with ADO (no reference needed here either)

[tt]currentproject.connection.Execute "Insert Into ModelTable (Model) Values ('" & NewData & "')"[/tt]

Roy-Vidar
 
Thank you Thank you. I finally got it working, but here's the next step (or catch). My combobox is multicolumn, - I have model.column(1) and model.column(2) displaying on the form.

While it is very exciting to have the combo box - add a new model to the table directly, (I'm sure I'll use this functionality MANY times, now that I have it working) I think I really need to use a different - simpler method - since I have a multi column issue

I have created a commond button that opens up a second form - this form is for the purpose of adding a new model to the [modeltable] - Complete with the other two column fields [ModelType] and [ModelMfgWar]. I can add here just fine, the problem is that the new information is not available to the combo box (that draws from it) until I leave Access & re-enter. I've tried a commond button to save the record, what am I missing?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top