Open form B to a record by clicking on the record in form A

[Datamulcher]

Hey everyone, I've have done this before but today nothing I have tried or sourced online will work. :|

I have a datasheet form, with a field of "HouseholdID". I want to be able to click this HouseholdID field in the datasheet and have the frmHouseholds form open to that record.
Simple, right?

I have tried these two methods using my basic VBA:

Private Sub HouseholdID_Click()


Dim sWHERE As String
sWHERE = "[HouseholdID] = '" & Me.HouseholdID & "'"
DoCmd.OpenForm "frmHouseholds", acNormal, , sWHERE
End Sub

-and-

DoCmd.OpenForm "frmHousehold", ,acNormal,,"HouseholdID = " & Me.HouseholdID

Neither of these work. Both display this same error message (which is not at all helpful to me)


TLDR;

I want to open a form to a record when that record is clicked in another form.

Thanks for your help!

[Bulzie]

In the OnOpen or OnLoad event of frmHousehold:

Me.Filter = "HouseholdID = " & Forms![frmDataSheet]![HouseholdID]
Me.FilterOn = True

[Datamulcher]

Hmmm. Thanks but the same error.
I tried a compact and repair, but still this error. At this point I am wondering if the database is corrupted. It has also started opening a separate form, unrelated in any way, in design view, when I close the coding window. It shouldnt be this hard.
I'm going to try the same code on another database and see if it works there!

[Datamulcher]

That also resulted in the same error message. I tried a compact and repair to no avail.
Then I applied the above coding into another similar database and it worked just fine. So, the database is corrupted.
Luckily it is a desktop copy of the networked database so no harm.

Now I can apply the code to a fresh copy of the database, I will have to import the form & associated queries from my version so fingers crossed it doesn't screw up the other database!

Thanks for your help!

[Missinglinq]

This particular error is almost always associated with opening a Form, and is apparently caused by all kinds of things, and, to the best of my knowledge, it is very seldom, if ever, actually connected with communicating with a OLE server or ActiveX Control, nor is it ever caused by the possible reasons given by the message!

The most common cause, going by what I've seen online for the past ten years, has to do with using a non-English language with Access, and need to set the language for non-unicode programs to that foreign language. Here's a link explaining this:

You receive an error message when you start Visual Basic for Applications code in an Access database

Note that although this link gives a specific Version of Windows and/or Access, the same problems persist over all combinations of Access and Windows.

And always a possible solution, when things suddenly go phooey, for no apparent reason, is to create a new, blank Access file and Import everything into it.

Linq ;0)>

[Datamulcher]

Thanks! that cleared this one up!

[Missinglinq]

Glad we could help!

Linq ;0)>