Double Click List Box

[Dannyboy11]

Hi

I have a list box where a user can double click a value in it, and based on the unique id of that row a form will come up where you can add information linked with what you've just opened from the list box.

However, if a user double clicks the blank white space below the values where they have ended, a VBA message comes up

Rune time error'3077':

Syntax error (missing operator) in expression.

END | DEBUG | HELP

Can anyone help me? How can I prevent a user double clicking where there's not an actual selection?

This only works if the user doesn't select a value before double clicking it, as otherwise what they previously clicked is the highlighted row and unique id. This is only if they click it first before anything else.

 

[Trevor G]

If the record source for the list box has to have a value then look to add something to the source to exclude null values.

Try this

Open the form in design view. Select the list box and open the properties go to the row source and then select the elipse button on the right, in the query builder select the ID field and in the criteria place in Is Not Null that should give you records only with an ID in it.

 

[Dannyboy11]

Hi Trevor

The row source is currently already filled in, as the list box returns the results of a query based on a previous select.

There is a combo box where the user selects a team, and then the list box will requery with all of the games that the selected team is involved in. I then want the user to be able to double click a game to see more information about it or edit the information about it.

The row source is currently:

SELECT [qrySearchGamesByTeam(w)].FixtureID, [qrySearchGamesByTeam(w)].Date, [qrySearchGamesByTeam(w)].[Home Team], [qrySearchGamesByTeam(w)].[Away Team], [qrySearchGamesByTeam(w)].KickOffTimes, [qrySearchGamesByTeam(w)].Referee FROM [qrySearchGamesByTeam(w)] ORDER BY [Date];

 

[Trevor G]

I understand the row source is showing the data source, but still go in and add the criteria as suggested to the id field.

How are blank records being displayed in the list box?

 

[Dannyboy11]

Ok I've added it, but it doesn't seem to have changed anything. The same error is displayed.

 

[Trevor G]

Is the double click event a macro or VBA? IF Macro you can convert the Form macro to VBA and then you could add an IF statement to deal with Null value being selected.

 

[Dannyboy11]

It's in the on double click event properties.

If CurrentProject.AllForms("frmGameInformation").IsLoaded Then
DoCmd.Close acForm, "frmGameInformation", acSaveYes
DoCmd.OpenForm "frmGameInformation", , , , , acDialog, lstGamesInvolvingTeam.Value
Else
DoCmd.OpenForm "frmGameInformation", , , , , acDialog, lstGamesInvolvingTeam.Value
End If

When I click 'Debut' what is highlighted is this code, that is in the form that is opening's on load event,

Dim rs As Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[ID] = " & Me.OpenArgs
Me.Bookmark = rs.Bookmark

 

[smig]

in the DoubleClick event add:

if me.ID = null then
msgbox "No record"
else
Open the form
end if

 

[Dannyboy11]

in the DoubleClick event add:

if me.ID = null then
msgbox "No record"
else
Open the form
end if

Hasn't worked. Using what you said, my code is now. If I change the code to Me.FixtureID (the name of the primary key) then the debug comes up with Compile error: Method or data member not found.

If Me.ID = Null Then
MsgBox "No record"
Exit Sub
Else
If CurrentProject.AllForms("frmGameInformation").IsLoaded Then
DoCmd.Close acForm, "frmGameInformation", acSaveYes
DoCmd.OpenForm "frmGameInformation", , , , , , lstUpcomingGames.Value
Else
DoCmd.OpenForm "frmGameInformation", , , , , , lstUpcomingGames.Value
End If
End If

 

[smig]

it should be a code, not a macro

why do you put Exit sub in the middle of the If ?

 

[Dannyboy11]

it should be a code, not a macro

why do you put Exit sub in the middle of the If ?

I'm a novice when it comes to Access. If there is no value and so the error message box shows, then I want it to finish and not then run the if statement.

 

[MikeLeBen]

the syntax of the if statement is the following:

the code is executed only if the condition/s is/are met, therefore if no selection is made, the only code that will be executed is that before the "else" statement (that is, only a msgbox will appear).

Conversely, if the condition/s isn't/aren't met (which means something has been selected), the execution will skip to the part after the "else" statement and everything will work as you expect it to.

 

[smig]

your code should look like

If Me.[COLOR=red]lstGamesInvolvingTeam[/COLOR] = Null Then
  MsgBox "No record"
Else
  If CurrentProject.AllForms("frmGameInformation").IsLoaded Then
     DoCmd.Close acForm, "frmGameInformation", [COLOR=red]acSaveNo[/COLOR]    ' You don't want to save the form (Data will be saved)
  end if
  DoCmd.OpenForm "frmGameInformation", , , , , , lstUpcomingGames.Value
End If

 

[ChrisO]

If IsNull(Me.lstGamesInvolvingTeam) Then

 

[twstew]

I know this is old, but for the benefit of those still reading it, I think the OP's problem was with a double click out of range of the list index, that is, a blank area of the listbox where there are no data rows. Given the various nuances of listboxes and how they behave, and how tedious it can become to control their behavior and account for their quirks, I think the best way to deal with this situation is to check the .ListIndex property. If you're out of range, it will be -1. It isn't always reliable to expect a check for null to work.

If MyListBox.ListIndex <> -1 Then
     DoCmd.OpenForm (etc)
End If

You still need to do things to tidy up the look and behavior, but this will always work to prevent the OP's problem. It ignores any double clicks in the listbox real estate not occupied by an actual row, which is what his problem was. (He wasn't clicking on a row that contained a Null, he was clicking outside of the list index.)