Help with subform in access 97

  • Thread starter Thread starter Sei
  • Start date Start date
S

Sei

I've created a form with a subform. The subform contains a field with
dates...what I want to do is to only display the records with dates in
the current year in the subform. I cant figure it out using a macro.
I'm able to do it if I use 2 separate forms (not a subform)....can
anyone help? Thanks.


For example:
Main form is for Client1 detail info (name, address), subform contains
Client1's expenses for 2003-2005

I dont want to view all the expenses in 2003-2004, I want to have an
option to only view the current year in the subform. If I first open
the Main form, I want it to display the current year's expenses in the
subform, but I also want to be able to change the view for previous
year.

I want to maintain only 1 form w/a subform, I dont want to create
multiple forms w/subforms for each year. Pls help. Thanks.
 
Sei said:
I've created a form with a subform. The subform contains a field with
dates...what I want to do is to only display the records with dates in
the current year in the subform. I cant figure it out using a macro.
I'm able to do it if I use 2 separate forms (not a subform)....can
anyone help? Thanks.


For example:
Main form is for Client1 detail info (name, address), subform contains
Client1's expenses for 2003-2005

I dont want to view all the expenses in 2003-2004, I want to have an
option to only view the current year in the subform. If I first open
the Main form, I want it to display the current year's expenses in the
subform, but I also want to be able to change the view for previous
year.

I want to maintain only 1 form w/a subform, I dont want to create
multiple forms w/subforms for each year. Pls help. Thanks.
Click to expand...

In the query that is used by the subform add a calculated field...

RecordYear: Year(CurrentDateField)

Then add a control to your main form where you select the desired year. Use the
new field and control as the ChildLink and MasterLink properties.
 
Rick Brandt said:
RecordYear: Year(CurrentDateField)

Then add a control to your main form where you select the desired year. Use the
new field and control as the ChildLink and MasterLink properties.
Click to expand...


Thank you so much. It worked!! I now know how to change the
LinkChildField and LinkMasterField. Thank you!!!
 
I have a new problem. I solved the issue about creating 2 links on
the subform but now, when focus is in the subform and I tried to enter
new data on the blank row, it gives me an error message "Field can't
be updated"

I made sure the properties are:
ENABLE = yes
ALLOW EDITS = yes
ALLOW DELETIONS = yes
ALLOW ADDITIONS = yes
Data Entry = no
Record Locks = no locks

and no one is using the database but me at the time.

I guess it's not much of a problem since if I click OK on the error
message box, it automatically insert a new row where I could enter my
data. But I dont get the error message when i only have 1 linked
object.

here's what link properties look like:
LinkChild: AcctNum;Year
LinkMaster: AcctNum;EnterYear

Year - is from the query to display only the specified year in the
subform based on the year entered in "EnterYear" control (textbox)
field on the main form.

Thanks.
 
(e-mail address removed) (Sei) wrote in message news:<[email protected]>...
Click to expand...
I have a new problem. I solved the issue about creating 2 links on
the subform but now, when focus is in the subform and I tried to enter
new data on the blank row, it gives me an error message "Field can't
be updated"

...
I guess it's not much of a problem since if I click OK on the error
message box, it automatically insert a new row where I could enter my
data. But I dont get the error message when i only have 1 linked
object.

here's what link properties look like:
LinkChild: AcctNum;Year
LinkMaster: AcctNum;EnterYear
Click to expand...

I'd suggest changing the name of the table field Year in the child
table. It's a reserved word and Access can do some very peculiar
things when it can't figure out if you mean the field named [Year] or
the function Year().

John W. Vinson[MVP]
 
John Vinson said:
here's what link properties look like:
LinkChild: AcctNum;Year
LinkMaster: AcctNum;EnterYear
Click to expand...

I'd suggest changing the name of the table field Year in the child
table. It's a reserved word and Access can do some very peculiar
things when it can't figure out if you mean the field named [Year] or
the function Year().

John W. Vinson[MVP]
Click to expand...

Thanks for the suggestion...but it didnt work. I changed the field
name from Year to VYear (I hope this is not another reserved word) and
it still gives me the same error message. Please help. Thank.
 
I have a new problem. I solved the issue about creating 2 links on
the subform but now, when focus is in the subform and I tried to enter
new data on the blank row, it gives me an error message "Field can't
be updated"

I made sure the properties are:
ENABLE = yes
ALLOW EDITS = yes
ALLOW DELETIONS = yes
ALLOW ADDITIONS = yes
Data Entry = no
Record Locks = no locks

and no one is using the database but me at the time.

I guess it's not much of a problem since if I click OK on the error
message box, it automatically insert a new row where I could enter my
data. But I dont get the error message when i only have 1 linked
object.

here's what link properties look like:
LinkChild: AcctNum;Year
LinkMaster: AcctNum;EnterYear

Year - is from the query to display only the specified year in the
subform based on the year entered in "EnterYear" control (textbox)
field on the main form.
Click to expand...

Which control is generating the error? Does EnterYear have a Control
Source property?


John W. Vinson[MVP]
 
Back
Top