Question Default Value

[johntology]

Hello,

I'm new to Access and am having trouble doing what should be simple. In my form I want one field to contain a default value held in a table. I've read instructions on various sites including Microsoft's but this gets me no closer. I'm forced into using an expression builder. I just want to put a query somewhere.

Where can I just put a Select statement that populates a form field with the result of the query for each new record? Why this expression builder, which doesn't seem to accept SQL?

Thanks.

 

[Adam Caramon]

Where can I just put a Select statement that populates a form field with the result of the query for each new record? Why this expression builder,

When you say "the result of a query" do you mean a field in the table, a calculation, or what? Basically, give us an example of what data you're trying to use as a "default value" in your form.

 

[johntology]

When you say "the result of a query" do you mean a field in the table, a calculation, or what? Basically, give us an example of what data you're trying to use as a "default value" in your form.

Thanks for your reply. I've learned a couple things since I posted. I now understand that you have to create a query and save it, then reference that query in the Default Value field. But this now is where I'm stuck.
On my form I have a field, of data type Memo. I have created a query and saved it, and have run the query to verify that it's working as intended. Now I simply want the results (i.e., output) of my saved query to be the default value in one field for each new record the user adds.
I have done the following so far:
1) selected [...] on the Default Value property of the Property Sheet.
2) This brings up the Expression Builder.
3) In the Expression Builder I choose Queries.
4) I select the query I created and this populates pane 2 of Expression builder with the field name that contains the value I want, and pane 3 with <value>.
5) Not sure what to do with this, I just double-click on the value in Pane 2 and this populates the expression builder text field with an expression of format, [QueryName]![RelevantColumnName]. Assuming this is what I want, I click Ok.

Now when I create a new Record I expect to see the value from my query in the form. However, I'm getting #Name?

I've looked around online to see what could cause this error. They reference typos in the name of the control and other things that I know are not the case. Is there something else I'm doing wrong?

Thanks.

 

[johntology]

When you say "the result of a query" do you mean a field in the table, a calculation, or what? Basically, give us an example of what data you're trying to use as a "default value" in your form.

My query, btw, is just a simple select statement from a table with a where clause, such that I get a single value as a result.

 

[Adam Caramon]

My query, btw, is just a simple select statement from a table with a where clause, such that I get a single value as a result.

I'm getting the feeling that you're going about this in the wrong way.

The default value is used whenever you enter a new record. You're trying to set the default value to the result of a query, but I don't understand why you're trying to do that? You probably could do what you're trying to do without a query.

Give me example data that would be used in the text field on your form.

Does the default value change each time you enter a new record? If so, does it change by a pattern? Is it a numbered scheme? For example, you have a field that requires a number to be entered, and you want the form to automatically tell the data entry person what the next appropriate number is? Or, something else?

 

[johntology]

I'm getting the feeling that you're going about this in the wrong way.

The default value is used whenever you enter a new record. You're trying to set the default value to the result of a query, but I don't understand why you're trying to do that? You probably could do what you're trying to do without a query.

Give me example data that would be used in the text field on your form.

Does the default value change each time you enter a new record? If so, does it change by a pattern? Is it a numbered scheme? For example, you have a field that requires a number to be entered, and you want the form to automatically tell the data entry person what the next appropriate number is? Or, something else?

Thanks. You might very well be right. It is in fact static data, with the exception that the owner of the DB will want to change it more or less permanently, occasionally (if that makes sense). So I thought it made sense to store the data (a few paragraphs of text) in a table and let him change the data field occasionally as he needs, directly in the table. All previously created records, however, should retain the old values.

 

[Adam Caramon]

I would recommend against having the owner directly access the table unless he has experience in operating relational databases, otherwise you're just asking for trouble.

I found this link:
http://www.access-programmers.co.uk/forums/showthread.php?t=194933&page=1

One such solution: depending on how many users in this database. You could set up a dummy form with no record source, add a button with an on_Click event to change the fields default value.

Something like this:

Dim strx As String
strx = InputBox("Please enter new default message", "Default Message", "")
CurrentDb.TableDefs("tblTest").Fields(1).DefaultValue = strx

This will cause an input box to pop up, and whatever he types in will become the new default value for new records. It will not affect the old records at all.

Not the best of solutions, and could definitely be improved on, but maybe it will work for you?

Edit: I noticed you said "a few paragraphs", which will probably make this solution a nogo. Let me see if I can come up with something else (unless someone beats me to it