Query Is Updateable, Form Is Not

[rpochoda]

I have a form where the data source is a SQL query set dynamically in VBA based on entries in two unbound combo boxes. The query is a one-to-many join that selects one client's data from the one side table and one project's data from the many side table. If I copy the resulting query from the record source line in the form properties to a SQL query screen, the query results are updateable (even the one side data, which surprised me). But the same data in the form, even those fields from the many side, is not. What am I missing? This is a stand-alone database, no networking, sharing, privileges, etc.

 

[Katerine]

Hi rpochoda,
Some quick things to check/confirm before we move on to more complicated troubleshooting:

[ul][li]In the form properties, Allow Edits is set to true (also Allow Additions and Allow Deletions, if those are relevant and desired)[/li]
[li]The controls are not locked[/li][/ul]

If both of these are already the case (or fixing it doesn't fix the problem), then the next step is for you to post the query.

Thanks, and HTH!

Katie

 

[rpochoda]

Controls are enabled and unlocked. Edits are allowed. Here's the query in the form data source property:
SELECT Grantees.*, Grants.* FROM Grants INNER JOIN Grantees ON Grants.GranteeID=Grantees.GranteeID WHERE Grantees.GranteeID=3 And Grants.GrantID=4139;

Here's where the query is assembled in the VBA code:
Me.RecordSource = "SELECT DISTINCT Grantees.*, Grants.* FROM Grants INNER JOIN Grantees ON Grants.GranteeID = Grantees.GranteeID where Grantees.GranteeID = " & Me.SelectGrantee & " AND Grants.GrantID = " & Me.SelectGrant

SelectGrantee and SelectGrant are the unbound combo boxes. The query works as the data source and the correct data always appears. It's just not updateable. But as I said, the same data is updateable if I run the query in the SQL query window.

 

[PHV]

Get rid of the DISTINCT predicate if you want an updatable recordset.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rpochoda]

Wow, nice catch. I was just examining the query in the form properties not in the code, and I didn't catch the difference even when I copied it here. The DISTINCT is vestigial, left over from a previous table structure.

But now I have a strange theoretical question, why didn't DISTINCT appear in the form properties, and yet, even stranger, was still operative?

 

[Katerine]

But now I have a strange theoretical question, why didn't DISTINCT appear in the form properties, and yet, even stranger, was still operative? "

When you open a form in design view, you see the saved recordsource, which is most likely the recordsource that was put into it when it was designed. If the recordsource is changed in the code, that's just temporary (unless the form design is then saved).

So you could set the recordsource to be:

SELECT Grantees.*, Grants.* FROM Grants INNER JOIN Grantees ON Grants.GranteeID = Grantees.GranteeID where Grantees.GranteeID = " & Me.SelectGrantee & " AND Grants.GrantID = " & Me.SelectGrant

...in the code, but unless you save the form (which I don't recommend doing all the time, btw - it causes database bloating. In fact, go out of your way to stop the form from being saved whenever possible), then what you see in design view will be the last saved recordsource. And if you initially designed the form with, "SELECT Grants.* FROM Grants", and never changed it in design view since then, and never saved the form, then that's still what you will see.

Which is good... in design view, you want to see what the form starts out with, before any of the code is applied. Otherwise, you wouldn't be able to follow it.

Katie

 

[rpochoda]

Thanks all. This makes sense. Katie, does this mean that I should save the form just once with the records source property empty? Also, why does saving causing bloat? Doesn't the db just overwrite the previous version of the form, or is the db saving them all somewhere (till a compact?)?

 

[Katerine]

What I normally do in these situations (where the recordsource is set in the code and initially shouldn't show anything until after the user picks something) is set the recordsource in the design view to something comparable, but either empty, or full (depending on your preference. Empty takes less time to load, but you may want the form to initially open with a full recordset). For example:

SELECT Grantees.*, Grants.* FROM Grants INNER JOIN Grantees ON Grants.GranteeID = Grantees.GranteeID where Grantees.GranteeID = 0 AND Grants.GrantID = 0

That way, you can see an approximation of what the recordsource will eventually be when you're in design view, and bound controls will show up correctly (flagged when there are problems and not flagged when they are not), and the form initially opens empty, until it encounters code that tells it to do otherwise.

As for whether the forms just get saved over and there's no bloating... I wish!! But no. When you overwrite anything in an Access database, it kind of treats it as though you're deleting the previous version and then saving the new version with the same name (and deleting in Microsoft Access does not clear the memory or the space that was used). This is true of forms, reports, queries... anything, even records. Open a test database, make a bunch of small changes in your form design, saving the form after each change, and watch the size of your database grow. This is why the Compact function exists.

Katie

 

[Katerine]

Sorry for the multiples; I just have a small self-correction.

I kind of implied that editing records causes database bloating. That's not strictly the case. I've never seen evidence that EDITING records causes bloating. However, if the table is stored in Access (and not linked to SQL Server or something), and you DELETE the record, the space that was taken up by that record is not freed up, until you compact the database. This is particularly noticeable if you have temp tables, which is why if temp tables are absolutely needed (say, for queries that need to simultaneously contain aggregate data, AND be updateable) should be as tiny as possible - preferably with only two fields: the key field and the aggregate value(s).

Editing and saving queries, forms, and reports does cause some database bloating. But it's not usually a big problem unless you either a) never compact, ever, or b) edit and save constantly.

Katie

 

[rpochoda]

Thanks again. And no, I didn't think you were talking about editing/deleting records, just form designs.