Filling a bound text box control on a form with field value from a query

[Buster55]

I am building a database for our police dept. I have a tablecalled “Tbl_Officers”. In this table are fields:
RPT_OFFICER_DB_ID (autoNumber) (PK)
OFFICER_LAST_NAME (Text)
OFFICER_FIRST_NAME (Text)
OFFICER_ID (Text) Thisis the Identifier that the police dept.gives each officer. Ie. Bagde Number)
OFFICER_RANK (Number) (Corresponding to another table wherethe number is the PK for each rank)
LoggedIn (A yes/no field where only 1 officer will be yes aslogged in to the database).



I have form in the database called: “Frm_UCR_Admin”
This form is bound to a table named “Tbl_UCR_Administrative”

In this table are fields:
RPT_OFFICER_DB_ID (Number) The default for this control, ona new record has to come from the table [Tbl_Officers].[ RPT_OFFICER_DB_ID]where the LoggedIn field is “True”.
I have accomplished this by creating a select query:
Here is the sql view of the select query:

SELECT Tbl_Officers.RPT_OFFICER_DB_ID,Tbl_Officer_Rank.OFFICER_RANK_ID, Tbl_Officer_Rank.OFFICER_RANK,Tbl_Officers.RPT_OFFICER, Tbl_Officers.OFFICER_LAST_NAME,Tbl_Officers.OFFICER_FIRST_NAME, Tbl_Officers.OFFICER_ID,Tbl_Officers.OFFICER_PHONE_HOME, Tbl_Officers.OFFICER_PHONE_CELL,Tbl_Officers.OFFICER_START_DATE, Tbl_Officers.OFFICER_CURRENT,Tbl_Officers.OfficerLoginPW, Tbl_Officers.OfficerLoginPWconfirm,Tbl_Officers.EnteredPW, Tbl_Officers.LoggedIn, Tbl_Officers.Active
FROM Tbl_Officers INNER JOIN Tbl_Officer_Rank ONTbl_Officers.OFFICER_RANK = Tbl_Officer_Rank.OFFICER_RANK_ID
WHERE (((Tbl_Officers.LoggedIn)=Yes));

I have set the default property setting for that control as:

=DLookUp("[RPT_OFFICER_DB_ID]","Qry_Officer_LoggedIn")

This Works

Now I have to populate the other bound controls with theActual Rank, First Name, Last Name etc. from that same query. The difference isthat the other fields are text and no matter what I put in nothing seems towork for me.

I know that sounds like a bad idea and redundant data but itreally is the only way as these records must be saved as the Officers name andrank at the time of the report. The officer’s name or rank may change in thefuture but retrieving old records must show the name and rank at the time.

Here is one of the default property settings that I havetried for the form control [txtRPT_OFFICER_RANK]

=DLookUp("’OFFICER_RANK’","Qry_Officers_LoggedIn")

[pbaldy]

Have you tried without those extra quotes around the field?

[Buster55]

Yes I did. I tried that first and then I decided I needed the extra set of quotes because it was text. I just tried it again and just a blank control.

[pbaldy]

The extra quotes would be in the criteria if you had one. I notice the query name is spelled differently in the failing one.