Copying details between multi-valued comboboxes

[Futures_Bright]

Hi all,

Firstly, I'm aware it's bad practice to use multi-valued comboboxes, but for the fields I use them it is a much nicer/cleaner input for the user to use.

The problem comes when copying to a similar record (for updated records I create a copy of the previous except for the change - this is because a record of the review of each record needs to be maintained).

I have a function in my code which, to be honest I forgot I had put in and don't know where I got it from (and have renamed variables to suit my application) - see below:

Public Function ReturnApplicabilityByIndex(ctl As Control, intIndex As Integer) As Variant
    Dim rstAppVal As DAO.Recordset 'array values from Applicability
    Dim lngCount As Long 'for copying Applicability
    Dim intRecord As Integer 'for copying Applicability

    Set rstAppVal = ctl.Parent.Recordset(ctl.ControlSource).Value
    rstAppVal.MoveLast
    lngCount = rstAppVal.RecordCount
    If intIndex > lngCount - 1 Then
        MsgBox "The requested index exceeds the number of selected values."
        GoTo exitRoutine
    End If
    rstAppVal.MoveFirst
    Do Until rstAppVal.EOF
        If intRecord = intIndex Then
            ReturnApplicabilityByIndex = rstAppVal(0).Value
            Exit Do
        End If
        intRecord = intRecord + 1
        rstAppVal.MoveNext
    Loop

exitRoutine:
    rstAppVal.Close
    Set rstAppVal = Nothing
    Exit Function

End Function

I definitely didn't write this myself because working with recordsets is something I'm not confident with! I'm now trying to call this function and can't for the life of me work out what intIndex is for and what I should input into the function? I don't know why this function needs to check if this intIndex is bigger than the record count for the recordset?

Any help/guidance (even alternate code) would be appreciated!


Kind regards,

Michael

 

[StarGrabber]

Hi Michael,

I've tested the function and it works fine, however, with the line

Set rstAppVal = ctl.Parent.Recordset(ctl.ControlSource).Value
​

replaced by

Set rstAppVal = ctl.Parent.Recordset
​

With the original line I get error 424, "Object required". Frankly, I've never seen code like this before.

First of all, I need your help. Although the title "Copying details between multi-valued comboboxes" tells what you're after, I have some understanding problems.

1. What do you mean with "multi-valued comboboxes"? Do they have several columns?
2. What do you mean with "copying to a similar record"? What is a "similar" record?
3. You say, you're "now trying to call this function". What exactly do you want the function to do?

 

[Futures_Bright]

Hi StarGrabber,

Sorry it has taken me a while to respond - I've had other priorities at work and moved onto other areas in my database in the meantime.

To answer your questions:

1. By Multi-valued combobox - I mean a lookup (which has multiple columns for usability but only one bound value of course) which allows multiple answers

2. By copying to a similar record, I mean identical except for one or two fields (it is an update to the previous one but I need to create a new record for it within the same table. When it is actually identical I just update the revision level on the existing record) - does that make sense?

3. I would like this function to copy all selected options from the previous records [Applicability] field and then to copy into the new record's [Applicability] field.

The bit in this code I don't understand is the role of intIndex - it is entered as a parameter when calling the function but doesn't change during the function; as far as I can tell, only the value of the field in the recordset where the index corresponds to intIndex is transferred. Also if the value of intIndex is greater than or equal to the number of records in the recordset it will only display a messagebox. Am I reading this correctly?

 

[StarGrabber]

The function the way I put it returns one single value, every time from the first recordset field ( --> index 0). The recordset should be the recordsource of the form which contains the control referenced by the variable 'ctl'. As the record number is determined by 'intIndex', for this no loop and no function is needed at all. You can get the same result with only six lines of code:

Dim rstAppVal As DAO.Recordset
Set rstAppVal = ctl.Parent.Recordset
rstAppVal.AbsolutePosition = intIndex
   
Dim varAppl as Variant
varAppl = rstAppVal(0).Value
Set rstAppVal = Nothing

The function you put up to discussion would be useful if you have several forms you want to read out certain values. In that case you should add a third parameter: the field index.

If I understand your application right, you have a form with an (unbound) lookup combobox. You select a value from that combobox (i.e. an entire record) and want to either duplicate it ("except for one or two fields") or "just update the revision level".

If you want to duplicate, how do you set the differing values?

Please explain the meaning of "...selected options from the previous records [Applicability] field". Are you talking about a multivalue field here?

 

[Futures_Bright]

Okay I'll try and go into a bit more depth. Each record in this table may only apply to a select few companies (a list of which is in a separate table). The majority will 'apply to all', but there are some where it only applies to 'Distributor' and/or 'Stockists' or 'Manufacturer'. Example for how this looks is attached. I need this relationship to be in place so that when we review records we know which apply to us and which we may need to flow to other companies or can ignore.

The lookup combobox displays the Name/Description of the company types but is bound to the ID of the company type (hidden key field). It is the contents of this combobox itself I wish to copy (In this example: the selection of Supplier, Distributor) to the same field in a new record (as I'm doing with the remainder of fields which will not change). The "except for one or two fields" is because the entire record is the same except for generally a maximum of two fields - it could be three including the 'Applicability' but it will be the same 99% of the time and easy enough for the user to add or remove one of the selections.

Does "ctl.Parent.Recordset" refer to all fields on the visible record? Would I instead want "me.Applicability"?

Has that cleared up the situation?

Edit: Okay I've been playing around and have a better idea of what I need to do in the code. So will have a go at amending tomorrow and feedback if it solves it or not.

 

[Futures_Bright]

Bah I'm giving up with this and just making a sub-form instead and a link table. The form won't look as clean but it saves a lot of headaches!

For anyone who's determined to make it work, this link appears the closest I could find - http://www.utteraccess.com/forum/lofiversion/index.php/t1903513.html