Originally Posted by
kennethnaga
......Here is the code that I'm currently working on, it's definitely wrong but any suggestions or tips will be greatly appreciated.
OK, here are some of the things I noticed:
- There is a serious lack of comments in the code.
- You have an extremely poor naming convention.
-------------------
Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.
-------------------
About the object names:
[tbl_Sch1 Adjust] - has a space in the table name
[Reporting Year] - has a space in the field name
[Index&Facility] - has a special character in the field name
[Reporting Facility (Facility Name)] - has spaces and punctuation in the field name
- You know DCount() syntax is wrong.
- The DoCmd.OpenForm syntax is wrong
Code:
DoCmd.OpenForm "SubAdjustForm", acNormal, , SQLAdj, acFormEdit, acWindowNormal
"SQLAdj" is a SQL string. The "WHERE Condition" clause should be a where clause WITHOUT the Where, NOT the full SQL
- And speaking of SQLAdj, there are at least 5 syntax errors. (Same for SQL Annex)
I created a form and table for testing purposes.
If I take "SQLAdj" and convert it to one line, this is your SQL :
Code:
SQLAdj = "SELECT [tbl_Sch1 Adjust] FROM [tbl_Sch1 Adjust] WHERE tbl_Sch1 Adjust.[Index&Facility] '" & Me.IndexFacility & "' AND tbl_Sch1 Adjust.[Reporting Year] " = Me.ReportingYear
You cannot use the table name to select all fields. You must use
Code:
"SELECT * FROM [tbl_Sch1 Adjust]
For the 2nd and 3rd reference to "tbl_Sch1 Adjust", they are missing the brackets (required because of the space in the table name)
In the WHERE clause, an operator is missing between [Index&Facility] and Me.IndexFacility
Code:
WHERE tbl_Sch1 Adjust.[Index&Facility] '" & Me.IndexFacility &
In this part of the SQL
Code:
"AND tbl_Sch1 Adjust.[Reporting Year] " = Me.ReportingYear
the equals sign is to the right of the double quote when it should be to the left of the double quote and there is a missing ampersand BEFORE "Me.ReportingYear"
Here is the way the SQL should be formed:
Code:
SQLAdj = "SELECT * FROM [tbl_Sch1 Adjust] WHERE [tbl_Sch1 Adjust].[Index&Facility] = '" & Me.IndexFacility & "' AND [tbl_Sch1 Adjust].[Reporting Year] = " & Me.ReportingYear
Having fixed the variable "SQLAdj", let's look at the "Openform" command.
Instead of
Code:
DoCmd.OpenForm "SubAdjustForm", acNormal, , SQLAdj, acFormEdit, acWindowNormal
you should have
Code:
Dim AdjCriteria As String
AdjCriteria = "[tbl_Sch1 Adjust].[Index&Facility] = '" & Me.IndexFacility & "' AND [tbl_Sch1 Adjust].[Reporting Year] = " & Me.ReportingYear
DoCmd.OpenForm "SubAdjustForm", acNormal, , AdjCriteria, acFormEdit, acWindowNormal
EDIT: You have commands like
Code:
DoCmd.OpenForm "SubAdjustForm", acNormal, , , acFormAdd, acWindowNormal
In this case, "SubAdjustForm" in not a sub form.
So I am just curious about your definition of a sub form. A form becomes a sub form when it is embedded in another form.
If you open a form and drag & drop a different form on the first form, then you have a sub form. The 2nd form is contained in a "Sub Form" control.
Or if you add a sub form control to a form and you set the source object to an existing form name, you have a sub form.