HELP: Using RecordSet.AddNew in a public function

[Chris O''Neill]

I recently found Dev Ashish's subroutine on The Access Web for adding fields
to a table using the NotInList event
(http://www.mvps.org/access/forms/frm0015.htm). Because I don't want to
repeat the subroutine for each combo box in my application, I'm trying to
convert the subroutine to a public function were I would call it and pass the
following variables:

strTableName (the table we're dealing with e.g. "tblEventTypes")
strFieldName (the field we're dealing with e.g. "EventTypeNames")
NewData (the actual data we're adding to the table e.g. "Birthday Party")

Using Dev's subroutine code, this is how I'd do it:

Set db = CurrentDb
Set rs = db.OpenRecordset("tblEventTypes", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!EventTypeNames = NewData
rs.Update

But, I'm trying to pass those variables to a functions, so I converted Dev's
code to this:

Set db = CurrentDb
Set rs = db.OpenRecordset(strTableName, dbOpenDynaset) ' This works!
On Error Resume Next
rs.AddNew
rs!strFieldName = NewData ' This doesn't work!
rs.Update

I getting an "Item not found in this collection" error message because (of
course) there isn't a "strFieldName" field in the table. Therefore, I end up
with a new but empty row in the table.

Does anybody know how I can accomplish this? Thanks, in advance, for any
help you can provide.

Regards, Chris

P.S. I think my main problem is that I know just enough about VBA
programming to be dangerous!

 

[Dirk Goldgar]

I recently found Dev Ashish's subroutine on The Access Web for adding
fields
to a table using the NotInList event
(http://www.mvps.org/access/forms/frm0015.htm). Because I don't want to
repeat the subroutine for each combo box in my application, I'm trying to
convert the subroutine to a public function were I would call it and pass
the
following variables:

strTableName (the table we're dealing with e.g. "tblEventTypes")
strFieldName (the field we're dealing with e.g. "EventTypeNames")
NewData (the actual data we're adding to the table e.g. "Birthday Party")

Using Dev's subroutine code, this is how I'd do it:

Set db = CurrentDb
Set rs = db.OpenRecordset("tblEventTypes", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!EventTypeNames = NewData
rs.Update

But, I'm trying to pass those variables to a functions, so I converted
Dev's
code to this:

Set db = CurrentDb
Set rs = db.OpenRecordset(strTableName, dbOpenDynaset) ' This
works!
On Error Resume Next
rs.AddNew
rs!strFieldName = NewData ' This doesn't work!
rs.Update

I getting an "Item not found in this collection" error message because (of
course) there isn't a "strFieldName" field in the table. Therefore, I end
up
with a new but empty row in the table.

Does anybody know how I can accomplish this? Thanks, in advance, for any
help you can provide.

Regards, Chris

P.S. I think my main problem is that I know just enough about VBA
programming to be dangerous!

Use

rs(strFieldName) = NewData

 

[strive4peace]

Hi Chris,

instead of
rs!strFieldName

try
rs(strFieldName)


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Tom van Stiphout]

On Thu, 3 Jul 2008 19:29:06 -0700, Chris O''Neill

rs(strFieldName) = NewData

-Tom.

 

[Chris O''''Neill]

Use

rs(strFieldName) = NewData

Way cool! That worked! Thanks, Dirk!

Uhhh... I do have one minor little problem, though. After that bit of code
there's a line that says this:

Response = acDataErrAdded

That's "supposed" to refresh the form and, therefore, include the new field
in the combo box. Unfortunately, the form isn't refreshing, so I'm still
getting the standard "it's not in the list" error message."

I wonder why?

Regards, Chris

 

[Chris O''''Neill]

Uhhh... I do have one minor little problem, though. After that bit of code
there's a line that says this:

Response = acDataErrAdded

That's "supposed" to refresh the form and, therefore, include the new field
in the combo box. Unfortunately, the form isn't refreshing, so I'm still
getting the standard "it's not in the list" error message."

I wonder why?

Never mind... I figured it out. I forgot to pass "Response" to the
function. I set Response = 0 in my main subroutine and passed that to the
function and now it's refreshing the form.

Thanks to EVERYONE who replied so quickly to my request for help. MUCH
appreciated!

Regards, Chris