Access Subforms

R

rigoutsosp

New member

Local time

Today, 13:05

Joined

Mar 11, 2011

Messages

8

• Mar 11, 2011

• #1

I have created three forms and three subforms in sequence also three tables i.e.

Tables : Year-YearID, Quarter-QuarterID, Date-DateID

Α) Form Year and field YearID, Year Subform and relationship on YearID
VB code of Year subform:

Code:

[/FONT]
[FONT=Arial]Private Sub ViewRptForm()[/FONT]
[FONT=Arial]   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70[/FONT]
[FONT=Arial]   DoCmd.OpenForm "Quarter", , , , acEdit[/FONT]
[FONT=Arial]End Sub[/FONT]

B) Form Quarter and field QuarterID, Quarter Subform and relationship on QuarterID
VB Code on Quarter Form:

Code:

[/FONT]
[FONT=Arial]Private Sub Form_Activate()[/FONT]
[FONT=Arial]On Error GoTo Err_Form_Activate[/FONT]
[FONT=Arial][/FONT] 
[FONT=Arial]Me.Requery[/FONT]
[FONT=Arial][/FONT] 
[FONT=Arial]DoCmd.Maximize[/FONT]
[FONT=Arial][/FONT] 
[FONT=Arial]If IsLoaded("Year") Then[/FONT]
[FONT=Arial][/FONT] 
[FONT=Arial]   If Forms![Year]![Year Subform].Form.RecordsetClone.RecordCount > 0 Then[/FONT]
[FONT=Arial]      DoCmd.GoToControl "QuarterID"[/FONT]
[FONT=Arial]      DoCmd.FindRecord Forms![Year]![Year Subform].Form![QuartertID][/FONT]
[FONT=Arial]   End If[/FONT]
[FONT=Arial]End If[/FONT]
 
[FONT=Arial]Exit_Form_Activate:[/FONT]
[FONT=Arial]   Exit Sub[/FONT]
 
[FONT=Arial]Err_Form_Activate:[/FONT]
[FONT=Arial]   MsgBox Err.Description[/FONT]
[FONT=Arial]   Resume Exit_Form_Activate[/FONT]
[FONT=Arial]End Sub[/FONT]

VB Code on Quarter Subform

Code:

[/FONT]
[FONT=Arial]Private Sub ViewRptForm()[/FONT]
[FONT=Arial]   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70[/FONT]
[FONT=Arial]   DoCmd.OpenForm "Date", , , , acEdit[/FONT]
[FONT=Arial]End Sub[/FONT]

C) Form Date and field DateID, Date Subform and relationship on DateID
VB Code on Date Form:

Code:

[/FONT]
[FONT=Arial]Private Sub Form_Activate()[/FONT]
[FONT=Arial]On Error GoTo Err_Form_Activate[/FONT]
[FONT=Arial]   Me.Requery[/FONT]
[FONT=Arial][/FONT] 
[FONT=Arial]   DoCmd.Maximize[/FONT]
[FONT=Arial]   [/FONT]
[FONT=Arial]If IsLoaded("Quarter") Then[/FONT]
[FONT=Arial]   If Forms![Quarter]![Quarter Subform].Form.RecordsetClone.RecordCount > 0 Then[/FONT]
[FONT=Arial]      DoCmd.GoToControl "DateD"[/FONT]
[FONT=Arial]      DoCmd.FindRecord Forms![Quarter]![Quarter Subform].Form![DateID][/FONT]
[FONT=Arial]   End If[/FONT]
[FONT=Arial]End If[/FONT]
 
[FONT=Arial]Exit_Form_Activate:[/FONT]
[FONT=Arial]   Exit Sub[/FONT]
 
[FONT=Arial]Err_Form_Activate:[/FONT]
[FONT=Arial]   MsgBox Err.Description[/FONT]
[FONT=Arial]   Resume Exit_Form_Activate[/FONT]
[FONT=Arial]End Sub[/FONT]

On Year Subform I point a record and then I open the Quarter Form at the selected record.
On Quarter Subform I point a record and then I open the Date Form.

Problems I face are:
a) Date Form is opened on the first record and not on the selected one.
b) When I try to open a new record on the Date Form pops up a message that record cannot be saved because is required to be recorded in the Quarter Table.


Please let me know where I am wrong bearing in mind that I am not well trained in Access.

Thanks a lot

 

Last edited by a moderator: Mar 11, 2011

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 20:05

Joined

Jan 20, 2009

Messages

12,895

• Mar 12, 2011

• #2

Firstly I implore you to change the names of those forms. Date and Year are both reserved words.

To open a form at a particular record, use the Where argument of the OpenForm Method.

I expect the Date form won't let you add records because of the referential integrity between the DateTable and the QuarterTable.

However your table structure seems very unlikely. It sounds like you should be simply working with dates in a single table. Access has functions for determining the components such as Year, Month and Quarter (and even WeekDay) directly from a date.

 

R

rigoutsosp

New member

Local time

Today, 13:05

Joined

Mar 11, 2011

Messages

8

• Mar 14, 2011

• #3

Dear GalaxiomAtHome,

Thank you for your reply. Actually I am not working with dates but I presented the simpliest edition.
I try to create a database with various fields to input various expenses for a school i.e.
School bus fuel
Wages
Electricity
Insurance etc.

All these expenses should be reported quarterly in the following format for a specific year:
Item 1stQuarter 2ndQuarter 3rdQuarter 4th Quarter Totals
School bus fuel
Wages
Electricity
Insurance
Etc

In order to get this out I thought I could start on a form that allows me to choose the year,
Then to open another form to choose the quarter and finally to open another form with the specific date so I can input the costs.

Then names of the form I quote in my original message are indicatively and not the real names so to assist to any one to give a solution.
Thanks
rigoutsosp

 

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 20:05

Joined

Jan 20, 2009

Messages

12,895

• Mar 14, 2011

• #4

Why not simply enter a date against the expense record?

You seem to be confusing storage, entry and reporting functionality. Your reports will use the target period to retreive the records that match the required period.

I think you need to study normalization before you go any further.

 

R

rigoutsosp

New member

Local time

Today, 13:05

Joined

Mar 11, 2011

Messages

8

• Mar 14, 2011

• #5

I tried to work on this way as I do not know how I could extract the report by quarter. As I mentioned in my original message I am not an expert therefore my luck of knowledge drives me through complicated plans.
I appreciate if you let me know which way I could report per quarter. Is the DatePart useful in this case and if so, how I can implement it.
Thanks a lot

 

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 20:05

Joined

Jan 20, 2009

Messages

12,895

• Mar 14, 2011

• #6

Dates can be parsed using the DatePart function.

Use a recordsource query for the report that selects the year using:
Year([datefield])

and the quarter using DatePart
DatePart("q", [datefield])

Put these expressions into the Field cell of a column in the query designer and set the criteria as appropriate.

 

R

rigoutsosp

New member

Local time

Today, 13:05

Joined

Mar 11, 2011

Messages

8

• Mar 14, 2011

• #7

The solution you sent me few minutes ago, solved my problem.
Thank you very much for your great assistance.

 

R

rigoutsosp

New member

Local time

Today, 13:05

Joined

Mar 11, 2011

Messages

8

• Mar 15, 2011

• #8

Here I am again,

While trying to build a query I type
Expr1: DatePart("m",[ExpenseDate])

after I press Enter it shows Error

I type
Expr1: DatePart("m";[ExpenseDate])
but it turns to
Expr1: DatePart("/m";[ExpenseDate])
and does not work

Any though to solve this problem?

PS I work on Access 2007 and I have tried to work in Access 2002-2003 but still same problem

 

boblarson

Smeghead

Local time

Today, 03:05

Joined

Jan 12, 2001

Messages

32,059

• Mar 15, 2011

• #9

You don't say where you are located so I can't be certain about which delimiter to use (semi-colon or comma) but this should work:

Expr1: DatePart("m",[DateField])

or if you are in a region which uses semi-colons instead of commas:

Expr1: DatePart("m";[DateField])

If it isn't working right, your database may be suffering from some corruption. Make a copy just in case anything goes wrong and then do a DECOMPILE and then Compact and Repair.

 

R

rigoutsosp

New member

Local time

Today, 13:05

Joined

Mar 11, 2011

Messages

8

• Mar 16, 2011

• #10

I Normally work with semi-colon rather than commas.
I have test this on another access file that is a Demo, but still does not work.

Attached is the relevant file

 

Attachments

• MonthSelectDemo.mdb

  220 KB · Views: 139

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 20:05

Joined

Jan 20, 2009

Messages

12,895

• Mar 16, 2011

• #11

rigoutsosp said:

I Normally work with semi-colon rather than commas.
I have test this on another access file that is a Demo, but still does not work.

Attached is the relevant file

Click to expand...

A very familiar demo.

Once I took the backslash out of the ControlSource expression it worked fine again for me (Australia). There must be something funny going on that it would put that slash into the expression.

The slash is entered automatically to indicate the following character is literal when the letter is not one of the options. For example if I type an x the slash is inserted automatically. In some functions this makes sense but just creates an error in this one.

What region is your computer set to? Maybe it has different letters for month etc?

 

R

rigoutsosp

New member

Local time

Today, 13:05

Joined

Mar 11, 2011

Messages

8

• Mar 16, 2011

• #12

My computer is set for Greece. Thanks to your comments I have tried to replace the "m" with the relevant letter of the Greek character and works fine.

Thanks a lot

 

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 20:05

Joined

Jan 20, 2009

Messages

12,895

• Mar 16, 2011

• #13

Curious now. How different is the Greek version?
I guess object properties and methods have different names in VBA too?

Clearly just on the semicolon versus comma issue alone it is essential to compile code before even thinking about running it in another region.

 

R

rigoutsosp

New member

Local time

Today, 13:05

Joined

Mar 11, 2011

Messages

8

• Mar 16, 2011

• #14

So far I have seen no difference in VBA from an English Version appart this case. All are in Greek appart the VBA that is in English.