• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

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

0
Ryan
Asked:
Ryan
  • 3
  • 3
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now