Access form -> another form against the same table not going to the right record

[MaMike]

Hi. This is my first post. I've found a lot of good answers here - THANK YOU. I am hoping to get some ideas for a problem I can't seem to overcome or find others with exactly the same issue.

I'm using a button to jump from one form to another. I want the other form to open to the record I just created on the first form. What's happening is the new form opens, but to a new record. Among the fields is a textbox that displays the record ID field. I see (new). [And in case you are thinking, well, don't do that, just stay on the same form, each step involves a few subforms. Keeping straight which subforms to open and close gets complicated. I thought a simple implementation would be form 1 (with its one subform) -> form 1' (with its three subforms) -> form 1'' (with its five subforms).]

At first I thought it was how I was opening the new form. I've tried all sorts of variants on DoCmd.OpenForm, using filters, where clauses, and OpenArgs - all of the sort ID=[id of the new record]. Then I thought the recordset might not reflect the change. I introduced a forced push of the data (via Me.dirty) and have verified the data are getting posted to the database before the jump.

On the target form, I've tried refresh() and requery() on Open, on Load, and on Current events. I've tried setting the TargetForm.recordSource:

[tt]Me.RecordSource = "SELECT * FROM [Base Table] WHERE ID=" & myID[/tt]​

I even tried the button wizard on the target form, mapping a text box that contains the ID (std autonumber PK field on the table) to the ID field.

Now what does work is in the target Form's onOpen() is if I query the database via vba for the record (using OpenArgs to pass the ID) and set each textBox control explicitly. The textBox that displays the ID then shows the right ID (I don't set that explicitly). This can't be the best way to do this, is it?

I appreciate any insights. I'm guessing I've done something wrong / missed on a config or there's something odd if you open a form against the same table.

Thanks!

Mike

 

[xwb]

Is ID a string? If it is, try LIKE instead of = and remember to put it in primes (') with a wildcard.

 

[MaMike]

Good suggestion. However, it turned out the code was fine. I had mistakenly set the form's DataEntry property to True, naively thinking that meant can edit data rather than only can edit data. In this case, someone made a vanilla form, which worked of course, and then did a property compare.

\begin{homer}
D'oh
\end{homer}