Set a field width to zero in a select statement

[sk84gtspd]

Hi, I am building a form that uses list box selections on the form to make a temporary query and open the results in Excel.

It mostly works in just trying to make it more functional.

Currently the listbox that contains the first and last names of the personnel also has a unique shorttext 'number' as a primary key for the table (bad choice in my opinion but I didn't design the database I just have to work with it).

The short text primary key is hidden by an option given in the listbox wizard that let me set that field width to zero but still search on it. This is how I currently build the query's where clause when it involves names. I search the primary key.

I would like to update the names list box based upon which cities and locations are selected (both are multi-select).

Do I need to change how I make the "where" clause to use first and last names or an I use the same query and just hide the USER_ID

The query looks like this

 SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID
FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO
ORDER BY t_asset_personnel.LAST_NAME;

and the list box wizard hides USER_PHY_ID
by setting the width to zero

If I make my own query in the City_After_Update()
can I do something like this

 SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, [COLOR=red]t_asset_personnel.USER_PHY_ID
[/COLOR][COLOR=red].fieldWidth(0)[/COLOR]FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO
[COLOR=red]WHERE [forms]![myform]![citylist].[itemsSelected][/COLOR][COLOR=seagreen]'obviously more code is needed here this is just for conceptual illustration[/COLOR]
 ORDER BY t_asset_personnel.LAST_NAME;

Thanks for the help

 

[pbaldy]

It can be in the WHERE clause without being in the SELECT clause. In design view you'd uncheck the Show box.

 

[sk84gtspd]

I'm not sure that helps with my particular issue. This is a code snippet I have in City_After_Update() that drive the listbox for the Locations. When you select a city or multiple cities the Locations listbox is populated with locations within those Cities.

 Set CityListCtl1 = [Forms]![MasterQueryGenerator]![CityList]
 LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
            "FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION " & _
            "WHERE t_location.CITY IN ("
 For Each varItem In CityListCtl1.ItemsSelected
     LocQryStr = LocQryStr & "'" & CityListCtl1.ItemData(varItem) & "', "
 '    testString = testString & "'" & CityListCtl1.ItemData(varItem) & "', "
  Next varItem
                
'Trim the end of LocQryStr
LocQryStr = Left$(LocQryStr, Len(LocQryStr) - 2)
LocQryStr = LocQryStr & ");"
                    
 'MsgBox (LocQryStr)
 'REQUERY THE LOCATION LIST TO SHOW LOCATIONS IN THE SELECTED CITY
' Nullify row source and then reassign it
Me.LocationList.RowSource = ""
Me.LocationList.RowSource = LocQryStr

I want to do the same with names but I'm am building a dynamic where clause for a main Query that uses the User_PHY_ID but I don't want it to show up in the listbox with the choices.

 

[pbaldy]

You control that with the column widths property of the listbox (that's the setting the wizard changed for the other).

 

[sk84gtspd]

You're right PBaldy I guess since I'm pulling that same fields with the same number then I properties setting took care of it. Is there a way to set the field width from within the code say if I wanted the listbox to have 4 fields and only show three?

For completeness this is my working code for that section

 '%%%%%%%%%%%%%%%%%%%%%%
'This section of code drives the names selection
'%%%%%%%%%%%%%%%%%%%%%%
 Dim NamesQryStr As String
Dim testString As String
Dim NamesListCtl1 As Control
 Set NamesListCtl1 = [Forms]![MasterQueryGenerator]![Name_List]
 NamesQryStr = "SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME," & _
    "t_asset_personnel.USER_PHY_ID " & _
    "FROM t_location INNER JOIN (t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = " & _
    "t_asset_master.ASSIGNED_TO) ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION " & _
    "WHERE t_location.CITY IN ("
 For Each varItem In CityListCtl1.ItemsSelected
     NamesQryStr = NamesQryStr & "'" & CityListCtl1.ItemData(varItem) & "', "
     testString = testString & "'" & CityListCtl1.ItemData(varItem) & "', "
  Next varItem
 'Trim the end of LocQryStr
NamesQryStr = Left$(NamesQryStr, Len(NamesQryStr) - 2)
NamesQryStr = NamesQryStr & ");"
 
'MsgBox (NamesQryStr)
  
 'REQUERY THE Names TO SHOW Names in the SELECTED CITY
' Nullify row source and then reassign it
Me.Name_List.RowSource = ""
Me.Name_List.RowSource = NamesQryStr

 

[pbaldy]

Unless the number of fields will vary, I'd set it in design view and forget about it. It can be set in code; help has some info on the subject.

 

[vbaInet]

I would like to update the names list box based upon which cities and locations are selected (both are multi-select).

I think I understand what sk84gtspd is talking about, I helped him/her yesterday with the Locations code.

You can build the same WHERE [Field] IN(..) clause for both the City and Locations listboxes and apply that to your Names listbox. Loop through the Cities, build the IN() clause, loop through your Locations, build the IN() clause and join them with an AND:

WHERE [Cities] IN (...) AND [Location] IN(...)

 

[pbaldy]

I'll get out of the way then.