The last time I'll ever mention combo boxes.

[Petemush]

Okay, after this I'll shut up...

I have a lovely piece of code to change the underlying table of a combo box, culled from the faq's here and another site.

I have a couple of combo boxes so I want them both to use this code so I created a public Function that returns the response for the On Not In List Event. Here it is:

Public Function Add2Combo(fieldName As field, rs As Recordset, NewData As String) As Integer

Dim strData, qts As String

qts = Chr(34)

strData = Trim$(NewData)

If MsgBox(qts & strData & qts & " does not appear in the database." _
& Chr(13) & "Do you want to add it now?", 36) = vbNo Then

Add2Combo = acDataErrContinue

Else

On Error Resume Next
rs.AddNew
rs!fieldName = strData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Add2Combo = acDataErrContinue
Else
Add2Combo = acDataErrAdded

End If
End If

End Function

I get the following error when it's run:

Item not found in this collection. And the "rs!fieldName = strData" line highlighted.

I've tried just putting in rs![Course Type] = strData and this works fine and as far as I know, this is what I'mm passing to the function in the firstplace.

Here's the procedure that calls this function

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

Dim db As Database, rs As Recordset, fieldName As field

Set db = CurrentDb
Set rs = db.OpenRecordset("tblCourseTypeLookup", dbOpenDynaset)
Set fieldName = rs![Course Type]

Response = Add2Combo(fieldName, rs, NewData)

Set db = Nothing
rs.Close
Set rs = Nothing
End Sub

Can anyone shed any light on what I promise is the last you'll hear (from me) about combo boxes?

Cheers,

Pete

 

[jebry]

Hi Pete!

Pass the field name (Course Type) as as string and use it like this:

rs.Fields(fieldname)

This should reference the appropriate field.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Petemush]

If only it was that simple!

Fraid that didn't work, still get the same error.

Tried passing the tabled name and opening the recordset inside the procedure but just got the same error. When I check the debug window, Inside rs is fields which has Item 1 who's name is Course Type.

Yet still it refuses to work!

Arrrrghhh!

Cheers,

Pete

 

[MarrowGenx]

I would take the space out of the field name (i.e. "CourseType" instead of "Course Type&quot. I dont know if this will help, but try it out.

Marrow

 

[jitter]

hmmm...jebry's way should have worked.

I checked it in 97 and 2000 and in both cases I am able to reference the field for both updating and adding a new record by passing the name of the field as a string and then referencing it using rs.fields(strFieldName) = "blah".

I just don't know...

 

[SBendBuckeye]

I'm not sure if this will work since I am at work and can't test it, but try putting [] around your field name since it has embedded spaces in it (eg rs![fieldName] = strData).

Good Luck!

 

[Petemush]

Ahh Jeff, you are indeed correct and I am a fool.

rs.fields("Course Type&quot does indeed work.

However, rs!fields("Course Type&quot does not.

Who wants to guess which piece of code I was using!

Cheers everyone for your help, even if it was just me being dumb.

Cheers,

Pete