Data entry not allowed 2

[puforee]

I have a form connected through a query to a table (admin). When I open the form I can enter data in all fields. One of the fields in the Form is named "Model". The data base will be used for several different Models...therefore I want to limit/filter the form by model. Since the form is fed by a query I changed the query to be linked to the active model table. There is only one active model per DB Front end. When I open the form, it shows data for only the active model...good...but I can no longer enter/change data on the form..

I must be missing something because I have been able to do this before in other DB's. I even set a relationship between the admin table and the active model....linking the model...same data type.

I also tried filter queries for the form....no luck. When Active Model is involved I cannot change the data.

This must be simple but I am just not seeing it...any help please?

Thanks,

 

[dhookom]

Please:
- SQL view
- Primary and Foreign key fields of the tables involved
- Data properties of the form

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

dhookom,
Data properties of the Form
Record Source Main Admin Setup qry
Filter...blank
Order By....blank
Allow Filters...yes
Allow Edits...yes
Allow Deletions...yes
Allow Additions...no
Allow Additions..no
Recordset Type...Dynaset
Record Locks...no locks
Fetch Defaults..Yes

Record Source SQL (when it works)
SELECT [Main Admin Setup].[Model-CourseID], [Main Admin Setup].CourseSet, [Main Admin Setup].Model, [Main Admin Setup].Course, [Main Admin Setup].[Course Name], [Main Admin Setup].[ReportCourse Name], [Main Admin Setup].Comments, [Main Admin Setup].Revision, [Main Admin Setup].LockCourse, [Main Admin Setup].RevisionDate
FROM [Main Admin Setup]
WHERE ((([Main Admin Setup].Model)="787-8"));

Record Sours SQL (when it does NOT work)
SELECT [Main Admin Setup].[Model-CourseID], [Main Admin Setup].CourseSet, [Main Admin Setup].Model, [Main Admin Setup].Course, [Main Admin Setup].[Course Name], [Main Admin Setup].[ReportCourse Name], [Main Admin Setup].Comments, [Main Admin Setup].Revision, [Main Admin Setup].LockCourse, [Main Admin Setup].RevisionDate
FROM [Model In Use Tbl] INNER JOIN [Main Admin Setup] ON [Model In Use Tbl].Model = [Main Admin Setup].Model
WHERE ((([Main Admin Setup].Model)="787-8"));

Primary and Foreigh Fields for Tables
Main Admin Setup...None
Mdel In use TBL....None

Is the lack of PK and FK settings my problem?

 

[puforee]

dhookom

I assigned a PK to Model In use TBL....Model field (text field)
The same text field, Model, resides in the Main Admin Setup table...also.

Still does not allow me to enter data.

 

[dhookom]

Does the datasheet view of the record source allow editing?
According to your form data properties, you can't enter new records.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

The query that drives the form...when viewed in Datasheet does NOT allow editing.

I turned on Allow Additions...no change.

I opened the form in Datasheet...no change..still will not allow changes.

Thanks,

 

[dhookom]

Apparently you don't have an updateable query so the form will never allow updates. Why are you including [Model In Use Tbl]?

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

dhookum,

The basic query above (the one that works) allows field updates in data sheet mode and through the Main Admin Setup form. This query will reture ALL models with their information. I have a split DB.

I have created many front ends for the db...I limit what data each of them show by setting the Active Model table for one model only for each front end. This is easy to do with the Active Model table for all formss except the Main Admin Setup form. Once I include the model table in the query...see second query above...neither the query nor the form will allow modifications to the fields.

It is not a huge issue for me to set the Working Query Criteria to a specific model for each of the front ends...but I am blown away because I can't control it with the Active Model table...this way I would only have to set one item...the Active Model...to filter to a specific model for my users.

 

[dhookom]

Can you set the Model field to primary key or unique index in both tables?

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

I think I found the problem...not sure how to fix it. The little thing I did not tell you....the Main Admin Setup table is in the background DB while the Active Models table is in the Front end DB.

When I went to the Data DB...the query worked fine...both tables reside there also. So, I am wondering if it is not working because the Active Model Table resides in both DB's. Or, it is not working because my front end query is bring data from a linked table (Main Admin Setup) and a non linked table...Active Model.

The first thing I will do is delete the Active Model table in the backend DB. It must be in the front end DB so it can be unique to all the different front end DB's.

I will let you know how it turns out.

Meanwhile...do you know of any code (just in case) that I could use to set the criteria of a query to a value?

 

[dhookom]

do you know of any code (just in case) that I could use to set the criteria of a query to a value?

consider faq701-7433


Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

The first thing I will do is delete the Active Model table in the backend DB. It must be in the front end DB so it can be unique to all the different front end DB's.

First of all, make SURE that the "table" in your front-end is not a LINKed table.. If it is, you'll be deleting ALL your data.

Secondly.. why do they each need a different copy of the same table? If you need to track who did what on what machine/database, then you could simply add fields for:
Last Modified By User
Last Modified from Database
Last Modified on MachineName

Then use an AutoNumber ID field for creating the UniqueIDs each time unless you have a specific formula otherwise.

Anyway, post back with your progres..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[puforee]

kjv1611 and dhookom

I did move the Active Model to the Front DB and removed it from the Back end.
We want to keep all the data in the Back end table. We have 7 different models. For each model it will have a front end DB...they are all the same except for the Active Model. This reduces the amount of data they have to weed through to just their Model. I set the model within each front end DB. I also have a DB front end....I have that one set to see all models.

I am working on using dhookom's FAQ about using the Change SQL property...so I can control the query model field criteria. I will keep you advised.

Thanks for all the help so far..I have learned....now to apply.