How to create a Dynamic Form! Help Please

[wsala33]

I'm trying to create a dynamic form, but I'm not being sucessfull. I created a query call frmReportCard that is using two tables one is a listing of Clients and is link to the Reportcard table by comanyname. When I open the form the option button are not accessablle can anyone please help me and tell me what Am I doing wrong. I have attache a sample of the database that I'm trying to do.

 

[trucktime]

Form

Because you joined the tables in the query the recordset is not updatable.
Probably the easiest way is to create a form for Clients table with a subform for the Reportcard table.

 

[Pat Hartman]

Because you joined the tables in the query the recordset is not updatable.

- Simply joining tables does NOT make a query not updatable. The query is not updatable in this case because neither of the tables has a primary key so Jet can't determine the cardinality of the relationship. Other things wrong with the tables:
1. CompanyID should be the relation field rather than the company name field.
2. Table and column names contain embedded spaces.
3. The Report card table contains a repeating group.

 

[wsala33]

what do you mean by repeating group

Ok, I understand that I can not update because I'm linking the tables. How would I be able to have the company name viewable for updating purpose. In the report card table I have a field name current client which is what the flag that Identified the current active clients.

- Simply joining tables does NOT make a query not updatable. The query is not updatable in this case because neither of the tables has a primary key so Jet can't determine the cardinality of the relationship. Other things wrong with the tables:
1. CompanyID should be the relation field rather than the company name field.
2. Table and column names contain embedded spaces.
3. The Report card table contains a repeating group.

 

[wsala33]

dynamic form

I would like the form to display only the companies name that have a value of 1 in the current client field. would I need to use arrays to archive this.

Ok, I understand that I can not update because I'm linking the tables. How would I be able to have the company name viewable for updating purpose. In the report card table I have a field name current client which is what the flag that Identified the current active clients.

 

[wsala33]

I under stand your advice but for productivity purpose I will like to just have a solid form and that's because I will be able to update the data in a 123 step. But I only need to display the companies that have a value of 1 in the current client in the client2 table.

Because you joined the tables in the query the recordset is not updatable.
Probably the easiest way is to create a form for Clients table with a subform for the Reportcard table.

 

[wsala33]

what I want to accomplish

what I want to accomplish is to be able to enter the value for grade and reason1,2... and when I finish updating the form I will like to add tha update information into the Report Card table.

 

[Pat Hartman]

Ok, I understand that I can not update because I'm linking the tables.

- That is NOT what I said. Linked tables ARE updatable as long as they have primary keys. EVERY table should have a primary key so what's the problem? If the linked table doesn't have a primary key, you won't be able to update it even if you bind the form directly to the table! So, the join is not what is relevant in this case.

what do you mean by repeating group

- Any time you have a column name that you copy and suffix or prefix, you are creating a repeating group. There is a 1-many relationship between the company and the reason field.

 

[wsala33]

thanks, now i have another delema

My next step is to take the updated data and inserted to the report card table and I also want to reset the value in the form. I know this will require vba code can you help

- That is NOT what I said. Linked tables ARE updatable as long as they have primary keys. EVERY table should have a primary key so what's the problem? If the linked table doesn't have a primary key, you won't be able to update it even if you bind the form directly to the table! So, the join is not what is relevant in this case.

- Any time you have a column name that you copy and suffix or prefix, you are creating a repeating group. There is a 1-many relationship between the company and the reason field.

 

[wsala33]

i have attached the file with the changes

I have attached the dabase with the changes I made. I know I will need some kind of query in the after update event.

My next step is to take the updated data and inserted to the report card table and I also want to reset the value in the form. I know this will require vba code can you help

 

[wsala33]

disregard my previous question

the date is mapping directly to the reason table,which has the same structure as the report card table the only different is that is mapping the comp_id, instead of the companyname. Thanks for all your help.

- That is NOT what I said. Linked tables ARE updatable as long as they have primary keys. EVERY table should have a primary key so what's the problem? If the linked table doesn't have a primary key, you won't be able to update it even if you bind the form directly to the table! So, the join is not what is relevant in this case.

- Any time you have a column name that you copy and suffix or prefix, you are creating a repeating group. There is a 1-many relationship between the company and the reason field.