Please help

[mjones]

You'll need to use a macro or (I'd prefer) VBA code in the AfterUpdate event
of the "master" GraduationDate (the one bound to tClass.GraduationDate) set
the other textbox. Guessing that tGraduateList is edited on a subform named
subGraduateList  and tClass is on the mainform, the code would be something
like

Private Sub GraduationDate_AfterUpdate()
If IsNull(Me!subGraduateList.Form!GraduationDate) Then
   Me!subGraduateList.Form!GraduationDate) = Me!GraduationDate
End If
End Sub

Of course you'll need to adjust the control names to your own.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Hi John,

Good to hear from you again. Ah ha. Interesting. And no wonder I
couldn't figure it out - it needs VBA and although I'm looking forward
to it I haven't gotten to that book yet- should get the basics down
first.

It seems I need to get better at detailing fundamental information
when I post. I'll try to do better.

A couple of notes to be sure before I try your idea. There isn't a
subform. GraduationDate object is straight on the fGraduateList form
- the one to default from tClass.GraduateDate and update if needed.

The date can't change in tClass, which looks to be what you suggest,
or everyone in the class will have their grad dates changed. It only
changes for a student who misses a day and has a make up (grad date)
day later, thus having the date again in the tGraduateList because it
could be different that the standard tClass.GraduateDate.

The "master" GraduationDate (the one bound to tClass.GraduationDate),
is only shown on a form and can't be edited. The data is, as of yet,
only typed into the table.

GraduationDate in both tables is data type date/time Long Date.

When you guess tClass is on the main form (the only form), I'm
guessing you're looking to see if the fGraduateList record source can
find it. The record source for fGraduateList is currently set to
SELECT tGraduateList.* FROM tGraduateList;. This is the record source
we've be playing around trying to update.

Hopefully that clears up some assumptions (to us project managers -
risks) and we can figure this out.

Have a great day!

Michele

 

[Phil Hunt]

It is hard to give advice for Access over email, because Access is visual
for the moast part. But I thought I would ask you a question before you go
into coding (you should not be doing a lot of that).

From what I can see, you need 3 tables at least

Courses
Students
Enrollment

The Enrollment is the junction table that tie the Courses and Students. So
you end up with a many-to-many relation between Courses and Students. You
have to understand this 'Many to many' first, otherwise you will just spin
the wheels forever.

 

[mjones]

It is hard to give advice for Access over email, because Access is visual
for the moast part. But I thought I would ask you a question before you go
into coding (you should not be doing a lot of that).

From what I can see, you need 3 tables at least

Courses
Students
Enrollment

The Enrollment is the junction table that tie the Courses and Students. So
you end up with a many-to-many relation between Courses and Students. You
have to understand this 'Many to many' first, otherwise you will just spin
the wheels forever.

Hi Phil,

Thanks for the advise. Actually, it's like this:

tClient (student contact info; some are lost prospects) has ID(unique
student#)
tClass (class/course info) has ClassCode and GraudationDate(unique to
each ClassCode)
tGraduateList (course certificate info) has ClientID (links with ID in
tClient) and GraduateDate (similar to tClass.GraduateDate, but it
might be changed for an individual student in the class who misses a
day so needs to be tracked separately)

Many tClient.IDs can take many tClass.ClassCodes and many
tGraduateList.CertificateNos can be issued to many tClass.ClientIDs
(students matching to tClient.IDs). Not really sure if there's a
junction table there because I think that's were there's a middle
table between two tables and this is more of a case where
tGraduateList gets some info from tClient and some from tClass.

Thanks. Every bit helps.

Michele

 

[Phil Hunt]

Your tGraduateList should be junction table. It should also has a ClassCode.
Otherwise how do you know which class the student graduate from. I am
asumming 1student can take more than 1 class.

It is hard to give advice for Access over email, because Access is visual
for the moast part. But I thought I would ask you a question before you go
into coding (you should not be doing a lot of that).

From what I can see, you need 3 tables at least

Courses
Students
Enrollment

The Enrollment is the junction table that tie the Courses and Students. So
you end up with a many-to-many relation between Courses and Students. You
have to understand this 'Many to many' first, otherwise you will just spin
the wheels forever.

Hi Phil,

Thanks for the advise. Actually, it's like this:

tClient (student contact info; some are lost prospects) has ID(unique
student#)
tClass (class/course info) has ClassCode and GraudationDate(unique to
each ClassCode)
tGraduateList (course certificate info) has ClientID (links with ID in
tClient) and GraduateDate (similar to tClass.GraduateDate, but it
might be changed for an individual student in the class who misses a
day so needs to be tracked separately)

Many tClient.IDs can take many tClass.ClassCodes and many
tGraduateList.CertificateNos can be issued to many tClass.ClientIDs
(students matching to tClient.IDs). Not really sure if there's a
junction table there because I think that's were there's a middle
table between two tables and this is more of a case where
tGraduateList gets some info from tClient and some from tClass.

Thanks. Every bit helps.

Michele

 

[John W. Vinson]

A couple of notes to be sure before I try your idea. There isn't a
subform. GraduationDate object is straight on the fGraduateList form
- the one to default from tClass.GraduateDate and update if needed.

aha. Ok in that case you'll need to look up the value directly from the Table.
Try:

Private Sub GraduationDate_AfterUpdate()
If IsNull(Me!GraduationDate) Then
Me!GraduationDate = DLookUp("GraduationDate", "tClass", _
"ClassID = " & Me!ClassID)
End If
End Sub
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[mjones]

aha. Ok in that case you'll need to look up the value directly from the Table.
Try:

Private Sub GraduationDate_AfterUpdate()
If IsNull(Me!GraduationDate) Then
   Me!GraduationDate = DLookUp("GraduationDate", "tClass", _
          "ClassID = " & Me!ClassID)
End If
End Sub
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Ahh. I tried your code John, but nothing happens. I think I know
why. First, I put the code in fGraduateList form's GraduateDate
object's after update event. The GraduateDate's object is in the
detail section. I see the code in the VBA module okay.

Then, I changed both ClassIDs to ClassCodes because that's what they
are named. Classes are named by ClassCodes instead of ClassIDs.
ClassCode field appears in both tClass and tGraduateList tables for
now. Still nothing happens.

Now I'm wondering if it's a timing thing. ClassCode, (although can be
looked up when I figure it out) is entered in an object called
ClassCode above the GraduationDate object (same detail section). Both
are fields in the tGraduateList table.

ClassCode is selected from a ClassCode list from the tClass table.
Because GraduationDate needs the ClassCode first, maybe the code
should be a Private Sub ClassCode_AfterUpdate() in the ClassCode
object instead of the GraduationDate object. That idea gave a code
error on the If IsNull line, but might work if the code is adjusted.

This mysterious Me! means the form I'm in, right? If so, the code
should work as ClassCode after update as well - both objects in the
same form's detail section.

I'm not giving up. I hope you all don't give up on me. What's the
magic trick?

Thanks all,

Michele

 

[John W. Vinson]

ClassCode is selected from a ClassCode list from the tClass table.
Because GraduationDate needs the ClassCode first, maybe the code
should be a Private Sub ClassCode_AfterUpdate() in the ClassCode
object instead of the GraduationDate object. That idea gave a code
error on the If IsNull line, but might work if the code is adjusted.

Please post your actual code (copy and paste) and the relevant field and table
names. We're getting there!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[mjones]

Ahh.  I tried your code John, but nothing happens.  I think I know
why.  First, I put the code in fGraduateList form's GraduateDate
object's after update event.  The GraduateDate's object is in the
detail section.  I see the code in the VBA module okay.

Then, I changed both ClassIDs to ClassCodes because that's what they
are named.  Classes are named by ClassCodes instead of ClassIDs.
ClassCode field appears in both tClass and tGraduateList tables for
now.  Still nothing happens.

Now I'm wondering if it's a timing thing.  ClassCode, (although can be
looked up when I figure it out) is entered in an object called
ClassCode above the GraduationDate object (same detail section).  Both
are fields in the tGraduateList table.

ClassCode is selected from a ClassCode list from the tClass table.
Because GraduationDate needs the ClassCode first, maybe the code
should be a Private Sub ClassCode_AfterUpdate() in the ClassCode
object instead of the GraduationDate object.  That idea gave a code
error on the If IsNull line, but might work if the code is adjusted.

This mysterious Me! means the form I'm in, right?  If so, the code
should work as ClassCode after update as well - both objects in the
same form's detail section.

I'm not giving up.  I hope you all don't give up on me.  What's the
magic trick?

Thanks all,

Michele

P.S. - Changing the code to a ClassCode after update, it now runs but
gives a run-time error '3075' syntax error (missing operator) in query
expression 'ClassCode=11OctNovWE'. It does pickup the correct
ClassCode of 11OctNovWE. Here is the code:
Private Sub ClassCode_AfterUpdate()
If IsNull(Me!GraduationDate) Then
Me!GraduationDate = DLookup("GraduationDate", "tClass", "ClassCode
= " & Me!ClassCode)
End If
End Sub

 

[mjones]

P.S. - Changing the code to a ClassCode after update, it now runs but
gives a run-time error '3075' syntax error (missing operator) in query
expression 'ClassCode=11OctNovWE'.  It does pickup the correct
ClassCode of 11OctNovWE.  Here is the code:
Private Sub ClassCode_AfterUpdate()
If IsNull(Me!GraduationDate) Then
   Me!GraduationDate = DLookup("GraduationDate", "tClass", "ClassCode
= " & Me!ClassCode)
End If
End Sub

Yeah! Searching the net for the syntax, I figured it out. It does go
in ClassCode after update and should be:

Me!GraduationDate = DLookup("GraduationDate", "tClass", "ClassCode =
'" & Me!ClassCode & "'") because ClassCode is a text field.

The only thing is that if an incorrect ClassCode is accidentally
selected, the GraduationDate will not update when changing to the
correct ClassCode. Any idea on how to make that part work?

Thanks!

Michele

 

[John W. Vinson]

P.S. - Changing the code to a ClassCode after update, it now runs but
gives a run-time error '3075' syntax error (missing operator) in query
expression 'ClassCode=11OctNovWE'. It does pickup the correct
ClassCode of 11OctNovWE. Here is the code:

Aha. I didn't realize that ClassCode was a Text field. If that's the case you
need the syntactically required quotemarks:

Private Sub ClassCode_AfterUpdate()
If IsNull(Me!GraduationDate) Then
Me!GraduationDate = DLookup("GraduationDate", "tClass", "ClassCode
= '" & Me!ClassCode & "'")
End If
End Sub

so it will search for

ClassCode = '11OctNovWE'

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John W. Vinson]

The only thing is that if an incorrect ClassCode is accidentally
selected, the GraduationDate will not update when changing to the
correct ClassCode. Any idea on how to make that part work?

Well, the cost of fixing that would be that it would overwrite a manually
entered classcode when you select any classcode. Just remove the IF block:

Private Sub ClassCode_AfterUpdate()
Me!GraduationDate = DLookup("GraduationDate", "tClass", "ClassCode='" &
Me!ClassCode & "'")
End Sub
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[mjones]

Well, the cost of fixing that would be that it would overwrite a manually
entered classcode when you select any classcode. Just remove the IF block:

Private Sub ClassCode_AfterUpdate()
Me!GraduationDate = DLookup("GraduationDate", "tClass", "ClassCode='"&
Me!ClassCode & "'")
End Sub
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Brilliant! Yeah! Yeah!

Now I can use this idea on my receipt. Thanks so much everyone!