Multiple block if

[ahuvas]

Hi

I know this is a simple question but I am still not sure of the answer.

If I was to test several fields on form close, each of which are independent of each other and will cause an action if they are not empty, do I do the following...

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

If Len(Me.Combo10 & "") <> 0 Then
Docmd....

Endif

If Len(Me.Text72 & "") <> 0 Then
Docmd....

Endif

End Sub

That is to say can I multiple blockif statements, separated by and EndIf...?

Also if Len(Me.Text000 & "") = 0 tests if the field is null or empty, is Len(Me.Text72 & "") <> 0 the way to check if it is NOT null or empty?

Many thanks.

 

[Galaxiom]

Yes, they are independent. The first one to be True will run.

Another alternative is to put multiple conditions into one If statement with AND between them.

Also have a look at If ElseIf Else constructs. They can be useful.

 

[Galaxiom]

If there is no Else then they can each be a single line with no End If.

If Len(Me.Combo10 & "") <> 0 Then Docmd....
If Len(Me.Text72 & "") <> 0 Then Docmd....

 

[CJ_London]

yes - except it is end if, not endif

You can combine 'ifs' whihc have the same action e.g.

If Len(Me.Combo10 & "") <> 0 or Len(Me.Text72 & "") <> 0 Then
    Docmd....

Endif

Also if Len(Me.Text000 & "") = 0 tests if the field is null or empty, is Len(Me.Text72 & "") <> 0 the way to check if it is NOT null or empty?

yes

Another way of testing is

if nz(me.text000)="" then

 

[ahuvas]

Thanks everyone.

This is basically the situation (see attached schematic) - sorry the description of the database is so long, but I can't TL;DR it.

I have created a database to track calls from potential participants about a study I am recruiting for.

These participants can approach us in two ways.

1. Either directly, by answering machine message or email etc, or
2. Through a short online survey.

We are asking trying to get people to do the online survey because we want to limit the amount of time we have to spend with them on the phone, especially if they aren't suitable (we get lots of inquiries). The online survey will ask them to provide answers to some basic questions that we help us filter the non-suitable people quickly.

Whichever way they contact us we want to store their contact details. This is because this database is also going to act as a repository for people interested in our research so we can contact them in the future for other studies, and filter them on the basis of their answers such as whether or not they have diabetes, or whether or not they are aged <30. As you can see from the schematic we will not always be able to get those details i.e. for example if they contact us by email and then choose not to go ahead with the telephone screening. In this case we will only have their contact details.

People who make the initial contact us by phone will only provide us with health details at one point - the telephone screening.

People who make initial contact us by web form will provide us with information at two points - the online survey as well as the telephone screening. Many of the questions in the online survey and telephone screening are the same.

I have set it up such that I will use one form to add a person to the database - the form has a tab control on it with one page that has the fields for contact details, and another page that has fields related to their online survey responses (if we have it). Once that person is entered we close the form and the information is stored. I can find the person again by opening a form in datasheet view which lists all the calls, with a primary key that can be clicked to open the form as I describe below.

When I am ready to call the person I open another form which looks slightly different. Under the form I can see all the information that I already entered as well as fields asking me whether they passed the telephone screening, if not why no, if yes, did I book an appointment with them and so on. There is also a button that will open a telephone screening form, filtered to that person's ID so I can conduct the telephone screening over the phone.

What I basically want to do is pipe the text from the online survey fields I filled out in the "add participant" form to the fields on the telephone screening form - we are only talking about 6 or so questions but I dont want to have to enter it twice. However I want the telephone screening fields to be independent of the initial "add participant" fields so that I can change the responses in the telephone screening form without affecting the initial fields (i.e. have two separate table columns - even though it technically stores the same info).

So I was thinking the way to get around this is to do the following on form close of the add participant form.

Docmd on form close

If field1 is not empty,
then field 10 (analagous field on the telephone screening form).value = field 1 value

... and so on...

===

Do you think this is the way to go about it?

 

[CJ_London]

Sounds reasonable to me - you might want to consider using the before update event rather than the form close event

 

[ahuvas]

OK. Getting there (slowly - with your help)

Now the issue for me is that when the AddNew form closes/does the before update (I have to think about this some more), the telephone screening form is not yet open. Which means I have to pass the addnew data to the table rather than the telephone screening form field and then closing the add new form (if you know what I mean).

 

[CJ_London]

We've moved on from the if statement question re your original post, this is now in a different area so I suggest you start a new thread on this subject to bring in 'new blood'

I'll add my two bits in here - it sounds like you need to normalise your data since you should not be storing data more than once - and it sounds like you are trying to do this.

I would also learn more about the order events occur and when they occur - here is a link which you should find helpful

http://office.microsoft.com/en-gb/access-help/order-of-events-for-database-objects-HP005186761.aspx

 

[ahuvas]

I know it seems like I am storing the same info twice but some people will never get to the telephone screening stage even if they have completed the online survey questions. Conversely not all people who do the telephone screening have done the online survey.

However everyone who wants to do the study has to complete the telephone screening. I just want to pass the information that I do have (in the case where someone has done the online survey) onto the telephone screening for so I dont have to enter the same information twice.

I will try and post this again.

Many thanks for your help.