Calling public function based on data from parent form syntax

[JCShort]

I have a public function that converts off-calendar month dates to a single specified date:

Public Function DateBucket(DDDate As Date) As String
Select Case DDDate


Case Is <= #3/20/2009# And DDDate >= #2/21/2009#
DateBucket = #3/20/2009#

Case Is <= #4/24/2009# And DDDate >= #3/21/2009#
DateBucket = #4/24/2009#
etc...

The DDDate is input into the Parent form. However when user inputs the various records into the Child form, I need to compare two dates to determine the propper date for the record (DDDate vs. CancDate [also on the parent form]).

Here's the concept of what I need:

If Me.parent.CancDate.Value > DateBucket(Me.parent.DDDate) Then
Me.PSRDate = Me.parent.CancDate

ElseIf DateDiff("d", Me.parent.DDDate.Value, DateBucket(DDDate)) <= 5 Then
Me.PSRDate = DateBucket(Me.parent.DDDate)

Else
Me.PSRDate = DateAdd("d", 7, Me.parent.DDDate)

End If

I think my syntax is off, but I can't figure out how. Code doesn't recognize DateBucket(me.parent.DDDate). I've tried me.parent.dddate.value, [me.parent.dddate], [me].[parent].[dddate], ect. I've even tried to create a variable:

Dim DDDateTemp as Date

DDDateTemp = Me.parent.DDDate.Value

Then replaced all of the parent DDDate stuff in the prior code with DDDateTemp.

Thanks in advance!

 

[c_smithwick]

Your function is set up to return a string value, but as formatted you are not doing that. Try the below changes:

Public Function DateBucket(DDDate As Date) As String
Select Case DDDate


Case Is <= #3/20/2009# And DDDate >= #2/21/2009#
DateBucket = "#3/20/2009#"

Case Is <= #4/24/2009# And DDDate >= #3/21/2009#
DateBucket = "#4/24/2009#"

 

[JCShort]

Thanks for the suggestion, smithwick. I'm still having the same issue though.

I've used the public function on every form that I've created over the past 1.5 years without an issue. We don't follow calendar months where I work, so I had to improvise for reporting purposes.

As part of my troubleshooting, I put unbound boxes on both my parent and child forms with =DateBucket(DDDate) and =DateBucket(Me.Parent.DDDate) respectively. The parent form returned the correct value, however the subform result was #Name?

That's why I assumed I had a syntax error. Thanks again for you insight. I appreciate the response.

 

[SOS]

Try this on the subform:

=DateBucket(Me.DDDate)

 

[JCShort]

Thanks SOS. Didn't work.

The DDDate & CancDate are fields on the parent form which are linked to a different table than the subform and respective inputs.

 

[SOS]

Thanks SOS. Didn't work.

The DDDate & CancDate are fields on the parent form which are linked to a different table than the subform and respective inputs.

Is DDDate on the main form a bound control? If so, make sure that it is not named the same as the field it is bound to.

 

[JCShort]

Uh oh... I always name my form fields after the bound control. I'm getting a sinking feeling that you're about to tell me I'm doing a DB faux pas!

 

[SOS]

It normally doesn't matter but in some instances it does. So, if you change DDDate control to txtDDDate and then use txtDDDate in your code, it might work then.

 

[JCShort]

Still nothing. The unbound test cell on the parent form returns the correct output, but the subform test cell still says "#Name?"

 

[SOS]

Can you upload a copy of the database with any sensitive data removed?

 

[JCShort]

Unbelievable. I built a test DB to post. Copy/Pasted all of the code & named all of the fields the same. The test DB works perfectly!......

I knew something was up when I pasted the code & all of the ".parent." became ".Parent.".... I'll try to figure out what the problem is in my form/subform design and post the solution....

 

[SOS]

Maybe you just need to import everything into a new, blank database shell to get rid of any corruption that might be hanging around.

 

[vbaInet]

It might be an idea to check the name of the module you have that function in. What's the name? Does the name clash with the names of your function, subform or Parent form?

 

[JCShort]

Thanks vbaInet. The module is listed in "Functions1" which doesn't clash with anything. It actually works perfectly for all other forms (roughly 50). I think this is coming back to haunt me:

http://www.access-programmers.co.uk/forums/showthread.php?t=174274

After I split my DB, I found that there is a known issue in Access 2003 in which switching between design view and form view when you have subforms can fool access into thinking that there is another user in the DB. I think this is when the corruption began. Whenever I need to add a new subform, combobox, etc., the respective wizard won't trigger because it says the DB is currently locked by my computer, so I have to manually add everything.

I'm sure the fact that my company's LAN is terrible & the server is outdated and within 5% of capacity doesn't help. My guess is that this is also the genesis of the corruption.

 

[JCShort]

Solution: I created a work-around that, while not ideal from a normalization standpoint, works. I added an event and a field on my parent form to trigger and store the DateBucket() function/output. My subform now simply compares the stored data instead of using a function.