Date added auto. in SubForm from MainForm

[Jody :o)]

Hello,

I homeschool my daughter and have a database to keep track of her attendance
and grades. It's setup so the attendance is the MainForm and the gradebook
is the subform. I enter only one date in for attendance and type in many
dates for the grades she's made for that day. I know you can setup the date
to be automatically inserted "Now()" for the day you enter information into
the forms, but is there a way to automatically have the date from the
MainForm (attendance) be entered automatically into each record in the
SubForm (gradebook) so I don't have to enter them everytime? I don't
normally enter grades in everyday so "Now()" wouldn't work for me. I
actually do not even want show the date in the Subform because it's not
necessary, but I still would like it to be entered into the table so I can do
specific queries for progress reports by dates.

I'd appreciate any information.

 

[strive4peace]

Now vs Date, Update query, form BeforeInsert event
---

Hi Jody,

firstly, hooray for you for homeschooling! I homeschool one of mine and
the difference in his attitude and learning is tremendous. If I could
do them both, I would -- but they are close in age and distract each
other too much.

you should be using Date(), not Now()

Date gives you JUST date whereas Now gives you date AND time -- which
you don't want because it will create problems when you want to generate
reports or use date criteria in queries

to correct records already entered:

1. make a new query based on your table (do this multiple times if you
have more than one table where you have used Now instead of Date)

2. change query to an UPDATE query instead of a SELECT query
from the menu --> Query, Update

3. put the date field on the grid

field --> DateFieldname
UpdateTo --> DateValue(DateFieldname)

4. Run the query

click the ! button
OR
from the menu --> Query, Run

back up your database before running action queries

~~~

is there a way to automatically have the date from the

MainForm (attendance) be entered automatically into each record in the
SubForm (gradebook) so I don't have to enter them everytime?

Yes

make sure the Date field is in the recordset for the subform -- it is
best to actually put it on the subform. If you don't want it to show,
make Visible --> No

on the BeforeInsert event for the subform you are collecting data:

~~~
if IsNull(me.parent.DateControlOnMainform) then
msgbox "You must enter date on the mainform before this record
can be created",,""Enter Date"
cancel = true
end if

me.DateControlOnSubform = me.parent.DateControlOnMainform
~~~

to help you understand Access a bit better, send me an email and request
my 30-page Word document on Access Basics (for Programming) -- it
doesn't cover VBA, but prepares you for it because it covers essentials
in Access.

Be sure to put "Access Basics" in the subject line so that I see your
message...
~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[John W. Vinson]

I homeschool my daughter and have a database to keep track of her attendance
and grades. It's setup so the attendance is the MainForm and the gradebook
is the subform. I enter only one date in for attendance and type in many
dates for the grades she's made for that day. I know you can setup the date
to be automatically inserted "Now()" for the day you enter information into
the forms, but is there a way to automatically have the date from the
MainForm (attendance) be entered automatically into each record in the
SubForm (gradebook) so I don't have to enter them everytime? I don't
normally enter grades in everyday so "Now()" wouldn't work for me. I
actually do not even want show the date in the Subform because it's not
necessary, but I still would like it to be entered into the table so I can do
specific queries for progress reports by dates.

I'd use Date() rather than Now() - Now() does NOT return today's date,
it returns the current date and time accurate to a few microseconds!

You can also use the Master Link Field and Child Link Field properties
of the Subform to have the value automatically linked and filled in.
What is the current master/child link field? What's the relationship
between the two tables?

John W. Vinson [MVP]

 

[Jody :o)]

Hello,

I'm not sure what master/child link fields are. The relationship between
the two fields is a field named AT-ID. Perhaps I'm in over my head?

Thanks for your help.

Jody )

 

[Jody :o)]

Hello,

First of all, thank you for the help. I have three children and I only
homeschool one of them because this is what's best for all of us for several
reasons.

Anyway, I tried pasting this in ...

Option Compare Database
Private Sub GB_Date_BeforeInsert(Cancel As Integer)

If IsNull(Me.Parent.DateControlOnMainform) Then
MsgBox "You must enter date on the mainform before this record "
can be created",,""Enter Date"
Cancel = True
End If

Me.DateControlOnSubform = Me.Parent.DateControlOnMainform

End Sub

And it turned red at can be created",,""Enter Date" Did I do something
incorrect?

By the way, may I ask how you keep track of your child's homeschooling
information? I can send you a copy of my database if you like. It might not
be anything that would work for you, but you could customize it. You
obviously know what to do with it.

Thanks again,

 

[strive4peace]

Hi Jody,

this was all supposed to be on one line, but it wrapped ... so I will
use line continuations so that is not a problem

MsgBox "You must enter date on the mainform " _
& "before this record can be created" _
,,""Enter Date"

now it is okay to be 3 lines


I appreciate the offer to share you database, Jody, but we have a
routine that works for us -- he prefers to do a course at a time instead
of bits of several classes each day, so tracking things is a lot easier.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[John W. Vinson]

Hello,

I'm not sure what master/child link fields are. The relationship between
the two fields is a field named AT-ID. Perhaps I'm in over my head?

O O O <<< tossing you liferings

Nah. Just climbing up the rocky slopes of the Access learning curve...
we've all been there!

What are the tables upon which the Mainform and Subform are based?

What is the Primary Key of each table?

The tables are related by joining AT-ID to AT-ID, I gather? I'd
suggest renaming the field, say to AT_ID with an underscore - Access
can get very strange about special characters in fieldname!

What are the names of the date fields in the two tables?

And if you view the Properties of your form, you can select the
Subform control (the box containing the subform, not the form within
that box) and view its Properties. If the wizard built it for you I
suspect that the master and child link field properties are both
[AT-ID] but you can look and let us know.

John W. Vinson [MVP]

 

[strive4peace]

Hi John,

"climbing up the rocky slopes of the Access learning curve...we've all
been there!"

I like it! Can I quote you?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hello,

I'm not sure what master/child link fields are. The relationship between
the two fields is a field named AT-ID. Perhaps I'm in over my head?

O O O <<< tossing you liferings

Nah. Just climbing up the rocky slopes of the Access learning curve...
we've all been there!

What are the tables upon which the Mainform and Subform are based?

What is the Primary Key of each table?

The tables are related by joining AT-ID to AT-ID, I gather? I'd
suggest renaming the field, say to AT_ID with an underscore - Access
can get very strange about special characters in fieldname!

What are the names of the date fields in the two tables?

And if you view the Properties of your form, you can select the
Subform control (the box containing the subform, not the form within
that box) and view its Properties. If the wizard built it for you I
suspect that the master and child link field properties are both
[AT-ID] but you can look and let us know.

John W. Vinson [MVP]

 

[John W. Vinson]

"climbing up the rocky slopes of the Access learning curve...we've all
been there!"

I like it! Can I quote you?

<g> Sure. I think I stole it from someone years ago myself...

John W. Vinson [MVP]

 

[Elena Lupu]

I'd use Date() rather than Now() - Now() does NOT return today's date,
it returns the current date and time accurate to a few microseconds!

You can also use the Master Link Field and Child Link Field properties
of the Subform to have the value automatically linked and filled in.
What is the current master/child link field? What's the relationship
between the two tables?

John W. Vinson [MVP]

 

[Jody :o)]

Hello,

First of all, sorry for the delay in getting back on this. My hubby decided
that was needed to tear a wall down in our home to make two rooms one. That
was quite a mess.

Thank you for your thoroughness in describing how to do things. I was able
to figure things out easily. And I took your advice and changed all my
fields to underscores.

The master and child link field properties are both
[AT_ID]

Thanks again,

The

Hello,

I'm not sure what master/child link fields are. The relationship between
the two fields is a field named AT-ID. Perhaps I'm in over my head?

O O O <<< tossing you liferings

Nah. Just climbing up the rocky slopes of the Access learning curve...
we've all been there!

What are the tables upon which the Mainform and Subform are based?

What is the Primary Key of each table?

The tables are related by joining AT-ID to AT-ID, I gather? I'd
suggest renaming the field, say to AT_ID with an underscore - Access
can get very strange about special characters in fieldname!

What are the names of the date fields in the two tables?

And if you view the Properties of your form, you can select the
Subform control (the box containing the subform, not the form within
that box) and view its Properties. If the wizard built it for you I
suspect that the master and child link field properties are both
[AT-ID] but you can look and let us know.

John W. Vinson [MVP]

 

[Jody :o)]

Hello Again Crystal,

I want to ask you if I did this correctly. First of all you understand that
I have a mainform which is attendance and have a subform in it that is the
gradebook, right? And I'm wanting to enter the attendance date in the
mainform and then enter all my daughter's subjects/grades for that day and
would like for the date to automatically be entered into each record I insert
for her grades.

That being said, this is what I understood that I needed to do.

1. I opened the mainform (it also has the subform already in it).
2. I right clicked on the date field (GB_Date) in the subform.
3. I clicked on Event Builder.
4. I chose Expression Builder.
5. I picked my subform GradeBook
6. I have this in VBA

Private Sub GB_Date_BeforeInsert(Cancel As Integer)


If IsNull(Me.Parent.DateControlOnMainform) Then

MsgBox "You must enter date on the mainform " _
& "before this record can be created" _
, , "'Enter Date"

Cancel = True
End If

Me.DateControlOnSubform = Me.Parent.DateControlOnMainform

End Sub



Is this correct? If I'm doing this correctly, am I forgetting something
else because it's not entering the date for me.

Thanks again,

 

[John W. Vinson]

First of all, sorry for the delay in getting back on this. My hubby decided
that was needed to tear a wall down in our home to make two rooms one. That
was quite a mess.

Thank you for your thoroughness in describing how to do things. I was able
to figure things out easily. And I took your advice and changed all my
fields to underscores.

The master and child link field properties are both
[AT_ID]

So? is it working OK?

John W. Vinson [MVP]

 

[Jody :o)]

Nope, I've not gotten it to work yet. I just started working on it again
today. Did you read my reply above in the thread?

Thanks,
Jody )

First of all, sorry for the delay in getting back on this. My hubby decided
that was needed to tear a wall down in our home to make two rooms one. That
was quite a mess.

Thank you for your thoroughness in describing how to do things. I was able
to figure things out easily. And I took your advice and changed all my
fields to underscores.

The master and child link field properties are both
[AT_ID]

So? is it working OK?

John W. Vinson [MVP]

 

[strive4peace]

Compile, check control names, verify valid date
---

Hi Jody,

it seems right

after
Cancel = True
you need
Exit Sub

before the End If

not that is matters, but you may want to change

"'Enter Date"
-->
"Enter Date"

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


this won't check controlnames on the mainform -- so that may be a
problem -- make sure you are using the NAME property of your mainform
date control and that what is in there is truly a date

You need to substitute your Names for DateControlOnMainform and
DateControlOnSubform


'~~~~
if not IsDate(Me.Parent.DateControlOnMainform) then
MsgBox Me.Parent.DateControlOnMainform _
& " is not a valid date" _
, , "Enter Date"
Cancel = True
Exit sub
End If

Me.DateControlOnSubform = Me.Parent.DateControlOnMainform
'~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

Hi Jody,

I see in another post that you got it to work --- great!

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Jody :o)]

Thank you for your help and time, Crystal.

Jody )

 

[strive4peace]

you're welcome, Jody happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*