Open new form with record data sorted from another form

[userGEMR]

Hi, relative newbie here. Hoping this question makes sense, have had a difficult time finding the right approach to my problem on the web thus far. I think there's a solution within the strWhere function but to be sure I'll lay out my problem.

I have a main form[frmResearchNotes] with combo box controls that filters a query populating [subfrmNotelist] containing several records from the filtered query. From there, I double click on a field within one of the remaining records, [CompanyName] for example, and it opens the new form[frmNoteDetail]. The problem is that second form is not displaying that selected record. The second form's record source has been set to the same query so when it loads, it displays the same info but it's displaying the 1st record out of the entire filtered list, not the record I clicked on in that list.

I figured I could use the strWhere function to copy the record I selected in the event procedure and then open the new form with those details. Not sure how to actually do this with VBA or if it's even the correct approach. Thanks in advance!

 

[pbaldy]

Is this what you're after?

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

 

[userGEMR]

I believe so, thanks, I think has me a few steps closer. However, I'm getting a type mismatch error on the following line of code, can't figure out why.

docmd.OpenForm "frmNoteDetail",,, "CompanyName = '" & Me.CompanyName & "'" And "Sector = '" & Me.Sector & "'" And "subsector = '" & Me.subsector & "'" And "PublishDate = #" & Me.PublishDate & "'" And "NoteTitle = '" & Me.NoteTitle & "'"

 

[pbaldy]

You've made a common mistake. The double quotes on either side of each And need to come out.

& "'" And "Sector...

 

[Simon_MT]

This is what I do I have already qualifiied the records through a Search foprm so basically I parse the forms filter between forms.

Function ClientsInterestClient() As String

    With CodeContextObject
            DoCmd.OpenForm "Clients Interest Enquiry", acNormal, "", ClientsFilter, acFormEdit, acWindowNormal
            DoCmd.GoToRecord acDataForm, "Clients Interest Enquiry", acGoTo, .CurrentRecord
            DoCmd.Close acForm, "Clients Details"
        End If
    End With
End Function

Simon

 

[userGEMR]

baby steps I guess... I fixed that syntax on the And and quotations but stumbled on the date syntax.

syntax error in date in query expression 'CompanyName = 'Apple Computer' And Sector = 'Tech' And PublishDate = #3/4/2014 10:20:55 PM'.

the date field seems to be getting populated but the debug takes me to the DoCmd line.

 

[pbaldy]

The date would need an # at the end too, not a '. Didn't notice that at first.

 

[userGEMR]

Success! many thanks.

I have to admit, I'm a little surprised it works as well as it as. I thought I'd have to make modifications to the form I was opening, especially since I didn't include every field name from that second form within the syntax. I was expecting to update the code once I got some of it to work.

 

[pbaldy]

Happy to help!

 

[GlenS]

This sounds similar to my problem that I posted about last night.

Creating a search to populate a subset of records - unfortunately, I can't post links yet.

When I include anything in the Where parameter of the OpenForm command, I receive a RTE 3000. I have yet to find anything that could be causing it.

Any help would be great...

 

[pbaldy]

It would help to see your code and know the data type(s) of any fields in the wherecondition.

 

[GlenS]

Thanks Paul but because I've posted less than ten times it won't let me post the link. The subject line of my OP in the Forms forum is

"Creating a search to populate a subset of records"

 

[pbaldy]

Have you done a Debug.Print on varWhere to see exactly what it contains?

 

[GlenS]

Here's the code. I've stripped out most of the search fields to target just the one until I can get it working, then I'll add the others back in.

The debug.print shows

([p_last_nam] LIKE 'Shevel*') OR ([s_last_nam] LIKE 'Shevel*')

Private Sub cmdSearch_Click()
Dim varWhere As Variant

' Initialize the search string to Null
varWhere = Null

If Not IsNothing(Me.srchLastName) Then
varWhere = "([p_last_nam] LIKE '" & Me.srchLastName & "*')"
varWhere = (varWhere + " OR ") & "([s_last_nam] LIKE '" & Me.srchLastName & "*')"
End If

' Check to see that we built a filter
If IsNothing(varWhere) Then
MsgBox "You must enter at least one search criteria.", vbInformation, gstrAppTitle
Exit Sub
End If

' Open Clients filtered
' Note: if form already open, this just applies the filter
DoCmd.OpenForm "frmClients", acNormal, , WhereCondition:=varWhere , acFormPropertySettings, acWindowNormal
' Done
DoCmd.Close acForm, Me.Name

End Sub

 

[pbaldy]

That looks okay offhand. Can you post the db here? What is the text of the error?

 

[GlenS]

That's what has me baffled Paul. I'm hoping a second set of eyes could see something I haven't been able to.

Here's the error text that I receive

Run-time error '3000':

Reserved error (|); there is no message for this error.

I have attached the database.

Cheers, Glen

 

[GlenS]

Update...just in case it could be a corrupt db, I created a new db, but with the same result. There seems to be very little written on the internet about this error.

 

[pbaldy]

It was blowing up on me too, and I suspected corruption as well. Didn't have time to explore further, but you might try the

SaveAsText/LoadFromText

method discussed here:

http://www.thatlldoit.com/Pages/howtosarticles.aspx

 

[GlenS]

Interesting update...I'm just began saving the objects as text and noticed the these two previously unknown objects in the list. I initially included them in the extract; however, an error was produced, so I excluded them.

Glen.

 

[GlenS]

More bizarre is that when I created a fresh database with tables loaded from the original and then loaded the objects from text, I got another hidden copy of my main query. Execution of the search in the new database with loaded text objects still results in the RTE 3000.

Glen. :banghead: