How do I get a name to display instead of a value?

[anoble1]

I have a database I am making. I have 1 table with data.
I am making a "limit" that will limit records based on a combobox. When I select the table column list it has all the names. When I hit limit it goes through here.

Private Sub cmbLocalOffice_AfterUpdate()
'Filter frmAll based on search criteria
Forms!frmMain!frmAll.Form.RecordSource = "SELECT tblAirwatch.ID, tblLocalOffice.LocalOffice, tblAirwatch.Region, tblAirwatch.Address, tblAirwatch.City, tblAirwatch.State, tblAirwatch.ZipCode, tblAirwatch.iPadSerialNumber, tblAirwatch.AirwatchName, tblAirwatch.PrinterBluetoothPin FROM tblAirwatch INNER JOIN tblLocalOffice ON tblAirwatch.LocalOfficeID = tblLocalOffice.LocalOfficeID WHERE (((tblLocalOffice.LocalOffice)=[Forms]![frmMain]![frmAll]![LocalOfficeID].value)) ORDER BY tblLocalOffice.LocalOffice;"
 
Forms!frmMain.Caption = "Search Results"
End Sub

the cmbLocalOffice.Value has a value of "1" instead of the Office name. I am missing something easy here. Does the code need to be the .value since I am getting text and not a number?

[ADezii]

Assuming cmbLocalOffice is Bound to your Table, a Query, or an SQL Statement based on the Table, you are more than likely reading the Value of the 'Bound' Column. The actual Value you are looking for resides in another Column other than the 'Bound' one. The Syntax for referring to a Column Value within a Combo Box selection is:

Me![cmbLocalOffice].Column(n)

where n is a Zero based Column Number.

[anoble1]

if you see my attachments, my cmblocaloffice is a combo box on my screen with the selections. on the left is the list i want to limit based on what i select. On screenshot Help3.jpg you see that it is finding the value 1 which is right, but why does it change my screenshot help4.jpg to say a #1 instead of Albany, and it still does not limit it?

On your statement, does that need to be coded like that? Or a form property?

[ADezii]

There appears to be some missing Code after GCriteria is established and Local Office is populated, kindly Post it. Also, post the Row Source for the Combo Box.

[anoble1]

I corrected the code above. I updated the code to something easier to me that may work better.

Combo box Row Source here:

SELECT tblLocalOffice.LocalOfficeID, tblLocalOffice.LocalOffice FROM tblLocalOffice ORDER BY tblLocalOffice.LocalOffice;

Please see this too.
When I limit the selections for "Americus" it prompts me to enter in the name for some reason. So, I typed in Americus, and the limit worked! but, my first Local Office turned from "Albany" to "2"

[NeoPa]

Look at your .ColumnWidths property. The value shown will always be the leftmost column in your list with any width. Either put the column you want shown as the first one, or make sure all columns to the left of it have width of zero (0).

This is not connected to the bound column, which is set in a different property - .BoundColumn.

[anoble1]

This is what I got when I changed the ColumnWidth to "2"
Same outcome.

[NeoPa]

There are three properties that are associated with width.

.Width
.ListWidth
.ColumnWidths

The one I drew to your attention was not misspelled. It is not designed to be used as a single item when multiple columns are used, but as a list.

If this is, indeed, the property you set to "2" then we would expect that to give the result you saw. It's certainly not what I suggested.

If you revisit my previous post and follow the information contained therein more carefully you should find a way that suits you.

[NeoPa]

Here's an example you may want to use :
.RowSource =

SELECT   [LocalOffice],[LocalOfficeID]
FROM     [tblLocalOffice]
ORDER BY [LocalOffice]

.ColumnWidths = "2;.5"
.BoundColumn = 2

There are other possibilities depending on how you want it, but this is at least an example of one of them that might help.

[anoble1]

I took your advise on the RowSource.

SELECT tblLocalOffice.LocalOffice, tblLocalOffice.LocalOfficeID
FROM tblLocalOffice
ORDER BY tblLocalOffice.LocalOffice;

Example - If I type in the second Office in the list "Alma", it will find and limit now! However, it changes the 1st records name to "Alma" too, instead of "Albany". When I did the BoundColumn, it limited my results, but still is changing my first record in the list to what I typed in. Why is that box coming up anyway?

I have noticed. Whenever I select something from the cmbLocalOffice it automatically changes my 1st record (Albany) on the main screen or search results to match what I selected. Example, if in the cmbLocalOffice I select "Americus" the (Albany) on the main screen will change to "Americus" and there will be 2 of them. just the Local Office name.

[ADezii]

As previously stated, I still think that part of the problem is in the remainder of the Code (after Criteria is set) in the cmbLocalOffice_AfterUpdate() Event, that you posted a Graphic of in Post# 3, 3rd Graphic. My eyes cannot decipher this, maybe NeoPa's can since he is a lot younger. (LOL).

[NeoPa]

It seems you are looking to provide a filter. Your earlier terminology confused me thoroughly. I assumed you were working with a control in the detail section of your form on a per-record basis.

For filtering, you can look at Example Filtering on a Form to get a better background understanding of the issue. In this case, things are somewhat clearer now, but still quite confusing when I try to understand what you're actually trying to achieve.

By reverse-engineering the SQL of your first post I have a clue as to what data is showing and from where. So, the form is basically showing the contents of [tblAirwatch] but shows the Office Description ([LocalOffice]) from [tblLocalOffice]. That I get.

It appears there are two issues with the SQL in the code of your OP though :

1. [Forms]![frmMain]![frmAll]![LocalOfficeID] doesn't appear to be the name used for the control I assume we're talking about.
2. Assuming some sort of basic consistency of naming in your project, you're comparing the name of the office on one side with the ID on the other.

An alternative way to filter is to use a reference in the SQL to the control you want to filter on. In the example SQL below I've assumed the name of your control is cmbLocalOffice and that it returns an ID value rather than a name/text one :

SELECT   tblAirwatch.ID
       , tblLocalOffice.LocalOffice
       , tblAirwatch.Region
       , tblAirwatch.Address
       , tblAirwatch.City
       , tblAirwatch.State
       , tblAirwatch.ZipCode
       , tblAirwatch.iPadSerialNumber
       , tblAirwatch.AirwatchName
       , tblAirwatch.PrinterBluetoothPin
FROM     [tblAirwatch]
         INNER JOIN
         [tblLocalOffice]
  ON     tblAirwatch.LocalOfficeID = tblLocalOffice.LocalOfficeID
WHERE    (tblAirwatch.LocalOfficeID=Nz([Forms]![frmMain]![frmAll]![cmbLocalOffice],tblAirwatch.LocalOfficeID))
ORDER BY tblLocalOffice.LocalOffice

[NeoPa]

ADezii
"My eyes cannot decipher this, maybe NeoPa's can since he is a lot younger. (LOL)."

I wish. That's not legible (to anyone I suspect). I increased the zoom right up, but the quality is far too poor for comfortable reading even when it's big. The text is all just very fuzzy. Posting code in a graphic is never a good idea. Posting it within the provided [CODE] tags, on the other hand, is.

[zmbd]

anoble1:
What is being asked is that post the text of the code and not screenshots.
You can cut and paste the script from the editor window, please format it using the [CODE/] button.

[anoble1]

Sorry for the confusion. I though I would code it an "easier" way that I may could understand better, tagging on your suggestions. let me try and clear up confusion that I have mode above. As of now. below is the code on the "AfterUpdate" on the filter that I attempted to make.

Private Sub cmbLocalOffice_AfterUpdate()
Dim strSQL As String
Dim dbs_curr As Database
Dim records As Recordset
 
Set dbs_curr = CurrentDb
 
Forms!frmMain!frmAll.Form.RecordSource = "SELECT tblAirwatch.LocalOffice, tblAirwatch.Region, tblAirwatch.Address, tblAirwatch.City, tblAirwatch.State, tblAirwatch.ZipCode, tblAirwatch.iPadSerialNumber, tblAirwatch.AirwatchName, tblAirwatch.PrinterBluetoothPin FROM tblAirwatch INNER JOIN tblLocalOffice ON tblAirwatch.LocalOfficeID = tblLocalOffice.LocalOfficeID WHERE (((tblLocalOffice.LocalOffice)=[Forms]![frmMain]![frmAll]![cmbLocalOffice].value)) ORDER BY tblAirwatch.LocalOffice;"
 
 
'Change the caption on frmMain
Forms!frmMain.Caption = "Search Results"
 
End Sub

@NeoPa
Don't know if i used your code correctly. I commented my line out and used yours. The filter did not work on mine. But let me do some reading on the link you posted and see what I'm missing.

[zmbd]

To put it mildly, Post#15 Code-Block-Line8 - YUCK!!!!

How can you posibly know if your string is resolving correctly... you can't

Try building the SQL String FIRST!

Dim strSQL As String
 
strSQL = "SELECT tblAirwatch.LocalOffice, tblAirwatch.Region" & _
   ", tblAirwatch.Address, tblAirwatch.City" & _
   ", tblAirwatch.State, tblAirwatch.ZipCode" & _
   ", tblAirwatch.iPadSerialNumber, tblAirwatch.AirwatchName" & _
   ", tblAirwatch.PrinterBluetoothPin" & _
   " FROM tblAirwatch INNER JOIN tblLocalOffice " & _
   " ON tblAirwatch.LocalOfficeID = tblLocalOffice.LocalOfficeID" & _
   " WHERE (((tblLocalOffice.LocalOffice)=" & _
   " [Forms]![frmMain]![frmAll]![cmbLocalOffice].value))" & _
   " ORDER BY tblAirwatch.LocalOffice;"

then you can use a debug.pring to find the issue....

You then use it in line 8 as:

Forms!frmMain!frmAll.Form.RecordSource = strSQL

Also, are you refering to a subform recordsource?


I suspect it is :

" [Forms]![frmMain]![frmAll]![cmbLocalOffice].value))" &

Because the way that is written, you're not getting the value returned from that control, you are getting the litteral string of "[Forms]![frmMain]![frmAll]![cmbLocalOffice].value))"
Also is this a string or a numeric value being returned by cmbLocalOffice ?

[anoble1]

Greetings NeoPa, ADezii who always help me and zmbd. Check this out. On the cmbLocalOffice I had a control source on it. I should have left it blank with no control source. I thought it was my SQL error. I un-bound the control source and everything worked fine.

Thanks guys

[NeoPa]

Z has a good point about working out string values into string variables before using them if you are debugging. It does make life so much easier for all.

I hear you that the problem was in the fact that you set a .ControlSource property value when it should have been left empty.

Nevertheless, rather than setting the whole .RecordSource again every time the ComboBox is updated, it would make a lot more sense simply to set the .Filter and .FilterOn properties. That's what they're there for. Simplifies not only the code, but also any work developing or maintaining it ;-)