If IsNull checking two text boxes on two different sub-forms (1 Viewer)

[andy_dyer]

Hi,

I have a database with a dynamic subform (sfrdata) which displays different subforms when menu buttons are clicked

When I open one subform I want it to check if I have a date in EITHER of two date fields which are on different subforms. If they are both blank I want it to display a message box, if I have a date in either then that is fine.

But I've treid lots of different combinations but it doesn't matter if I have a date in both, neither or either of the fields I get my message box triggered...

Why???

Private Sub Form_Load()
On Error Resume Next ' We have to go through with this come what may!
Dim Answer As Integer

If IsNull(Forms![frmprojectmaster]!sfrData!txtStartDate) And IsNull(Forms![frmprojectmaster]!sfrData!txtProposedProjectStartDate) Then
Answer = MsgBox("Please enter either a Project Start Date on the 'General' tab or a Proposed Start Date on the 'Funding Application' tab", vbOKOnly, "Please enter a Start Date")
Else

End If
End Sub

 

[vbaInet]

It depends on whether the frmProjectMaster subform opens before the subform you're loading. The tab order of the subform controls will determine this. And you need to refer to the subform properly, not using Forms.

 

[JANR]

To refrence a control on a subform add .Form

Private Sub Form_Load()
On Error Resume Next ' We have to go through with this come what may!
Dim Answer As Integer

If IsNull(Forms![frmprojectmaster]!sfrData[B][COLOR=red].Form[/COLOR][/B]!txtStartDate) And IsNull(Forms![frmprojectmaster]!sfrData[B][COLOR=red].Form[/COLOR][/B]!txtProposedProjectStartDate) Then
Answer = MsgBox("Please enter either a Project Start Date on the 'General' tab or a Proposed Start Date on the 'Funding Application' tab", vbOKOnly, "Please enter a Start Date")
Else

End If
End Sub

JR

 

[vbaInet]

.Form is actually optional unless you are referring to specific form attributes.

This is the sort of reference I would use.

Me.Parent.SubformControlName.TextboxName

 

[andy_dyer]

It depends on whether the frmProjectMaster subform opens before the subform you're loading. The tab order of the subform controls will determine this. And you need to refer to the subform properly, not using Forms.

The frmProjectMaster is the masterform which is open the whole time...

I then have frmProjectGeneral, frmProjectFundingApplication and frmProjectFinnace which are the three subforms that open in sfrdata...

 

[vbaInet]

I see. So you're testing against fields in the parent form? Well, the thing is the subform loads before the parent form so your test will always fail.

If you know the ID of the first record that will get displayed, use a DLookup() function to get the values and test against that.

 

[andy_dyer]

Now this code and still get the msgbox even though i have a date in one of the fields...

Private Sub Form_Load()
On Error Resume Next ' We have to go through with this come what may!
Dim Answer As Integer

If IsNull(Me.Parent.sfrData.txtStartDate) And IsNull(Me.Parent.sfrData.txtProposedProjectStartDate) Then
Answer = MsgBox("Please enter either a Project Start Date on the 'General' tab or a Proposed Start Date on the 'Funding Application' tab", vbOKOnly, "Please enter a Start Date")
Else

End If
End Sub

I'm not sure if it complicates things by the fact that the subforms I am refering too are not active because I'm opening frmProjectFinance in the sfrdata control and I want to check the two other subforms that are not open at that stage...

But I've referred to text boxes before in this database so know this is possible it's all down to how i refer to them...

Thanks for your help guys any more ideas?

I've added a cut down version of my database so you can see what I mean... a sample speaks a thousand posts I think...

 

[RainLover]

Have you given some thought to doing a Compile on your Code.

Also some error trapping might help.

I can't find "sfrData"

 

[vbaInet]

I thought I had already explained that referencing a parent form from a subform in the Load event will fail? I would re-iterate that a subform loads before the parent form so when you're referencing a field or control on a parent form, there's nothing to reference because it hasn't yet been loaded into memory.

Use a DLookup() function.

 

[andy_dyer]

Have you given some thought to doing a Compile on your Code.

Also some error trapping might help.

I can't find "sfrData"

Hi RainLover - sorry yes because I'd tried a quick and dirty cut down so I could post something I'd not fully cleaned up the code so there were some compile errors relating to fields and boxes that didn't exist.

With regard to Error Trapping - I've no idea where to begin... my limit is on error next... just to supress error messages...

Every bit of vba I know has been taught to me via this forum so it takes a while...

 

[andy_dyer]

I thought I had already explained that referencing a parent form from a subform in the Load event will fail? I would re-iterate that a subform loads before the parent form so when you're referencing a field or control on a parent form, there's nothing to reference because it hasn't yet been loaded into memory.

Use a DLookup() function.

Thanks vbaInet - and thanks for your patience I do understand why my code cannot work because of the reasons you say BUT

I do want to do this check on the subform frmProjectFinance opening/loading

I cannot find any DLookup example anywhere on the net for checking multiple fields and then I don't know where I can place this code when I do find something I can use to to call it when I want to because of the reasons you state...

Apologies if I am missing something very obvious and/or am being a pain...

I am very grateful for your help

 

[RainLover]

Andy

I can understand your situation however doing a decompile is an important way to find and fix problems.

It may be easier if you copied the Forms etc you are having problems to a Test Database. Then you should be able to decompile and just fix a few things.

This is basic stuff but can appear difficult to a newbie.

Its up to you how you wish to proceed. Besides you do have to fix it sooner or later.

With regard to Error Trapping you can always do a search for help.

 

[vbaInet]

No problem. You're only checking two fields so two DLookups should do it. You will need to perform the check on an ordered set of records. So I'm implying that you create a query and base your parent form on this query, then use the DLookup function to extract the Start and End dates from the query.

A DLookup() function will return the first record in a dataset based on the ordering, hence, why I recommended you order your records in a query.

Here's a good link on DLookup useage:

http://access.mvps.org/access/general/gen0018.htm

 

[andy_dyer]

Ok I've managed to find something that I've butchered and appears to work - does this look to ok to you guys? Or will this miss something?

Dim varStartDate As Variant
varStartDate = DLookup("ProjectID", "tblProject", IsNull([Start Date]) And IsNull([Proposed Project Start Date]))
If varStartDate = 1 Then
Answer = MsgBox("Please enter either a Project Start Date on the 'General' tab or a Proposed Start Date on the 'Funding Application' tab", vbOKOnly, "Please enter a Start Date")
Else
End If

 

[vbaInet]

Not quite. You are still using a table and not a query (as already advised) and the code syntax is off.

A table is not ordered in anyway. You must use a query and apply some ordering.

If IsNull(DLookup("[Start Date] & [End Date]", "QueryName")) Then
    ... action here...
End If

Note the syntax in the link provided. The third argument is the criteria, just like the Criteria row in a query.

Also, you should avoid spaces in your field names.

 

[andy_dyer]

Not quite. You are still using a table and not a query (as already advised) and the code syntax is off.

A table is not ordered in anyway. You must use a query and apply some ordering.

If IsNull(DLookup("[Start Date] & [End Date]", "QueryName")) Then
    ... action here...
End If

Note the syntax in the link provided. The third argument is the criteria, just like the Criteria row in a query.

Also, you should avoid spaces in your field names.

I had my forms based on a query before and it caused me no end of problems when it came to actually using the form and inputting data so I went back to link to a table...

When I put the code back on on form load code on my frmProjectFinance I found the number being passed to varStartDate varied considerably (not sure what the number actually is...)

But by changing my code to this:

Dim varStartDate As Variant
varStartDate = DLookup("ProjectID", "tblProject", IsNull([Start Date]) And IsNull([Proposed Project Start Date]))
If varStartDate > 0 Then
Answer = MsgBox("Please enter either a Project Start Date on the 'General' tab or a Proposed Start Date on the 'Funding Application' tab", vbOKOnly, "Please enter a Start Date")
Else
End If

It works right 100% of the time - if one of the fields is empty it gives me this message box if at least one is populated it doesn't!

And yes sorry I know about naming conventions and have this in most of my database but a few have slipped through...

I know my code might not be pretty but can you see any major problems with it - I'd like to try and avoid re-writing half of my database to refer to a query and then work through the list of errors it gives me?

Thank you for your time

 

[RainLover]

I could change from a Table to a Query in a matter of seconds.

I would not class this as rewriting half a Database.

 

[andy_dyer]

I could change from a Table to a Query in a matter of seconds.

I would not class this as rewriting half a Database.

Yes ok in terms of physically changing the record source yes a matter of seconds

But I had several subforms that simply wouldn't allow data entry when set to having a query as a record source...

I am not good enough at this stuff to work through the why's of this - I simply wanted something that worked...

I don't understand why linking to a query when all the data is in a table and all new and changed data from my form I want to stored in a table means that having the record source as a query is better?

I generally only have 'display' subforms/forms which use a datasheet view linked to queries so that I can then add in the spaces in field names and pull through the actual data behind the foreign keys etc

For data entry forms I always link to the tables themselves...

 

[vbaInet]

You might think that it's working, but it's not. The third argument is completely off. I've already given you the correct way of writing it so it's up to you to use it.

And using the Query Wizard you can convert a table output to a query in a few seconds (like RainLover mentioned).

 

[andy_dyer]

Thanks to you both - I'll have anothe rlook at this query link instead...

Is it the case that you should always link forms to queries and not directly to the tables?