combo box with UserDefined function and wrong ...

I have a cmbo box with fnVin in its RowSourceType (nothing in its RowSource) which populates the combo box.

When an item is selected from the dropdown, it shows some other value (from the same field).  Why it doesn't display what user selects.  For example, when user select 5, it shows 7 or some other number.

Function fncboVin(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
'On Error Resume Next

    Dim ReturnVal As Variant
    ReturnVal = Null
    Select Case code
        Case acLBInitialize                ' Initialize.
            Entries = 0
            Entries = Entries + 1
.
.
.
    End Select
    fncboVin = ReturnVal
End Function

Code works fine because it populate the combo box ok. Is there some setting

Thanks
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harfangCommented:
I'm guessing that you have a mix-up with a non-unique hidden bound column. A combo box does several things:
- displays a list (one or several columns)
- translates a code to text (e.g. bound column is hidden)
- allows user-editing
- translates text back to a code (again for a hidden bound column)

If the list is displayed, it does not mean that your function really works as intended. Try first with bound column = 0, or at least without any hidden columns.

Else, I guess we will need the full function, and the relevant properties of the combo box, namely bound column and column widths.

Cheers!
(°v°)
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi (°v°),

Function fncboVin(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
On Error GoTo Errs      
    Dim ReturnVal As Variant
    ReturnVal = Null
    Select Case code
        Case acLBInitialize                ' Initialize.
            Entries = 0
            Entries = Entries + 1
            ReturnVal = Entries
        Case acLBOpen                        ' Open.
            ReturnVal = Timer
        Case acLBGetRowCount            ' Get number of rows.
            ReturnVal = lngRowVin
        Case acLBGetColumnCount    ' Get number of columns.
            ReturnVal = 1
        Case acLBGetColumnWidth    ' Column width.
        Case acLBGetValue
            ReturnVal = rsVin!VIN 'dbs(row)
            'MsgBox VarType(rsVin!VIN)
            'If Entries = lngRowVin Then
            'End If
            If Entries < lngRowVin Then
                rsVin.MoveNext
            Else
                rsVin.MoveFirst
            End If
        Case acLBEnd                        ' End.
   
    End Select
    fncboVin = ReturnVal
On Error Resume Next
Forms!fSearch!lblVinQty2.Caption = Entries    'to see if Entries  matches lngRowVin
Forms!fSearch!lblVinQty.Caption = lngRowVin ' on the form
Forms!fSearch.Repaint
   
Exit Function
Errs:
'SeRs just sets rsVin again, it becomes some how it losses it
If Err.Number = 91 Or Err.Number = 3021 Then
    rsVinYN = False
    Forms!fSearch.SeRs
Else
    MsgBox Err.Description
End If

Resume Next

End Function

I suspect, my code has some problem.  It doesn't return the same set consistantly.  In the past I was very good with UserDefined functions.  I guess I am a bit rusty.

It is a real pleasure to hear/read from you.  Now, I know I am at good fnags, LOL.

Will check back in a couple of hours.

Thanks,

Mike
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Longh day here

good fangs is correct.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

harfangCommented:
I see a few problems:

acLBInitialize should just return true or false (false meaning failure to initialize)

The variable Entries is confusing. What is it suposed to contain?

You do not use the variable "row" to find the proper record. In other words, the returned record is unpredictable.

Anyway, to return rows from a recordset, use this as a template:
(for a two-column combo, first colum = key field, hidden)


Public Function GetEmployees( _
    varFld As Control, _
    varID As Variant, _
    varRow As Variant, _
    varCol As Variant, _
    varCode As Variant _
    ) As Variant
   
    Static srecEmployees As DAO.Recordset
   
    Select Case varCode
   
        Case acLBInitialize
            'On Error Resume Next
            Set srecEmployees = CurrentDb.OpenRecordset("Employees", dbOpenSnapshot)
            GetEmployees = (Err = 0)
           
        Case acLBOpen
            GetEmployees = Timer ' but why?
           
        Case acLBGetRowCount
            With srecEmployees
                If .RecordCount Then .MoveLast
                GetEmployees = .RecordCount
            End With
           
        Case acLBGetColumnCount
            GetEmployees = 2
           
        Case acLBGetColumnWidth
            GetEmployees = -1    ' use default
           
        Case acLBGetValue
            With srecEmployees
                .AbsolutePosition = varRow
                If varCol = 0 Then
                    GetEmployees = !EmployeeID
                Else
                    GetEmployees = !LastName & ", " & !FirstName
                End If
            End With
           
    End Select
   
End Function


Happy hacking!
(°v°)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
How can have text A+ appear like A°  (+ in the place of °)

Mike
0
harfangCommented:
> How can have text A+ appear like A°

I do not understand. Do you mean: Replace(strSomeText, "°", "+") ???

(°v°)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.