Parameter Query

[Casper2012]

Hello-

I was wondering if there was a way to run a parameter query and have the results open up in a form?

Thanks for any help anyone could pass along!

 

[John Big Booty]

Simply use your parameter query as the Record Source of the Form. When the Form is opened the query will prompt the user for the parameter, and populate your Form accordingly.

The one thing you might want to check for is that the query is not empty. The following Code in the On Load event of your form should do the trick;

If Me.RecordSet.RecordCount = 0 Then
     MsgBox "The parameter you entered had no results, please try again"
     DoCmd.Close acForm, "YourFormName"
End If

 

[Casper2012]

Sounds easy enough JOhn!

Just wondering though...

What would be the better way to perform my query?

A. Like [Enter phone Number] & "*"

<or>

B. Like "*" & [Forms][Search]![Phone Number] & "*"


I really appreciate your help John! I have been reading alot of your posts. You are really great with Access!

 

[John Big Booty]

Personally I'd probably go with option B, however I have also used option A in the past. It really depends the feel that you want from you forms and the way you want the user to progress through the various stages. In the end both will achieve your desired goal, so it's just up to your personal preference.

 

[Casper2012]

I tried the code and I am getting a Syntax error:

DoCmd.Close acForm. "Contact Details"

Any ideas?

 

[John Big Booty]

Option A will give you a little be more control over what criteria your users enters, as you can put some data validation in the button click to open the form. Thus ensuring that A) the user has actually entered something to search on and B) that it is valid, from there you can then prompt the user if they've not entered a criteria or it's not valid.

 

[John Big Booty]

I tried the code and I am getting a Syntax error:

DoCmd.Close acForm. "Contact Details"

Any ideas?

Replace the full stop after acForm with a comma.

 

[John Big Booty]

That's my bad my apologies, I'll fix that post.

 

[Casper2012]

Also, in my query I have the Field: Contacts.*
Table: Contacts

could I use the Like [Enter phone Number] & "*" command here or would I have to spread out each of the fields independently to get it to apply for the phone number?


I hope that wasnt too confusing.

 

[John Big Booty]

If you are after a prompt for both contact and phone number you will need to put the code into each filed that you want to prompt the user for. You will also need to consider if you wan that to be Contact And Phone Number or if you want Contact Or Phone Number

 

[Casper2012]

I tried that and I dont think it will work for what I am trying to do. I was trying to run the command from the Button I had on the form. But when I run the command (which runs the query) it show me the query in a table. I need the results to show in a form so I can choose the result from the many that it returns. Because I have more than one number in the search return.

Any ideas of making it work that way John?

thank you greatly!

 

[Casper2012]

looks like i only need to search for phone numbers, not a contact criteria...

 

[Casper2012]

Buit can the same results come from the search button I created on my form? Can it search the query and return the result like you metioned before?

 

[John Big Booty]

Buit can the same results come from the search button I created on my form? Can it search the query and return the result like you metioned before?

Sorry I'm not quiet sure I follow

 

[John Big Booty]

I was just playing with this DB it might give you some pointers.

 

[Casper2012]

Sorry John, didnt mean to confuse you.

What I was asking was, is there a way to use the button I created on my form to perform the parameter query, and populate my form with the results.

See, some of my customers may have 2 -3 phone numbers and I need to be able to choose from a list the correct one to open. Creating a parameter query and searching the phone numbers is PERFECT!, However, the problem rests in trying to get all that "perfect" data... to show up in my form... UHGGG.....

this has been so disappointing.

I will look at the database you sent over.... thanks!

 

[Casper2012]

John-

I am checking out your db5 project and was wondering what is the command to enter a "CLear Form" button. If I wanted to put a button on the welcome page that would clear the data out and start again....

 

[John Big Booty]

You could add a button with the following code in the On Click event;

    Me.Combo2 = Null
    Me.Combo4 = Null

I should have done this previously but I would also change the On Click event for Button Command 6 ( that's the one with the caption Search 2 on it) to;

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    
[COLOR="Purple"]    If IsNull(Me.Combo2) And IsNull(Me.Combo4) Then
        MsgBox "please make a selection from one of the Combo Boxes"
        Me.Combo2.SetFocus
        Exit Sub
    End If[/COLOR]

    stDocName = "FRM_ResultMain2"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command6_Click:
    Exit Sub

Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click
    
End Sub

Note the purple highlighted section which now check to make sure that the user has made a selection in at least one of the combo boxes.

 

[Casper2012]

This is great JOHN! I think we may actually have something here. I will need to do a LOT of work with merging the databse you showed my with what I am actually trying to do. I will basically use the db you sent as a template to bring data into my database. Man this is great. I will post the final result once I get it for anyone else with the similar issue.

You da man John!!!!!! (two thumbs up)

-hey really, thanks for everything John-

 

[John Big Booty]

Glad to have been able to helped you make some headway with your project