Want to add query result in a form

[MHO]

I have a simple database I am trying to put together. One table, one form, very vanilla. I created a query that ranks values that I enter in a table. I would like to put the result of the query (the rank) next to the value in the form itself. I thought if I created a text box and entered the rank field in the text box, that would be it, but when I run the form, I get "#Name?".

Any thoughts? Is this even possible?

Thanks in advance.

 

[ejstefl]

Why don't you base your form on the Query that includes the Rank rather than the table? Then you'll be able to add Rank easily.

 

[MHO]

I don't think I can do that because there are at least 4 other values I want to rank separately based on at least 4 different query results. After some research, I believe I want to use the DLOOKUP function, but I cannot for the life of me get the syntax to work. On top of that, wherever I see examples of the code, it's never really explained WHERE to put the code. I've been throwing darts in the AFTERUPDATE section, as well as control source area. Any other suggestions, or can someone point me to some examples of DLOOKUP where queries are involved? I can usually figure things out by looking at examples, but this one has my head spinning.

Thanks.

 

[MHO]

On top of that, and more importantly, it appears that I cannot enter new information in a form based on a query. Does that sound accurate?

 

[ejstefl]

The syntax for DLookUp is as follows:

=DLookUp("FieldName", "QueryName", "OtherField = " & SomeValue)

It would go in the ControlSource of a textbox. If the "OtherField" is a text field, you'll need to add quotes:

=DLookUp("FieldName", "QueryName", "OtherField = '" & SomeValue & "'")

The reason the recordset is not updateable is that you need to have a defined relationship between the two sources, which in this case you would not.

 

[MHO]

I really appreciate the reply.

Can "SomeValue" be a field in a table outside of the query. In other words, my function looks something like:

=dlookup("rank","querytablename","querytablename!id"="tablename!id")

I set up a "relationship" between the id fields in the query and the table. Not sure if that was necessary.

All I get is a blank text box in the form, which is a step up from the "#Name?" I have been reliving in my nightmares.

Also, I am experimenting (or should I say, "settling"), for the query result to be displayed in a subform. I'm following the instructions in a book I have, and it says that as soon as I write the rectangle of where I want the subform, a wizard should be activated. That doesn't happen. I don't think that's the bad part, but when I go into the format propery, I want to be able to disable the record and navigational selectors, but there is no option for it. Not sure if the two are related.

Thanks again for trying to help the helpless.

 

[CraigDolphin]

For starters, the dlookup you posted has some errors. Pay careful attention to the placement of the quotes and the &-symbols as per ejstefl's example, and avoid typos in your field/table names.

dlookup searches for a single value (that satisifies a condition) in a field of a table, and returns the matching value for that record in a different field. If more than one record matches the search criteria it returns the first suitable value it encounters. So, to use all values in a table's field as your criteria doesn't really work if your dlookup is to be used in the control of a form (which can only hold one value).

To get the wizard to operate, make sure that the wizard button (looks like a wand with stars) on the toolbox is 'on' before you click the subform button on the toolbox.

In any event, I'm not really sure what you are trying to achieve. Are you wanting to add a new value to a table via a control on a form, then query all values in the table and determine what rank your new value is? Then you want to rank 4 'other' values...are these values in the same field or are they different somehow?

Maybe a better explanation of what you are trying to achieve might help your cause. I have attached a db that might help you. The form is in continuous form view and is bound to the table of values. The query ranks all the values in the table. The rank textbox on the form is unbound and uses the dlookup function to find the rank of the relevant value in the MyValue textbox on the form (from the query). The dlookup is nested inside an iif statement to avoid showing an error if the MyValue txtbox is null.

You can enter new values into the form and the after_update event is used to update the rankings shown on the form if you enter new values.

Hope it helps.

 

[MHO]

Thank You!

I have figured it out. I was receiving an error message when I first entered in your formula, but I sub'd the myvalue with id fields (in the criteria section), and now it works like I envisioned.

Thanks again. One headache down.....

 

[CraigDolphin]

Glad to help