not on list - add record to combo box help

[keiserc]

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?

 

[Hfnet]

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

 

[Hfnet]

Sorry, replace Combo0 with model

 

[TheAceMan1]

How are ya keiserc . . . . .

Have a look at faq702-5205

See Ya! . . . . . .

 

[keiserc]

thank you, but I'm coming up with an error on this line -

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

something missing perhaps?

 

[keiserc]

sorry, also

Model.Undo is coming up error???

 

[Hfnet]

Check your references in vb. I think it uses Microsoft Active Data Objects 2.5 upwards, depending on your version of Office.

 

[mustangcoupe]

Hfnet..... thanks worked great in my combobox.

 

[Hfnet]

No probs, glad I could help someone else for a change!

 

[keiserc]

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.

 

[RoyVidar]

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

 

[keiserc]

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?