Not In List 2

[Begin76]

Hello,

I have a form with a list box. I want to use the not in list property to allow entry of new items.

I have coded the not in list as follows

Private Sub CategoryID_NotInList(NewData As String, Response As Integer)

If MsgBox("Category Not Found, Add?", vbYesNo + vbQuestion, _
"Please Respond&quot = vbYes Then
DoCmd.OpenForm "frmcategories", _
Datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData

If IsLoaded("frmcatagories&quot Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmcategories"
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If
End Sub

I have also coded my pop up form as follows

Private Sub cmdcancel_Click()
DoCmd.RunCommand acCmdUndo
DoCmd.Close
End Sub

Private Sub cmdOk_Click()
me.visible = false
End Sub

Private Sub Form_Load()
Me.Category.Value = Me.OpenArgs
End Sub

Everything is working until it trys to run 2nd half of the code in the Not in List property. I keep geting a compile error on the IsLoaded property.

Am I missing something??

 

[judgehopkins]

Are you trying to open a form with your code? Why are you doing that?

Anyway, here's what works for me:

'-------------------------------------------------------------
'Not in list procedures for combo boxes below
'When you set the Response argument to acDataErrAdded,
'Microsoft Access enables you to add the value of the
'NewData argument to the RowSource property setting.
'The value DataErr will contain the error number for the error
'that just occurred, and Response allows you to specify how you
'want Access to handle the error. Response can take the
'following constants:
'   Response = acDataErrContinue
'This will prevent Access from displaying its own message
'   Response = acDataErrDisplay
'This will cause Access to display its own error message
'-------------------------------------------------------------

Private Sub Lender_NotInList(NewData As String, Response As Integer)
Dim strNewLender As String
Dim varTemp As Variant
strNewLender = ComboboxCleanUp(NewData)

If DCount("[Lenders]", "Lenders", _
    "[Lenders]='" & strNewLender & "'") > 0 Then
        Me.Undo
        MsgBox "Choose " & strNewLender & " from the list.", _
        vbOKOnly, "Use the drop-down list!"
        Response = acDataErrContinue
    Exit Sub

ElseIf MsgBox("Add " & strNewLender & " to list?", _
    vbYesNo, "Add a new lender?") = vbYes Then
    Dim db As Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Lenders")
    rst.AddNew
    rst!Lenders = strNewLender
    rst.Update
    Response = acDataErrAdded
    rst.Close
    Me.Lender.Value = strNewLender
'the line above refers to the control name on the form, not the table
    Me.Lender.RowSource = Me.Lender.RowSource
'Sometimes the requery does not work so I use the line above
'for a forced requery
    Response = acDataErrContinue
Else
    Response = acDataErrContinue
    Me.Undo
    MsgBox "Use a lender that is already in the list.", _
        vbOKOnly, "A new lender will not be added."
End If

End Sub

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[scriverb]

For clarrification for others that read this thread we are talking about a ComboBox not a List Box. ComboBoxes have the options discussed here while List Boxes do not.

Judge Hopkins: See the code below as I am confused by your having two different entries for Response on the Yes branch of your Prompt for adding the new entry:

rst.Update
Response = acDataErrAdded
rst.Close
Me.Lender.Value = strNewLender
'the line above refers to the control name on the form, not the table
Me.Lender.RowSource = Me.Lender.RowSource
'Sometimes the requery does not work so I use the line above
'for a forced requery
Response = acDataErrContinue
Else
. . ..

I believe the last one should be removed for this code to work properly. Yes??

You already have a acDataErrContinue on the No side of the IF statement.

In response to your question about opening another form during the process of adding a new record to the table, I have done just that and the reason is that the new record has more information necessary in it than just the name that is added. For example it may be a combobox to select a business name. The User types in the new Business Name and the code allows for the opening of a form to add a new record in the table Businesses. This table has Name, Address, phone#'s, contacts etc. The full form is necessary to be used rather than just the combobox entry info. This may be what Begin76 is trying to do.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[judgehopkins]

Thanks, Bob. You were right about my code and now I think I know what he was trying to do!

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[Begin76]

Hi Judge,

I was trying to open the form as there is a category description that goes with the category. What I was trying to accomplish was to open the category form enter the data, then when the OK button is clicked it hides the form and focus is set back to the original form. It then runs the rest of the code - This is where my problem is. The code should see if the frmcategories is still open - if so it closes the form and updates the combo box. If the form is closed it will not add the record.

I think I understand what you are doing - if the entry is not in the list it will add the entry to the table it came from. I did try that as well but maybe I missed something.

It keeps giving me an error at the strnewcategory = ComboBoxCleanUp(NewData) - It tells me that the sub or funtion is not defined?

Private Sub CategoryID_NotInList(NewData As String, Response As Integer)
Dim strNewcategory As String
Dim varTemp As Variant
strNewcategory = ComboboxCleanUp(NewData)

If DCount("[CategoryID]", "CategoryID", _
"[CategoryID]='" & strNewcategory & "'&quot > 0 Then
Me.Undo
MsgBox "Choose " & strNewcategory & " from the list.", _
vbOKOnly, "Use the drop-down list!"
Response = acDataErrContinue
Exit Sub

ElseIf MsgBox("Add " & strNewcategory & " to list?", _
vbYesNo, "Add a new Category?&quot = vbYes Then
Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblcategories&quot
rst.AddNew
rst!Category = strNewcategory
rst.Update
Response = acDataErrAdded
rst.Close
Me.categoryid.Value = strNewcategory
'the line above refers to the control name on the form, not the table
Me.categoryid.RowSource = Me.categoryid.RowSource
'Sometimes the requery does not work so I use the line above
'for a forced requery
Response = acDataErrContinue
Else
Response = acDataErrContinue
Me.Undo
MsgBox "Use a Catergory that is already in the list.", _
vbOKOnly, "A new Category will not be added."
End If

End Sub

Thanks for your Help!!!

 

[Begin76]

Hello,

I tried to erase the second instance of the Response = acDataErrContinue but am contining to get a error on the Comboboxcleanup line.

Ideally I would love to open the form but I can't seem to get my head around the problem I am having with this line of code.

If IsLoaded("frmcatagories&quot Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmcategories"
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If

From original post.

Thanks!!

 

[judgehopkins]

I beg your indulgence...it's Monday.

Okay, ComboBoxCleanup cleans up the string that goes in the list box. Trims the leading and trailing spaces--Bob, I think I could have said

(Trim(strTemp))

, right?
Then it capitalizes the first letter of each word.

In other words, it turns [red] john dOE [/red] into [red]John Doe[/red].

Let me know if you have further questions.

Public Function ComboboxCleanUp(ByVal strTemp As String) As String
'Used with all comboboxes on control
        strTemp = LTrim(RTrim(strTemp))
'Leading and following spaces stripped
        strTemp = StrConv(strTemp, vbProperCase)
'First letter capitalized
        ComboboxCleanUp = strTemp
'Cleaned-up string replaces old string
End Function

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[scriverb]

Your problem with providing the Category Description can be resolved two ways.

First, you could just prompt with an InputBox for the Category description and allow the user to enter it and then using the recordset code as originally posted update both the Category and Category Description fields. There would be no need to open the form with this option.

Secondly, the reason you are having problems with the OpenForm method is that the original NotInList code continues to execute past the opening of the form while your user is entering the data in the form. There is a parameter in the OpenForm method that pauses the original code from continuing.
DoCmd.OpenForm acForm, "frmcategories", , , , acDialog

InputBox Method:
Private Sub CategoryID_NotInList(NewData As String, Response As Integer)
Dim strNewcategory As String
Dim varTemp As Variant
strNewcategory = Proper(Trim(NewData))
If MsgBox("Add " & strNewcategory & " to list?", _
vbYesNo, "Add a new Category?&quot = vbYes Then
Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblcategories&quot
rst.AddNew
rst!Category = strNewcategory
rst!CategoryDesc = InputBox("Enter Category Description: ", "User Prompt&quot
rst.Update
Response = acDataErrAdded
rst.Close
Me.categoryid.Value = strNewcategory
Me.categoryid.requery
Response = acDataErrContinue
Else
Response = acDataErrContinue
Me.Undo
MsgBox "Use a Catergory that is already in the list.", _
vbOKOnly, "A new Category will not be added."
End If
End Sub

If you have more than just the Category Description to enter into the Category record then use the following Open Form method:
Private Sub CategoryID_NotInList(NewData As String, Response As Integer)
Dim strNewcategory As String
Dim varTemp As Variant
strNewcategory = Proper(Trim(NewData))
If MsgBox("Add " & strNewcategory & " to list?", _
vbYesNo, "Add a new Category?&quot = vbYes Then
Response = acDataErrAdded
DoCmd.OpenForm acForm, "frmcategories", , , ,acDialog
Me.categoryid.Value = strNewcategory
Me.categoryid.requery
Else
Response = acDataErrContinue
Me.Undo
MsgBox "Use a Catergory that is already in the list.", _
vbOKOnly, "A new Category will not be added."
End If
End Sub

'This above form should be a special copy of the categories form where only one record can be added and the Category control is prefilled with the value strNewcategory and the only button on the form is Save/Close which saves the new record and closes the form. Also make sure that all the controls on the form are completed and don't let the Categories control be changed.

I removed the checking to see if the NewData is in the table because that is redundant activity. The ComboBox property LimitToList set to Yes and the NotInList event procedure performs just this activity. If the entry is in the list the NotInList code will never execute and the focus just moves to the next control. It is only with a new value that is not in the list that triggers the event procedure to handle this new data.

Let me know what you decide to do.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Begin76]

Hello,

Thanks a million to both of you. I was able to get it to work with a few modifications. I have posted it below.

I like the way this event works because it promts you before opening the form with the Category name to add. Allows you to verify typing errors.

A star to Bob for this great code and a star to judge because his post reminded me of a few other things I was trying to do and was very helpful!!

Private Sub CategoryID_NotInList(NewData As String, Response As Integer)
Dim strNewcategory As String
Dim varTemp As Variant
strNewcategory = Trim(NewData)
If MsgBox("Add " & strNewcategory & " to list?", _
vbYesNo, "Add a new Category?&quot = vbYes Then
Response = acDataErrAdded
DoCmd.OpenForm "frmcategories", , , , acFormAdd, acDialog, strNewcategory

' Me.Category.Value = strNewcategory
' Me.categoryid.Requery
Else
Response = acDataErrContinue
Me.Undo
MsgBox "Use a Catergory that is already in the list.", _
vbOKOnly, "A new Category will not be added."
End If
End Sub

 

[scriverb]

Begin76: Glad to have helped you with this project. Good luck and thanks for the Star.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[judgehopkins]

[blue]As General Patton would say:[/BLUE][red]"TANKS!"[/red]



Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.