Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282

    Cascading Combo Boxes

    A while ago I posted some queries on here about how to do different things for a database, I've since had to perform other tasks so this dropped down the priority list, but now I have a bit of free time it has come back again.



    I have a problem with my cascading combo boxes, which I was told how to do on the following thread: https://www.accessforums.net/showthread.php?t=58037.

    When attempting to insert information on the "AddAssessor" form, all is fine until I click the "Add" button, where the following box appears:
    Click image for larger version. 

Name:	DistrictError.PNG 
Views:	37 
Size:	5.5 KB 
ID:	25584 I have made sure that there was information entered into the indicated field at the time. Once this box appears, I am then unable to close the form or proceed without losing the information I entered.
    Click image for larger version. 

Name:	CloseFormError.PNG 
Views:	37 
Size:	22.9 KB 
ID:	25585
    I have included the database I am using, to assist your diagnosis.

    Your assistance in this matter is much appreciated.
    Attached Files Attached Files

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I did not look at your DB. However, it seems you are trying to use a query that depends on a form and a control where the Form and or Control is not available. Maybe the Form is not open or the control name does not exist.

    The other issue is you are trying to edit a record to a table that has a constraint defined and this constraint/rule is not met. Perhaps you defined a relationship in the Relationships Window of Access.

  3. #3
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Great, thanks - I think I've fixed the first query - and it may have fixed the second as well. I now have another similar error which could be linked to the second... I'm a little unsure as you've probably worked out by now!

    When I click the "Add Assessor" button, it takes me to the "Assessor Records" page, pulling through all the data from the previous page and then closes the previous page. This however then seems to update the "Caseholding District" field which then is wiped clear and does the same to the "Caseholding Team" field. It then asks me to enter the District ID from the previous page.
    Click image for larger version. 

Name:	AssessorRecords from AddAssessor.PNG 
Views:	29 
Size:	5.2 KB 
ID:	25628
    I have included the code which I believe is causing it and the pop-up box that stops me from progressing to assist you with helping me.
    Click image for larger version. 

Name:	UpdateDistrictAssessorRecords.PNG 
Views:	29 
Size:	2.7 KB 
ID:	25629
    Attached Files Attached Files

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Taking the prompt that Access provided to you at face value, the Control in your AddAssesor form is not available to a Query or contains a Null value that cannot be associated with the data type defined in the table for the column named District.

    Make sure the form is open and the Control name and or Field is spelled correctly. Make sure there is a valid value associated with that Field, meaning the space defined in that specific record/row/control.

    Ultimately, you may need to use another query for this subsequent operation that is performed by your user. A query that is not dependent on parameters defined by this form/field.

  5. #5
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Actually I think I may have found the problem - the field in AssessorRecords didn't have a source, so if someone tried to find an assessor that had already been added, they'd have encountered the same problem. Now I've added a source however, it won't let me change it - for instance if the Assessor changed team and/or district, they are not able to select anything from the dropdown, any ideas?
    Attached Files Attached Files

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    You can reference an unbound control in your Query. If the control is not bound, the User will need to enter a value into the control.

    If you have a bound control, there may be data in the table that the query can rely on. Also, the User can interact with the control and edit/update the value.

    Simply binding a control to see if that helps is not advisable. You want to pass very specific data to your query. Data validation is a big part of defining criteria for a query.

  7. #7
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I want the "Assessor Records" form to have the Assessor's information in the tblAssessor, which can then be edited as mentioned before, I wasn't just applying controls as and when I felt like it - I thought that was probably the reason as otherwise there's nothing for it to use or edit. There's probably a section in the property sheet I've missed, but I just can't spot it.

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I was not trying to send the message that you are adding controls willy nilly. I was trying to illustrate that you cannot address the query issue by simply causing an unbound control to be bound. And I would continue by saying that I do not believe the issue lies in the property settings of a control.

    In order for your query to work it will need a valid value for the conditions you defined. The query is looking for an argument to be provided for its parameters. I would stay focused on data validation.

  9. #9
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    OK, good.

    I'm not sure that's the problem anymore, as I realised that if the user found the assessor by searching for them on the Homepage, the district and team would not then populate as there was nowhere for it to get the information from, which is why I went down the path I did - previously it was just trying to pull the information directly from the AddAssessor form, which would not be opened by going through this route.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Heathey94 View Post
    - previously it was just trying to pull the information directly from the AddAssessor form, which would not be opened by going through this route.
    I see this as the likely culprit. Are you satisfied this is this issue and do you understand how to address it?

  11. #11
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I thought I was addressing it when trying to bound it, but it doesn't seem to have worked.

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Heathey94 View Post
    I thought I was addressing it when trying to bound it, but it doesn't seem to have worked.
    And this is why I made the comment I did in post #6.

  13. #13
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    "If you have a bound control, there may be data in the table that the query can rely on. Also, the User can interact with the control and edit/update the value."
    In said comment, you state that the query can rely on something in the table, which I believe is what I want. If I'm doing something majorly wrong, I think you may need to give me a bit more of a push in the right direction...

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I took a quick look at your DB. What you likely have is a corrupt Query Object. I am not sure which one. It is likely "qryAssesor". Having said that, it may also be a corrupt form, AddAssessor.

    If I had to take a guess, you had something open in design view with the debugger running. Then you made and saved a design change like changing the name of a form or changing the WHERE clause in your query.

    These things are hard to nail down. I cannot even be certain it is corruption. But, I can't see why you would get that message. I am not understanding why it is looking for a field or control named District. So that leaves corruption.

    Before rebuilding stuff, make a copy of you DB!

    You can rebuild queries really easily. Just copy the SQL, while in SQL view, and paste it into a text file. If you have several Query objects that depend on each other, copy the SQL of each one in preparation for deleting those queries. After deleting the queries, do a compact and repair. Then rebuild the queries.

    Forms are a little more involved. It is almost always necessary to rebuild the entire form. You can copy code from within a sub procedure and save it to a text file. But, you want Access to create the start and finish of any procedure in the new form (paste the guts of the sub procedure into the middle of the new one).

    If you think it is easier to copy controls from a suspect form into a new one, understand it is possible to copy the corruption and paste the corruption into your new form. In other words, Controls can become corrupt too.

  15. #15
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Yes, that may be true... although it just happened again on another form when all I did was remove some tables from a query, I definitely didn't have the debugger running this time. It's on a separate thread: https://www.accessforums.net/showthread.php?t=61740
    I'll give it a try tomorrow, I got a bit overrun with over stuff today.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cascading Combo Boxes
    By JCW in forum Programming
    Replies: 4
    Last Post: 04-02-2014, 05:05 PM
  2. Cascading Combo boxes
    By finsmith in forum Forms
    Replies: 10
    Last Post: 02-12-2013, 09:37 AM
  3. Sum of Cascading Combo Boxes
    By alonewolf23 in forum Forms
    Replies: 2
    Last Post: 11-20-2011, 02:10 PM
  4. Cascading combo boxes
    By combine21 in forum Forms
    Replies: 3
    Last Post: 12-02-2010, 12:57 PM
  5. Cascading Combo Boxes
    By gjw1012 in forum Access
    Replies: 1
    Last Post: 07-25-2009, 04:59 PM

Tags for this Thread

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