Opening Subform Search Results in another Form

[sTrongFuse]

I have a fairly simple Access 2007 database.

It consists of a form, called "Complaints" which, as the name suggests, is used to record customer complaints. These are saved into a table called "ComplaintData".

I have another form called "Search" which has a subform called "SearchComplaintData"

When the user enters criteria into "Search" the matching complaints show in "SearchComplaintData" in datasheet format.

The idea is that someone enters the initial complaint data using the "Complaints" form and saves it. If that complaint needs updating, they use the "Search" form to locate it.

This all works fine.

What I want to do next is for the user to be able to click (or double-click) on the relevant record returned by "Search" and reopen it in "Complaints" so that the updates can be added to the record.

This is where I come unstuck. I just can't get this bit to work.

I have tried all manner of variations of:

DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'"
But the best that I have ever managed to do, is open an empty version of "Complaints" when what I want is for the full populated (so far) form to be displayed.

Can anyone help? The source can be supplied if needed.

I suspect that I'm overcomplicating what I'm trying to do so if anyone can suggest a simpler alternative, that may also be welcome.

Thanks in Advance,

T

 

[pbaldy]

That's a valid method, though the syntax varies based on the data type of the field:

http://www.baldyweb.com/wherecondition.htm

You might need to make sure the displayed record is saved so it's available to the second form:

If Me.Dirty Then Me.Dirty = False

 

[sTrongFuse]

That's a valid method, though the syntax varies based on the data type of the field:

You might need to make sure the displayed record is saved so it's available to the second form:

If Me.Dirty Then Me.Dirty = False

Thanks. Should I add the on double click to the search form or the sub form?

Also, what is the "IF me.Dirty" command and where should it be added?

T

 

[pbaldy]

From the sound of it, in a subform textbox.

The Dirty code will force a save of the record. You can try it right before the OpenForm line.

 

[sTrongFuse]

From the sound of it, in a subform textbox.

The Dirty code will force a save of the record. You can try it right before the OpenForm line.

I now have:

Option Compare Database
Private Sub ComplaintID_Click()
DoCmd.OpenForm "Complaints", , , "[ComplaintiD] = '" & Me.ComplaintID & "'"
End Sub
Private Sub Form_Current()
If Me.Dirty Then Me.Dirty = False
End Sub

In the Subform where "Complaints" is the form I want to load and [ComplaintID] is the name of the primary key in both "Complaints" and the subform "SeacrhComplaintData".

Double clicking on the ComplaintID field in the search results, now opens a blank version of "Complaints"

 

[pbaldy]

well, I wouldn't call that

right before the OpenForm line.

Can you post the db here?

 

[sTrongFuse]

well, I wouldn't call that



Can you post the db here?

I have done, and I've removed all extraneous code from it.

T

 

[pbaldy]

ComplaintID was not in the record source of the Complaints form. After adding that, this appears to work as expected:

DoCmd.OpenForm "Complaints", , , "[ComplaintID] = " & Me.ComplaintID

 

[sTrongFuse]

ComplaintID was not in the record source of the Complaints form. After adding that, this appears to work as expected:

DoCmd.OpenForm "Complaints", , , "[ComplaintID] = " & Me.ComplaintID

AAARRRGGGHHH!!!!!!

I can't believe it was something so simple. No I understand why I kept getting error messages telling me ComplaintID was undefined.

Many thanks, it was driving me crazy.

T

 

[pbaldy]

No problem. It's always the little things that trip us up.