UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Extracting Value From A Combo Box Based On A Multi Value Field, Access 2016    
 
   
namron
post Oct 1 2019, 03:18 PM
Post#1



Posts: 91
Joined: 31-August 11
From: Lancashire,UK


Hi

Not sure if this is the right place for this question but here goes.

I have a form containing a combo box which allows multiple selections from a multi-value field. I know these aren't recommended but that's how the system was set up.

What i'm trying to do is create an If statement in VBA which will check if a particular entry in the list has been chosen and, if it has, mark a separate Yes/No field in the form as 'Yes'.

The code I've used will not work. I've tried it on another combo box which is based on a field which doesn't allow muti-value selection and it works fine.

The code I'm using is
CODE
Private Sub cboSessionType_LostFocus()
If Me.cboSessionType = 22 Then
Me.OISC = True
End If
End Sub


This gives me a Run Time Error 13, Type mismatch. I've tried various adjustments to the code without success.

I understand that multi value fields use hidden tables that I may not be able to get to? Is there a way of identifying the particular items selected in the combo box?

Thanks



Go to the top of the page
 
Larry Larsen
post Oct 1 2019, 03:48 PM
Post#2


UA Editor + Utterly Certified
Posts: 24,375
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
I'm not familiar with MVF but I know some members that have spent some time putting a couple of interesting profile and demo's about those "pesky" MVF..

Check these out:
Multi Value Fields Based On Standard Join Tables, Access 2003

Multi Value Fields

Multi-value Combo For Filtering With Enhancements, Office 2007

Multi value Combo Boxes in A2007

Reading the above should keep you up most of the night..LOL..
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
namron
post Oct 3 2019, 11:55 AM
Post#3



Posts: 91
Joined: 31-August 11
From: Lancashire,UK


Thanks Larry

That was slightly useful as it made me realise I should be using Me.cboSessionType.Column(1).

I've moved on slightly but still not fully working.

Just to reiterate my problem:
I have a table tblSessions containing a field called SessionType which has been set up as a multi value field. I have a subForm a containing a combobox control called cboSessionType.

If Session type 22 is selected either by itself, or with other selections, I need to update a seperate Yes/No field called OISC on the same form as 'True'.

I'm struggling to get this to work. I've tried an If statement and a Select statement and both give the same result:

If Session Type 22 is selected alone then it does mark the OISC field as True.

If anything other then Type 22 is selected it doesn't mark the OISC field as True, which is correct.

However if Type 22 is selected along with anything else then it doesn't update the OISC field to True which I want it to do.

My latest attempt at the code is

Private Sub cboSessionType_LostFocus()
Select Case Me.cboSessionType.Column(1)
Case 22
Me.OISC = True
Case Else
Me.OISC = False
End Select
End Sub

I've also tried having a seperate case using wild cards:

Case "*," & 22 & ",*"
Me.OISC = True

but that doesn't work either.

Anyone any idea where I'm going wrong?

Thanks
Norman

Go to the top of the page
 
arnelgp
post Oct 3 2019, 12:39 PM
Post#4



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


do you PK field in your subform, what is its fieldname and controlname on the subform?
on tblSessions, what is the RowSource (SQL) of SessionType field?
CODE
Private Sub cboSessionType_AfterUpdate()
    Dim rp As DAO.Recordset2
    Dim rc As DAO.Recordset2
    Dim bolChecked As Boolean
    Set rp = Me.Recordsetclone
    rp.FindFirst "[pkFieldName]=" & Me.pkFieldName
    Set rc = !SessionType.Value
    With rc
        If Not (.BOF An .EOF) Then .MoveFirst
        Do While Not .EOF
            If .Fields(0) = 22 Then
                bolChecked = True
                Exit Do
            End If
            .MoveNext
       Loop
       .Close
    End With
    Set rc = Nothing
    If bolChecked Then
        rp.Edit
        rp!OISC = -1
        rp.Update
    End If
    set rp = Nothing
End Sub

This post has been edited by arnelgp: Oct 3 2019, 12:57 PM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
namron
post Oct 3 2019, 01:45 PM
Post#5



Posts: 91
Joined: 31-August 11
From: Lancashire,UK


Hi arnelgp

Thanks very much for your input. Your suggested code is a bit complex for me to follow and I'm not sure I understand what you are asking.

The Primary Key of tblSessions is typeID and is included in the rowsource of the subform but is not included as a field on the subform. The control name of the combo box on the subform is cboSessionType.
The SQL of the rowsource of the combo box is
SELECT tblSessionType.TypeID, tblSessionType.SessionType
FROM tblSessionType
ORDER BY tblSessionType.SessionType;

In your code
If Not (.BOF An .EOF) Then .MoveFirst should this read BOF And EOF ?

Could you please clarify your reply, particularly which parts I need to update and with what information?

Thanks again.

Norman
Go to the top of the page
 
arnelgp
post Oct 3 2019, 01:56 PM
Post#6



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


I will explain.
to access the values on Multi-Value field, you
need to treat it as recordset (actually recordset2).
you cannot get it directly throught some column index.

I ask for the pk becauses we need it to position the
parent recordset properly. so you Must add typeID field
to your subform (on it's Visible Property set to No, so it will not be shown).

BOF = Beginning of file
EOF = End of file

if the recordset has BOF=True and EOF=true, then the recordset has no record,
since the record pointer is at BOF and EOF.

tblSessions is the parent recordset
SessionType field is the child recordset.

try this code:
CODE
Private Sub cboSessionType_AfterUpdate()
    Dim rp As DAO.Recordset2
    Dim rc As DAO.Recordset2
    Dim bolChecked As Boolean
    Set rp = Me.Recordsetclone
    rp.FindFirst "[typeID]=" & Me.typeID
    Set rc = !SessionType.Value
    With rc
        If Not (.BOF And .EOF) Then .MoveFirst
        Do While Not .EOF
            If .Fields(0) = 22 Then
                bolChecked = True
                Exit Do
            End If
            .MoveNext
       Loop
       .Close
    End With
    Set rc = Nothing
    If bolChecked Then
        rp.Edit
        rp!OISC = -1
        rp.Update
    End If
    set rp = Nothing
End Sub

This post has been edited by arnelgp: Oct 3 2019, 02:03 PM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
theDBguy
post Oct 3 2019, 02:07 PM
Post#7


Access Wiki and Forums Moderator
Posts: 76,484
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just FYI, you could also try using DLookup() to see if it will be easier for you.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
namron
post Oct 3 2019, 03:10 PM
Post#8



Posts: 91
Joined: 31-August 11
From: Lancashire,UK


Hi again

Thanks very much for your explanation and your patience.

I've added the typeID field to the subform and then added your code to the AfterUpdate event of the combo box control. I then went into a record in the form and made a selection in th combo box. When I clicked OK to commit the entry I received an error message:
Compile error:
Invalid or unqualified reference

this part of the code was highlighted - !SessionType.Value

Thanks
Norman

Go to the top of the page
 
arnelgp
post Oct 4 2019, 02:38 AM
Post#9



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


replace the fault field with the fieldname where
cboSessionType is bound.

I am afraid you can't do it on the combos afterUpdate event.
if there are no records on the parent recordset, it will fail.

see this example, and see the code on the Form's
AfterUpdate.
This post has been edited by arnelgp: Oct 4 2019, 03:08 AM
Attached File(s)
Attached File  sessionSample.zip ( 33.22K )Number of downloads: 4
 

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
namron
post Oct 4 2019, 05:16 AM
Post#10



Posts: 91
Joined: 31-August 11
From: Lancashire,UK


Hi

Thanks again for the time you've spent on this. I've looked at the example and confirm that it works when adding new records.

There is one remaining issue. If a User later goes back and edits one of the records with 22 in and amends it to a different session type, it does not then update the 'True' indicator for OISC to No.

Is there a way of getting around that?

Regards

Norman



Go to the top of the page
 
arnelgp
post Oct 4 2019, 05:32 AM
Post#11



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


remove the statement:

If bolFound … End If

instead directly Edit the parent recordset:

rp.Edit
rp!OISC = bolFound
rp.Update

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
namron
post Oct 4 2019, 02:25 PM
Post#12



Posts: 91
Joined: 31-August 11
From: Lancashire,UK


Hi arnelgp

That's wonderful, thank you so much. Got it working now.

Norman

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th November 2019 - 06:09 PM