Adding New Values to a Combo Box, but ID numbers are showing up, not the names! (1 Viewer)

[mare]

Hello:

I have a database in which I keep track of associates. One of the fields is for their business position (i.e., buyer). This field is a combo box.

I ‘ve created a form so that when add new associates, I can click on the down arrow at the Business Position field and select one of the choices. I have also got an event going in case a new associate with a new (not in the list) position needs to be added. when I type in the new business position, a dialog box appears if I really want to add it. Here’s the VBA to add the new business position

BusPosition NotInList


Private Sub BusPosition_NotInList(NewData As String, Response As Integer)
On Error GoTo BusPosition_NotInList_Err
'Add a new record to the BusPositionTBL table
'and requery the BusPosition combo box
Dim NewPosition As Integer, MsgTitle As String, MsgDialog As Integer
Const MB_YESNO = 4
Const MB_ICONEXCLAMATION = 48
Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7
'make sure the user really wants to add it
MsgTitle = "Business Position is not in the list"
MsgDialog = MB_YESNO + MB_ICONEXCLAMATION + MB_DEFBUTTON1
NewPosition = MsgBox("Do you want to add this business position?", MsgDialog, MsgTitle)
If NewPosition = IDNO Then
Response = DATA_ERRCONTINUE
Else
DoCmd.OpenForm "BusPositionFRM", acNormal, , , acAdd, acDialog
Response = DATA_ERRADDED
End If
BusPosition_Exit:
Exit Sub
BusPosition_NotInList_Err:
MsgBox Err.Description
Resume BusPosition_Exit

End Sub


The new position is added to the BusPosition table (which is what it is supposed to do). When I bring up the associate (to edit some information), everything is where it should be

The problem:
When I run a report on the associates, where there should be their Business Position title, I get the Business Position ID number. I just want the titles.

I have tried all kinds of things to try to reason this out and fix it, but nothing works.

I have gone into the properties for the BusPosition field:

Column Count is 2 (BusPositionID and BusPositionName)

Column Widths are 0”, 1.5”

Row Source Type is Table/Query

The Row Source is BusPositionQRY

This has got me going loopy. Any help is greatly appreciated.

 

[Galaxiom]

Could your BusPositionQRY for the Row Source in the report have the field order reversed? Or an extra field before the ID?

 

[boblarson]

Make sure you aren't using lookups at table level, which can confuse things. See here for more about that.

 

[wiklendt]

a) when you open the table where the new associate data goes in, does it look correct? i.e., is your code putting the new data in as it ought to?

b) when you use the combobox, are the previous data presented as they had been, or are ALL the records showing the ID value and not the text field?

c) your 'not in list' code looks a bit funny to me... though the other posters seem not too bothered by it, so maybe it's just that i'm newer at this game than most... this is what one of my not-in-list events looks like (it does not have "Const"s or IDNO = 7 or anthing, and my feeling is that perhaps this is causing your issues):

(for adding new items via a form)

Private Sub cmbPersonID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cmbPersonID_NotInList

Dim intAnswer As Integer

intAnswer = MsgBox("The person " & Chr(34) & NewData & _
    Chr(34) & " is not currently listed." & vbCrLf & _
    "Would you like to add them to the list now?" _
    , vbQuestion + vbYesNo, "The Professional Equine Database")


    If intAnswer = vbYes Then
        Response = acDataErrContinue
        Me.cmbPersonID.Undo
        DoCmd.OpenForm "frmPeople", , , , acFormAdd, acDialog, NewData
        Me.cmbPersonID.Requery
    Else
        Response = acDataErrContinue
        Me.cmbPersonID.Undo
        Me.cmbPersonID.SetFocus
    End If
    
Exit_cmbPersonID_NotInList:
    Exit Sub

Err_cmbPersonID_NotInList:
        Msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
        MsgBox Msg, vbOKOnly, "The PED", Err.HelpFile, Err.HelpContext
    Resume Exit_cmbPersonID_NotInList

End Sub

(for adding new items directly with VBA)

Private Sub cmbProjectAssociation_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cmbProjectAssociation_NotInList

Dim intAnswer As Integer
Dim strSQL As String

intAnswer = MsgBox("The project " & Chr(34) & NewData & _
    Chr(34) & " is not currently listed." & vbCrLf & _
    "Would you like to add it to the list now?" _
    , vbQuestion + vbYesNo, "Specimen Database")
    
If intAnswer = vbYes Then

    strSQL = "INSERT INTO tblProjects([ProjectName])" & _
             "VALUES ('" & NewData & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "New project added.", vbInformation, "Specimen Databse"
    Response = acDataErrAdded

Else
    MsgBox "Please choose a project from the available list." _
            , vbInformation, "Specimen Database"
    Response = acDataErrContinue
End If

Exit_cmbProjectAssociation_NotInList:
    Exit Sub

Err_cmbProjectAssociation_NotInList:
    Msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    Resume Exit_cmbProjectAssociation_NotInList

End Sub

(this isn't PHP, it's VBA - i just use the tags to get colour into the code)