small if statement help please

[taylorosso]

i have a list box which i populate via a search box.

When I enter my search string the list box shows all of the matching results,
the user then clicks 'ok' and the selected record is loaded into a form.

This works great untill it finds no matches. If i click "ok" the form then crashes.

I would like to create an IF statemnt for my "ok" button that says:


If List returns no results (i.e is empty) then

Close form

else......(runs my other code)



Hope you can help

[RuralGuy]

The form should not crash regardless of what you are doing. What are you doing after the user presses "OK"?

[taylorosso]

This is the dialogue i get when I select 'ok' ;

syntax error (missing operator) in query expression '[clientID]='

Basicly the 'ok' button opens the 'Clients' form and displays the selected record from the list box, then closes the search form.

Ive attached a gif for you to help picture whats going on.

cheers

[RuralGuy]

I don't see an "OK" button. Is there code behind the button? What makes the button appear? Is it a Click or DoubleClick event on the ListBox? If so then what is the code in that event?

[taylorosso]

my bad its the "continue" button im referring to.

here is the code behind the button:

Private Sub Command3_Click()

On Error GoTo Err_Command3_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmClients"

stLinkCriteria = "[ClientID]=" & Me!
[List3]
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "frmSearchClient2", acSaveNo

DoCmd.Close acForm, "frmSearchClient1", acSaveNo

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click


End Sub

here is the code behind the form;

Private Sub Form_Load()
Dim strSQL As String
Dim MyFilter As String
If Len(Me.Filter) > 0 Then
MyFilter = "WHERE " & Me.Filter
Else
MyFilter = ""
End If
strSQL = "SELECT Clients.ClientID, " & _
"Clients.Firstname, " & _
"Clients.Surname " & _
"FROM Clients " & _
MyFilter & ";"
Me.List3.RowSource = strSQL
End Sub

[RuralGuy]

Now I think you have posted enough details to offer a solution if I could figure out what form you want to close in your first post.

[taylorosso]

the form i will be closing is the form in the gif. (frmSearchClient2).

[RuralGuy]

So you want to close the form with the button on it which you will do in any case right? The OnLoad code you posted is from the frmClients form right?

[taylorosso]

the onload code is from frmSearchClient2,

That search list box is actually another form that opens to sit infront of the parent form which is 'Clients'.

The 'Continue' button then closes this 'frmSearchClient2' to reveal the parent form sitting beneath

[RuralGuy]

You managed to confuse me with your last post so I'll just take a flyer. Try:

Private Sub Command3_Click()
   On Error GoTo Err_Command3_Click
   Dim stDocName As String
   Dim stLinkCriteria As String
   If Me.List3.ListCount > 0 Then
      stDocName = "frmClients"
      stLinkCriteria = "[ClientID]=" & Me![List3]
      DoCmd.OpenForm stDocName, , , stLinkCriteria
   End If
   DoCmd.Close acForm, "frmSearchClient2", acSaveNo
   DoCmd.Close acForm, "frmSearchClient1", acSaveNo
Exit_Command3_Click:
   Exit Sub
Err_Command3_Click:
   MsgBox Err.Description
   Resume Exit_Command3_Click

End Sub

[taylorosso]

ha sorry mate,

it all makes so much sense when i am writing it out :P

I'll give your solution a try and get back to you.

Cheers

[RuralGuy]

Describing what the code does wrong may help me better understand what you need.

[taylorosso]

I used your if statement in the code for my command button.

Now when there are no search results and the user presses 'continue' it just closes the search box instead of try to open the next form with a selected record.

My working code is as follows

Private Sub Command3_Click()

On Error GoTo Err_Command3_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Me.List3.ListCount > 0 Then
stDocName = "frmClients"
stLinkCriteria = "[ClientID]=" & Me!
[List3]
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "frmSearchClient2", acSaveNo
DoCmd.Close acForm, "frmSearchClient1", acSaveNo
Else
DoCmd.Close acForm, "frmSearchClient2", acSaveNo
DoCmd.Close acForm, "frmSearchClient1", acSaveNo
End If

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub

Thanks for the help again RuralGuy, I knew what i wanted to do just didnt know how to phrase my syntax.

[RuralGuy]

Didn't the code I posted do *exactly* the same thing as the code you are using?

[taylorosso]

yeah it did, I just needed the If statement part really