I think I have a problem with my query

[Phil]

I have the following query:

SELECT [Attendance].[Attended], [Attendanceform].[AttendeeFirstName],
[Attendanceform].[AttendeeLastName], [Attendance].[AttendanceDate],
[Attendanceform].[EventName], [Attendanceform].[Daynumber],
[Attendanceform].[Event ID], [AttendeeFirstName] & " " & [AttendeeLastName]
AS Name
FROM Attendance INNER JOIN Attendanceform ON
([Attendance].[ID]=[Attendanceform].[ID]) AND
([Attendance].[AttendeeID]=[Attendanceform].[AttendeeID])
WHERE ((([Attendanceform].[Daynumber])=[Forms]![Events]![DaySelect]));

This does work and get the data I want, however when I try to base a form on
this query I cannot use the form for dat entry it displays the form with data
correctly but as soon as i change the property to data entry the form goes
blank

hope you can help if you need more info please say

thanks

Phil

 

[tina]

try changing your INNER JOIN to a LEFT JOIN. if this doesn't work, try
reversing the join to a RIGHT JOIN.

hth

 

[John Spencer]

If you set the form's data entry property to true it is going to show a new
record (and only a new record).

If you run the query, does it allow you to add a record in the grid view? If
so, it is updateable. If not, then we need to work with the query.

Hi Tina

Thanks for your speedy reply. I have tried both your suggestions and the
query is still not updateable, looking at it I thought it was something
stupid I had done, more likely I have to try a different route to the data I
want. thanks again

Phil

try changing your INNER JOIN to a LEFT JOIN. if this doesn't work, try
reversing the join to a RIGHT JOIN.

hth

I have the following query:

SELECT [Attendance].[Attended], [Attendanceform].[AttendeeFirstName],
[Attendanceform].[AttendeeLastName], [Attendance].[AttendanceDate],
[Attendanceform].[EventName], [Attendanceform].[Daynumber],
[Attendanceform].[Event ID], [AttendeeFirstName] & " " & [AttendeeLastName]
AS Name
FROM Attendance INNER JOIN Attendanceform ON
([Attendance].[ID]=[Attendanceform].[ID]) AND
([Attendance].[AttendeeID]=[Attendanceform].[AttendeeID])
WHERE ((([Attendanceform].[Daynumber])=[Forms]![Events]![DaySelect]));

This does work and get the data I want, however when I try to base a form on
this query I cannot use the form for dat entry it displays the form with data
correctly but as soon as i change the property to data entry the form goes
blank

hope you can help if you need more info please say

thanks

Phil

 

[OfficeDev18 via AccessMonster.com]

When you change the form's Data Entry property to "Yes", you are telling
Access that you want to use the form for new record entry ONLY. That's why
you can't see records that are already entered; Access is giving you a blank
record to enter. Change the property back to "No" and you will be able to
both view old records and enter new ones. To enter new records, click on the
far right box of the record navigation bar on the bottom of the form (the one
with the star in it), and Access will go to the bottom of the recordset and
allow you to enter new data.

Hope this helps,

Sam

I have the following query:

SELECT [Attendance].[Attended], [Attendanceform].[AttendeeFirstName],
[Attendanceform].[AttendeeLastName], [Attendance].[AttendanceDate],
[Attendanceform].[EventName], [Attendanceform].[Daynumber],
[Attendanceform].[Event ID], [AttendeeFirstName] & " " & [AttendeeLastName]
AS Name
FROM Attendance INNER JOIN Attendanceform ON
([Attendance].[ID]=[Attendanceform].[ID]) AND
([Attendance].[AttendeeID]=[Attendanceform].[AttendeeID])
WHERE ((([Attendanceform].[Daynumber])=[Forms]![Events]![DaySelect]));

This does work and get the data I want, however when I try to base a form on
this query I cannot use the form for dat entry it displays the form with data
correctly but as soon as i change the property to data entry the form goes
blank

hope you can help if you need more info please say

thanks

Phil

 

[Chris O'C via AccessMonster.com]

I cannot use the form for dat entry it displays the form with data
correctly but as soon as i change the property to data entry the form goes
blank

I'm not sure I understand what your question is because you have conflicting
statements in a subsequent post. In this post, you've stated that when you
change to data entry the form goes blank, as it is designed to (see the Help
Topic, "Prevent a data-entry form or page from showing existing records").
If your problem is that you don't want it to be blank, then the problem is
solved by setting the form's "Data Entry" Property to No.

You also mentioned that the query isn't updateable after trying Tina's
suggestions. By guessing at your table structures, it appears there are some
very serious problems with them, but I'd rather give you the benefit of the
doubt. Please give us the SQL statements of your table structures, including
primary and foreign keys, and some data we can test with, and we'll try to
duplicate what you have and advise you on how to fix it.

For example:

CREATE TABLE Attendance
(
ID Long NOT NULL,
AttendeeID Long NOT NULL,
Attended Text (10) NOT NULL,
CONSTRAINT PrimaryKey PRIMARY KEY (ID, AttendeeID)
);

 

[Phil]

Thank you all for your replies:

sorry I did not understand that was what the data entry property was for.
The form opened completely blank no fields so could not enter any data. The
query was not updatable, so I did another simpler query to get the
information I needed this now works. I hope I dont have problems with my
table structure, I have posted my table structure a number of times but not
the SQL (where can I get this from access or do I need to type it myself)

thanks again

Phil