Results 1 to 7 of 7
  1. #1
    Nixx1401 is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    Feb 2010
    Location
    Barbados
    Posts
    115

    input form from query

    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.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Show us the final SQL for your query, please.

  3. #3
    Nixx1401 is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    Feb 2010
    Location
    Barbados
    Posts
    115
    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"));

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Are any of the fields in this query updateable? Maybe this link applies: http://allenbrowne.com/ser-61.html

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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:

    Code:
    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"));

  6. #6
    Nixx1401 is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    Feb 2010
    Location
    Barbados
    Posts
    115
    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.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-18-2012, 07:31 AM
  2. Find duplicates query to populate input form
    By kctalent in forum Queries
    Replies: 6
    Last Post: 08-22-2011, 03:12 PM
  3. Run a query based on an input from a form
    By apoorv in forum Queries
    Replies: 4
    Last Post: 07-11-2011, 01:39 PM
  4. Replies: 2
    Last Post: 05-27-2011, 05:55 AM
  5. Replies: 10
    Last Post: 02-02-2011, 05:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums