Question Auto-filled fields not populating table (1 Viewer)

[elbowman]

We had a work emergency today, so I'm not going to be able to work on it.

I'll get back with you, hopefully, tomorrow.

Thanks!

 

[elbowman]

Success!!!

Beetle is da' man!

 

[Beetle]

Glad it worked for you. Happy to help.

 

[elbowman]

Remember me, Beetle?!

Since I work for a school district things had stopped on this project you helped me with over the summer. Now, a new school year is approaching and my boss has thrown a new feature request at me.

Since I got myself in trouble with this template previously by changing existing fields and tables I thought I'ld ask you for tips on the best approach to take.

What the boss wants is for me to change the 'Priority' and 'Category' fields on the 'IssueNew' form to a cascading combo-box. Priority will be a combo with two possible selections, minor or major. Category will be a combo with different possible choices depending which Priority is selected. I've created two new tables called Priority and Category. I found a tip online from Albert Kallal describing how to setup cascading combo-box fields in a web-enabled template. The after-update macro code seems simple enough.

How do you recommend I proceed? The Priority and Category fields on the 'IssueNew' form that came with this Issues web-enabled template are value-lists, not table/queries. These fields are also in the 'Issues' table. They also carry over to the 'IssueDetail' form.

I'm not even sure how to ask the question properly. Do I delete the fields from the forms and tables and create new fields? Do I just modify the existing fields? When setting up the new tables do I delete the fields from the Issues table, or modify the existing fields?

There are underlying relationships and I don't want to break what you helped me to build so far.

Thanks in advance for any advice!

Lance

 

[Beetle]

Before we proceed I have some questions.

1) Do you have existing data in this app that you need to keep, or are you still in the testing phase with dummy data?

2) How did you set up the new Priorities and Categories tables?

To be more specific regarding question 2, In the Issues table you have these as Lookup fields with value lists like the following;

Priority
1 - High
2 - Normal
3 - Low

Category
1 - Violation of Safety
2 - Destruction of Property
etc.

where the above are just string values in a single column. Now, normally, when you are setting up separate tables for your combo boxes, you would have the integer portion and the string portion in separate columns where the integer column would be the PK, like this;

[B][U]tblPriority[/U][/B]
PriorityID      Description
1               High
2               Normal
3               Low

and the only value that would be stored in the related table (in this case the Issues table) would be PriorityID as a Foreign Key. Ditto for Categories.

So, if you can give me a few more details about your existing data (real or bogus) and how you set up (or intend to set up) the new tables, then we can go from there.

 

[elbowman]

First, we're still in the testing phase using dummy data.

Second, Priority and Category tables are setup like in your example with integer and string portions in separate columns. Priority is a two record table, with IDs 1 and 2 relating to strings, Minor and Major. Category is a four column table with ID, Priority, Category and Definition as column data. Priority column is a combo-box relating to the Priority table. Category and Definition are strings describing the different behaviors of student conduct. I've attached screenshots of the tables, fields, and macro.

The Priority and Category fields exist in at least the IssueNew and IssueDetail forms. There are probably other places where these fields impact forms and queries.

Lance

 

[Beetle]

First, we're still in the testing phase using dummy data.

Well, I would still make a backup copy of the db just in case, but since you have no real world data in there you can just delete the existing Priority and Category fields from the Issues table and then create new fields with the same names. The new fields will simply be number fields (not Lookup fields) because all you need to store here is the PK value from the Priority and Category tables.

Now on to the combo boxes on the forms. Since the new fields have the same name as the old fields, the Control Source of those combo boxes should still be valid, so you should not need to modify anything there. What you will need to modify is the Row Source of those combo boxes (which it appears you already have) to query the Priority and Category tables respectively. The key to making this work is having a reference to the TempVar in the criteria row of the appropriate column in the embedded sql of the second combo box (in this case the Category combo box). I can't tell from the image you posted whether or not you have this reference set. It would be in the criteria row of the Priority column and would look like;

[TempVars]![strPriority]

What I can tell from the image you posted of your Category Row Source is that you don't appear to have returned the ID column from the Categories table. This should be the first column returned because this is the value that you actually want to be stored in the Category field in the Issues table.

 

[elbowman]

I don't know how I get myself into these things! :banghead:

I think I've done everything you suggested. I edited the Category field to include the ID column. I edited the Issues table to include the Priority field from the Priority table as a number field. I edited the Issues table to include the Category field from the Category table as a number field. I've attached screenshots of those elements. They seem to be storing the test record selections properly.

I'm getting errors when trying to create a test record. I'm attaching 3 error message screenshots.

The first error (error.jpg) comes up when selecting the Priority combo-box on the IssueNew form, either Minor or Major.

The second error comes up when clicking OK to the first error message. If I click Stop All Macros I can escape out of the record.

Going into IssueNew form and going into the embedded macro in the Priority field, and clicking the Run button, gives me error three.

 

[Beetle]

One thing that I forgot to mention earlier is that since you are now dealing with data in more than one column, you need to modify the Column Count and Column Width properties of your combo boxes, so that may be at least part of what is screwing you up.

I still had a copy of your db from before on my machine, so I am attaching it as an example. I have made the previously discussed modifications to create the cascading combos, so you can hopefully use the example to resolve the issues with your copy.

 

[elbowman]

Problem resolved!

I can't believe what the head-banging problem was all along! A misplaced = sign.

In the Priority field macro I had an equal sign preceding the Control Name in the Requery portion, Control Name =[Category]. Removing the equal sign so it was, Control Name [Category] eliminates the error.

Again, Beetle, thanks so much for your guidance!

 

[elbowman]

Well, I would still make a backup copy of the db just in case, but since you have no real world data in there you can just delete the existing Priority and Category fields from the Issues table and then create new fields with the same names. The new fields will simply be number fields (not Lookup fields) because all you need to store here is the PK value from the Priority and Category tables.

Beetle,
Two issues related to this change.

One, in the IssueNew form if I exit the saved form and then go back into the record the Category field appears blank, and Priority retains it's selection (either Minor or Major). If I click in the Priority field, then the value originally selected in Category reappears. Is there a way to make Category retain its value and display it when a user opens, closes, and reopens the record?

Two, since leaving the primary key value in the Issues table that numeric value is what is displayed in related forms, like IssuesDS. I need my users to see the text value of that numeric in IssuesDS and other related forms like Main. It doesn't appear I can make the text value appear in IssuesDS. Any ideas?

Lance

 

[Beetle]

One, in the IssueNew form if I exit the saved form and then go back into the record the Category field appears blank, and Priority retains it's selection (either Minor or Major). If I click in the Priority field, then the value originally selected in Category reappears. Is there a way to make Category retain its value and display it when a user opens, closes, and reopens the record?

I didn't catch that before, but I have the same issue with the copy I have. I was able to resolve it by repeating the macro actions in the Form Load event (i.e. set the PriorityID Temp Var and requery the Category control).

Two, since leaving the primary key value in the Issues table that numeric value is what is displayed in related forms, like IssuesDS. I need my users to see the text value of that numeric in IssuesDS and other related forms like Main. It doesn't appear I can make the text value appear in IssuesDS. Any ideas?

The IssuesDS form uses the IssuesOpen query as its Record Source. The IssuesOpen query is in turn based on the IssuesExtended query. So, modify the IssuesExtended query by adding the Priorities and Categories tables and creating the following joins;

Issues.Priority <---> Priorities.ID
Issues.Category <---> Categories.ID

Then pull the Priority and Category fields from the Priorities and Categories tables respectively, instead of from the Issues table. If it's possible for any of the records in Issues to have no value for Priority and Category, then you'll need to use a Left join for the above relationships.

 

[elbowman]

The school year is growing ever nearer, and my plate is filling fast.

I tried adding your suggested corrections, and met minimal success. The suggestion for item #1..."I didn't catch that before, but I have the same issue with the copy I have. I was able to resolve it by repeating the macro actions in the Form Load event (i.e. set the PriorityID Temp Var and requery the Category control)."... doesn't seem to provide the desired effect.

Item #2's suggested resolution..."The IssuesDS form uses the IssuesOpen query as its Record Source. The IssuesOpen query is in turn based on the IssuesExtended query. So, modify the IssuesExtended query by adding the Priorities and Categories tables and creating the following joins;

Issues.Priority <---> Priorities.ID
Issues.Category <---> Categories.ID

Then pull the Priority and Category fields from the Priorities and Categories tables respectively, instead of from the Issues table. If it's possible for any of the records in Issues to have no value for Priority and Category, then you'll need to use a Left join for the above relationships."... is now causing error to crop up when the database is opened. I'm attached a screen shot of the table relationships I've built, and the error.

Lance

 

[Beetle]

If you want to upload a current copy I'll take a look at it tomorrow.

 

[elbowman]

Here's where I am so far, Beetle. I tried doing your suggestions, and I've obviously screwed up the SQL in the Issues query.

I'm up to a$$ in alligators, and will check back with you as soon as I can.

Thanks!

Lance

P.S. Beetle you were probably wondering what happened to me. I thought I attached my db file, but apparently it was too large, and I didn't see the error message. Here's a zipped file that gets under the size wire. School starts tomorrow! We're SO busy!

 

[elbowman]

Beetle you were probably wondering what happened to me. I thought I attached my db file, but apparently it was too large, and I didn't see the error message. Here's a zipped file that gets under the size wire. School starts tomorrow! We're SO busy!

See my original note, above.

Lance

 

[Beetle]

I tried adding your suggested corrections, and met minimal success. The suggestion for item #1..."I didn't catch that before, but I have the same issue with the copy I have. I was able to resolve it by repeating the macro actions in the Form Load event (i.e. set the PriorityID Temp Var and requery the Category control)."... doesn't seem to provide the desired effect.

My bad. Repeat the SetTempVar and the Requery in the On Current event of the form, not the On Load event.

Then pull the Priority and Category fields from the Priorities and Categories tables respectively, instead of from the Issues table. If it's possible for any of the records in Issues to have no value for Priority and Category, then you'll need to use a Left join for the above relationships."... is now causing error to crop up when the database is opened. I'm attached a screen shot of the table relationships I've built, and the error.

In the IssuesExtended query there is a field named Searchable that concatenates values from several other fields. Two of the fields involved in this are Priority and Category. However, now that you have added the Priority and Category tables to the query's record source these field names appear in more than one table, so you need to explicitly reference the table name prior to the field name so the query engine knows which table you want these field values to come from. The field should look like;

Searchable: [Issues].[ID] & " " & [Student Name] & " " & [Status] & " " & [Priority].[Priority] & " " & [Category].[Category] & " " & [Project] & .....

This should hopefully resolve those issues. Post back if you have more questions or issues.

 

[elbowman]

Beetle,
Once again you come through for me! Thanks so much!

Must go put children on buses...Sigh.

Lance