Results 1 to 12 of 12
  1. #1
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80

    Enter parameter value (subform combo)

    I ahbve the following query:
    Code:
     
    INSERT INTO tblFacultyEvent ( FacultyID, EventID, EventStartDate, EventEndDate )
    VALUES (Forms![sfrmFacultyEvent]!cmboFaculty, [Forms]![frmEvent]![EventID], [Forms]![frmEvent].[EventStartDay], [Forms]![frmEvent]![EventEndDate]);
    That is supposed to enter the FacultyID from the cmboFaculty into the FacultyEvent table but it asks for the parameter of the combobox. I have checked the names of the combobox and subform are correct and I can only assume its something to do with it being on a subform and my syntax is wrong. If I take that part out of the query it runs OK or if I enter a value into the box that pops up it runs Ok and saves the data where I want. This is really bugging me as i know it's a simple mistake but i can't see it. Can anyone help please.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So cmboFaculty is the only one that is on subform? Yes, referencing subform controls gets tricky because have to go through the subform container. Make sure the container name is different from the form it holds, like ctrFacultyEvent. The combobox is bound to FacultyID?

    I presume this code is behind [frmEvent], try this:
    (Me.ctrFacultyEvent!FacultyID, Me![EventID], Me![EventStartDay], Me![EventEndDate]);

    or
    (Me.ctrFacultyEvent.Form.cmboFaculty, Me.EventID, Me.EventStartDay, Me.EventEndDate);

    The dot will provoke intellisense popup tips. Are the control names same as field names? I use the dot when referencing forms/controls/properties and ! when referencing field names.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    The combofaculty is yes the only one on the subform. The subform is called sfrmFacultyEvent and the main form is frmEvent so I think I'm refering to it right.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Look at my post again (did some edits). You have to refer to the subform CONTAINER CONTROL. At least, that is the way I make this work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    The query runs on the afterupdate of the combobox not on the main form and as i say the rect works fine even though it is referencing the main form it is just the actual combobox that I've got the issue refering to. Intellisense won't make a difference in this because its composed in the query design not in code. I have a DoCmd Openquery in the code behind.

  6. #6
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Sorry our posts are getting crossed. When you say ctr is that instead of sfrm and to rename the subform as ctrFacultyEvent.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, it is to name the container control that holds the subform sfrmFacultyEvent.

    I was seeing that query as VBA code, okay it is in the query grid, then the Me and dot and intellisense certainly don't apply.

    Don't think I've ever referred to a subform control in a query, just in VBA. Breaking new ground here. This worked:
    Forms!frmEvent!ctrFacultyEvent.Form!cmboFaculty
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    I'm sorry I don't know what you mean by a container The subform is on the main form, it is designed to look like a normal input field for the user for the reason that I needed something that would also store the choices in a joining table so that when the user returned to the eventform the facultys who had already been scheduled for that event were shown. I don't know where i would find the container properties or even where it is on the form.

  9. #9
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    I've worked out what you mean by the container I think and have changed the subform name in the properties so it now has Name: ctrFacultyEvent and source object sfrmFacultyEvent the query runs but now it doesn't update the table with the FacultyID although the rest of the query works. I now have:

    Code:
     
    INSERT INTO tblFacultyEvent ( FacultyID, EventID, EventStartDate, EventEndDate )
    VALUES (Forms!frmEvent!ctrFacultyEvent.Form!cmboFaculty, [Forms]![frmEvent]![EventID], [Forms]![frmEvent].[EventStartDay], [Forms]![frmEvent]![EventEndDate]);
    I'm thinking maybe I need to change it to

    Code:
    INSERT INTO tblFacultyEvent ( FacultyID, EventID, EventStartDate, EventEndDate )
    VALUES (Forms!frmEvent!ctrFacultyEvent.sfrmFacultyEvent!cmboFaculty, [Forms]![frmEvent]![EventID], [Forms]![frmEvent].[EventStartDay], [Forms]![frmEvent]![EventEndDate]);
    But I'm not sure that will work either as the Query runs from the subform not the main form.

  10. #10
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    As suspected I tried that but it asks for the parameter value again and then when i just select Ok it says it can't find the referenced form.

  11. #11
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    I have now changed it to:
    Code:
     
    INSERT INTO tblFacultyEvent ( FacultyID, EventID, EventStartDate, EventEndDate )
    VALUES (Forms![frmEvent]![ctrFacultyEvent].[Forms]![cmboFaculty], [Forms]![frmEvent]![EventID], [Forms]![frmEvent].[EventStartDay], [Forms]![frmEvent]![EventEndDate]);
    But this still asks me for the parameter.

  12. #12
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Solved it now for anyone following this the syntax was:
    INSERT INTO tblFacultyEvent ( FacultyID, EventID, EventStartDate, EventEndDate )
    VALUES (Forms![frmEvent]![ctrFacultyEvent].Form![cmboFacultyEvent], [Forms]![frmEvent]![EventID], [Forms]![frmEvent].[EventStartDay], [Forms]![frmEvent]![EventEndDate]);

    AS you see all along I had the wrong name for the cmbobox. I am an idiot.

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

Similar Threads

  1. enter parameter value
    By alliandrina in forum Access
    Replies: 1
    Last Post: 06-05-2011, 01:02 AM
  2. HELP NEEDED! Enter Parameter Value
    By lpfluger in forum Queries
    Replies: 4
    Last Post: 03-27-2011, 04:38 PM
  3. Enter Parameter Value
    By T001 in forum Access
    Replies: 1
    Last Post: 08-11-2010, 05:43 AM
  4. Enter Parameter Value
    By gutes2 in forum Forms
    Replies: 3
    Last Post: 03-03-2010, 05:03 PM
  5. Enter Parameter Value
    By plesser in forum Access
    Replies: 1
    Last Post: 11-08-2008, 10:27 AM

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