Calendar .Value not recognised???

[Bird_FAT]

Hey all - been making a Form today with multiple subforms in - lots of feilds need dates entering, so trawled the net and bastardised various VBA code to make a single Calendar form that is activated upon double-clicking the required field. - All good to here!!

Problem is that I want to be able to parse the date back to the field upon double clicking the required date. And when I do double-click it debugs to the line

 Form_Student_Tracking!Status_subform!Withdrawal_Date = MiniCal.Value

telling me that

Form_Student_Tracking!Status_subform!Withdrawal_Date = <Object Required>

As stated before - the VBA is not mine, but I have worked my way through to this point OK, but please understand that I am a complete noob to VBA in Access (I'm sure that'll be obvious in a mo anyway).

Module Code

Option Compare Database

Global GCalSource As String
Global GCalDate   As Date

Sub GetDate(pObject As Object)
    
    If IsNull(pObject) Then
        GCalDate = Date
        
    Else
        GCalDate = pObject
    End If
    
End Sub

Form_Calendar Code

Private Sub MiniCal_DblClick()
    
    'Set date in source object
    Select Case GCalSource

        Case "Withdrawal_Date_subform"
            Form_Student_Tracking!Status_subform!Withdrawal_Date = MiniCal.Value

    End Select
    
    'close form
    DoCmd.Close acForm, "Calendar"
    
End Sub

Private Sub Form_Load()
    
    MiniCal.Value = GCalDate
    
    DoCmd.RepaintObject acForm, "Calendar"
    
End Sub

Form_Student_Tracking Code

Option Compare Database

Private Sub Form_Open(Cancel As Integer)

End Sub

Form_Comments_subform Code

Option Compare Database

Private Sub Date_DblClick(Cancel As Integer)
 GCalSource = "Date_subform"
    GetDate [Date]
    DoCmd.OpenForm "Calendar", acNormal, , , acFormAdd, acDialog
End Sub

I also guess that the last line in the Comments subform is going to be a problem as it doesn't contain a close command, but I can't get on to that until I understand where I've mucked up already LOL!

Any help appreciated!

Bird

 

[boblarson]

Try changing this:
Form_Student_Tracking!Status_subform!Withdrawal_Date = MiniCal.Value


to this:

Forms!Student_Tracking!Status_subform.Form.Withdrawal_Date = Me.MiniCal.Value

 

[boblarson]

And I forgot again (sheesh) -

 

[Bird_FAT]

Try changing this:
Form_Student_Tracking!Status_subform!Withdrawal_Date = MiniCal.Value


to this:

Forms!Student_Tracking!Status_subform.Form.Withdrawal_Date = Me.MiniCal.Value

Thanks for the quick response Bob,

Still debugs to that line, but now hovering the mouse gives me the date that I double-clicked!

I take it now, that I have to go and look at the last lines of the subform code

DoCmd.OpenForm "Calendar", acNormal, , , acFormAdd, acDialog

Cool - I love tinkering!

 

[Bird_FAT]

OK - tried both of these

Bob's

Case "Date_subform"
[COLOR=Red]Forms![/COLOR]Student_Tracking!Comments_subform[COLOR=red].Form.[/COLOR]Date = [COLOR=red]Me.[/COLOR]MiniCal.Value

and also

Case "Date_subform"
Form_Student_Tracking!Comments_subform!Date = Me.MiniCal.Value

And both give me the date when I hover, but still this line is being tagged for debugging!!

Any ideas y'all?


Bird

 

[boblarson]

Make sure MiniCal is actually the name and it is on the form that you have the code on. If the MiniCal is on another form you would need to give it an explicit reference:

Forms!YourFormNameHere.MiniCal

 

[Bird_FAT]

Make sure MiniCal is actually the name and it is on the form that you have the code on. If the MiniCal is on another form you would need to give it an explicit reference:

Forms!YourFormNameHere.MiniCal

The code is from the Form_Calendar which has the MiniCal object on it.
Tried changing it to

Forms!Student_Tracking!Comments_subform.Form.Date = Forms![COLOR=Red]Form_Calendar[/COLOR].MiniCal.Value

but it comes up saying that it can't find the form 'Form_Calendar'

Then tried

Forms!Student_Tracking!Comments_subform.Form.Date = Forms![COLOR=Red]Calendar[/COLOR].MiniCal.Value

and now it says that it cant find the form 'Student_Tracking'

Hmmmm!

 

[boblarson]

Okay, first things first.

What are the names of your forms (as listed in the database window and NOT in the VBA window)?

 

[Bird_FAT]

Okay, first things first.

What are the names of your forms (as listed in the database window and NOT in the VBA window)?

Calendar - a seperate form

Student Tracking - the main form

4 Subforms (all with the word 'subform' in the name):

Comments subform
Courses subform
Reviews subform
Status subform

Bird

 

[boblarson]

Okay, that would explain why you are having problems. You don't use the naming that shows in the VBA window. You use the names that show in the database window.

So, let's try this:
Forms![Student Tracking]![Comments subform].Form.Date = Forms!Calendar.MiniCal.Value

You don't add underscores to the names and if you have spaces in names you have to use brackets (that's why I don't use spaces in object names as it is a pain).

 

[Bird_FAT]

Okay, that would explain why you are having problems. You don't use the naming that shows in the VBA window. You use the names that show in the database window.

So, let's try this:
Forms![Student Tracking]![Comments subform].Form.Date = Forms!Calendar.MiniCal.Value

You don't add underscores to the names and if you have spaces in names you have to use brackets (that's why I don't use spaces in object names as it is a pain).

Hooop - all working!! YAAAAAAY!

Thanks a lot Bob - I'll try to remember your advice and NOT use spaces from now on (did say I was a noob ).

I'll be on these forums often, I think, LOL!

Thanks again Bob!

 

[boblarson]

Hooop - all working!! YAAAAAAY!

Thanks a lot Bob - I'll try to remember your advice and NOT use spaces from now on (did say I was a noob ).

I'll be on these forums often, I think, LOL!

Thanks again Bob!

Whew! Glad we got it working.

Oh, also as far as my "underscore" comment goes - I meant that you don't add them where there are spaces (like Access does in the VBA Window). But you CAN use underscores for naming if you wish but I prefer what is known as CamelCase (i.e. frmStudentTracking, sfrmComments).

 

[Bird_FAT]

AAAAAAAAAAAAAAARRRRRRRRRRRRRG!

Took your advice and renamed all the forms/subs, now it keeps bugging out!

New names

Calendar
FrmStudentTracking
SubComments
SubCourses
SubReviews
SubStatus

"Date_subform"
Forms!FrmStudentTracking!SubComments.Form.Date = Forms!Calendar.MiniCal.Value

Errors with Run-time error '2645' - Can't find field 'SubComments' reffered to in your expression

What have I done? (name the object I should be beating myself with at this point!!)

Bird

 

[boblarson]

Don't get too upset. You probably didn't realize that when referring to a subform, you actually refer to the name of the CONTAINER that houses the subform on the main form and not the subform itself. Now, a lot of the time the container is named the same as the subform, and that's what yours is named currently "Comments subform" because of the way you added it. So, just go change that name too and you should be good to go.

For a screenshot on how to select the subform container see the first screenshot here:

http://www.btabdevelopment.com/main/LinkClick.aspx?link=76&tabid=55&mid=385

 

[Bird_FAT]

You the man! Good to go!


Gee thanks for the info on this Bob!


As I've said before - I LOVE THIS LEARNING THING!!

 

[Bird_FAT]

So - a further question to help me understand this:

What is the difference between

Form_FrmStudentTracking!SubCourses!Date = Forms!MinCal.Value

and

Forms!FrmStudentTracking!SubCourses.Form.Date = Forms!MinCal.Value

?

Bird

 

[boblarson]

If you read my tutorial (the one where I pointed you to for the screenshot) you will find out what the .Form. does.

 

[Bird_FAT]

If you read my tutorial (the one where I pointed you to for the screenshot) you will find out what the .Form. does.

Cool - added to bookmarks

Thanks Bob!

Bird