Macro 1

[mouseman2006]

Hi,

I have a form that lists Students Information, Parents Information and a subform called Attendance.

I want a macro to work in the attendance subform. So when i click on absent (yes/no)data type it will automatically go to the Notes form.

Thanks in advance.

Stephen DeSerio

 

[fneily]

Can't you just put
DoCmd.Openform "NoteFormName"
on the AfterUpdate event of the checkbox?

 

[mouseman2006]

Thanks for your help, it worked like a charm. I have another question.

In my notes form I have the following fields:

Student ID
Date
Notes

Can I run a macro that will place the correct Student ID number from the Student Information form to the Student ID in the Notes Form? In other words, when I click on absent for a particular student, I want the Student ID number to carry over to Notes form.

Thanks for your help

Stephen

 

[MajP]

Not sure why, you already have the Student ID field on the form. Is this automatically linked?

If you want this value for all notes, why not set the default value of the notes form?
[StudentID] & ": "

If you only want this for notes about abscences then
use Fneily suggestion and add
forms("frmNotesName").Notes = Me.StudentID
And if your not setting the StudentID as a link then
forms("frmNotesName").[Student ID] = Me.StudentID

 

[mouseman2006]

Thanks for your help. I am using Fneily suggestion, but I did in the Macro Action page.
I have the following information

Echo line 1
OpenForm line 2
Go to record line 3. In GoToRecord, I have the following information:
Form
Notes
New
I would like, if possible when I click on absences it would automatically link enter in the correct StudentID. I am not sure how to use your argument
forms("frmNotesName").Notes = Me.StudentID in the GoToRecord field. I also don’t know how to use VBA.

Thanks again for your help.

 

[fneily]

In your macro, on the Openform action, in its' parameters at the bottom, you'll see a Where box. To open the new form with the same StudentID, type in the box next to Where
[StudentID]=[Forms]![FormNotesName]![StudentID]

 

[fneily]

I just reread your questioned. I assumed that your Notes Table already has the StudentID entered (by the way, when you create a new Student record, StudentID should populate all the other associated tables automatically. You do this through code. It makes life alot easier when things are done up front so it might benefit you to lean VBA and DAO/ADO).
However, on the OnOpen event of the Notes form you could have
Me![StudentID].value =[Forms]![StudentInfoForm]![StudentID]

 

[mouseman2006]

Hi, thanks for your continous help, but i received an error, when i ran the code.

It says: The expression On Open you entered as the event property setting produced the following error: The object does not contain the Automation object 'Me'.

I entered the following information in On Open Property

=Me![Student ID].Value=Forms![Student and Parents Information]![Student ID]

Thanks again

 

[PHV]

Set the OnEvent property to [event procedure] and then click the ellipsis (...)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

In fact I'd play with the Load event procedure ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fneily]

To enter code, you would click on the box next to OnOpen. You'll see a button with three dots. Click on that button and then select CODE BUILDER. You'll be entered into VBA. The SUB, END SUB statements will already be there for you. In between these two statements is where you code. Then put:

Me![Student ID].Value=Forms![Student and Parents Information]![Student ID]

Notice no equal sign to start statement.

Also, just to be sure, click Tools, References. Make sure you have OLE Automation is checked. If not, check it and make it the third reference.

Then just close VBA. It will be saved with the form.

 

[mouseman2006]

Thanks again for your continued help. I am adding the expression you gave me to the Notes form, next to OnOpen. I enter the code fine, but I get an error message when I run it. I am not sure what I am doing wrong. When I click on the command button labeled Notes it is saying Run Time Error ‘2448’. Also ‘You can’t assign a value to this object’.

When I click Notes under Forms it is saying Run Time Error ‘2450’. It is also saying Microsoft Access can’t find form ‘Student and Parents Information’

I made sure that everything is spelled the same and that is not the issue.

I am not sure if it is easier, if I send you the database, it is very small.

Thank you.

Stephen

 

[fneily]

Oh yeah. You can't set a bound control like that. Oops.

Let's go back to where you originally input a StudentID. I guess that's on the "Student and Parents Information" form. On the AfterUpdate event of StudentID (in design view, click on the textbox StudentID, bring up the property sheet, click the event tab, click next to AfterUpdate, click the little box with three dots, click Code Builder then OK). In between the Sub and End Sub, put:

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Notes_Table_Name", dbOpenDynaset)
RS.AddNew
RS![StudentID] = Me![StudentID]
RS.Update
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing

Now the StudentID is in the Notes table.

Now create a Macro that has:
OpenForm at bottom Form Name is Notes
Where Condition is
[StudentID] = Forms![Student and Parents Information]![StudentID]

Now notes will open with the same StudentID as the other form. Both forms MUST be opened.

 

[mouseman2006]

Thank you for your helped, it worked!!

 

[fneily]

Isn't it amazing what you can do when someone explains it correctly. Sorry for the previous goofs.

Also, notice with just a little VBA code, you can make things happen you can't do by just creating forms. So study the tidbits of code you get and build on it. As you can see, it can make forms more "user friendly". Plus it's kinda neat when it works.