Set default for text box from sql linked table

[Rick Beach]

I have a linked Sql view that retrieves the max number for a column named CHSRNumber. This query adds 1 to this number to give the next sequential number for a new record to be created and it is called NewChsrNumber.
I have a text box named CHSRNumber on a form that I set the property default to be =[Tables].[FacilitiesQueries_MaxChsrNumber].[NewCHSRNumber].
When the form is opened, the text box states "#NAME?"

I have also attempted to use an event to set the text box to this new number by using the below:

DoCmd.OpenTable "FacilitiesQueries_MaxChsrNumber"
Me.CHSRNumber = [Tables].[FacilitiesQueries_MaxChsrNumber].[NewCHSRNumber]

The sql table opens with the correct info but then I receive an error 2645 stating it cannot find the field referred to in the expression.

Any suggestions would be appreciated.

[NeoPa]

[Tables].[FacilitiesQueries_MaxChsrNumber].[NewCHSRNumber] is simply not a way you can access that data. Sorry, but it's not.

In your code (where you omitted to post the event procedure wrappings so we don't see where it fires) what you're doing is :

1. Open a recordset to the correct table, but then drop the useful recordset object returned.
2. Try the same failed code you had in the DefaultValue (not Default) property, but assign it to the Value (which is the default property) of control instead of the DefaultValue property (which is the one you actually want to set).

Had you used the recordset object returned by the first line, you could have set the DefaultValue to RecordsetObjectVariable!NewCHSRNumber and it probably would have worked for you.

Not forgetting, of course, to close the recordset before proceeding if you're done with it.

I hope this clarifies things for you.

[Rick Beach]

My apologies for not giving what fires this procedure. The "On Open" event is where I set the "OpenTable" command and the Me.CHSRNumber = [Tables].[FacilitiesQueries_MaxChsrNumber].[NewCHSRNumber] code.

The On Open event gives the error 2645 after the table has opened.(I only open the table to verify the info was available. This will be removed after working properly)

With the above not working I attempted the below:

The property "Default Value" is where the =[Tables].[FacilitiesQueries_MaxChsrNumber].[NewCHSRNumber] was placed.

This resulted in the "#NAME?" to be displayed in the text box.

Both of these methods work when utilizing an Access table or an Access query that retrieves info from an Access table, but do not work with the sql linked table or a query that utilizes the sql table as a reference.

I am sure you will ask if the sql view is updateable. It is but it should not matter as I only use this to retrieve the number for the default info and is not being written to the sql view.

[NeoPa]

Rick Beach:
The "On Open" event is where I set the "OpenTable" command and the Me.CHSRNumber = [Tables].[FacilitiesQueries_MaxChsrNumber].[NewCHSRNumber] code.

I have already commented on what I feel is needed for this. As far as the event is concerned, I don't see the Form_Open() event as the only place to assign the DefaultValue. It would need to be set again with every Form_AfterInsert() too, I expect.

That's always assuming this is not a multi-user project. For portable and multi-user projects such a value should only be determined and set immediately prior to assignment. That would be in the Form_BeforeInsert() event procedure. It is never necessary to display this value on a form until after it has been used. Many people seem to think it's necessary, but in fact whatever it says is misleading, because until it is assigned it doesn't even logically exist, so displaying it at that point is misinformation. The fact that it is also inherently unreliable as an indicator of what the value will later be, is simply further good reason to avoid displaying it in advance of its actual existence.

I feel the previous paragraph is important information to share, but ultimately it's not an answer to the specific question. For that, see the earlier comments in the previous paragraph.

Rick Beach:
The On Open event gives the error 2645 after the table has opened.(I only open the table to verify the info was available. This will be removed after working properly)

Generally, when reporting errors it's helpful to give the line number and particularly the error message. In this case I guess it was for your posted line #2, and I have already explained that this cannot possibly work.

Rick Beach:
With the above not working I attempted the below:

The property "Default Value" is where the =[Tables].[FacilitiesQueries_MaxChsrNumber].[NewCHSRNumber] was placed.

This resulted in the "#NAME?" to be displayed in the text box.

As above. This won't work.

Rick Beach:
Both of these methods work when utilizing an Access table or an Access query that retrieves info from an Access table, but do not work with the sql linked table or a query that utilizes the sql table as a reference.

I have no idea why you believe this. I know that the reference cannot possibly work as the start-point ([Tables]) doesn't match any available object, but I tested anyway (sometimes I get surprised and find my assumptions are wrong), using a single local database (nothing linked anywhere - all local) and wasn't surprised to see that it returned #NAME just as yours did.

Rick Beach:
I am sure you will ask if the sql view is updateable. It is but it should not matter as I only use this to retrieve the number for the default info and is not being written to the sql view.

Don't be too sure. Your reasoning is perfectly reliable, and you can assume I know that.

Rick, before we go further, why don't you try out my suggestions and see if they take you anywhere, rather than trying to explain why your code should really be working in spite of all I say.

[Rick Beach]

Using the same sql view, I created a form with only the NewChsrNumber in a text box. I then could use the below:

DoCmd.OpenForm "NewChsrNumberForm", acNormal
Me.CHSRNumber = [Forms]![NewChsrNumberForm]![CHSRNumber]
DoCmd.Close acForm, "NewChsrNumberForm"

Everything works properly when utilizing the form.

[NeoPa]

One almost wonders why you bothered asking for help in the circumstances. At no point do you give any indication you were prepared to consider it.

Your solution will work obviously. It's not the most straightforward by any means, involving the opening of a screen-visible object as it does, but it certainly gives you the value, and if you're satisfied with such a solution then I can assume you're happy and find a thread I can be more help in.