Auto Complete issue

[lucindaaps]

After creating the lookup field and adding records, the field will auto
complete, based on the first few letters i type in. However, after I close
and re-open the database, the lookup will no longer autofill when i begin
typng a selection from the list. and if i type if the full lookup value,
access tells me that its not on the list when it is typed exactly as on the
list; if i then remove the lookup relationship and restore the relationship,
the autofill will again work...until the database is closed and
re-opened...i've never had a problem with lookups before in prior versions of
MS Access and can't see any issue with the sql code for the lookup: SELECT
[1a-EmbFormat-tbl_Formats].[FormatID], [1a-EmbFormat-tbl_Formats].[Format]
FROM [1a-EmbFormat-tbl_Formats] ORDER BY [Format]; Can you please advise as
to setting changes, properties changes needed, etc? thank you!

 

[Dirk Goldgar]

After creating the lookup field and adding records, the field will auto
complete, based on the first few letters i type in. However, after I close
and re-open the database, the lookup will no longer autofill when i begin
typng a selection from the list. and if i type if the full lookup value,
access tells me that its not on the list when it is typed exactly as on
the
list; if i then remove the lookup relationship and restore the
relationship,
the autofill will again work...until the database is closed and
re-opened...i've never had a problem with lookups before in prior versions
of
MS Access

What version of Access are you using?

and can't see any issue with the sql code for the lookup: SELECT
[1a-EmbFormat-tbl_Formats].[FormatID], [1a-EmbFormat-tbl_Formats].[Format]
FROM [1a-EmbFormat-tbl_Formats] ORDER BY [Format]; Can you please advise
as
to setting changes, properties changes needed, etc? thank you!

The only thing that occurs to me off the top of my head is that, because
Format is a reserved word, it would be safer to qualify the field name with
the table name even in the ORDER BY clause:

SELECT
[1a-EmbFormat-tbl_Formats].[FormatID],
[1a-EmbFormat-tbl_Formats].[Format]
FROM [1a-EmbFormat-tbl_Formats]
ORDER BY [1a-EmbFormat-tbl_Formats].[Format];

It would be better if the field name "Format" were something else
altogether, but please let me know if changing the RowSource SQL as above
makes any difference.