Results 1 to 12 of 12
  1. #1
    tb1150 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Location
    Canterbury
    Posts
    16

    Join query is prompting for parameter?

    Hi all,


    No idea why this is happening, but i have 2 queries, qry_EmailResolver_***ORGANISATION NAME*** and qry_EmailResolver_External. Both generate different results using the same format, and I have joined them together with the UNION query qry_EmailResolver_All. Both the first 2 queries run perfectly, without prompting any values and generating the expected results. However, when I run qry_EmailResolver_All, I get a prompt for qry_EmailResolver_All.E-mail for some reason, which is unexpected.
    The union query is as such:
    Code:
    SELECT [tbl_*StudentProfile].[Student ID], [tbl_*StudentProfile].Forename, [tbl_*StudentProfile].Surname
    FROM [tbl_*StudentProfile] INNER JOIN tbl_AuditImport ON [tbl_*StudentProfile].[***ORGANISATION NAME*** Email] = tbl_AuditImport.[E-mail]
    UNION 
    SELECT [tbl_*StudentProfile].[Student ID], [tbl_*StudentProfile].Forename, [tbl_*StudentProfile].Surname
    FROM [tbl_*StudentProfile] RIGHT JOIN tbl_AuditImport ON [tbl_*StudentProfile].[External Email] = tbl_AuditImport.[E-mail];
    Any help much appreciated.

    EDIT:: My field name is not actually ***ORGANISATION NAME***, I just replaced the name of my organisation with that in the post.

  2. #2
    Ajax is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,233
    if each query runs individually then I suspect it is something to do with all those asterisks you are using. Whether or not it is the issue, using non alpha numeric characters in table and field names is generally a bad idea. If you are trying to save yourself a few seconds by calling a field what you want to see on a form, use the caption property. Try removing the asterisks and see if it solves the problem

  3. #3
    tb1150 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Location
    Canterbury
    Posts
    16
    Thank you for your reply. That would be a problem, but that is not the actual field name, I just didn't want to post the name of my organisation in a public forum

  4. #4
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Is it asking you for a parameter for some field name? You may have something named similarly to your fields in some other location. This has happened to me before and I had to use database documenter to find the issue

  5. #5
    Ajax is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,233
    what about tbl_*StudentProfile?

  6. #6
    tb1150 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Location
    Canterbury
    Posts
    16
    Oh yeah I forgot those had asterisks, well spoted but this has never caused problems before and it's not something I can change due to the scale of what I'm working with

  7. #7
    tb1150 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Location
    Canterbury
    Posts
    16
    Click image for larger version. 

Name:	prompt.png 
Views:	6 
Size:	4.1 KB 
ID:	21051
    Thank you for your reply. This is what it asks me

  8. #8
    tb1150 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Location
    Canterbury
    Posts
    16
    Quote Originally Posted by nick404 View Post
    Is it asking you for a parameter for some field name? You may have something named similarly to your fields in some other location. This has happened to me before and I had to use database documenter to find the issue
    Click image for larger version. 

Name:	prompt.png 
Views:	6 
Size:	4.1 KB 
ID:	21051
    Thanks for your reply. This is what it asks me.

  9. #9
    tb1150 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Location
    Canterbury
    Posts
    16
    This is also why it is so confusing as it is a union between two working values it shouldn't ask for any values at all

  10. #10
    tb1150 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Location
    Canterbury
    Posts
    16
    Weird. I C+V'd the SQL from my join query and the ones on the next level up into new queries with similar names and now its working fine. I just don't know sometimes...

  11. #11
    Ajax is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,233
    as I'm sure you realise the problem appears to be with tbl_AuditImport.[E-mail]

    Can only suggest you start again and build and test your queries from scratch - perhaps your current one has been corrupted.

  12. #12
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Before re-making your db I suggest giving database documenter a shot. If you know what you are looking for it takes a mere 15-20 minutes to scroll through and possibly find the problem.
    1. In Database Tools select Database Documenter.
    2. Click All Objects tab in the pop-up and select all the objects that are tied to your query (tbls, frms, rpts, etc.).
    3. Click Ok. It takes you to a print preview page.
    4. On the right side in the Data section select either Word or Text File (I prefer text file).
    5. Check the box that says "open the destination file..." and click ok.
    You will basically be looking at a text file of your database. You can peruse it and search for where you may have an extra Requery statement.

    Just scroll through all the text and look for anywhere you see qry_EmailResolver_All.E-mail. If you see this in an unfamiliar or unwanted location in the text file, go back to your db and remove it. Happened to me a few times where a random name for something in a report was the same as something in my vba and it kept doing the parameter check. Used documenter and found it.

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

Similar Threads

  1. Prompting for user input in query
    By sherrygroves in forum Access
    Replies: 7
    Last Post: 08-31-2014, 09:32 PM
  2. Replies: 3
    Last Post: 12-28-2013, 09:13 AM
  3. parameter prompting multiple times
    By sandyg in forum Access
    Replies: 1
    Last Post: 08-15-2011, 10:10 AM
  4. Crosstab Query Prompting twice for data?
    By AccessFreak in forum Queries
    Replies: 5
    Last Post: 01-07-2011, 10:38 AM
  5. Query prompting for values?? Why?
    By Schon731 in forum Queries
    Replies: 2
    Last Post: 12-23-2010, 01:19 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