Calendar displaying autonumber from a combo box..

[dagsgaz]

Hi all,
First post here and very new to VBA so apologies in advance! I've tried searching on Google and also here and couldn't find much.
I have created a calendar using MS Access and VBA. I'm trying to get it to display holidays and what shift the person is on. My code almost works correctly...
Issue im having is that it displays the autonumber from the combo box. I've set the colums widths etc, and to view my tables and query it does display the name correctly.

"Fullname" is the name of the the fieldname that is displaying the number.

Sub DisplayBirthdaylist(dselectdate As Date, lcontrol As Control) ' This is the popup when cal dates are clicked
    Dim rst As Recordset
    Dim sql As String
    Dim sList As String
        
        sql = "select * from qryHolidays where Holday=" & Format(dselectdate, "dd") _
        & " and HolMonth=" & Format(dselectdate, "mm") & ";"
            
    Set rs = CurrentDb.OpenRecordset(sql)
        
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        
        Do Until rs.EOF
        
            sList = sList & rs!Fullname & " - " & rs![Shift?] & vbCrLf
                      
            rs.MoveNext
        Loop
    End If
    
    Set rs = Nothing
    
    lcontrol.Caption = sList

 

[CJ_London]

Sounds like you are using a lookup field in your table

Remove the lookup and you will then see what you actually need for your sql

 

[dagsgaz]

Sounds like you are using a lookup field in your table

Remove the lookup and you will then see what you actually need for your sql

Hi;

It is a lookup field, it’s submitted via a form where the operator can drop down a list, select there name and then Input the dates they are off.

 

[CJ_London]

I'm talking about in your table

see this link about the problems with lookup fields

The Access Web - The Evils of Lookup Fields in Tables

 

[theDBguy]

Hi. Welcome to AWF!

Can you post a screenshot of your table in design view showing the properties screen of your lookup field?

 

[Pat Hartman]

The problem may be a table level lookup field or it might be simply not understanding how a combo on a form works.

The combo is bound to the FK field which is normally numeric. This is the ControlSource. The RowSource of the combo is a table or query that displays data from the foreign table. It includes the PK plus at least one field to display. The Columns property of the combo, contains width settings for each field selected by the RowSource. If the width is 0, the column is hidden. If the width is not zero, the column is visible when the list is dropped. The field that you see when the list is closed is the first non-zero width column defined by the Columns property.

So, if you have three columns selected by the RowSource and the Column widths are 0,2,1. When the list is dropped, you will see the second and third columns but when the list is closed, you will see only the second column. If you wanted to always see the third column, there are two options,
1. Concatenate columns two and three in the RowSource query so that the RowSource now only contains two columns. Expand the width and the Column property now becomes - 0,3
2. Display the third column in a separate control. The ControlSource of the separate control would be something like :

=cboSomeName.Column(2)

The Columns property of the combo is a zero based array. therefore, the columns are numbered 0, 1, 2 == the third column is .Column(2)