Sub sub forms

[visualAd]

Hi, My first post

I am developing a form in Access which enables a student to be searched or added to the database. The form I created contians two sub forms - one of the queries which the form is based on takes three criteria from other form fields which are as follows:

[StudentId] = [Forms]![studentselect]![txtStudentId]
LCase([Surname]) = LCase([Forms]![studentselect]![txtsurname])
LCase(Left([FirstName], 1)) = IIf([Forms]![studentselect]![txtInitial] Is Not Null,[Forms]![studentselect]![txtInitial],LCase(Left([FirstName],1)))

The query and the form work fine on their own, however, I want to add this form as a sub form in another form. When I attempt to do this I get prompted for the query criteria and access refuses to pick up the values from the fields. Does anyone have any idea why?

 

[Pat Hartman]

In a function or VBA, you should use the IsNull() function. "Is Not Null" is SQL syntax.
IIf(IsNull([Forms]![studentselect]![txtInitial]), 1, [Forms]![studentselect]![txtInitial],LCase(Left([FirstName])))

To refer to controls on a subform:

Forms!MainFormName!SubformName.Form.SubformControl

 

[visualAd]

Thanks for the reply. That solution worked perfectly.

Does it matter whether or not you use IsNull() or the SQL - Is Not Null? I would have thought that using the SQL syntax over VBA was better becuase it will make it more portable

 

[Pat Hartman]

But, you're writing VBA so why would you confuse the issue with SQL syntax that may not work in all versions?