Forms/subforms from queries not working

[Cheryl13]

Hi,

I had originally built all my forms/subforms directly from the tables but, I was having problems printing and was told that it would be better to write queries and create my forms/subforms from there. I created all the necessary queries and edited each of my forms/subforms to have the record source of the query. This did correct the printing errors but, now if I create a new record all the checkboxes are greyed out and when I put a check in them I get a message that "You can't assign a value to this field". Any idea how to fix this (they were all working before)?

Also, I have one query that is too large. When I try to set the form/subforms to the query it says to many fields defined. I have tried to break it down into smaller queries but now I can't add any new records.

Any suggestions would be appreciated - I have been poring through the field and form properties but, can't find anything that seems relevant

 

[TheAceMan1]

How are ya Cheryl13 . . . . . .

You need to be more specific Cheryl13. Your post is too vague as far as resolution is concerned.

Can you supply: Tables & Relationships, PrimaryKeys, who's on the one side, who's on the many? The more you can supply the better.

Just how many fields are involved in this over-sized query?

Can you post the queries for the form/subform combo giving you the checkbox error?

You have more than one problem here, requiring more than one resolution. There are many here who can help, but we're gonna need more info! . . . . . . .

TheAceMan

 

[Cheryl13]

Hi,

OK - I have to admit I'm not an Access expert so, I hope these details don't confuse.

For the over sized query, there are 267 fields. The reason there are so many is that I have to have on every report (generated from the forms/subforms) a set number of lines (ie. 8) or the govt. agency we send the forms to won't accept them so, I created several fields MAT1, MAT2, MAT3....RGE1, RGE2, RGE3....This is probably not very efficient - I wanted only one field but, I couldn't figure out another way to get the right number of boxes on the reports.

For the checkboxes (I think the problem is not just the check boxes now that I look closer) The main table is called Main Table and contains a RecNo which is the primary key. Each of the other tables (Schedule1, Schedule2, Schedule 2-1, Schedule3...) contains it's own primary key (Sched1_ID, Sched2_ID) and also contains a RecNo.

The query contains the RecNo from the Main Table and some necessary fields (ie. company, coordinator) and all the other fields from the other table (Schedule1, 2...) except the RecNo.

When I had the forms connected to the tables (instead of a query), the records were all generating correctly. Now, it seems when I try to create a new record in the form, it does actually create the record in the table but does not update the RecNo (they are now blank) and the new record does not update in the query either so, I think it has something to do with my RecNo. I have posted the SQL for one query below (I hope it's not too long) but, I actually created the query in design view.

Thanks so much for your help! Please let me know if it's still not enough information.

SELECT MainTable.RecNo, Schedule1.Sched1_ID, MainTable.[Requisition#], MainTable.Date, MainTable.Company, MainTable.RegTechID, MainTable.Coordinator, MainTable.Phone, MainTable.EmailID, MainTable.[BA Code], Schedule1.AppTypeFacility, Schedule1.AppTypePipeline, Schedule1.AppTypePLInstall, Schedule1.[App Type Wells], Schedule1.[CompleteSch 2a], Schedule1.[Complete Sch 2-1a], Schedule1.[Complete PFD/Plot Plansa], Schedule1.[Complete Sch 3a], Schedule1.[Complete Sch 3-1a], Schedule1.[Complete Base Mapsa], Schedule1.[Complete Sch 4a], Schedule1.[Complete Sch 4-1a], Schedule1.[Complete Sch 4-2a], Schedule1.[Complete Sch 4-3a], Schedule1.[Complete Survey Plansa], Schedule1.RegTechID, Schedule1.[CompleteSch 2], Schedule1.[Complete Sch 2-1], Schedule1.[Complete PFD/Plot Plans], Schedule1.[Complete Sch 3], Schedule1.[Complete Sch 3-1], Schedule1.[Complete Base Maps], Schedule1.[Complete Sch 4-1], Schedule1.[Complete Sch 4-2], Schedule1.[Complete Sch 4-3], Schedule1.[Complete Survey Plans]
FROM Schedule1 INNER JOIN MainTable ON Schedule1.RecNo = MainTable.RecNo;