How to pull out data based on a search made (using a search form) (1 Viewer)

boknoval · Jan 16, 2013

Hi, I need help with this one.
I have a database wherein I have a table called Properties and a table called Mail_Campaign.

For both tables, they have this one field in common, which is the field PID (it’s the property id, and is unique).

I have this Search Form (frmSearch) created which searches the properties based on the criteria/filter entered and the result is shown on the subform (frmsubProperties).

What I wanted to happen, is that, when I click a button, it would open a new form where I could pull out these properties from the search result, in the Mail_Campaign table (and put it in a subform maybe). It would be based on the PID coming from the search result.

For example, in the image below, I have done a search and it returns 4 properties as a result. I wanted to pull out these 4 properties from the Mail_Campaign table (in a subform that is not locked) so that I could enter dates under such marketing activity on my mail_campaign table.

sample image:

Would this be possible?

I may need some thorough guidance here, as I am really not a programmer, nor am I that good about databases.

I hope somebody could help me. Thank you very much!

PS. I have also attached my database if ever you may need it as a reference.

ADDITIONAL(edited):
I have updated my attached database. I have added a button to my search form (frmsearch) to supposedly call in my frmMailCampaign.. but instead of showing just the 4 properties based on the search made (example in the image above), it just returns all properties.

This is what's on the code:

Code:

Private Sub Command24_Click()
    DoCmd.OpenForm "frmMailCampaign", , , "PID=" & BuildFilter
End Sub

I totally have no idea what I am doing..
help pls..

John Big Booty · Jan 16, 2013

You can use the Where Condition of the OpenForm method to open a form filtered with reference to the current record

John Big Booty · Jan 16, 2013

Check the Double Click event of the List Box in the sample here for a practical example.

boknoval · Jan 16, 2013

Hi John, how can I apply it? sorry for the dumb question. Tried reading the link you gave me but it was kind of hard form me to understand.. (not really a programmer, still learning)

boknoval · Jan 16, 2013

help anyone?

pr2-eugin · Jan 16, 2013

Bok, The function BuildFilter you have, returns a String that will have the WHERE part of the SELECT statement.. Something like..

Code:

SELECT * FROM someTable 
[COLOR=Red]WHERE (someField = someValue AND anotherField = anotherValue)[/COLOR];

Where as the DoCmd.OpenForm method with a WHERE condition will be like..

Code:

DoCmd.OpenForm "frmMailCampaign", , , "PID= 1234"

So as Peter has mentioned, you need to use the SubForm's Double click event.. When I looked at your DB, I could see that you have used a SubForm to list the Details, which is why you are getting all the list of data.. So I used the frmSubMailCampaign, to filter.. See the attachment for more info.. Let me know if you could not understand anything..

boknoval · Jan 16, 2013

Hi Paul, you're just simply the best!

I see what you did.. Thank you for that..
According to what you did, I have to double click any of the properties from the search result, then from there, it would send me to the frmsubMailCampaign...

But I was wondering if it is possible to view all the properties from the search result all at once in a datasheet view?

pr2-eugin · Jan 16, 2013

boknoval said:
Hi Paul, you're just simply the best!

I see what you did.. Thank you for that..

Most welcome.. Thanks for the comment, I am not that of a big deal, just trying to help out.. There are so many others who are so much better than me.. Anyway..

boknoval said:
But I was wondering if it is possible to view all the properties from the search result all at once in a datasheet view?

You can, but it would be a bit messy.. (according to my opinion) but it sure is possible.. You need to reconstruct the Filter and pass that as an open argument to the (main)Form to set the SubForm's record source in the OnLoad event.. Do you want to go down that route?

boknoval · Jan 16, 2013

Uhm, yeah paul.. would you by chance, willing to work me through it? it would really be helpful... thanks in advance!

pr2-eugin · Jan 16, 2013

As mentioned earlier, you need to make use of the Button, in which you will pass the Query you created to filter the Search data, then you have to pass that to the Form you are trying to open..

Code:

Private Sub Command24_Click()
    [COLOR=Red]Dim argStr As Variant
    argStr = BuildFilter[/COLOR]
    If Not IsNull(argStr) Then
        argStr = "SELECT PID FROM qryPropertiesData " & argStr
    Else
        Call MsgBox("Just to let you know you have not selected a filter, you will see all information now..", vbInformation)
    End If
    DoCmd.OpenForm "frmMailCampaign", _
        OpenArgs:=argStr
End Sub

If you can see that I have added a small Check before opening the form this is because, if in case the User did not select any filter, it has to open all data..

There is a small catch for this though.. as the Query you obtain does not include all details form the Mail_Campaign table, does it? So you have to use the query generated by the BuildFilter function, as a SubQuery to the form you are opening.. something like.. This would be done in the Form_Load event of the frmMailCampaign.. As this is the first method that will be called when a form is opened..

Code:

Private Sub Form_Load()
    If Len(Me.OpenArgs & vbNullString) > 0 Then
        Dim sqlStr As String
        sqlStr = "SELECT Mail_Campaign.* FROM Mail_Campaign INNER JOIN (" & Me.OpenArgs & ") AS tmpJoin ON Mail_Campaign.PID = tmpJoin.PID"
        Debug.Print sqlStr
        Me!frmsubMailCampaign.Form.RecordSource = sqlStr
    End If
End Sub

The If Check ensures if you are opening a filtered result or the whole.. Makes sense? Let me know if this works..

boknoval · Jan 16, 2013

Awesome! It works perfectly Paul! I get to learn from you day by day thank you!

pr2-eugin · Jan 16, 2013

Most welcome, Glad I could help..

boknoval · Jan 16, 2013

Hi paul, I may need your help once more. I have finished editing the database with the codes you have provided. It worked just perfectly.. My problem now is, I can't seem to add dates under the fields on the frmMailCampaign even if I have set it to allow editing? What do you think is wrong with it? I am allowed to do editing from the frmMailCampaign right?

pr2-eugin · Jan 16, 2013

Not sure, I thought the reason might be because of the JOIN, so I changed the Join to something different using IN clause,

Code:

"SELECT Mail_Campaign.* FROM Mail_Campaign WHERE Mail_Campaign.PID IN (" & Me.OpenArgs & ")"

I changed it in your and it still does not seem to edit..

However it allows me to change from the old copy I had.. Check the attachment..

boknoval · Jan 16, 2013

Hi paul, I have went through the database I have sent you and re-edited it with the changes you made! Thank you! It works perfectly fine now! Thank you very much!

pr2-eugin · Jan 16, 2013

Good to hear Bok..

Good luck..

John Big Booty · Jan 16, 2013

Thanks for jumping in whilst I slept Paul

pr2-eugin · Jan 16, 2013

John Big Booty said:
Thanks for jumping in whilst I slept Paul

No prob's Peter.. Bok was getting a bit desperate, so thought I would just carry on for a bit.. Lol..

How to pull out data based on a search made (using a search form) (1 Viewer)

Registered User.

Attachments

AWF VIP

AWF VIP

Registered User.

Registered User.

Super Moderator

Attachments

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Attachments

Registered User.

Super Moderator

AWF VIP

Super Moderator

Similar threads

Users who are viewing this thread