I have a Parameter query, but would like to double click to retreive 1

[perrydaniel]

record. Basically I have a database that has lots of records. One of the fields is an autonumber.

I have a form set up which displays 5 of the 10 fields from the table, I also have a button which when you click requests the autonumber. (Running off a parameter query).Which will then display the remaining fields.

Could I get the query to run by double clicking on the autonumber, rather than typing the number into parameter query?

Any help will be appreciated


perry

 

[dhookom]

You can set up the on double-click event of the autonumber text box to open a datasheet form that has your query as the record source:
Dim strWhere as String
strWhere = "[IDField] =" & Me.[IDField]
DoCmd.OpenForm "frmDSYours", acFormDS, , strWhere

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[perrydaniel]

Duane,

Thanks, nearly there, but I am still being prompted to input the ID Number,after the DClick. I would ideally like Access to recognise the number by way of the double click.

Would appreciate your assistance


Cheers


Perry

 

[grnzbra]

I think you're going to have to get the parameter statement out of the query. then you could work with the events for that box to control what it does.

 

[dhookom]

Good catch grnzbra. The parameter has to come out of the query or it will always prompt.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[perrydaniel]

I appreciate your comments, about parameter coming out of query. But not knowing VB, and never used events, not sure where to go from here?

Any solutions?

Perry

 

[dhookom]

The form wizard provides the functionality to write code that will open a form to a specific record.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[grnzbra]

If you open the form in the design view and select the control you want to double click, you then have the ability to get to the events by clicking on the "Properties" button (it's up in one of the tool bars at the top and has an icon of a left hand pointing the index finger down toward the left while holding - or perhaps in front of - a form). Or, you could right-click the control and select "Properties" from the menu that pops up (Properties is near the bottom of the menu).

This will open a window with several tabs, one of which is "Events". Click on this and you will see a bunch of things that you can do to that particular control; click, double click, etc. Select Click and you will get a button with three dots at the right of the selected event. Click the button with the three dots (not the one with the arrow) and you will get a window that lists, among other things, Code Builder. Select Code Builder and you will get a witdow with two lines of code. Between these, enter the code that Duane gave you in his first response.

You wouldn't believe the amazing things you can make Access do once you have a little knowledge of VBA. Take a walk on the wild side; jump in and have some fun.

 

[perrydaniel]

Grnzbra,

Thanks for the reply, I am with you as far as Duanes code, but would I drop the parameter query to automatically select a record? Really not sure where to start from that point.

Thanks


Perry

 

[grnzbra]

Duane's code seems to be letting the query run free and then filtering on the form. I personally would rather apply the parameter to the query. I don't know which is a better way to do things. What I did when I was asked to get rid of the parameters was to create a public variable and a function to read it. Then I set the query criteria equal to the function.

How would you do that?

Go to the module section of the database and click the Modules tab. Click "New". A window will open up ready for you to type code.

Type the following:

Public kyfld as Long

Public function keyfd() as long
keyfd = kyfld
End Function


Actually you won't have to type the last line; it will automatically be generated when you type the Public function line.

What you have done is declare a variable by name (kyfld) and type (long - can handle big integers). I would recomend naming the variable exactly the same as the field in the table to which the criteria will be applied. (At this stage it doesn't really matter, but in complex query gathering forms it can cut the code down substantially and eliminate the need to change code when new criteria are added)

You have also created a function by name (keyfd) and type as you did with the variable. (a procedure that returns a value such as Now() which returns the current date and time)

Now go to the On DoubleClick event for the combo box where you want to select your criteria as I described previously and type:

kyfld = Me.ComboboxName

ComboboxName is whatever the name of the combo box is (see the properties window, "Other" tab. The name is the first line.

Now in the query, in the criteria box in the grid type:

keyfd()

When you click a value in the combo box, the doubleclick event will set the global variable equal to the value you selected.

When the query is run, it will call the function. The function will open and middle line will set the function equal to the global variable and that is what the query will use for its parameter.

Doing it this way really is nice when you have to do this with several criteria (Such as StartDate, EndDate, PriorPeriodStartDate, PriorPeriodEndDate, OfficeNumber) which is what was happening here. It was a parameter query that asked all five questions One - At - A - Time. My boss asked me to do something about it and that's how I did it.

 

[grnzbra]

OOps. In the Double Click event, after the line:

kyfld = Me.ComboboxName

you have to have the line:

DoCmd.OpenForm "frmDSYours", acFormDS

So the on doubleclick would look like:


Automatically generated header line

kyfld = Me.ComboboxName
DoCmd.OpenForm "frmDSYours", acFormDS

Automatically generated footer line


What kind of control is it that you want to double click? I've been assuming it's a combo box. Is that correct?

 

[grnzbra]

I just thought of something. Your first post said that you push a button. Go to the OnClick event of the button. Whatever happens there, you want to happen in the On Doubleclick event for the combo box AFTER the line in On Doubleclick sets the value of the global variable. Also, you would not want Duane's line.

Please note, that this is A way of doing this, not THE way to do it. I don't know which would be better; only that it worked for me.

 

[dhookom]

Filtering in the query or in the "where" clause is a matter of choice. I almost always let my forms and reports run "wide-open". I have much better control over the filtering by using the where clause.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[perrydaniel]

Thanks guys, great help. Will try this morning and let you know how I get on.

Many Thanks again!

Perry