Query adds record to table?!?

[dantejazz]

I know that I have seen this on the forum prior - but, I just can't find it. I have a Form with a Combo Box (Student Name) and two Date/Time fields (Beginning Date and Ending Date) with all data stored on a Table (tbl_StudentGrades). The Form calls a Query (qry_GradesByStudent) based on the Student Name, Beginning Date and End Date selected to show grades for a date range.

Here is what baffles me: The process works perfectly! A resulting Datasheet shows everything correctly. However, each time the query runs, it ADDS a new "blank" record to the (tbl_StudentGrades) for the (Student Name) selected?!? I would love to turn this into a Report based on the Query results,...but, I'm still in the learning-curve.

Info


Pop Up - Yes
Modal - No
Default View - Single Form
Data Entry - No
Cycle - Current record

Nothing else has been modified.

I greatly appreciate any/all input on this!

Thx.

[jzwp11]

Welcome to the forum!

The form where you select the student and input the dates should not be bound to a table or query since you are just using the form to provide criteria for the query. BTW, how does the form call the query? Are you using a button?

[dantejazz]

jzwp11,

Thank you for the "Welcome" and for replying. I verified that the form is Unbound. And yes, after the Student Name is selected and the Begin Date and End Date are selected, I press a button to execute the query.

As I mentioned previously, the data returns perfectly in Datasheet view. But, adding a blank record to (tbl_StudentGrades)?!? My logic is that the Query should JUST return data and nothing else,...

Note: I have only been working with Access for about (2) months and really enjoy it! It isn't for work - just for enjoyment.

Thank you again.

[jzwp11]

My logic is that the Query should JUST return data and nothing else,...

It depends on the query. You are correct in that a SELECT query will just return existing data, but an append query will add records, so what is the SQL text of the query that runs after you provide the student & date info?

Are there any other events that run in conjunction with any of the controls on the form? I assume there is at least some code or macro that runs after you provide the student & date info in order to trigger the query. Can you provide that code/macro?

[dantejazz]

The Form button (RUN) has an Event action of:

On Click
ACTION = OpenQuery
Argument = qry_StudentGrades, Datasheet, Edit

The (qry_StudentGrades) executes:

In Column 1:
Field = Student Name
Table = tbl_StudentGrades
Sort = Ascending
Show = "checked
Criteria = [Forms]![Review Student Grades]![Student Name]

In Column 2:
Field = Date Added
Table = tbl_StudentGrades
Sort = <blank>
Show = "checked"
Criteria = Between [Forms]![Review Student Grades]![BeginningDate] And [Forms]![Review Student Grades]![EndingDate]

----------------------------------------------------------------------------------------------------------------------------------

FYI - I scrapped the Form and the Query and started over. Now it looks like the query runs WITHOUT adding an additional record to the (tbl_StudentGrades)! I have absolutely no idea what I did differently,...but,...

However, now the Report doesn't accept the parameters from the query and prints a 'blank' preview.

I do not want to waste any more of your time. I am hoping that this is a permanent fix. I will head over to the Reports forumn and start reading everything I can get my hands on.

I greatly appreciate your time!

Danté

[jzwp11]

However, now the Report doesn't accept the parameters from the query and prints a 'blank' preview.

Since the form is supplying the criteria to the query, you have to keep the form open for the report to have data. Also, since you scrapped the query, and I assume rebuilt it, did you name it differently. If so, you will have to correct the query name if the report's record source property.

By the way, it sounds like your original form was bound, and it was because of that that the report had data (and why a blank record was added).

[dantejazz]

jzwp11,

I wound up renaming everything except the (tbl_StudentGrades) and changed the Query as you indicated. The information is still correct in the Datasheet results. But, I'm fussing with the Report still - nothing prints.

I really enjoy doing the research. Steep learning curve - but, I wouldn't have it any other way.

(Note: Small world! I grew up 50 miles from Dayton in Medway, OH.)

Cheers!

Danté

[jzwp11]

If you want, I can take a look at your database. You can zip and post a copy; of course you will have to remove or alter any sensitive data.

[dantejazz]

jzwp11,

As I mentioned, I dumped everything and started over. This time I made sure that I was careful about Relationships, Bound and Un-bound criteria. So far - so good. I would like to take advantage of your offer to look at some of my work going forward. Some things can only be gained through experience.

Again, thank you.

*This can be coded as SOLVED.

Danté

[jzwp11]

You're welcome. Please post back when you are ready for us to take a look at your database.