Look up field in a table is not showing first row

[Leslie]

Table 1 is account numbers, table 2 is the NCM table and in Table 2 I have a
link to the account numbers in table 1 it lists 4 fields when you click on
the drop down row and places the information in the first field when you
click on the account. This has worked fine for all the time I've used
access. Recently it has suddenly lost the first field of data. The
field1-account no, field2-type, field-3-department, field4-product, are aprt
of a query as I use the account numbers for several departments and the query
to filter for just the accounts needed for this dataentry form. This is
making it very difficult to choose the correct account as fields 2 - 4 are to
verify that the person doing the dataentry has the correct account number. I
have 4 fields because we have a number of accounts for each product line.

This is not happening in just one database. I have checked to make sure the
first row is not set to 0" in the column widths but neither I nor another
access user can see anything wrong or any reason why this has suddenly
started happening.

Thank you
Leslie

 

[Jerry Whittle]

Please provide the SQL statement for the query.

Is this being done at the table level or on a form?

 

[Leslie]

It is being done at the table level.
The select statement on the table:
SELECT [QRY_NCM_AccountNumbers].[AccountNumber],
[QRY_NCM_AccountNumbers].[AcctType], [QRY_NCM_AccountNumbers].[ProductLine],
[QRY_NCM_AccountNumbers].[Title] FROM [QRY_NCM_AccountNumbers] ORDER BY
[AccountNumber];

The statment that creates the query:

SELECT TBL_AccountNo.ActNoID, TBL_AccountNo.ProductLine,
TBL_AccountNo.AccountNumber, TBL_AccountNo.Title, TBL_AccountNo.Description,
TBL_AccountNo.Notes, TBL_AccountNo.AcctType
FROM TBL_AccountNo
WHERE (((TBL_AccountNo.AcctType)="NCM"));

These are tables linked to a backend database but the links have been
updated/created on the frontend part of the database because that is where
the queries are.

 

[Jerry Whittle]

If you are talking about a lookup field at table level, you just ran into one
of many reason to not use them. Below is a great list of reasons to dump them.

http://www.mvps.org/access/lookupfields.htm

Further check out the second commandment here:

http://www.mvps.org/access/tencommandments.htm

Your best bet is to create relationships between your table in the
Relationships window (hopefully with Referential Integrity enabled) and
create lookups or subforms on your forms.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

It is being done at the table level.
The select statement on the table:
SELECT [QRY_NCM_AccountNumbers].[AccountNumber],
[QRY_NCM_AccountNumbers].[AcctType], [QRY_NCM_AccountNumbers].[ProductLine],
[QRY_NCM_AccountNumbers].[Title] FROM [QRY_NCM_AccountNumbers] ORDER BY
[AccountNumber];

The statment that creates the query:

SELECT TBL_AccountNo.ActNoID, TBL_AccountNo.ProductLine,
TBL_AccountNo.AccountNumber, TBL_AccountNo.Title, TBL_AccountNo.Description,
TBL_AccountNo.Notes, TBL_AccountNo.AcctType
FROM TBL_AccountNo
WHERE (((TBL_AccountNo.AcctType)="NCM"));

These are tables linked to a backend database but the links have been
updated/created on the frontend part of the database because that is where
the queries are.

Please provide the SQL statement for the query.

Is this being done at the table level or on a form?

 

[Leslie]

Thank you,

If you are talking about a lookup field at table level, you just ran into one
of many reason to not use them. Below is a great list of reasons to dump them.

http://www.mvps.org/access/lookupfields.htm

Further check out the second commandment here:

http://www.mvps.org/access/tencommandments.htm

Your best bet is to create relationships between your table in the
Relationships window (hopefully with Referential Integrity enabled) and
create lookups or subforms on your forms.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

It is being done at the table level.
The select statement on the table:
SELECT [QRY_NCM_AccountNumbers].[AccountNumber],
[QRY_NCM_AccountNumbers].[AcctType], [QRY_NCM_AccountNumbers].[ProductLine],
[QRY_NCM_AccountNumbers].[Title] FROM [QRY_NCM_AccountNumbers] ORDER BY
[AccountNumber];

The statment that creates the query:

SELECT TBL_AccountNo.ActNoID, TBL_AccountNo.ProductLine,
TBL_AccountNo.AccountNumber, TBL_AccountNo.Title, TBL_AccountNo.Description,
TBL_AccountNo.Notes, TBL_AccountNo.AcctType
FROM TBL_AccountNo
WHERE (((TBL_AccountNo.AcctType)="NCM"));

These are tables linked to a backend database but the links have been
updated/created on the frontend part of the database because that is where
the queries are.

Please provide the SQL statement for the query.

Is this being done at the table level or on a form?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Table 1 is account numbers, table 2 is the NCM table and in Table 2 I have a
link to the account numbers in table 1 it lists 4 fields when you click on
the drop down row and places the information in the first field when you
click on the account. This has worked fine for all the time I've used
access. Recently it has suddenly lost the first field of data. The
field1-account no, field2-type, field-3-department, field4-product, are aprt
of a query as I use the account numbers for several departments and the query
to filter for just the accounts needed for this dataentry form. This is
making it very difficult to choose the correct account as fields 2 - 4 are to
verify that the person doing the dataentry has the correct account number. I
have 4 fields because we have a number of accounts for each product line.

This is not happening in just one database. I have checked to make sure the
first row is not set to 0" in the column widths but neither I nor another
access user can see anything wrong or any reason why this has suddenly
started happening.

Thank you
Leslie