Entering data using a partial prefilled form

[June7]

Can each student have more than one class? If so, your data structure looks non-normalized. Student name/ID/password would have to be entered for each class. I assume student has only one ID/password. Class/Section/Course should be in Grade table. Student table should have unique record for each student (name, birthdate, address, phone, etc).

Have you looked at the Access 2007 student database template? http://office.microsoft.com/en-us/te...001225355.aspx

[stevehoyle]

I have a query that works fine. It solicits date parameters, I enter then and it retrieves based on dates. I might use it 2 or 3 times, or close the data base. When I go back into the Query it now solicits the parameters twice. It asks for the from date, I enter it, it asks for the to date, I enter it and it immediately asks for the from date again, the to date again - if I enter them it works fine, if I don't enter them it doesn't get. Thanks

[June7]

Time to provide db for analysis? Follow instructions at bottom of my post. Some dummy records can be helpful.

[stevehoyle]

This the DB

[June7]

Don't remember ever encountering this issue. Remove the criteria from QryDailyTotals and it goes away. I don't use dynamic parameters in queries, I use VBA to set filters of forms and reports. Especially don't like popup inputs because can't validate the entry.

Sorry, can't make the repeated popups stop.

Create a form for input of criteria. Reference the form controls as inputs. No popup prompts and the query should run.

Consider building a report with Grouping & Sorting instead of GROUP BY query. The report allows display of detail data as well as summary calcs.

[stevehoyle]

OK, thanks. I am building the form and all parameters will by controls at the top of the form.

[stevehoyle]

Thanks for all your help. I’m close to completing this, but I can’t solve this last problem.


I execute the form. I fill in the unbound controls in the form header. The following code writes the records to the ADJUSTMENTS table.
----------------------------------------
Private Sub cmdEnter_Click()
Dim strSQL As String
strSQL = "INSERT INTO Adjustments(AdjStuID, AdjStfInits, AdjDate) " & _
"SELECT StuID, """ & Me.cboAdjStfInits & """, #" & _
Format(Me.txtAdjDate, "yyyy-mm-dd") & "# " & _
"FROM Students WHERE (StuClass = """ & Me.cboStuClass & """ OR " & _
IsNull(Me.cboStuClass) & ") AND (StuSection = """ & Me.cboStuSection & """ OR " & _
IsNull(Me.cboStuSection) & ") AND (StuID = """ & Me.cboStuID & """ OR " & _
IsNull(Me.cboStuID) & ")"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
Me.Requery
End Sub
-------------------------------------------------------
The correct subset of records are written to ADJUSTMENTS based upon whatever was entered for Class and Section in the Form Header. Then the form does the requery. The requery executes this SQL code, which I defined in a query and specified this query as the Record Source in my form.
----------------------------------------------------------
PARAMETERS Forms!EnterAdjustments!txtAdjDate DateTime, Forms!EnterAdjustments!cboStfInits Text ( 255 );
SELECT ADJUSTMENTS.*, STUDENTS.*
FROM ADJUSTMENTS INNER JOIN STUDENTS ON ADJUSTMENTS.AdjStuID=STUDENTS.StuID
WHERE (StuClass=Forms!EnterAdjustments!cboStuClass Or Forms!EnterAdjustments!cboStuClass Is Null) And (StuSection=Forms!EnterAdjustments!cboStuSection Or Forms!EnterAdjustments!cboStuSection Is Null) And (StuID=Forms!EnterAdjustments!cboStuID Or Forms!EnterAdjustments!cboStuID Is Null) And AdjDate=Forms!EnterAdjustments!txtAdjDate And AdjStfInits=Forms!EnterAdjustments!cboStfInits;
----------------------------------------------------------------
These are the tables defs.

STUDENTS
StuID	Text
StuLName	Text
StuFName	Text
StuClass	Text
StuSection	Text
ADJUSTMENTS
AdjStuID	Text
AdjStfInits	Text
AdjDate	Date/Time
AdjEng	Number
AdjRespon	Number
AdjBehav	Number
AdjRespect	Number
AdjPunc	Number
AdjCommit	Yes/No
STAFF
StfInits	Text
StfLName	Text
StfFName	Text

---------------------------------------------------
This is what happens:

If I have the query open, and the form closed, and I switch to Datasheet view for the query, it prompts me for the parameters that I was retrieving from the form. I type them in and then it works, the query displays those records from the ADJUSTMENTS table that match the parameters I had typed in when prompted. However, if I execute the query with the form open, it doesn’t prompt me for the parameters and no data is displayed in the rows.

Then, if I close the query (even if I keep it open) and I then open the form, fill in the parameters in the form header and write to the ADJUSTMENTS table, and then issue the requery from the form, nothing happens (but the correct data was written to ADJUSTMENTS). There is no data displayed in the rows, and no errors. It is as if the parameters are all blank when the query executes, as if I can't retrieve the parameters in the Query SQL code when referencing them by the form name and parameter name. Everything is spelled correctly, I don't get any compile errors or run time errors, but it seems as if these form parameters cannot be referenced in the query.


Any ideas?

[stevehoyle]

I tried one last step. I ran the form and entered into the control fields. Nothing showed. But this time I left the form open, with the control boxes filled in, and then opened the query in datasheet. Everything showed! So it isn't a problem with cross communication, it is that my requery in the form doesn't execute. Not even when I also defined it on the After Update. Here is the code again. Do you see any problem with the requery?

Option Compare Database

Private Sub cmdEnter_Click()

Dim strSQL As String

strSQL = "INSERT INTO Adjustments(AdjStuID, AdjStfInits, AdjDate) " & _
"SELECT StuID, """ & Me.cboStfInits & """, #" & _
Format(Me.txtAdjDate, "yyyy-mm-dd") & "# " & _
"FROM Students WHERE (StuClass = """ & Me.cboStuClass & """ OR " & _
IsNull(Me.cboStuClass) & ") AND (StuSection = """ & Me.cboStuSection & """ OR " & _
IsNull(Me.cboStuSection) & ") AND (StuID = """ & Me.cboStuID & """ OR " & _
IsNull(Me.cboStuID) & ")"

Debug.Print strSQL

CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

End Sub

[June7]

Maybe need field names in the SELECT subquery:

...
SELECT StuID, '" & Me.cboStfInits & "' AS SI, #" & Me.txtAdjDate & "# AS Dte " & _
FROM ...

Why using Format function for the date?

Also, referencing the controls with IsNull as criteria is nonsense.

FROM Students WHERE (StuClass = '" & Me.cboStuClass & " OR [StuClass] Is Null) " & _
"AND (StuSection = '" & Me.cboStuSection & "' OR [StuSection] Is Null) " & _
"AND (StuID = '" & Me.cboStuID & "' OR [cboStuID] Is Null)"

Are StuClass, StuSection, StuID text type fields? If not, remove the delimiters (I prefer apostrophe as text delimiter).

[stevehoyle]

I know that everyone has different styles, but if each style works I don't think that would be my problem. The reason I do not suspect the SQL code in the Query is because if I open the Query in Datasheet view it will prompt me for the parameters identified in the code. I supply them and the table is then displayed; the code works fine, only returning those records that matched the criteria that I supplied. If the Form is open, and the controls in the header have been populated, I leave the Form open. I then open the Query in Datasheet. This time it doesn't prompt me for the parameters (because it can get them all from the Form Header) and it works fine, getting the correct records. So I don't think it is productive to change the SQL code in the Query when it always works. The problem is when I try to open the form and have the form display the data that the Query SQL code was retrieving. Nothing gets displayed, even after issuing a me.requery in the form. Thanks

[June7]

Regardless of what happens when opening the query directly and responding to the popups, the SQL structure is still wrong and is why it doesn't work in the form.

Referencing the controls with IsNull as criteria is still nonsense. Criteria must be applied to fields in query, not controls on form. Controls provide the parameter values. The criteria should be if the field value Is Null, not the control on form. However, I did not have the Is Null syntax correct and have fixed the post.

[stevehoyle]

June, I'm still confused. StuSection, StuClass, and StuID can never be null. They are required fields in the table. The only items that can be null are the form controls, they can be optionally filled in on the form by the user depending on how restrictive they want to be when viewing the records. And you say "regardless of what happens when the opening the Query..". I don't see how that doesn't matter - if the code was defective as your saying then it would not work - but it does. When running the query by itself it has no bearing on the form when I supply the parameters. Also, the code taht test for Null on the controls was supplied by a major contributor from the Microsoft Community Forum. I probably shouldn't have labeled it "different styles", the Stu... fields can never be Null, and the Form Controls for Class and Section can be. So, I do think that is how the check for Null does have to be done, and so did the MS contributor, and as I said, that code returns the data when the query runs directly. And it works properly when either class or section or both are null on the form controls. My problem is I can't figure out why the Form doesn't display the data that the query is Selecting. I don't see how it is the query SQL code when I do see that table properly displayed using the code that you are saying won't work. And, again, I can't test for the fields you are saying to test for null, they are never null.

[stevehoyle]

Solved! The gentleman from the Microsoft Community forum thought of it. I never would have found this. He said

"One thing has occurred to me. Is the form's DataEntry property True (Yes)? It should be False (No). Whoever named this property seems to have had a somewhat eccentric take on the semantics of the English (or American) language. It actually only allows the entry of new records, so if it's True, it won't show any records when the form is requeried."

I saw that, but the user was going to enter data on the form so I never would have thought to make this field No. Thanks everyone.

[June7]

Well that really does explain a lot! A common mistake, wish I had thought to ask about that property setting. I know it is has been the bane of many novice.

And yes, if the fields absolutely will never be null then the Is Null criteria is irrelevant.

However, you might want LIKE operator and wildcards in the criteria. That will allow records to be retrieved if any of the controls are left empty:

FROM Students WHERE StuClass LIKE '" & Me.cboStuClass & "*' AND StuSection LIKE '" & Me.cboStuSection & "' AND StuID LIKE '" & Me.cboStuID & "*'"