input form from query

[Nixx1401]

Hi there,



I have a parameter query which prompts for the date parameter and then the query filters the data before it is run. I added 2 more fields to one of the tables and added these two fields to the query and created a form from the query hoping to enter data in the 2 new fields but the fields appear but I am unable to enter data in the two fields. One of the fields is a date field and the other is a yes or no field. When I attempt to enter data in these fields it just keeps a noise. Can someone assist me or guide me where I am going wrong.

[RuralGuy]

Show us the final SQL for your query, please.

[Nixx1401]

Below is the sql for the query

SELECT tblTransaction.DteTransDate, tblTransaction.StrMailType, tblTransaction.StrProcess, [StrFirstName] & " " & [StrLastName] AS [Inmate Name], tblContactInfo.StrRelationship, [StrFName] & " " & [StrLName] AS [Contact Name], tblInmateInfo.strCategory
FROM tblInmateInfo INNER JOIN (tblContactInfo INNER JOIN tblTransaction ON tblContactInfo.StrContactNo = tblTransaction.StrContactNo) ON (tblInmateInfo.StrInmateNo = tblContactInfo.StrInmateNo) AND (tblInmateInfo.StrInmateNo = tblTransaction.StrInmateNo)
WHERE (((tblTransaction.DteTransDate) Between [Enter Beginning Date (example mm/dd/yyyy)] And [Enter Ending Date (example mm/dd/yyyy)]) AND ((tblTransaction.StrMailType)="Visit") AND ((tblTransaction.StrProcess)="Out"));

[RuralGuy]

Are any of the fields in this query updateable? Maybe this link applies: http://allenbrowne.com/ser-61.html

[rpeare]

You're not going to be able to make this sort of query updatable unless you're linking on PrimaryKey Fields, plus you are linking 3 tables to each other which is going to make it impossible to have an updatable query. I'm assuming you want to update/add records to your tblTransaction table but be able to put in your StrInmateNo and have it look up information in both the tblInmateInfo and tblContactInfo tables? If so you'll have to to make sure your strInmateNo is the PRIMARY KEY of the tblInmateInfo table and your strContactNo is the PRIMARY KEY of the tblContactInfo table and use this query:

SELECT tblTransaction.DteTransDate, tblTransaction.StrMailType, tblTransaction.StrProcess, [StrFirstName] & " " & [StrLastName] AS [Inmate Name], tblContactInfo.StrRelationship, [StrFName] & " " & [StrLName] AS [Contact Name], tblTransaction.StrInmateNo, tblTransaction.StrContactNo, tblInmateInfo.strCategory, [strlname] & ", " & [strfname] AS ContactName
FROM (tblTransaction LEFT JOIN tblContactInfo ON tblTransaction.StrContactNo = tblContactInfo.strContactNo) LEFT JOIN tblInmateInfo ON tblTransaction.StrInmateNo = tblInmateInfo.strInmateNo
WHERE (((tblTransaction.DteTransDate) Between [Enter Beginning Date (example mm/dd/yyyy)] And [Enter Ending Date (example mm/dd/yyyy)]) AND ((tblTransaction.StrMailType)="Visit") AND ((tblTransaction.StrProcess)="Out"));

[Nixx1401]

Thank you all for your assistance. You have been very helpful. I understand what the link from allen is indicating and what rpeare is saying. Ref rpeare in my tblInmateInfo - strInmateNo is the PK and in tblContactInfo - strContactNo is the pk also. I tried the code you provided but that will not work to do what I want to do. Basically the query filters the data for all visits sent out to inmates contacts and provides a list of all the visits sent containing the Inmate Name, Contact Name, etc and allow for appointment date and if the visitor visited indicated. How can this be accomodated any suggestions other than how I attempted it.

[rpeare]

I do not understand what you wrote about your needs. Perhaps you can give an example of what the data looks like and what you want your output to be.