Adding data to Combobox, Not in List

I have a combobox with 3 fields.
I put this code in, and within the dialog form my Insert statement works to add the data to the table which populates the cb, but the cb doesn't requery until after I move away from the record I'm adding.

This means I'd have to select something else in the combobox, then come back to it to change to what I really want.
Private Sub cbSchoolID_NotInList(NewData As String, Response As Integer)
    DoCmd.OpenForm "frmSchoolInput", acNormal, , , , acDialog, NewData
    If Forms!frmschoolinput!tbSchoolName <> NewData Then
        Response = acDataErrContinue
        'msgbox "Stick to list, or add a new"'
    Else
        'Don't display error message'
        Response = acDataErrAdded
    End If
    'Close dialog box'
    DoCmd.Close acForm, "frmSchoolInput"
End Sub

Open in new window

LVL 13
RyanProject Engineer, ElectricalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
Your If statement is refering to the form that you are opening, however, this is being opened with the acDialog parameter, which means your VBA code is "SUSPENDED" until you close your form on line 2.

I suspect you are opening the same form with the DoCmd.OpenForm that you are currently working on, if this is the case you will have serious issues trying to get this to work. The form You open on Line 2 should not be the form you are currently using.

Cheers, Andrew



Private Sub cbSchoolID_NotInList(NewData As String, Response As Integer)
    DoCmd.OpenForm "frmSchoolInput", acNormal, , , , acDialog, NewData
    'Don't display error message'
    Response = acDataErrAdded
End Sub

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How is your code running with the acDialog option set? You'd have to first close frmSchoolInput, which means you wouldn't be able to refer to it in the IF Forms!frmSchoolInput line immediately after the OpenForm call.

I'd do the requery in frmSchoolInput, after the data was added to the table:

<add data to table>
Forms("FormContaining_cbSchoolID").Requery
'/now select the value
Forms("FromContaining_csSchoolID") = TheNewID

Now just close frmSchoolInput, and your code in cbSchoolID_NotInList will continue to run.

According to online help, you'll need to Requery the combo:

If you need to add items to a bound combo box dynamically, you must prompt the user to enter data for all required fields, save the new record, and then requery the combo box to display the new value.

From here: http://msdn2.microsoft.com/en-us/library/aa211431.aspx
0
RyanProject Engineer, ElectricalAuthor Commented:
Ya, I guess that code would have been important too.
Heres the code for the dialog.

The if statement is suspended until i make the dialog form invisible.

If i run combobox.requery I get an error that the current record has to be saved first. (The idea is, the record isn't yet complete)
From what I read, setting response = acDataErrContinue caused the combobox to requery. Otherwise setting that doesn't solve anything. It prevent the error from happening immediately, but when you try to get off the combobox you get the error.
Private Sub cmdCancel_Click()
    tbSchoolAbbrev = ""
    Me.Visible = False
End Sub
 
Private Sub cmdOK_Click()
    'Verify Data is entered'
    If Nz(Me.tbSchoolName, "") = "" Then
        MsgBox "Need to enter a school name", vbCritical, "Missing Data"
    Else
        'All good, continue'
        CurrentProject.Connection.Execute "INSERT INTO tblSchool (SchoolName,SchoolAbbrev,D1) VALUES ('" & tbSchoolName & "','" & tbSchoolAbbrev & "'," & CBool(ckD1) & ")"
        Me.Visible = False
    End If
End Sub
 
Private Sub Form_Open(Cancel As Integer)
    Me.tbSchoolAbbrev = OpenArgs
    Me.tbSchoolName.SetFocus
End Sub

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

TextReportCommented:
When you have added the record you need to use Response = acDataErrAdded.
Setting response = acDataErrContinue is for when you are handling the error yourself but doesn't force the requery it simply surpresses the error.
Cheers, Andrew
 
0
TextReportCommented:
I think you have a logic error, in the NotInList you are saying - If Forms!frmschoolinput!tbSchoolName <> NewData Then

But the NewData passed as OpenArgs is being set to - Me.tbSchoolAbbrev = OpenArgs

Cheers, Andrew
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RyanProject Engineer, ElectricalAuthor Commented:
Ah ha!  Stupid little switch up. Good catch.

Set to Abbrev, then was checking Name.
0
RyanProject Engineer, ElectricalAuthor Commented:
Good eye.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.