Form not allowing entry

  • Thread starter Thread starter Aaron Howe
  • Start date Start date
A

Aaron Howe

I have a really weird problem with a form I created. I
have a set of tables in a split database which hold
important data. I run a query from them to find missing
information using two criteria: That field A is completed
and that field B is not. I made a form based on this
query to fill in that missing information. But my form
won't let me complete that field.

I have tried entering data directly into the table and
it's fine. I tried entering into the query and it won't
allow it. I tried removing the constraints (is null, is
not null) in case that was causing some kind of loopback
but that's not it either. I even copied a very similar
form to see if I could change the fields in case it was a
property issue - but that didn't work.

Has anyone else come across such a problem?
 
Aaron Howe said:
I have a really weird problem with a form I created. I
have a set of tables in a split database which hold
important data. I run a query from them to find missing
information using two criteria: That field A is completed
and that field B is not. I made a form based on this
query to fill in that missing information. But my form
won't let me complete that field.

I have tried entering data directly into the table and
it's fine. I tried entering into the query and it won't
allow it. I tried removing the constraints (is null, is
not null) in case that was causing some kind of loopback
but that's not it either. I even copied a very similar
form to see if I could change the fields in case it was a
property issue - but that didn't work.

Has anyone else come across such a problem?
Click to expand...

It's nothing to do with your form, you've created a non-updatable query.
How many tables does it include? Are the joins all on defined relationships
i.e. primary key to foreign key? Can you post the SQL?
 
Aaron Howe said:
I have a really weird problem with a form I created. I
have a set of tables in a split database which hold
important data. I run a query from them to find missing
information using two criteria: That field A is completed
and that field B is not. I made a form based on this
query to fill in that missing information. But my form
won't let me complete that field.

I have tried entering data directly into the table and
it's fine. I tried entering into the query and it won't
allow it. I tried removing the constraints (is null, is
not null) in case that was causing some kind of loopback
but that's not it either. I even copied a very similar
form to see if I could change the fields in case it was a
property issue - but that didn't work.

Has anyone else come across such a problem?
Click to expand...

Is the query based on a single table? With a primary key? Does it use
distinct values, top values or grouping? If yo still can't work it out, go
to the design of the query, select View>SQL View and copy the SQL. Post
this statement.
 
This is normally because you have a Auto number or Key
are in the main table and when you created the queries
you didn't add this field. This is usually a manditory
field. Hope it helps!! Lonnie
 
This is normally because you have a Auto number or Key
are in the main table and when you created the queries
you didn't add this field. This is usually a manditory
field. Hope it helps!! Lonnie
Click to expand...

I've been through and looked at all the joins etc and as
far as I can see, there are no glaring issues...

Here's the SQL:

SELECT tblSuppliers.AgName, tblXInvs.XInv,
tblAgInvs.CltName, tblXInvs.AgInv, tblXInvs.ClrDate,
tblAgInvs.Value, tblAgInvs.WE
FROM tblSuppliers INNER JOIN (tblAgInvs INNER JOIN
tblXInvs ON tblAgInvs.AgInv = tblXInvs.AgInv) ON
tblSuppliers.AgName = tblAgInvs.AgName
GROUP BY tblSuppliers.AgName, tblXInvs.XInv,
tblAgInvs.CltName, tblXInvs.AgInv, tblXInvs.ClrDate,
tblAgInvs.Value, tblAgInvs.WE
HAVING (((tblXInvs.XInv) Is Not Null) AND
((tblXInvs.ClrDate) Is Null))
ORDER BY tblXInvs.AgInv;

It still doesn't make much sense to me I'm afraid...
 
Aaron Howe said:
I've been through and looked at all the joins etc and as
far as I can see, there are no glaring issues...

Here's the SQL:

SELECT tblSuppliers.AgName, tblXInvs.XInv,
tblAgInvs.CltName, tblXInvs.AgInv, tblXInvs.ClrDate,
tblAgInvs.Value, tblAgInvs.WE
FROM tblSuppliers INNER JOIN (tblAgInvs INNER JOIN
tblXInvs ON tblAgInvs.AgInv = tblXInvs.AgInv) ON
tblSuppliers.AgName = tblAgInvs.AgName
GROUP BY tblSuppliers.AgName, tblXInvs.XInv,
tblAgInvs.CltName, tblXInvs.AgInv, tblXInvs.ClrDate,
tblAgInvs.Value, tblAgInvs.WE
HAVING (((tblXInvs.XInv) Is Not Null) AND
((tblXInvs.ClrDate) Is Null))
ORDER BY tblXInvs.AgInv;

It still doesn't make much sense to me I'm afraid...
Click to expand...

For a start, you should remove the GROUP BY, it's doing nothing useful, and
replace the HAVING clause with a WHERE clause.
 
-----Original Message-----


For a start, you should remove the GROUP BY, it's doing nothing useful, and
replace the HAVING clause with a WHERE clause.


.
Click to expand...

Fantastic, that works now. So it was the HAVING clause
that caused the issue? I don't totally understand why,
but now I have a starting point to investigate and see
what it does. Thanks again everyone for your help!
 
Aaron Howe said:
Fantastic, that works now. So it was the HAVING clause
that caused the issue? I don't totally understand why,
but now I have a starting point to investigate and see
what it does. Thanks again everyone for your help!
Click to expand...

GROUP BY/HAVING are only meaningful in an aggregate query, i.e. one that is
doing a sum, count or whatever, which you are not doing. Aggregate queries
are NEVER updatable, and I guess that by including GROUP BY you fooled
Access into treating it as an aggregate query.
 
Back
Top