Help with query

M

mwest

I keep getting an error message when I try and run this: COMPILE ERROR BLOCK IF
WITHOUT END IF.
I have changed things around but nothing seems to work

Private Sub MultiOK_Click()
On Error GoTo Err_multiOK_Click

If IsNull(Forms![Find contaminant,department,date]!Contaminant) Then
If IsNull(Forms![Find contaminant,department,date]!Department) Then
If IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find contaminant,department,date"
'MsgBox "oops"
Exit Sub

End If

DoCmd.OpenQuery "Retreive Selected contaminant,department,date",
acNormal, acEdit
DoCmd.OpenForm "contaminant,department,date Display"
DoCmd.Close acQuery, "Retreive Selected contaminant,department,date"

DoCmd.Close acForm, "Find contaminant,department,date"
DoCmd.SelectObject acForm, "contaminant,department,date Display"

Exit_multiOK_Click:
Exit Sub
 
J

John Spencer

Well, you have three IF and one EndIf. Perhaps you want to add two more
endIf lines above or below the endif line you currently have.

Or perhaps you simply want
IF IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then

Exit Sub
End if
 
M

mwest

I have changed it but now I get a syntax error. Desperate to get this to work
Thanxs for your help
If IsNull(Forms![Find contaminant,department,date]!Contaminant)
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find Contaminant,department,date"
'MsgBox "oops"
Exit Sub

Exit Sub
End If


John Spencer said:
Well, you have three IF and one EndIf. Perhaps you want to add two more
endIf lines above or below the endif line you currently have.

Or perhaps you simply want
IF IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then

Exit Sub
End if

mwest said:
I keep getting an error message when I try and run this: COMPILE ERROR
BLOCK IF
WITHOUT END IF.
I have changed things around but nothing seems to work

Private Sub MultiOK_Click()
On Error GoTo Err_multiOK_Click

If IsNull(Forms![Find contaminant,department,date]!Contaminant) Then
If IsNull(Forms![Find contaminant,department,date]!Department) Then
If IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find contaminant,department,date"
'MsgBox "oops"
Exit Sub

End If

DoCmd.OpenQuery "Retreive Selected contaminant,department,date",
acNormal, acEdit
DoCmd.OpenForm "contaminant,department,date Display"
DoCmd.Close acQuery, "Retreive Selected contaminant,department,date"

DoCmd.Close acForm, "Find contaminant,department,date"
DoCmd.SelectObject acForm, "contaminant,department,date Display"

Exit_multiOK_Click:
Exit Sub
Click to expand...
Click to expand...
 
H

HartJF

You need a continuation (a space and an underscore) at the end of the first
line:

If IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
...

Without it, the if statement ends without "then", and a new statement begins
with "Or isNull...".


mwest said:
I have changed it but now I get a syntax error. Desperate to get this to work
Thanxs for your help
If IsNull(Forms![Find contaminant,department,date]!Contaminant)
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find Contaminant,department,date"
'MsgBox "oops"
Exit Sub

Exit Sub
End If


John Spencer said:
Well, you have three IF and one EndIf. Perhaps you want to add two more
endIf lines above or below the endif line you currently have.

Or perhaps you simply want
IF IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then

Exit Sub
End if

mwest said:
I keep getting an error message when I try and run this: COMPILE ERROR
BLOCK IF
WITHOUT END IF.
I have changed things around but nothing seems to work

Private Sub MultiOK_Click()
On Error GoTo Err_multiOK_Click

If IsNull(Forms![Find contaminant,department,date]!Contaminant) Then
If IsNull(Forms![Find contaminant,department,date]!Department) Then
If IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find contaminant,department,date"
'MsgBox "oops"
Exit Sub

End If

DoCmd.OpenQuery "Retreive Selected contaminant,department,date",
acNormal, acEdit
DoCmd.OpenForm "contaminant,department,date Display"
DoCmd.Close acQuery, "Retreive Selected contaminant,department,date"

DoCmd.Close acForm, "Find contaminant,department,date"
DoCmd.SelectObject acForm, "contaminant,department,date Display"

Exit_multiOK_Click:
Exit Sub
Click to expand...
Click to expand...
Click to expand...
 
M

mwest

Thank-you
I am getting "Can't find the field"
My form is called contaminant,department,date
My query is called retrieve selected contaminant,department,date
In the three columns of my query I have typed Like "*" & [Enter Contaminant]
& "*"
I really don't know where to go from here.
this is what it looks like now
Private Sub MultiOK_Click()
On Error GoTo Err_multiOK_Click



If IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find Contaminant,department,date"
'MsgBox "oops"
Exit Sub


End If


DoCmd.OpenQuery "Retreive Selected contaminant,department,date",
acNormal, acEdit
DoCmd.OpenForm "contaminant,department,date Display"
DoCmd.Close acQuery, "Retreive Selected contaminant,department,date"

DoCmd.Close acForm, "Find contaminant,department,date"
DoCmd.SelectObject acForm, "contaminant,department,date Display"

Exit_multiOK_Click:
Exit Sub

Err_multiOK_Click:
MsgBox Err.Description
Resume Exit_multiOK_Click

End Sub
HartJF said:
You need a continuation (a space and an underscore) at the end of the first
line:

If IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
...

Without it, the if statement ends without "then", and a new statement begins
with "Or isNull...".


mwest said:
I have changed it but now I get a syntax error. Desperate to get this to work
Thanxs for your help
If IsNull(Forms![Find contaminant,department,date]!Contaminant)
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find Contaminant,department,date"
'MsgBox "oops"
Exit Sub

Exit Sub
End If


John Spencer said:
Well, you have three IF and one EndIf. Perhaps you want to add two more
endIf lines above or below the endif line you currently have.

Or perhaps you simply want
IF IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then

Exit Sub
End if

I keep getting an error message when I try and run this: COMPILE ERROR
BLOCK IF
WITHOUT END IF.
I have changed things around but nothing seems to work

Private Sub MultiOK_Click()
On Error GoTo Err_multiOK_Click

If IsNull(Forms![Find contaminant,department,date]!Contaminant) Then
If IsNull(Forms![Find contaminant,department,date]!Department) Then
If IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find contaminant,department,date"
'MsgBox "oops"
Exit Sub

End If

DoCmd.OpenQuery "Retreive Selected contaminant,department,date",
acNormal, acEdit
DoCmd.OpenForm "contaminant,department,date Display"
DoCmd.Close acQuery, "Retreive Selected contaminant,department,date"

DoCmd.Close acForm, "Find contaminant,department,date"
DoCmd.SelectObject acForm, "contaminant,department,date Display"

Exit_multiOK_Click:
Exit Sub
Click to expand...
Click to expand...
Click to expand...
 
H

HartJF

Three thoughts...

In MultiOK_click, you open the query "Retreive...". Is the query's name in
the database window spelled the same way, or is it correctly spelled
"Retrieve..."?

Your query is using data from a field -- or a parameter -- called [Enter
Contaminant]. Are you sure that it exists? I believe what you really want
is to test the input from the "Find..." form. Your query criteria would then
be
Like "*" & Forms![Find contaminant,department,date]!Contaminant & "*"
which is not visible to the query. You may want to go to SQL view and add a
PARAMETERS statement, and then set the parameters in MultiOK_click before you
open the query.

Look at your If statement in MultiOK_click. You are saying that if
Contaminant is null or Department is null or Date is null, exit the
subroutine. You will exit if any one or more of the fields are null. Is
this what you want?

At what point do you receive the error message? Does the message identify
the field it cannot find? If the error occurs in the code, what is the line
that triggers the error?

mwest said:
Thank-you
I am getting "Can't find the field"
My form is called contaminant,department,date
My query is called retrieve selected contaminant,department,date
In the three columns of my query I have typed Like "*" & [Enter Contaminant]
& "*"
I really don't know where to go from here.
this is what it looks like now
Private Sub MultiOK_Click()
On Error GoTo Err_multiOK_Click



If IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find Contaminant,department,date"
'MsgBox "oops"
Exit Sub


End If


DoCmd.OpenQuery "Retreive Selected contaminant,department,date",
acNormal, acEdit
DoCmd.OpenForm "contaminant,department,date Display"
DoCmd.Close acQuery, "Retreive Selected contaminant,department,date"

DoCmd.Close acForm, "Find contaminant,department,date"
DoCmd.SelectObject acForm, "contaminant,department,date Display"

Exit_multiOK_Click:
Exit Sub

Err_multiOK_Click:
MsgBox Err.Description
Resume Exit_multiOK_Click

End Sub
HartJF said:
You need a continuation (a space and an underscore) at the end of the first
line:

If IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
...

Without it, the if statement ends without "then", and a new statement begins
with "Or isNull...".


mwest said:
I have changed it but now I get a syntax error. Desperate to get this to work
Thanxs for your help
If IsNull(Forms![Find contaminant,department,date]!Contaminant)
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find Contaminant,department,date"
'MsgBox "oops"
Exit Sub

Exit Sub
End If


:

Well, you have three IF and one EndIf. Perhaps you want to add two more
endIf lines above or below the endif line you currently have.

Or perhaps you simply want
IF IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then

Exit Sub
End if

I keep getting an error message when I try and run this: COMPILE ERROR
BLOCK IF
WITHOUT END IF.
I have changed things around but nothing seems to work

Private Sub MultiOK_Click()
On Error GoTo Err_multiOK_Click

If IsNull(Forms![Find contaminant,department,date]!Contaminant) Then
If IsNull(Forms![Find contaminant,department,date]!Department) Then
If IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find contaminant,department,date"
'MsgBox "oops"
Exit Sub

End If

DoCmd.OpenQuery "Retreive Selected contaminant,department,date",
acNormal, acEdit
DoCmd.OpenForm "contaminant,department,date Display"
DoCmd.Close acQuery, "Retreive Selected contaminant,department,date"

DoCmd.Close acForm, "Find contaminant,department,date"
DoCmd.SelectObject acForm, "contaminant,department,date Display"

Exit_multiOK_Click:
Exit Sub
Click to expand...
Click to expand...
Click to expand...
 
M

mwest

As you can tell I am very new at this and still lost. Retreive is spelled the
same in all cases. When I run the query it runs fine ( I can fill in one or
all three fields)
My form Find contaminant,department,date - The OK button keeps giving me the
error now I am getting a [syntax error] It is in the first few lines.

If IsNull Like "*" & Forms![Find contaminant,department,date]!Contaminant &
"*" _
Or Like "*" & Forms![Find contaminant,department,date]!Dept & "*" _
Or Like "*" & Forms![Find contaminant,department,date]!Date & "*" Then
DoCmd.Close acForm, "Find Contaminant,department,date"
'MsgBox "oops"
Exit Sub

HartJF said:
Three thoughts...

In MultiOK_click, you open the query "Retreive...". Is the query's name in
the database window spelled the same way, or is it correctly spelled
"Retrieve..."?

Your query is using data from a field -- or a parameter -- called [Enter
Contaminant]. Are you sure that it exists? I believe what you really want
is to test the input from the "Find..." form. Your query criteria would then
be
Like "*" & Forms![Find contaminant,department,date]!Contaminant & "*"
which is not visible to the query. You may want to go to SQL view and add a
PARAMETERS statement, and then set the parameters in MultiOK_click before you
open the query.

Look at your If statement in MultiOK_click. You are saying that if
Contaminant is null or Department is null or Date is null, exit the
subroutine. You will exit if any one or more of the fields are null. Is
this what you want?

At what point do you receive the error message? Does the message identify
the field it cannot find? If the error occurs in the code, what is the line
that triggers the error?

mwest said:
Thank-you
I am getting "Can't find the field"
My form is called contaminant,department,date
My query is called retrieve selected contaminant,department,date
In the three columns of my query I have typed Like "*" & [Enter Contaminant]
& "*"
I really don't know where to go from here.
this is what it looks like now
Private Sub MultiOK_Click()
On Error GoTo Err_multiOK_Click



If IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find Contaminant,department,date"
'MsgBox "oops"
Exit Sub


End If


DoCmd.OpenQuery "Retreive Selected contaminant,department,date",
acNormal, acEdit
DoCmd.OpenForm "contaminant,department,date Display"
DoCmd.Close acQuery, "Retreive Selected contaminant,department,date"

DoCmd.Close acForm, "Find contaminant,department,date"
DoCmd.SelectObject acForm, "contaminant,department,date Display"

Exit_multiOK_Click:
Exit Sub

Err_multiOK_Click:
MsgBox Err.Description
Resume Exit_multiOK_Click

End Sub
HartJF said:
You need a continuation (a space and an underscore) at the end of the first
line:

If IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
...

Without it, the if statement ends without "then", and a new statement begins
with "Or isNull...".


:


I have changed it but now I get a syntax error. Desperate to get this to work
Thanxs for your help
If IsNull(Forms![Find contaminant,department,date]!Contaminant)
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find Contaminant,department,date"
'MsgBox "oops"
Exit Sub

Exit Sub
End If


:

Well, you have three IF and one EndIf. Perhaps you want to add two more
endIf lines above or below the endif line you currently have.

Or perhaps you simply want
IF IsNull(Forms![Find contaminant,department,date]!Contaminant) _
Or IsNull(Forms![Find contaminant,department,date]!Department) _
Or IsNull(Forms![Find contaminant,department,date]!Date) Then

Exit Sub
End if

I keep getting an error message when I try and run this: COMPILE ERROR
BLOCK IF
WITHOUT END IF.
I have changed things around but nothing seems to work

Private Sub MultiOK_Click()
On Error GoTo Err_multiOK_Click

If IsNull(Forms![Find contaminant,department,date]!Contaminant) Then
If IsNull(Forms![Find contaminant,department,date]!Department) Then
If IsNull(Forms![Find contaminant,department,date]!Date) Then
DoCmd.Close acForm, "Find contaminant,department,date"
'MsgBox "oops"
Exit Sub

End If

DoCmd.OpenQuery "Retreive Selected contaminant,department,date",
acNormal, acEdit
DoCmd.OpenForm "contaminant,department,date Display"
DoCmd.Close acQuery, "Retreive Selected contaminant,department,date"

DoCmd.Close acForm, "Find contaminant,department,date"
DoCmd.SelectObject acForm, "contaminant,department,date Display"

Exit_multiOK_Click:
Exit Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help opening up query into form 1
Msgbox vbretry (get user to retry and fill in information) 2
Resume Next versus GoTo 5
The below code isn't working. Please help. 5
Check all Records 0
Password Protect form 1
Report based on Query? 2
Looping through records 1

Top