ComboBox notinlist using AddNew 1

[murpl]

It ain't happenin', folks. Here is my complete sub.. htis should work, right???

Private Sub CategoryID_NotInList(NewData As String, Response As Integer)
    Dim db As Database, rst As Recordset
    Dim strMsg As String, nd As Variant
    nd = NewData
    strMsg = "'" & nd & "' is not in the Categories List"
    strMsg = strMsg & (Chr(13) & Chr(10)) & "Do you want _  to add this name to the list of categories?"
    strMsg = strMsg & (Chr(13) & Chr(10)) & "Click Yes to _ add or No to re-type it."
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new _ name?") = vbNo Then
        Response = acDataErrContinue
    Else
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Categories", _ dbOpenDynaset)
        On Error Resume Next
        rst.AddNew
            rst!CategorieName = nd
        rst.Update
        
        If Err Then
            MsgBox "An error occurred."&(Chr13)&(Chr(10))_
               & "(" & Err.Description & ")" & (Chr(13) &_ 
               Chr(10)) & "(Damn!  I hate it when that_
               happens!)" & (Chr(13) & Chr(10)) & "Please_
               try again."
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If
    End If
End Sub

What I'm trying to do is save the users typed in text and give them the option of adding it to the list of available options (table "Categories" column "CategoryName&quot. I added the err.Description to the msgbox to see why and it says "item not found in this collection". DuH! Apparently the AddNew isn't adding the new text into the record.
I've beat my head against the keyboard to no avail so once again I must consult with the gurus... any ideas, people?
This is for Access 2000.
'preciate any response.

Thanks, Mike

 

[ghubbell]

Hi Mike! try this:
Dim strMsg As String
Dim Rs As Recordset
Dim Db As Database

If MsgBox(NewData & " is not a listed catagory. Would you like to add it now?", vbYesNo + vbQuestion + vbDefaultButton1, "Catagory not found...&quot = vbNo Then
Response = acDataErrContinue
Else
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("Catagories&quot
Rs.addnew
Rs!CatagoryName = NewData
Rs.Update
Rs.Close
End If Gord
ghubbell@total.net

 

[murpl]

Hmmm, still getting an error "jet database engine can't find query or table 'categories'" (or words to that effect.)
The table exists... <scratching chin>
I've seen this elsewhere in the forums before but . . .

Thanks for that, btw.

Mike

 

[ghubbell]

I see better if I do it this way:

Dim SQL as String
Dim strMsg As String
Dim Rs As Recordset
Dim Db As Database

If MsgBox(NewData & &quot; is not a listed catagory. Would you like to add it now?&quot;, vbYesNo + vbQuestion + vbDefaultButton1, &quot;Catagory not found...&quot = vbNo Then
Response = acDataErrContinue
Else
SQL = &quot;Select Catagories.* FROM Catagories&quot;
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset(SQL,DbOpenDynaset)
Rs.addnew
Rs!CatagoryName = NewData
Rs.Update
Rs.Close
End If
Gord
ghubbell@total.net

 

[murpl]

:-/
The first error msg was due to a simple typo. Fixed that but it just seems to fall thru to Access' regular message that it's &quot;not in the list go back and do it again&quot; msgbox.
Your revised code does the same thing.
er, waitaminit . . . If I open my table I find several blank record entries (autonumbered fields are incremented) and there are two with the foobar data I was trying with. It appears your code works (I had no doubts) and my coding attempt was adding blank records.
Is there a way to suppress the regular Access notinlist msgbox and also a means to have it recognise the new entry without closing the form and re-opening it? (I expect one solution will solve both of these issues). I'd have expected the rs.update to take care of that. No?
Summary: User types in new combobox data, notinlist fires and runs code (as you supplied thankyou very much) which does what it's supposed to, notinlist event continues as if nothing happened and tells user to select from list. Typed in text isn't available until form is closed and reopened again.
But on the bright side I'm miles closer than I was before! Thanks for your input and guidance, Gord.

Mike

 

[murpl]

Whelp, I've been hacking away at this off and on (in between more pressing demands - Sheesh! Wottaweek!) and I've finally got something that works but I still can't get rid of the final &quot;The text you entered isn't an item in the list&quot; Access msgbox. But if I click past that the list is open (expanded? Dropped down?) and lo, there's my new list item ready to be selected. Great except for the erroneous error message.
Here's my code:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub CategoryID_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_CategoryID_NotInList

Dim lngCategoryID As Long
Dim newTxt As String, varBookMark As Variant

newTxt = NewData
newTxt = UCase(newTxt)

If MsgBox(newTxt & &quot; is not a listed catagory. Would you like to add it now?&quot;, vbYesNo + vbQuestion + vbDefaultButton1, &quot;Catagory not found...&quot = vbNo Then
Response = acDataErrContinue
Else
DoCmd.OpenForm &quot;Categories&quot;, , , , , , &quot;GotoNew&quot;
Forms![Categories].[CategoryName] = newTxt
If IsNull(Me![CategoryID]) Then
Me![CategoryID].Text = &quot;&quot;
Else
lngCategoryID = Me![CategoryID]
Me![CategoryID] = Null
End If
If lngCategoryID <> 0 Then Me![CategoryID] = lngCategoryID
Me![CategoryID].Requery
DoCmd.Close , , acSaveYes

Me![CategoryID].Requery
Forms![Products].[CategoryID].DefaultValue = newTxt
End If

Exit_CategoryID_NotInList:
Exit Sub

Err_CategoryID_NotInList:
MsgBox Err.Description
Resume Exit_CategoryID_NotInList
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
I've confused the issue by naming the bound controls on each form with the same name... I'll try to avoid that in the future. The last part where I'm trying to set the defaultvalue of the combobox is where I'm really messing up. Any ideas how I can insert the new text in there AND have it recognise it from the list? It really IS there!

Gord, believe it or not I started out with your code suggestion and this is what I wound up with. <lol> I ended up a little off the mark, huh?

Any help with this and or comments, good, bad or otherwise on style, syntax and keyword/command selections greatly appreciated.

Thanks, Mike

 

[ghubbell]

Hi Mike, ditto on that tough week thing. My brain hurts.

Try this please:

Private Sub CatagoryID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_CategoryID_NotInList
Dim SQL As String
Dim Rs As Recordset, Db As Database
Dim lgCatID As Long, ctl As Control
Set ctl = Me.CatagoryID
Response = acDataErrContinue

If MsgBox(UCase(NewData) & &quot; is not a listed catagory. Would you like to add it now?&quot;, vbYesNo + vbQuestion + vbDefaultButton1, &quot;Catagory not found...&quot = vbYes Then
Set Db = CurrentDb()

' for no autonumber
SQL = &quot;SELECT Max(Catagories.CatagoryID) AS MaxCID FROM Catagories&quot;
Set Rs = Db.OpenRecordset(SQL, dbOpenDynaset)
If Rs.RecordCount = 0 Then
lgCatID = 1
Else
lgCatID = Rs!MaxCID + 1
End If
Rs.Close
'end for no autonumber

SQL = &quot;Select Catagories.* FROM Catagories&quot;
Set Rs = Db.OpenRecordset(SQL, dbOpenDynaset)
Rs.AddNew
Rs!CatagoryID = lgCatID 'for no autonumber
'lgCatID = Rs!CatagoryID 'for autonumber
Rs!CatagoryName = NewData
Rs.Update
Rs.Close
ctl = Empty
ctl.Requery
ctl = lgCatID
Else
ctl.Undo
End If

Exit_CategoryID_NotInList:
Exit Sub

Err_CategoryID_NotInList:
MsgBox Err.Number & &quot; &quot; & Err.Description, vbInformation, &quot;''Not in list'' error.&quot;
Resume Exit_CategoryID_NotInList
End Sub

You're not using an autonumber ID are you? Paste this in your code and you'll clearly see two options: As it would run right now is for no autonumber. First it gets the maximum number from your table and adds one. Then we do it again and dump in your max+1 and NewData.

Your plan is swell although the time to open and read and close the catagories form shouldn't be able to compete with the speed of the recordsets.

Response=DataErrContinue means no system message. In this cut I placed it right up top so no matter what we do, no message. Only your message.

NewData is a &quot;given&quot; string variable so there is no need to declare another. You wanted uppercase: Uppercase &quot;newdata&quot;!

Take care leaving unused variables kicking around (varbookmark..probably from another assault!): they suck up memory if even for a moment.

Use dots versus bangs &quot;!&quot; whenever you can. You'll get a much more truthful compile if you do, and it is supposed to be faster too. You also get the bonus of picking the control from a list as you type.

If you have to refer to the same control more than a few times then setting it as a value (ctl) will save lots of typing and makes it a little easier to swipe this code and use it elsewhere. (and you will!)

Naming of controls? Probably around Access 2010 they'll make some sort of agreement with Reddick, code in that fieldname and combobox = CboFieldName. Until then, well, I still do this 'cause its quick and easy! (I am currently ripping through a 300 form database changing label names; yes label names (no one ever does that!) for a multilingual application. Not fun).

You're doin' excellent! Keep at it and curse me if this doesn't fly. Till then & be good tomorrow ,





Gord
ghubbell@total.net

 

[murpl]

Gord, as I glance over this the lights are going on all over the place. 1000W+ ! Thanks for taking the time not only for the code but for the helpful tips and information! VBA help is still Greek to me but I know a bit of Latin ::chuckle:: You've answered more than a few questions for me. Thanks again!
I do have autonumbered fields in both forms (Products.ProductID and Categories.CategoryID) hmmm, uh-oh. No, wait, ne'er mind... it's okay, anyway you've given me lots to chew on.

Have a good weekend and good luck with the labels. <eeooow> That's almost mean but I didn't mean it that way.

Take care,
Mike

 

[ComputorMike]

I made a function to handle this. It work but is there any hidden trap.

Function MyAddtoList(NewData As String, Response As Integer, sFrmName As Form)
'MSB 06/14/01 MyAddtoList function in module1

'Private Sub ComboName_NotInList(NewData As String, Response As Integer)
'MyAddtoList NewData, Response, Me 'add this line to NotInList [Event Procedure]
'End Sub

Dim db As Database
Dim rst As Recordset
Dim MyForm As Form
Dim ctlCurrentControl As Control
Dim iAnswer As Integer
Dim TableName As String
Dim FieldName As String

Set MyForm = [sFrmName]
Set ctlCurrentControl = MyForm.ActiveControl
Set db = CurrentDb
TableName = ctlCurrentControl.RowSource
iAnswer = MsgBox(NewData & &quot; is not currently not in the list!&quot; & _
&quot; Would you like to add it!&quot;, vbYesNo + vbQuestion)
If iAnswer = vbYes Then
Set rst = db.OpenRecordset(TableName)
FieldName = rst.Fields(1).Name
Set rst = db.OpenRecordset(TableName)
rst.AddNew
rst.Fields(FieldName) = NewData
rst.Update
rst.MoveLast
rst.Close
End If
Response = acDataErrAdded
Set db = Nothing
End Function