Query by form - open result in form?

[fraser_lindsay]

Hi, I followed this guide: http://support.microsoft.com/kb/304428 to create a query by form example.

I created a search form with unbound controls (frmJSASearch).
I created a query with all of my fields (qryQBF_JSA) and used criteria as defined in the article above for the search fields on my form above.
I created a macro to run the query from a command button.

It works and returns the reuslt in the query datasheet.


What I'm struggling with is returning the result in one of main forms, frmJSA. I want to see the particular 'JSA' I have searched for using my QBF in my main JSA form.

I've been messing with the macro but can't quite get it to do what I want.

Can anyone help please?

 

[dkinley]

Hey Fraser,

The common technique is to create something from that datasheet to open the specific record - usually in the form of a double-click.

Suppose for your datasheet, you had a field called "Company Name" you wanted to key off of. In design mode of the data sheet, use the OnDblClick() event of the control.

The code behind should look something like ...

Dim sFormName As String
Dim sLinkCriteria As String
 
sFormName = "FormNameToOpen"
sLinkCriteria = "[pkRecordID]=" & Me![pkRecordID]
 
DoCmd.OpenForm sFormName, , , sLinkCriteria

Be sure you have the recordID in the query if you use these fields to link.

-dK

 

[fraser_lindsay]

dK, hello again.

Thanks for your response, it looks like it makes sense only I not quite following.

How do I create edit a dbl click event on teh datasheet of the query. If I go into design mode that takes me into the query builder and I don't think there are (or at least I can't see) any of the events in the properties window for me to do that.

Also, while i'm at it. Although I followed the Microsoft guide my query looks strange. First of all, I typed the criteria string in one long string into the criteria box, as it requires but for some reason it has automatically chopped it in half and lost the 'Is Null' part. So the criteria is just checking against the field on the search form. There is no Is Null argument.

Secondly, the query is filling up with repeat strings of this criteria. I assume this happens when I run it? Why doesn't it clear?


Is this the most effective way to search and return records in a form? (I don't want to use filters as I want to keep the database simple (on the face if it) for the user.

I want the user to be able to type or select various fields and find matching records but in the form, not a datasheet as the form is formatted.

I have to do this on two forms and then I'm pretty much ready to set people lose on it!

 

[dkinley]

Oh ... I see. The confusing bit is that I thought you had the query as the data source for a form in datasheet view.

A trick to make the search return look pretty is to have an unbound form (with a close button and instructions on how to get to the single record by double-clicking on something) and on that unbound form make a subform that has the query as it's data source.

This subform is in datasheet view and here, on the control, is where you put the code I posted.

Just an idea ...
-dK

 

[fraser_lindsay]

dk, sorry I was out for the rest of the day...

So, I've done some searching and I'm attempting a change of tactic. I thought perhaps I could enact the filter option by code from a my popup search form.

I'm using the same unbound form with unbound text and combo's but this time hopefully for entering filter criteria. So I assigned this code to my filter command button:

Private Sub cmdFind_Click()

Dim strFilter As String

strFilter = "1=1"

If whatJSA <> "" Then
  strFilter = strFilter & " AND [JSAref]='" & whatJSA & "'"
End If

If whatJobGroup <> "" Then
   strFilter = strFilter & " AND [JobGroup]='" & whatJobGroup & "'"
End If

If whatCategory <> "" Then
   strFilter = strFilter & " AND [Category]='" & whatCategory & "'"
End If

If whatBusinessUnit <> "" Then
   strFilter = strFilter & " AND [BusinessUnit]='" & whatBusinessUnit & "'"
End If

If whatBusinessName <> "" Then
   strFilter = strFilter & " AND [BusinessName]='" & whatBusinessName & "'"
End If

If whatRisk <> "" Then
   strFilter = strFilter & " AND [Risk]='" & whatRisk & "'"
End If

Me.Form.Filter = strFilter

End Sub

I haven't got it working yet though. I think my references may be wrong, can you help?

Here's my limited understanding of it:

My unbount popup form is called 'frmJSA_Search' this is the one with the unbound controls for criteria.
Each of these criteria is a field which starts with 'what...' e.g. 'whatJSA'

The other fields I have in the middle of each expression is the target field on the main form I want to filter 'frmJSA'. These are field names not control names.

Does the last line of this code want to filter the form that this code is running from i.e. frmJSA_Search? I assume so, but my attempt at changing this to 'frmJSA' didn't work.

Any suggestions?

 

[dkinley]

This link: http://www.techonthenet.com/access/index.php

Under forms there are examples of how do search forms (you have to scroll down).

See if that helps you out first - this could get hairy.

-dK

 

[fraser_lindsay]

dk,

Thanks, this looks like a useful site. There does appear to be a couple of potential examples on there so I'll give them a whirl...