Using crosstab queries with forms (1 Viewer)

[k0r54]

Hi,

I have a form and its source is a query. There are only 5 things on the form that can be modified, the rest is just displayed.

the 5 things are within a table b. In my design view i link up table b with the already made crosstab query. (everything within the crosstab query are just for display on the form). Within the design view I put * from the crosstab query (everything) and then the fields from table b (all 5 of them).Everything on the form is working and is linked up properly, but when i go to type in them it is locked. (well they arn't within the property), but i cant type in them. This is for all 5 fields on the form.

When i then go back into the source and remove the crosstab query, i can then type into the fields.

Any ideas?

** p.s When i create the same query normal it doesn't allow me to type in the datasheet view either **

Thanks
k0r54

 

[KenHigg]

So your form recordsource is based on a query which includes the pre-built crosstab query?

 

[k0r54]

Yes,

But none of the fields within the crosstab are being edited

 

[KenHigg]

That may be locking the form. Maybe as a work around, you could do a sub form with the crosstab query as recordsource...

 

[k0r54]

Ok, i will have a play.

Thanks

 

[k0r54]

Unfortunatly i can not use a sub form, because the fields within the crosstab query change all the time.

 

[k0r54]

mmm, is there a way i can source from two queries without having them directly connected?

 

[Pat Hartman]

If the fields from the crosstab change all the time, what difference does it make if you use a form or a subform, you still have the same problem.

You cannot update a query that includes any aggregated data, even though it seems you should be able to. You need to try making the crosstab a subform. Depending on what the crosstab columns will be, you may be able to use the column headings property of the crosstab to fix the set of columns returned by the query.

 

[k0r54]

Hi,

Ok as a work around i have done a make table query from the crosstab. When i open the new table (tablea) i can type in it. All the data i want is there and its fine. I can also type in tableb which has some other data for my form.

However when i then link these two tables in a query (just a normal select query) with no grouping or anything. I am unable to type in the datasheet???

why is this?

 

[Pat Hartman]

Crosstabs are not updatable because they aggregate data. There is no way for Access to identify the specific record which needs to be updated.

What are the columns of the crosstab? Did you try fixing them with the column headers property? For example, if you are working with a calendar year and some months may not have data, you can guarentee that the query returns a column for each month even if it is empty.

 

[k0r54]

Hi,

the thing is i am not using the crosstab at all now, apart from to make the table. I can open up the table and write to it fine. But when i put the table that has been made into a query it locks. How come i can write to it when its not in a query?

 

[Pat Hartman]

There is something about the query that makes is not updatable. Look up "updatable queries" in help for possible problems.

 

[k0r54]

Problem is no longer a from issue: -

Has been moved to : -
http://www.access-programmers.co.uk/forums/showthread.php?p=441477