Full Version: Combobox NotInList Event
UtterAccess Forums > Microsoft® Access > Access Forms
Andycap
Please see attached,
I have a NotInList event on cboPrefixID on frmContact that populates tlkpPrefix with any new data and opens frmLookUpTableManager.
I would like the code to have frmLookUpTableManager display Prefix in cboCategory so the new data is included in lstItems so the user has visual confirmation that the new data has been added to tlkpPrefix.
I have tried altering LinkCriteria = "[strPrefix] = '" & Me!cboPrefixID.Text & "'" but none of the combinations work.
Could someone please correct my code so that it populates the table as well as opens it.
Many Thanks
Andy
sredworb
Hello,
Take a look at this, I think this is what you are asking.
Jerry
cheekybuddha
Hi Andy,
dd the following 2 lines after the 'Response = ' line
CODE
' ...
        Response = acDataErrAdded
        Forms!frmLookUpTableManager.cboCategory = "Title"
        Forms!frmLookUpTableManager.cboCategory_AfterUpdate

hth,
d
cheekybuddha
Also,
dd this line in the 'Else' part of the NotInList event:
CODE
' ...
    Else
        Me.cboPrefixID.Undo
        Response = acDataErrContinue
' ...

hth,
d
sredworb
Made a slight change to open the Form
Good Luck,
Jerry
Andycap
Jerry,
Thanks for your response.
Sorry if my request was unclear.
My existing code does the same as yours. I would prefer it to do more!
What I would like it to do is open frmLookUpTableManager and show the new data in the listbox as well so the user has a visual confirmation of it being added to the look-up table and give them the chance to make further changes if required.
Many Thanks
Andy
sredworb
Check out cheeky buddha's his is awsome...
Andycap
Cheekybuddha,
erry is not wrong.
Your solution works perfectly.
Thank you very much.
Andy
cheekybuddha
Hi Andy,
You're welcome. (Cheers Jerry too for the kind words)
If you want the new item to be selected as well in the listbox (lstItems) when it is opened change your NotInList event like this:
CODE
Private Sub cboPrefixID_NotInList(NewData As String, Response As Integer)
Dim strSQL As String, x As Integer, lNewID As Long
    Dim LinkCriteria As String
    x = MsgBox("Do you want to add this value to the list?", vbYesNo)
    If x = vbYes Then
        strSQL = "Insert Into tlkpPrefix ([strPrefix]) values ('" & NewData & "')"
        'MsgBox strsql
        CurrentDb.Execute strSQL, dbFailOnError
        With CurrentDb.OpenRecordset("Select @@Identity;")
            If Not (.BOF And .EOF) Then
                lNewID = .Fields(0)
            End If
            .Close
        End With
        LinkCriteria = "[strPrefix] = '" & Me!cboPrefixID.Text & "'"
        DoCmd.OpenForm "frmLookUpTableManager", , , LinkCriteria
        Response = acDataErrAdded
        With Forms!frmLookUpTableManager
            .cboCategory = "Title"
            .cboCategory_AfterUpdate
            .lstItems = lNewID
        End With
    Else
        Me.cboPrefixID.Undo
        Response = acDataErrContinue
    End If
End Sub

hth,
d
Andycap
Even better!
any thanks to you and Jerry for all your help.
cheekybuddha
You're welcome, glad we could help.
thumbup.gif
d
AccDB4Free
Hello Cheekybudha,
First, I thank you for your code, sorry I should have ask permission first, but anyway,
Using code without knowing what those lines meaning is a very dangerous one, if you have enough time
do you mind putting a comment each line "what this line do"..
Thank you,
ca
cheekybuddha
Hi ca,
First, please feel free to use any code posted on this forum. The forum is here to help people with Access and to help them learn. It is a policy of UA that any code found within the forum can be used, re-used, reworked etc...
Sure, I will explain the lines in the code. Most of it was written originally by Andy and I have adapted it from his db.
CODE
Private Sub cboPrefixID_NotInList(NewData As String, Response As Integer)
[color="green"]'   Declare a string variable for the SQL "Insert..." statement (strSQL),
'   an integer variable to hold the response of the MsgBox (x),
'   and a Long integer variable to hold the PK of the newly inserted data (lNewID).[/color]
    Dim strSQL As String, x As Integer, lNewID As Long
[color="green"]'   The following variable is unnecessary.
'   It is used to open a form filtered to a specific record.
'   The form in question is unbound (I think;) ).[/color]
    Dim LinkCriteria As String
[color="green"]'   Ask the question, grab the answer into variable 'x'.[/color]
    x = MsgBox("Do you want to add this value to the list?", vbYesNo)
    If x = vbYes Then
[color="green"]'       User answered 'Yes', so add the new data to table tlkpPrefix.
        First, build SQL statement.[/color]
        strSQL = "Insert Into tlkpPrefix ([strPrefix]) values ('" & NewData & "')"
        'MsgBox strsql
[color="green"]'       Perform insert.[/color]
        CurrentDb.Execute strSQL, dbFailOnError
[color="green"]'       Retrieve newly inserted PK.
'       Use the @@Identity feature to do this.
'       We open a recordset that uses a special command that will
'       retrieve the last inserted ID from a DAO.Database object.
'       This is only available as of Jet4 (Access 2000 and later, I think).[/color]
        With CurrentDb.OpenRecordset("Select @@Identity;")
[color="green"]'           Check a record was returned (belts and braces!)[/color]
            If Not (.BOF And .EOF) Then
[color="green"]'               We only selected one field so it has index 0.[/color]
                lNewID = .Fields(0)
            End If
[color="green"]'           Close the recordset - important![/color]
            .Close
        End With
[color="green"]'       This next line is uneccesary in this procedure![/color]
        LinkCriteria = "[strPrefix] = '" & Me!cboPrefixID.Text & "'"
[color="green"]'       Open the lookup form.[/color]
        DoCmd.OpenForm "frmLookUpTableManager", , , LinkCriteria
[color="green"]'       Tell Access that we have added the new data.[/color]
        Response = acDataErrAdded
[color="green"]'       In the form that has just been opened:
'       1. Set cboCategory to 'Title'.
'       2. Perform the AfterUpdate procedure that sets
'          the RowSource of listbox 'lstItems'. T
'          This sub is declared as PUBLIC in frmLookupTableManager.
'       3. Select the new item in the listbox.[/color]
        With Forms!frmLookUpTableManager
            .cboCategory = "Title"
            .cboCategory_AfterUpdate
            .lstItems = lNewID
        End With
    Else
[color="green"]'       User answered 'No', clear the new value from the combo
'       and inform Access to continue without notification.[/color]
        Me.cboPrefixID.Undo
        Response = acDataErrContinue
    End If
End Sub

I hope that explains what is going on. For a lengthy discussion on '@@Identity' it's worth reading this thread.
Please ask if you have any further questions.
hth,
d
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.