MS-Access VBA: Passing variable from one form to another giving a run-time error

Question

I've got a form frm_New_Datasheet to enter datasheets from volunteers into a database I'm making. For the volunteer id text box txt_Vol_ID I've got two buttons to either search for an existing volunteer or add a new one, each opening a respective form. I want the Vol_ID from frm_Volunteers to get passed back to frm_New_Datasheet once I add a new volunteer.

To do this, I added the following code to my volunteer form:

Private Sub Form_AfterInsert()
    If Not IsNull(Me.vol_Vol_ID) Then
        Dim volVal As Double
        volVal = Me.vol_Vol_ID
        Forms!frm_New_Datasheet!txt_Vol_ID.Value = volVal
    End If

End Sub

And it worked perfectly. Then I tried creating my 'Search for Volunteers' form, added the same code to it, but now I get the following error on both:

Run-time error -2146500594 (800f000e):
Method 'Item' of object 'Forms' failed

The debugger is highlighting the line where I set the .Value = volVal so I've looked for other ways to call that text box but no luck yet. I've tried

Forms([frm_New_Datasheet]).[txt_Vol_ID] = volVal`

but that gives me a different error:

Run-time error '2465':
Microsoft Access can't find the field '|1' referred to in your expression

I'm pretty lost on this because I think I'm calling the other form's text box properly, and when I search for the first run-time error the best advice I've found is "Sometimes VBA is buggy and try restarting" which hasn't helped. So far this is the best idea I've had to pass the value from one form to another. If there's another place I can drop this code or another way to skin this cat I'm all ears.

Thanks!

Pretty sure I followed those instructions correctly but still no dice. Thanks for the link to that process, though, a good method to have at hand. — plytheman
That first error message suggests Access does not see a form named "frm_New_Datasheet" in the Forms collection. Check which form names Access sees. Paste this line of code into the Immediate window and press Enter: for each f in forms : ? f.name : next Is "frm_New_Datasheet" listed there? — HansUp
I'm sorry but I'm pretty new to VBA/Access but I can't seem to get your suggested code to do anything. I enter it into the Immediate window and hit enter but it just goes to a new line and nothing happens. Tried following the OP here: bytes.com/topic/access/answers/829700-list-all-forms-database and I get an "Invalid in Immediate Pane" Compile error. — plytheman
Open some other form, then run the code I suggested again. Is the name of the other form listed? — HansUp

Sergey S. Sergey S. · Accepted Answer · 2018-10-22T13:29:54

Looks like you should check form/control names more carefully and make sure that the form frm_New_Datasheet is open.

Here is the correct variant for referring using Forms collection by form name:

Forms("frm_New_Datasheet").txt_Vol_ID = volVal

MS-Access VBA: Passing variable from one form to another giving a run-time error

2 Answers