Open a detail form from a query

[ArjaySee]

As I mentioned in my intro, I am a novice ACCESS user, so my questions may be elementary. Please forgive me if that is the case.

In Access 2007, I have a query that presents the user with a list of the contacts he/she is approved to access. I want the user to be able to "drill down" to the detail form for a specific contact by double clicking on his last name, but I have not been able to find a property sheet for the last name field in the query to which I can apply the code.

Can I do this, and, if so, how?

Thanks

 

[pbaldy]

The short answer to your question is no, you can't do it. You have little if any control while in a query. Most of us never let users in tables or queries, just reports and forms. I'd present the data with a form based on your query, and use this:

http://www.baldyweb.com/wherecondition.htm

 

[ArjaySee]

Thanks for responding. So I would use a multi-record form? I thought I had done that, but ended up with a query. I'll try again.

 

[pbaldy]

No problem. A form in Continuous or Datasheet view would display multiple records. Post back if you're still stuck.

 

[ArjaySee]

Okay

I have the datasheet in a form, and have two questions about that. (1) I am trying to filter the list to include records on-year old and newer. I am getting an error on the field, which seems to say that the formula is not correct. What would be a formula to display only records where modifydate is one year old or less?

(2) The forms VIEW presents four types (Design, Layout, Form, and Datasheet). How do I set the form to open in the datasheet view?

Thank you very much for your patience with me.

 

[pbaldy]

As a query criteria?

> Date() - 365

or you could use the DateAdd() function. For the view, look in the form's properties on the Format tab for Default View.

 

[ArjaySee]

Thanks. I see my default is the datasheet, so that's cool. I had been trying that date formula, but I keep getting an error. I am using the RECORD SOURCE property to do this. Should I be doing in in VBA instead?

 

[pbaldy]

No, but it would help to see exactly what you're putting in the record source.

 

[ainstien]

just on a related topic,

In excel, you can find all the named ranges, cells, functions you have running in the spreadsheet. Is there anything similar for Access?

 

[ArjaySee]

I appreciate your prompt replies, and will respond. In my attempt to answer you question, I accidentally hit a wrong key and have to rebuild the form. May be tomorrow.

 

[ArjaySee]

The formula, built by the wizard, is =[uni_modifydate] > date() -365.

 

[pbaldy]

That wouldn't be appropriate as the record source on its own. The record source of a form is either the name of a table or saved query, or SQL like

SELECT * FROM Tablename WHERE [uni_modifydate] > date() -365

 

[ArjaySee]

Cool. I conected it to a query and it works fine. I was confused by the ellipse drop down in the properties, which presented a wizard that lookl to be for the purpose of building the criteria.

My only remiaining issue on this form is minor and insignificnt. I can't see how to left align the headings on the data sheet

Thank you, again, for your help.

 

[pbaldy]

Happy to help. Personally I don't like and don't use Datasheet, in part because I feel like I have more control over the look and feel with Continuous. Offhand, it does appear the headings ignore the settings of the labels. If you want, start a new thread on that topic and hopefully somebody with more experience with datasheet will respond. I did a quick search and the answer I seemed to see most was "can't be done, use continuous view and format it to look like datasheet".

 

[ArjaySee]

Since I'm in the early stages and will be setting precedents, I would like to try a continuous form before going farther.

I'm not seeing that as one of the options, though. After CREATE FORMS, where do I go?

 

[pbaldy]

You should certainly play with both and make up your own mind. Make a copy of your datasheet form, then go into the properties of the copy and change the Default View property to Continuous. At first it may look pretty bad, because datasheet automatically formats everything. In continuous, you can move and size everything to your liking.

 

[ArjaySee]

Thanks - that's exactly what I'm looking for in the first place. I'll leave you alone for a while now ")

 

[pbaldy]

LOL! No problem.