Re: Query form coding

Jay,

I've been trying to understand how things are linked. Here goes......

So you have a form (name unknown to me) with two list boxes (namelst and sourcelst), an option group (statusfra), a button (filter_cmd) and a report (trkng_rpt). There is an unbound text box on the form that is for ????

----
The record source for the list box "namelst" is:

"SELECT NAME_qry.NAME from Name_qry ORDER BY [NAME];" which is based on a query (NAME_qry) which concatenates the last name, first name and middle initial from table "Personnel_tbl".


I would have the query "NAME_qry" be

"SELECT [Last Name] & "," & [First Name] & ","
[Middle Initial] AS NAME from Name_qry ORDER BY [NAME];"

then set the list box record source to

"NAME_qry"


----
The record source for the list box "sourcelst" is:

(I don't know) **but seems to give the desired records**

----
The option frame works.

----
The report record source seems to give the desired records.

----
The code for the button (create the filter) works if no names have been selected in the names list box.

If names are selected in the name list box and sources are selected in the source list box and the Status selected is "IS NULL", then he filter generated looks something like:

"[Name] IN (a list of name) AND [Source] IN (a list of sources) AND [Status] IS NULL"

----

The report record source (reformatted for ease of reading) is:

SELECT
Tracking_tbl.[DAC],
Tracking_tbl.Source,
Tracking_tbl.ReqReceived,
Tracking_tbl.EntACAT,
Tracking_tbl.EntBy,
Tracking_tbl.InsSent,
Tracking_tbl.SentBy,
Tracking_tbl.FormRet,
Tracking_tbl.RetTo,
Tracking_tbl.ReqFor,
Tracking_tbl.ForBy,
Tracking_tbl.PermFor,
Tracking_tbl.ForBy,
Tracking_tbl.DtgRec,
Tracking_tbl.RecBy,
Tracking_tbl.Status,
Tracking_tbl.CloBy,
Tracking_tbl.ACATUp,
Tracking_tbl.UpBy,
Tracking_tbl.Comments,
Tracking_tbl.Record AS Tracking_tbl_Record,
Personnel_tbl.[Last Name],
Personnel_tbl.[First Name],
Personnel_tbl.[Middle Initial],
Personnel_tbl.Record AS Personnel_tbl_Record


FROM Personnel_tbl INNER JOIN Tracking_tbl ON
Personnel_tbl.ACATrec=Tracking_tbl.ACTrec;

----



Looking at the report record source and the filter string, I see the fields for
[Source] and [Status] in each.

But the filter also has a [Name] field, which is not found in the record source. Not only do you not nave a field [Name] in the report record source, you don't *any* field where the first name, last name and MI are concatenated!!





(Question: What are you going to do when there are 4 John O. Smiths??? Or three James J. Browns?? Don't you have a unique Personnel_ID number or even an autonumber for the table?)




To sort the report, you use "Sorting and Grouping" from the VIEW menu when in the report design view, not the report query.


--Repeat warning.... RECORD and NAME are Access reserved words--


Does this make sense??


Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Jay wrote:
Steve,

Me again. I have been busy with other things but want to get this kicked in the rear. The table is one to many. One personnel will have many "tracking records." The personnel_tbl.record is the key for the personnel table and the tracking_tbl.record is the key for the tracking table. Not sure if this is the best way to do it but I got it to work like that. All of the tracking records show for each personnel record selected.

The SQL for the query is Name: [Last Name] & "," & [First Name] & "," [Middle Initial]

I have tried to include the query when using the wizard to design the report but I keep getting an error saying I have used a table or query that is based on a table or query (something like that) and when I remove the query fields from the list it allow me to continue with the report design. So I decided use the name fields that the query is based on but only hide them (once I get the report to work) and make a unbound text field that duplicates the query results (combines the names). I am not sure if trying to have a form query an unbound text field in a report is possible. Thanks for all of the time and help you have given to me.

Jay

"SteveS" wrote:


Jay,

What is the SQL of the query 'NAME_qry'?

Is the relationship between Personnel_tbl and Tracking_tbl a one-to-many
1 ----> many
Personnel_tbl Tracking_tbl

Personnel_tbl.Record is the primary key in Personnel_tbl and Tracking_tbl.Record is the foreign key to Personnel_tbl?


(BTW, 'Record' is also a reserved word in Access. 'Record_ID' or 'lngRecord' is a better name for the field)

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
.

Loading