Link to home
Create AccountLog in
Avatar of Stephen Byrom
Stephen ByromFlag for Ireland

asked on

Not in List

Hi there,
I modified this code I found on here, but could do with some help.
the code is;
Private Sub cboInitials_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboInitials_NotInList
  Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The Initials " & Chr(34) & NewData & _
        Chr(34) & " are not currently listed." & vbCrLf & _
        "Would you like to add them to the list now?" _
        , vbQuestion + vbYesNo, "Mergon Maintenance")
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO tblPeople([FirstName],[LastName]) " & _
                 "VALUES ('QC','" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The Initials have been added to the list." _
            , vbInformation, "Mergon Maintenance"
        Response = acDataErrAdded
        Me.cboInitials.Requery
    Else
        MsgBox "Please choose a job title from the list." _
            , vbInformation, "Mergon Maintenance"
        Response = acDataErrContinue
    End If
Exit_cboInitials_NotInList:
    Exit Sub
Err_cboInitials_NotInList:
    MsgBox Err.Description, vbExclamation, "An Error has occurred please inform S.Byrom"
    Resume Exit_cboInitials_NotInList
End Sub

It adds initials that are not in the list of the combo box
The problem is this.
If for example I type into the combo box "SB" (not in the list), the procedure fires and the appropriate initials are added to the "tblPeople" with the first name being "QC" and the last name being "SB" or whatever was entered into the combobox, so far so good, but it also adds another record with the first name being "QC" and it inserts the Primary Key Value as the last name!
Another thing is, that it will not perform the Requery on the combobox so that I can then select the newly added initials until the record is saved.

The combobox rowsource is:
SELECT tblPeople.pkPeopleID, tblPeople.FirstName, tblPeople.LastName FROM tblPeople WHERE (((tblPeople.FirstName)="QC"));

The form is bound to tblPeople

I've tried the me.dirty false and numerous other things for the past couple of hours now.
Any help would be appreciated.
Thanx in advance.
Avatar of Sheils
Sheils
Flag of Australia image

To Requery use

Me.ComboBoxName.Requery

As regards the additional record please check the property of your combo box and make sure that LimitToList is set to yes
Avatar of Stephen Byrom

ASKER

thnx for the response

see the code I inserted in my first post.
excerpt
     MsgBox "The Initials have been added to the list." _
            , vbInformation, "Mergon Maintenance"
        Response = acDataErrAdded
        Me.cboInitials.Requery    
Else
        MsgBox "Please choose a job title from the list." _
            , vbInformation, "Mergon Maintenance"
        Response = acDataErrContinue

Also the Limit to List is set to yes
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanx for the response LMS
I adjusted the code as you suggested;
Private Sub cboInitials_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboInitials_NotInList
  Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The Initials " & Chr(34) & NewData & _
        Chr(34) & " are not currently listed." & vbCrLf & _
        "Would you like to add them to the list now?" _
        , vbQuestion + vbYesNo, "Mergon Maintenance")
    If intAnswer = vbYes Then
        Response = acDataErrAdded
        strSQL = "INSERT INTO tblPeople([FirstName],[LastName]) " & _
                 "VALUES ('QC','" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The Initials have been added to the list." _
            , vbInformation, "Mergon Maintenance"
    Else
        MsgBox "Please choose a job title from the list." _
            , vbInformation, "Mergon Maintenance"
        Response = acDataErrContinue
    End If
Exit_cboInitials_NotInList:
    Exit Sub
Err_cboInitials_NotInList:,
    MsgBox Err.Description, vbExclamation, "An Error has occurred please inform S.Byrom"
    Resume Exit_cboInitials_NotInList
End Sub

I now get an error saying that I must place a value in the tblPeople.FirstName.field
and yet when I look at the table after closing all forms the value is in there .."QC" which I what I wanted.
Maybe I should try it with an unbound form
I managed to get it working thanx to LMS advice and using an UNBOUND combo on the form.
The code is this:
Private Sub cboInitials_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboInitials_NotInList
  Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The Initials " & Chr(34) & NewData & _
        Chr(34) & " are not currently listed." & vbCrLf & _
        "Would you like to add them to the list now?" _
        , vbQuestion + vbYesNo, "Mergon Maintenance")
    If intAnswer = vbYes Then
        Response = acDataErrAdded
        CurrentDb.Execute "INSERT INTO tblPeople([FirstName],[LastName]) VALUES ('QC','" & NewData & "');"
        MsgBox "The Initials have been added to the list." _
            , vbInformation, "Mergon Maintenance"
            DoCmd.RunCommand acCmdUndo
            DoCmd.Close acForm, "frmQCPerson"
            DoCmd.OpenForm "frmQCPerson"
    Else
        MsgBox "Please choose a job title from the list." _
            , vbInformation, "Mergon Maintenance"
        Response = acDataErrContinue
    End If
Exit_cboInitials_NotInList:
    Exit Sub
Err_cboInitials_NotInList:
    MsgBox Err.Description, vbExclamation, "An Error has occurred please inform S.Byrom"
    Resume Exit_cboInitials_NotInList
End Sub

It aint pretty, as I have to close the form then reopen the form so as to pass the newly added primary key to a different form, but at least it works.
Thanx for your time.
Maybe curing my dyslexia would help in my writing of code.
Sorry for the slip LSM
:)