VBA Set Recordet using a variable table name

Question

I'm trying to access a form's recordset clone from a called function which we're using to essentially dump the data in the recordset into a table we're creating. I'm trying to call the form from the Forms collection, but I can't figure out how to insert the form name (held in a string passed to the function) into the middle of the set statement. Here's what it looks like basically:

Public Function EditFormData(frmName As String)
  Dim strSQL As String
  Dim strTableName As String
  Dim rst As DAO.Recordset
  Dim i As Integer

  Set rst = Forms![[[frmName]]].RecordsetClone

I'm at a bit of a loss here. For this particular function, I could pass a recordset to the function, but I have similar quandaries in another place, where passing one isn't an option. Thoughts?

mwolfe02 · Accepted Answer · 2012-03-06 15:04:34Z

3

Try this:

Set rst = Forms(frmName).RecordsetClone

answered Mar 6, 2012 at 15:04

mwolfe02

24.3k10 gold badges102 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fionnuala Over a year ago

+1 I generally use form name, but you can also pass the form itself EditFormData(frm As Form)

mwolfe02 Over a year ago

Good point. The set statement would then be: Set rst = frm.RecordsetClone

Crimius Over a year ago

That's right. Both work, and I feel like an idiot. Much appreciated, will mark as answer when it lets me.

Collectives™ on Stack Overflow

VBA Set Recordet using a variable table name

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related