?
Solved

combo box with UserDefined function and wrong ...

Posted on 2006-03-23
6
Medium Priority
?
250 Views
Last Modified: 2006-11-18
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
0
Comment
Question by:Mike Eghtebas
  • 3
  • 3
6 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 16276714
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 16276788
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 16276789
Longh day here

good fangs is correct.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 16276902
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 16284249
How can have text A+ appear like A°  (+ in the place of °)

Mike
0
 
LVL 58

Expert Comment

by:harfang
ID: 16285506
> How can have text A+ appear like A°

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

(°v°)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question