Strange value added to table

[Piri]

Access 2007
I have a combo box based on a query (design) on a Locations table:
Three fields in the table - City, State, Country. All three are called
by the query as the RowSource of the CBox.
The SQL view of the query is:
SELECT tblLocations.City, tblLocations.STATE, tblLocations.Country
FROM tblLocations
ORDER BY tblLocations.City, tblLocations.STATE, tblLocations.Country;

When I add the record to a data table, instead of writing say, "New
York" as the city selected into the table it writes what appears to be
the line (Record) number of the table "23305". There is no reference
to any Record Number in the table or the underlying query that I can
find. I need the expression "New York" saved in the table.

Any help appreciated.
Piri

 

[John W. Vinson]

Access 2007
I have a combo box based on a query (design) on a Locations table:
Three fields in the table - City, State, Country. All three are called
by the query as the RowSource of the CBox.
The SQL view of the query is:
SELECT tblLocations.City, tblLocations.STATE, tblLocations.Country
FROM tblLocations
ORDER BY tblLocations.City, tblLocations.STATE, tblLocations.Country;

When I add the record to a data table, instead of writing say, "New
York" as the city selected into the table it writes what appears to be
the line (Record) number of the table "23305". There is no reference
to any Record Number in the table or the underlying query that I can
find. I need the expression "New York" saved in the table.

Any help appreciated.
Piri

It wuld appear that you are yet another victim of Microsoft's midesigned,
misleading, irksome "Lookup Wizard".

My guess is that the fields in the Locations table are Lookup Fields, meaning
that the actual field content is a number, a foreign key to the Lookup Table
(of Cities). The actual contents of the field are concealed from your view by
the Lookup feature - what you see is the text from the table of Cities.

Is that the case? Open tblLocations in design view; what is the Lookup
property of the City field?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Piri]

It wuld appear that you are yet another victim of Microsoft's midesigned,
misleading, irksome "Lookup Wizard".

My guess is that the fields in the Locations table are Lookup Fields, meaning
that the actual field content is a number, a foreign key to the Lookup Table
(of Cities). The actual contents of the field are concealed from your view by
the Lookup feature - what you see is the text from the table of Cities.

Is that the case? Open tblLocations in design view; what is the Lookup
property of the City field?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hello John,
The plot thickens.
When I save the Form data to the data table tblClientInfo the city
name is saved as "23304" (not 23305) which is actually one number less
than the actual record number in the tblLocations with the
tblLocations alpha sorted acsending by city name.

If I sort the city field in desc order (via the query in form design
view) the city field changes to show a new city (in India) which
reflects its new record number returned by the query / combo box.
When I reselect New York in the CBox and rerun the form save, the data
saved for the city New York is 13851 which is one less than the actual
record number according to the new desc sort order.
I presume the table record numbers start with zero?

In tblLocations Design View the table field Lookup properties for
"City" shows Display Control Text Box?
I actually created the tblLocations from another table using a
MakeTable query.
I have copied all records from tblLocations to a spreadsheet, deleted
the current tblLocations and imported the data afresh into a new
tblLocations - deleted the old underlying query and recreated it,
saved the form - still getting same result. It's got me puzzled. I
suspect something simple staring me in the face!

Cheers and thanks for your input,
Piri

 

[John W. Vinson]

Hello John,
The plot thickens.
When I save the Form data to the data table tblClientInfo the city
name is saved as "23304" (not 23305) which is actually one number less
than the actual record number in the tblLocations with the
tblLocations alpha sorted acsending by city name.

If I sort the city field in desc order (via the query in form design
view) the city field changes to show a new city (in India) which
reflects its new record number returned by the query / combo box.
When I reselect New York in the CBox and rerun the form save, the data
saved for the city New York is 13851 which is one less than the actual
record number according to the new desc sort order.
I presume the table record numbers start with zero?

In tblLocations Design View the table field Lookup properties for
"City" shows Display Control Text Box?
I actually created the tblLocations from another table using a
MakeTable query.
I have copied all records from tblLocations to a spreadsheet, deleted
the current tblLocations and imported the data afresh into a new
tblLocations - deleted the old underlying query and recreated it,
saved the form - still getting same result. It's got me puzzled. I
suspect something simple staring me in the face!

Cheers and thanks for your input,
Piri

Well, without being able to see the actual contents of tblLocations and the
RowSources of your combo boxes, I'm perplexed. I'm absolutely and positively
certain that storing "New York" into a Text field will not under any
circumstances have the field set to 13851, though.

Do you have any Lookup Fields in any of these tables? Could you post the SQL
of the MakeTable query, and the datatypes and origins of the fields in its
source table? For one thing, Access tables do not HAVE "Record numbers"; a
table may have an Autonumber field which is sorta-kinda like a record number,
if you accept that a record number might have gaps and not be sequential.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Mike Painter]

Access 2007
I have a combo box based on a query (design) on a Locations table:
Three fields in the table - City, State, Country. All three are called
by the query as the RowSource of the CBox.
The SQL view of the query is:
SELECT tblLocations.City, tblLocations.STATE, tblLocations.Country
FROM tblLocations
ORDER BY tblLocations.City, tblLocations.STATE, tblLocations.Country;

When I add the record to a data table, instead of writing say, "New
York" as the city selected into the table it writes what appears to be
the line (Record) number of the table "23305". There is no reference
to any Record Number in the table or the underlying query that I can
find. I need the expression "New York" saved in the table.

Any help appreciated.
Piri

Your form should be based on a query and the record key 23305 is what should
be written to the main table.

Your query would relate tblLocations with the main table.
MainForm.LocationID. tblLocations.City, etc would be on the query
Storing the name may not be a particular problem here but in most cases it
will require unneeded code and cause problems down the road.

If that was an employee for example storing their last name would have to be
done in all the tables that the employee's last name is needed.
If it is a woman and she gets married you have to find and change all those
references.
Properly related, you only make one change.