#Name? error in form field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to get the default value for a field to be generated by a query.

The query uses the value "CustID" from an open form (CustDataForm) which is based on the "CustDataTable", and then finds the max value for "ItemID" in the "ItemDataTable" where the CustID's are equal... i.e. it returns the correct value for the max item number for the customer I'm currently working with.

The query works fine when I run it directly... but when I try to create a new "Item" record in the ItemDataTable using the ItemDataForm, the field for ItemID says #Name?...

As I enter data, it sets the field to zero rather than 2 (if there's already a record associated with that customer) or 1 (if it's the first item for that customer). Basically I just want the form to start counting from 1 and work it's way up (for each customer) without the autonumber function (so that the records are sequential). Any suggestions how to solve the #Name? problem?? thanks
 
I'm not sure where you're trying to invoke this query, but please recall
that a query returns a recordset, not a value - to Access these are quite
different things, even if the recordset contains only a single record with a
single field.

Perhaps you'd find the DMax() function useful.
Look for it in the Access Help File.

HTH
- Turtle

Scott said:
I am trying to get the default value for a field to be generated by a query.

The query uses the value "CustID" from an open form (CustDataForm) which
Click to expand...
is based on the "CustDataTable", and then finds the max value for "ItemID"
in the "ItemDataTable" where the CustID's are equal... i.e. it returns the
correct value for the max item number for the customer I'm currently working
with.
The query works fine when I run it directly... but when I try to create a
Click to expand...
new "Item" record in the ItemDataTable using the ItemDataForm, the field for
ItemID says #Name?...
As I enter data, it sets the field to zero rather than 2 (if there's
Click to expand...
already a record associated with that customer) or 1 (if it's the first item
for that customer). Basically I just want the form to start counting from 1
and work it's way up (for each customer) without the autonumber function (so
that the records are sequential). Any suggestions how to solve the #Name?
problem?? thanks
 
Hi Scott -

This is Steven from Microsoft Access Technical Support replying to your
newsgroup post.

For generating your own counter so not to make use of an AutoNumber type,
you may want to review the following documentation:

ACC2000: How to Create a Multiuser Custom Counter
http://support.microsoft.com/default.aspx?scid=KB;EN-US;210194

A function is used to retrieve the next sequential number. The number
retrieved can be used as the default value for your field.

Please let me know if this solves your problem or if you would like further
assistance. I look forward to hearing from you.

Sincerely,
Steven Parsons [MSFT]
Microsoft Access Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! (http://www.microsoft.com/security)
 
Back
Top