Add new Records in a form

[shafara7]

I know this might sound simple but I am trying to enable the addition of new records through a subform on a form.
Before this there is a * at the bottom of my subform to add new records.
But since I added one query in my subform's record source, the * row disappear.

I have tried setting the Allow Addition to Yes at my subform's data properties but the * is still not appearing.
Any idea how to fix this?

 

[arnelgp]

Make shre your qhery is updatable

 

[shafara7]

What should I be looking for?
In the query there is also no * at the bottom.

 

[isladogs]

Check whether your record source is read only by trying to modify one of the records.

Then check this site for reasons why this happens http://allenbrowne.com/ser-61.html

 

[arnelgp]

What query did you add to the subform's recordsource? That will make your query not updatable,. Try to incorporate the new query using join on common field.

 

[shafara7]

Okay the new query that I added is a Totals query. That's the culprit.
Is there any idea to bypass this query? Because that query is added there only because I wanted to add a new row to my subform that will show the status of the record.

 

[arnelgp]

You can either remove it or use left outer join with the original query if you really want to show additional info. It would be easier for us if you can post both query. Then we can help you how to join in common field.

 

[isladogs]

The easiest solution is to remove the aggregate query with te total field.

Alternatively, change the current select query with that field to a make table query and run it.
Now use the new table as your form record source.
You would need to add append and update queries to save any data changed in the form back to the original table(s)
This will work but be messy.
You will also find it causes database bloat so compact regularly.

My opinion is to remove the totals field.

EDIT Arnelgp got there first. I'll drop out or we'll keep duplicating our replies

 

[shafara7]

Arnelgp, how do I make Left Join for the query?
I have attached the screenshots of the queries and form and below is the SQL

SQL for Subform
SELECT tblBelegung.indBemusterungsverantwortlicher, tblBelegung.indEMMessanlage, tblBelegung.indEMMesstechniker, tblStammdaten.*, *
FROM (tblBelegung RIGHT JOIN tblStammdaten ON (tblBelegung.txtTeilesachnummer = tblStammdaten.txtTeilesachnummer) AND (tblBelegung.indFOLand = tblStammdaten.indFOLand)) LEFT JOIN qryMAstatus ON (tblStammdaten.txtTeilesachnummer = qryMAstatus.txtTeilesachnummer) AND (tblStammdaten.indFOLand = qryMAstatus.indFOLand)
ORDER BY tblStammdaten.txtTeilesachnummer, tblStammdaten.indFOLand;

SQL for added Query
SELECT tblMessauftrag.txtTeilesachnummer, tblMessauftrag.indFOLand, Max(tblMessauftrag.datLieferterminSoll) AS MaxvondatLieferterminSoll, tblMessauftrag.indMessaufgabe, Last(tblMessauftrag.indStatus) AS LetzterWertvonindStatus, Max(tblMessauftrag.lngMessauftragNr) AS MaxvonlngMessauftragNr
FROM tblMessauftrag
GROUP BY tblMessauftrag.txtTeilesachnummer, tblMessauftrag.indFOLand, tblMessauftrag.indMessaufgabe
HAVING (((tblMessauftrag.indMessaufgabe)=3 Or (tblMessauftrag.indMessaufgabe)=4))
ORDER BY tblMessauftrag.txtTeilesachnummer;

Ridders, I need the Total in the query so I cannot just remore it.

 

[isladogs]

Arne seems to be offline so I'll answer

Looks like you already have the outer join.
Sometimes using SELECT DISTINCTROW will make it editable. Try it.
If not, you could remove the extra table and use a DCount formula to get the totals
It will then be editable but the DCount may make it slow to run

 

[shafara7]

I have done SELECT DISTINCT but nothing changes.
And I don't think I can replace the Totals with DCount because I am not using the Totals query to count the records, but to filter out some records. See photo Query - MA_Status.
I use this Query so that it will only show the Records with the latest dates for specific tasks (Task 3 Or 4 only).

 

[isladogs]

Wrong - try SELECT DISTINCTROW - its not the same thing

If that still fails, do the DCount on your query with the filter instead of the table

 

[shafara7]

I did the SELECT DISTINCTROW but also nothing changes.
I will try DCount and see if it works.

 

[shafara7]

I just figured a simpler way.
I created a new form with an 'Add' button to and write CurrentDB.Execute INSERT INTO codes to save the new records.
Thank you for your help.

 

[isladogs]

That's great.
Your own solutions are always the best solutions...