Data Entry form based on Query? 1

[nedstar1]

HELP!

I've got a new data entry form I built last week, and no one can enter data into it, including myself (the administrator). It does have a security file, but I've gone through and basically opened up the permissions to see if I can get it working.

Because I wanted to display a lot of information on the form, I built a massive query that pulls data from five different tables, theoretically allowing me to enter data into all 5 tables at once(right??!!?), but it won't let me enter any data!

I've been through the Form properties, and it is set to allow additions, deletions, data entry, and filters. It says the recordset type is a dynaset.

It is pulling the data that already exists in the tables, so I know it is working on some level. I can even make a change at the table level and see it in the form.

SO, please correct me if I am wrong - I was under the impression that I could update data in the tables through a query - is this wrong? Does that change if you build a form based on a query? Do I have the wrong query type?

Please help - if I don't figure this out in short order, I'll be forced to spend the rest of the day figuringh out a way to rebuild the form from scratch, and it won't be nearly as attractive or easy to use, and I'll be terribly grouchy.

I'm in a tizzy, folks. Please help if you can.

TIA,

Regards,
Nedstar1

 

[KenReay]

Hi

Is the query updatable?

quick way to test this

run the query from the database window, try to chnage any column

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[nedstar1]

Further information:

The query is in fact a select query,a nd uses five tables, using the asterisk to include all fields. I ran the query on its own, and voila - I can't update the data.

Is there a simple way to convert this query into one that would allow me to update the data, or do I basically need to start from square one? There are seven pages in a tab control, with well over three hundred fields to update - if I have to go back to basing it on tables, then I'm going to have to redesign the form, or worse, the table structure, to include all of the needed fields on each page. I'm up the proverbial creek here. Anyone got a paddle?

TIA

Regards,
Nedstar1

 

[KenReay]

It may be possible to use sub forms to overcome your problem

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[nedstar1]

Hi Ken,

Thanks for the two rapid replies. I can and have used subforms, but I had made this one form simply perfect - no ugly form lines, etc, and I am reticent to reverty back to the subform process when some of the fields I need to inlude would essentially require the creation of a subform with a solitary field in it. Naturally, if this is the way I need to do it, then this is the way I will do it.

So no quick fix? (can you tell I'm American?) lol

TIA

Regards,
Nedstar1

 

[WAR40K]

You may be able to update if you define each of the fields in your query individually rather than using the asteriks. Many times Access can't perform certain functions when the "all fields" method is used. It will take less time to redefine your query identifying or selecting each field individually than it would to re-design your form. Good luck.

 

[nicalina]

Can you do data entry to the query (in Datasheet view)?
If not, then that's what you need to fix, not the form.

If your query is based on tables with a one-to-many relationship, you might not be able to edit the data. Using the "cascading updates" setting in the table relationships can usually solve this.

Look up "When can I update data from a query?" in Access Help for details.