Editable query in subform

[Sergey Bogdanov]

Hello,

the strange things are going on. I can't explain why the problem is
following I have two tables which are LEFT joined. I'd like to show all
records from one table and join records from another table:

"SELECT p.nosaukums,g.price,g.date FROM produkti AS p " & _
" LEFT JOIN PriceGroup AS g ON ((p.kods = g.product) AND (g.Name = "
& ClientGroup.Value & "))")

This recordset is editable when I'll remove this part of query -- AND
(g.Name = " & ClientGroup.Value & "), otherwise it is readonly. Please,
explain me why it happens?
Or maybe there are workaround?

Thnx,
Sergey



Dim dbs As Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset

Set dbs = CurrentDb
If ClientGroup.Value <> "" Then
Set rst = dbs.OpenRecordset("SELECT p.nosaukums,g.price,g.date FROM
produkti AS p " & _
" LEFT JOIN PriceGroup AS g ON ((p.kods = g.product) AND (g.Name = "
& ClientGroup.Value & "))") '" & & "')")
' AND g.name = """ & ClientGroup.Value & """)")
Set Me.PriceGroup_subform.Form.Recordset = rst
End If

 

[John Vinson]

"SELECT p.nosaukums,g.price,g.date FROM produkti AS p " & _
" LEFT JOIN PriceGroup AS g ON ((p.kods = g.product) AND (g.Name = "
& ClientGroup.Value & "))")

This recordset is editable when I'll remove this part of query -- AND
(g.Name = " & ClientGroup.Value & "), otherwise it is readonly. Please,
explain me why it happens?

Because, I'd guess, there is no unique index on g.Name. I'm guessing
that the query is simply using ClientGroup.Value as a criterion, so it
should be in a WHERE clause (allowing for NULL so that the criterion
on the outer table doesn't cause failure) rather than the JOIN clause.
I'll also assume that g.Name is a Text field - if it is you need the
syntactically required quote (Chr(34)) delimiters around the
criterion.

"SELECT p.nosaukums,g.price,g.date FROM produkti AS p " & _
" LEFT JOIN PriceGroup AS g ON p.kods = g.product " & _
WHERE g.Name = " & Chr(34) & ClientGroup.Value & Chr(34) & _
& ")) OR g.Name IS NULL")

 

[John Vinson]

Thanx for you reply, but I've noticed that if g.Name doesn't contain records
it shows only that records of products which are not used by PriceGroup.

I'm not quite certain what you're trying to accomplish. This should
find those products where the field kods in the Produkti table matches
the Product field in PriceGroup; if there is a PriceGroup record then
its [Name] field must match the value of the form control ClientGroup.
If the Product is not associated with any ClientGroup it will still be
shown with NULL fields for the PriceGroup fields - that's what the
left join is designed to do.

What do you WANT to see?