Multi-column unbound combo data to report: error 2

[SBM1745]

Read through

http://www.tek-tips.com/viewthread.cfm?qid=1199817&page=1

and found an answer I needed. I was doing something similar in my form. I did get one oddity. My form loads and queries a table to populate comboTermFinalCheck.

SELECT tblPAY_EMPLOYEES_Extension_UserCode12.ID, tblPAY_EMPLOYEES_Extension_UserCode12.Description FROM tblPAY_EMPLOYEES_Extension_UserCode12;
*Column Width = 0";1", Bound Column = 1

When a checkbox is clicked, the combobox (which is nested in a tab control) should display the the option that relates to this employee record (the query that the form is based on).

Private Sub chkTerm_Click()
'Set Viewable Tab
    TabActions.Pages(0).Visible = False
    TabActions.Pages(1).Visible = False
    TabActions.Pages(2).Visible = False
    TabActions.Pages(3).Visible = False
    TabActions.Pages(4).Visible = False
    TabActions.Pages(5).Visible = True
    TabActions.Pages(6).Visible = False
'reset click values
    chkHire.Value = 0
    chkRehire.Value = 0
    chkWage.Value = 0
    chkTransfer.Value = 0
    chkLOA.Value = 0
    'chkTerm.Value = -1
    chkOther.Value = 0
'set Page values
    txtTermDate.Value = Date
    txtTermLastDay.Value = [UserCodeT_11]
    chkEligibleRehire.Value = [EligibleForRehire]
    Me.comboTermFinalCheck = Me!comboTermFinalCheck.ItemData([UserCodeL_12] - 1)
End Sub

What I found is that the combo box would display the next option. When [UserCodeL_12] = 3 (Hand Delivered), it would display 4 (Other). I don't know why that is, and subracting 1 fixed it.

Using the above code, has caused a different problem in my report for this form. Using:

=[Forms]![frmByChris_PersonnelActionNotice]![comboTermFinalCheck]

Shows 3 instead of Hand Delivered, even though the combobox is bound to column 1 (the descriptions). I could use some help on this.

-Chris
Starlight B. M.

 

[MajP]

What I found is that the combo box would display the next option. When [UserCodeL_12] = 3 (Hand Delivered), it would display 4 (Other). I don't know why that is, and subracting 1 fixed it

Reason: It is a zero based array. itemdata(0) is the first item.

Shows 3 instead of Hand Delivered, even though the combobox is bound to column 1 (the descriptions). I could use some help on this.

I think column 1 is the ID field, but you just have it hidden. What is the recordsource for this control?

And this?
=[Forms]![frmByChris_PersonnelActionNotice]![comboTermFinalCheck].column(1)
where column(1) is actually the second column

 

[SBM1745]

That combo box uses this as the RowSource:

SELECT tblPAY_EMPLOYEES_Extension_UserCode12.ID, tblPAY_EMPLOYEES_Extension_UserCode12.Description FROM tblPAY_EMPLOYEES_Extension_UserCode12;

ID is the ID/Key and Description is the corresponding description. The user selects a Final Check Disposition from a drop down list in the main applacation that gets stored by it's ID (an integer). In this case, 1 through 8. I want to display the label on this form and report. The original query just returns an integer (say 3), to negate having to create a Select Case, I used a Combo Box. Based on the users selection on the form, The combo box displays a speficic value (see set date values in code box below). I want that Description (the second column from the combo box) to show up in a report for printout.

Private Sub chkTerm_Click()
'Set Viewable Tab
    TabActions.Pages(0).Visible = False
    TabActions.Pages(1).Visible = False
    TabActions.Pages(2).Visible = False
    TabActions.Pages(3).Visible = False
    TabActions.Pages(4).Visible = False
    TabActions.Pages(5).Visible = True
    TabActions.Pages(6).Visible = False
'reset click values
    chkHire.Value = 0
    chkRehire.Value = 0
    chkWage.Value = 0
    chkTransfer.Value = 0
    chkLOA.Value = 0
    'chkTerm.Value = -1
    chkOther.Value = 0
'set date values
    txtTermDate.Value = Date
    txtTermLastDay.Value = [UserCodeT_11]
    chkEligibleRehire.Value = [EligibleForRehire]
    If chkEligibleRehire.Value <> -1 Then
        chkNoRehire.Value = -1
    End If
    If [UserCodeL_12] <> "" Then
        Me.comboTermFinalCheck = Me!comboTermFinalCheck.ItemData([UserCodeL_12] - 1)
    End If
End Sub

-Chris
Starlight B. M.

 

[PHV]

If UserCodeL_12 is a valid tblPAY_EMPLOYEES_Extension_UserCode12.ID, why not simply this ?
Me!comboTermFinalCheck = [UserCodeL_12]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SBM1745]

PHV, UserCodeL_12 is a valid ID. I'll play with that. That addresses how to get the combo box to show the correct value. I'm getting the ID value on the report, and not the corresponding description.

FYI: The contents of the table (tblEMPLOYEES_Extension_UserCode12) look like this:
[ID][Description]
1[tab]Mail
2[tab]Mail To Alt. Address
3[tab]Hand Deliver
4[tab]Other
5[tab]Mail Certifified
6[tab]Express Mail
7[tab]Will Pick Up
8[tab]DHL Delivered

-Chris
Starlight B. M.

 

[MajP]

And this?
Column widths = 0;1
Column count = 2
bound column = 1

 

[PHV]

Have you tried this in the report ?
=[Forms]![frmByChris_PersonnelActionNotice]![comboTermFinalCheck].Column(2)
Or this ?
=DLookUp("Description","tblPAY_EMPLOYEES_Extension_UserCode12","ID=" & [Forms]![frmByChris_PersonnelActionNotice]![comboTermFinalCheck])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, typo ...
Have you tried this in the report ?
=[Forms]![frmByChris_PersonnelActionNotice]![comboTermFinalCheck].Column(1)
Or this ?
=DLookUp("Description","tblPAY_EMPLOYEES_Extension_UserCode12","ID=" & [Forms]![frmByChris_PersonnelActionNotice]![comboTermFinalCheck])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SBM1745]

And this?
Column widths = 0;1
Column count = 2
bound column = 1

Oh man! I set the bound column = 2 and that fixed it. I was basing the column bounding rules on a 0,1,2... type of list, not 1,2,3...

PHV, you know a lot of great Access functions! I would have tried the DLookUp() had MajP's suggestion not fixed the problem. Thanks for the suggestion!

-Chris
Starlight B. M.