Creating Datasheet Form from Query

[Ekhart]

I am trying to create a datasheet form, simply for speed reasons due to a slow network. I can create the form fine, however when I create it it defaults to 'Data Entry' being set to no. Obviously when I run this, I cannot edit the info. When I change 'Data Entry' to Yes and click back on Datasheet view, all of the records are gone and it is blank. If I go back to Design view and change Data entry back to No and return, the records are back.



Any idea what is going on here? Basically I have a query that runs in a report, and in this report people can click on anything and edit it so on... the network is slow and I cannot control this (it is being worked on, but no idea what to do until then) so I wanted to give them a 'datasheet' or 'spreadsheet' type view that would allow them to work on all right away so if I could figure out how to just make the query datasheet view editable that would work as well.

[davegri]

You are interpreting the data entry mode incorrectly. Data entry mode means the form is set for NEW RECORDS ONLY and old records cannot be viewed.

[Micron]

Data entry is a mode that opens the form to a single new record to allow you to do exactly what the name infers. It is not 'editing'. If you cannot edit any of the records when they are all displayed, you're probably getting a warning or error message that you're not sharing here. Open the query that the form is based on and see if you can edit anything in the query datasheet view. If you cannot, the problem is with the query - some are not editable because of their joins, some because aggregate functions are used in it. If you can, some other form property is not set correctly, such as 'Allow Edits'.

[Ekhart]

Data entry is a mode that opens the form to a single new record to allow you to do exactly what the name infers. It is not 'editing'. If you cannot edit any of the records when they are all displayed, you're probably getting a warning or error message that you're not sharing here. Open the query that the form is based on and see if you can edit anything in the query datasheet view. If you cannot, the problem is with the query - some are not editable because of their joins, some because aggregate functions are used in it. If you can, some other form property is not set correctly, such as 'Allow Edits'.

Thanks, I was reading that option incorrectly.
When I open the Queries I am not able to edit them in datasheet view... If I could that would solve my problem easily. I can't seem to figure out why it wont allow editing. No errors come up.
Is it because the query uses multiple tables?

[NTC]

Some queries are not update-able. This is a fundamental database principle not specific to Access per se. Whenever an aggregate is used or a 1:Many join such that there is an uncertainty - then the query becomes not update-able.

Sometimes one can alter the query. Other times one must move to a form/sub form design in order to be able to edit fields.

[Micron]

Agreed. In a worst case scenario, a set of temp tables with one or more sub forms may also solve the issue. This would be a complicated one to figure out without looking at the db or at least pictures of the relationships (if established) and the full query design grid, unless
- you are using aggregate functions (DMax, Sum, Avg, etc) as already noted
- it is a cross tab query
- it is a union query
- it is a pass through query
Pretty sure none of these are updatable
- somewhere the 'all records' level of record locking is being applied (doubtful this is the case here)

[Ekhart]

The query in question is a simple query, but I do have union queries I was hoping to do this with so I guess that is out of the question. I have forms set up to edit this info, but due to the slow network opening forms for each person and so on makes the entire process slow.

[Micron]

Hope you're not using a db over wifi. A lot of union queries might be an indication of a db that is not normalized.

[Ekhart]

No just a slow network, not wifi. Any other suggestions on how to essentially take a query and make it workable? I tried finding a way to export it to excel, work on it, and import it but it does not seem to update the records.

[NTC]

A union query will never be updateable. For a complex query you would think is possible to adjust to being updateable you must experiment by deleting tables one at a time until it becomes updateable. Then you'll see which element is the cause. Typically the way around this situation is to present the data to the user in a Form/subform rather than a single record set based Form.

It was stated that writing to temp tables is sometimes a possibility and this is true but should be a last ditch effort. Your writing to excel is the same idea but that should be avoided as linking to excel just makes things even slower and more complicated - besides a temp table is essentially the same role. If you do rely on temp tables remember to put them in the front end not the back end so they are user-unique and not common.

[Micron]

Maybe check this out
http://allenbrowne.com/ser-61.html

[Missinglinq]

...but due to the slow network opening forms for each person and so on makes the entire process slow...

Just to be clear, this app has been split, with the Back End, containing the Tables, on the shared network, and everything else, including Forms, in the Front End, with each user having a copy of the Front End on their PC...correct?

Linq ;0)>