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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Updating Combobox Selected Items Not Working, Access 2016    
 
   
B99
post May 27 2020, 02:04 PM
Post#1



Posts: 62
Joined: 15-April 20



Hi All,

I've wandered onto thin ice (again) and need some help. I have a form that has a combo box bound to a MVF (I know...). The user will search for records by clicking a button which opens a popup form. On the popup form, the user will select one or more search results by ticking a checkbox. Once satisfied, they click a button to copy the results back to the other form. Specifically, there is a text field that is concatenated for multiple results, and the combobox should have the appropriate values checked. The combobox can have quite a few values so the user wants it to default which values are selected based on what results were chosen on the popup form.

I've been playing with this and I got it to work a couple of times but not reliably and I can't figure out what I am missing. Most of the time, when I click the 'copy' button, the text box is updated but the combobox is not.

Here is the sanitized code:
CODE
Option Explicit

Private Sub cmdCopyResults_Click()
  Dim db As Database
  Dim rsText As Recordset
  Dim strSelected As String
  
  ' Open a Recordset for the Selection table.
  Set db = CurrentDb
  Set rsText = db.OpenRecordset("SELECT ID, Text, MyMVF " _
                 & "FROM tblTest " _
                 & "WHERE tblTest.DraftCheckbox = True")
  
  If rsText.RecordCount > 0 Then
    rsText.MoveLast
    rsText.MoveFirst
  
    While Not rsText.EOF
    
      strSelected = strSelected & ";" & Nz(rsText.Fields("Text"), "")

      'Get the MVF values associated with the selected records
      Dim rsMVF As Recordset
      Dim iMVF As Integer
      Set rsMVF = rsText!MyMVF.Value

      If rsMVF.RecordCount > 0 Then
        rsMVF.MoveLast
        rsMVF.MoveFirst
        
        While Not rsMVF.EOF
        
          'Inconsistent results with this part
          Forms!MyMainForm!MySubForm.Form!cboMVF.Selected(rsMVF(iMVF)) = True
  
          rsMVF.MoveNext
        Wend
      End If

      rsMVF.Close
      Set rsMVF = Nothing
      rsText.MoveNext
    Wend
    rsText.Close
    Set rsText = Nothing
    db.Close

     Forms!MyMainForm!MySubForm.Form!txtText = strSelected
  
  End If
  
  DoCmd.Close acForm, Me.FormName, acSaveNo
  
End Sub

I know this probably isn't much to go on. Hopefully it's a small syntax error. Any ideas?

EDIT: The use of iMVF is a little confusing. The value will always be 0 which works, meaning I get the expected values with this line right before I set the combobox value:
CODE
          Debug.Print "MVF Value for "; iMVF; ":"; rsMVF(iMVF)

This prints "MVF Value for 0 : 63", etc. with one line for each MVF value on the record. I had one iteration of code where I tried incrementing the value, which does not work and I removed the increment command, but left the variable in place.
This post has been edited by B99: May 27 2020, 02:27 PM
Go to the top of the page
 
theDBguy
post May 27 2020, 02:26 PM
Post#2


UA Moderator
Posts: 78,487
Joined: 19-June 07
From: SunnySandyEggo


Hi. Probably best to post another demo db. Just a thought...

--------------------
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
 
projecttoday
post May 27 2020, 02:34 PM
Post#3


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


You need to .MoveLast before you test the record count, not after. There are 2 places in your code where you do this.

--------------------
Robert Crouser
Go to the top of the page
 
B99
post May 27 2020, 02:39 PM
Post#4



Posts: 62
Joined: 15-April 20



Hi theDBGuy, I knew that was coming! This one will take me a while to strip down but I will do so if needed. I was just hoping it was something obvious to those more experienced than me. The code does exactly what I want except for assigning the combobox values. If I put in debug statements, I can see that it is pulling each MVF value correctly, but the assignment is not working for some reason. It worked twice but I can't figure out what I changed that made it work (if anything) and now it isn't working pullhair.gif
Go to the top of the page
 
B99
post May 27 2020, 02:45 PM
Post#5



Posts: 62
Joined: 15-April 20



Hi Robert, the .movefirst / .movelast concept is new to me and was suggested by Doug Steele in another thread. This was his explanation for the placement:
QUOTE
Just to elaborate, AFAIK, the MoveLast and MoveFirst methods won't work with an empty recordset, which is the reason why I check the value of the RecordCount property first.


He explained that the value of recordcount may not be accurate if there are records returned, but if there are no records, then it will always be 0. Those two lines can be removed for this version of the code; they were leftover from an iteration where I used a for loop and needed the record count. Apologies for the confusion.

But the code does seem to work as far as navigating the records correctly. It correctly loops whether there are 0, 1 or many values on the MVF.
Go to the top of the page
 
B99
post May 27 2020, 03:06 PM
Post#6



Posts: 62
Joined: 15-April 20



One step closer! It seems to be related to the combobox having focus. While I'm on the popup form, if I click the 'copy' button, the combobox on the other form is not updated. But if I move the popup form to the side (it is not modal at the moment), then click in the other form's combobox, and then click the popup form's copy button, it works and the combobox is updated with the right values.

Unfortunately I can't figure out how to do it in the code. I've tried this right before setting the value:
CODE
Forms!MyMainForm!MySubForm.Form!cboMVF.SetFocus

but this doesn't seem to do anything. Is my syntax off?

Go to the top of the page
 
projecttoday
post May 27 2020, 03:09 PM
Post#7


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


Are you stepping through the code? Which line fails?

It doesn't do any harm to .MoveFirst before a loop and .MoveLast before a .RecordCount.

EDIT: I didn't see your the last post.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post May 27 2020, 03:13 PM
Post#8


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


If that combobox is on this form shouldn't it be Me.cboMVF.SetFocus?

--------------------
Robert Crouser
Go to the top of the page
 
B99
post May 27 2020, 03:25 PM
Post#9



Posts: 62
Joined: 15-April 20



It's on a different form. There are actually 3 forms involved. I tried to simplify in my explanation but after re-reading it, I realized it's not very clear. Here's a little more detail: I have a 'main' form that has a parent record and a subform that has child record details (1:M). The subform has a text box and a MVF combobox that can be populated directly, or the user can search for similar child records and copy values from those records.

To do that, the user clicks a 'search' button which opens another form as a popup. This is a continuous form with relevant child records. The user will scroll through and check a box for one or more records that they want to copy. Then they click a copy button on the popup form, which calls the code I posted to copy the data and close the popup form. The copy button (and code) are on the popup form but the text box and the combobox are on the subform.

That's still not a great explanation but hopefully it makes sense.
Go to the top of the page
 
projecttoday
post May 27 2020, 03:39 PM
Post#10


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


It's better to post it.

Where did you put Forms!MyMainForm!MySubForm.Form!cboMVF.SetFocus ?

--------------------
Robert Crouser
Go to the top of the page
 
B99
post May 28 2020, 12:54 AM
Post#11



Posts: 62
Joined: 15-April 20



OK, attached is an example DB. This is very stripped down to just the components needed for this scenario. I left in quite a few debug statements and comments in the code that will hopefully make it easier to follow along, and I tried to leave in anything that I thought *might* be relevant. The problem is in "cmdCopyResults_Click" on the frmSearchResults form and I have a comment showing the line that is not working.

The main for is frmBuilder. It has two subforms, one of which contains the offending combobox. I put several notes/instructions in a label on the form.

QUOTE
Where did you put Forms!MyMainForm!MySubForm.Form!cboMVF.SetFocus ?

I have tried putting it all over. It's been at the start of the procedure; just before the outer (table) loop; inside the outer loop; inside the inner (MVF) loop; and I tried putting it in the Click action of the button on the search form. Using debug statements, I can see that the combobox does get focus, but it doesn't affect the ability to programmatically select the values. Incidentally, I found that I had to first set the focus to the main form, then the subform, and then the combobox. If I don't do all three, then the Screen.ActiveControl value would either give an error or it would be the Copy button.

Something else odd: in my full version, if I click in the combobox either before doing a search, or after the search form opens (but before copying the results back), then the values are selected - regardless of whether I include the SetFocus statements. But that is not the case in this example. I also tried putting a .DropDown command in various places, to no avail.

Thanks for taking a look! Hopefully I've missed something small and just can't see it.

EDIT - I just realized that I removed the Tag combobox from the Task Detail subform. Fortunately it doesn't impact the functionality for this test.
This post has been edited by B99: May 28 2020, 12:59 AM
Attached File(s)
Attached File  SetCboExample.zip ( 236.3K )Number of downloads: 1
 
Go to the top of the page
 
projecttoday
post May 28 2020, 02:50 AM
Post#12


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


To select an item in a combobx use the .ListIndex property:

Forms!frmBuilder!fsubTaskDtl.Form!cboFmkDtl.ListIndex = n
n is the number of the item in the list minus 1.

--------------------
Robert Crouser
Go to the top of the page
 
B99
post May 28 2020, 09:55 AM
Post#13



Posts: 62
Joined: 15-April 20



Hi Robert,

I'm not sure how to use the .ListIndex property for this. One the MS site (https://docs.microsoft.com/en-us/office/vba...bobox.listindex), it says that the .ListIndex property is read only (I've seen several posts contradicting that) but it also says if there are multiple values selected to use the .Selected array, which is what I have in the code.

As a test, I hardcoded Forms!frmBuilder!fsubTaskDtl.Form!cboFmkDtl.ListIndex = 1 but it did not select anything. It didn't give any errors, just didn't do anything that I can see.

Go to the top of the page
 
projecttoday
post May 28 2020, 10:23 AM
Post#14


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


I did some googling, too. I don't believe I ever used it myself. According to this post which is also from Microsoft and which you may have already seen you can set it. Scroll down to where it says "Example": ListIndex.

That's about all I have at this point about setting the combobox in code.

--------------------
Robert Crouser
Go to the top of the page
 
B99
post May 28 2020, 11:17 AM
Post#15



Posts: 62
Joined: 15-April 20



Hi Robert, I think I have it figured out! Your comment on the .ListIndex got me thinking and I retraced my steps. It turns out I had (at least) two mistakes. First, this was wrong:
CODE
Forms!MyMainForm!MySubForm.Form!cboMVF.Selected(rsFmk(iFmk)) = True

The value of rsFmk(iFmk) is the value of the bound column on the combobox; i.e. the ID of the value in the source table. What I needed to do was find the row number of the value:
CODE
Forms!MyMainForm!MySubForm.Form!cboMVF.Selected(rownum) = True

It just happened that the values of the IDs that were selected were also valid row numbers (but the wrong row numbers); and since nothing was being selected, I didn't notice the mistake.

Second, there are two things going on with the combobox- I first select the values (above) and then I filter the combobox RowSource to be only the selected values. When I do the filter, it resets the row numbers of the values. So if the the first part selected rows 1,2 and 5, but then the second part filtered the list to only 3 choices, the result is row 5 is no longer a valid row.

I'm working on making the changes for the second part. Once I have it working, I'll post the code...or the next wall I run into.

Thanks for your help!
Go to the top of the page
 
projecttoday
post May 28 2020, 11:25 AM
Post#16


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


You're welcome.

--------------------
Robert Crouser
Go to the top of the page
 
B99
post May 28 2020, 12:56 PM
Post#17



Posts: 62
Joined: 15-April 20



Attached is an updated version that works woohoo.gif

Here's the reworked code:
CODE
Private Sub cmdCopyResults_Click()
  Dim db As Database
  Dim rsTask As Recordset   'Task table
  Dim strSelected As String     'Concatenated list of Tasks
  Dim strNext As String         'String to hold a visual separator
  Dim i As Integer
  Dim FirstRec As Boolean          'First record
  Dim strSQL As String
  
  Set db = CurrentDb
  'Select the tasks where the user clicked the checkbox
  Set rsTask = db.OpenRecordset("SELECT ID, Task, FrameworkName " _
                 & "FROM tblProjectDtl " _
                 & "WHERE tblProjectDtl.DraftCheckbox = True")
  
  'Set some first record variables
  FirstRec = True
  strSelected = ""
  strNext = "*****  NEXT TASK   *****"
          
  ' Start the SQL string used to filter the Framework combobox
  strSQL = "SELECT qryFullFramework.ID, qryFullFramework.FullFramework " _
         & "FROM qryFullFramework " _
         & "WHERE qryFullFramework.ID IN ("

  rsTask.MoveLast
  rsTask.MoveFirst
  Debug.Print "User selected"; rsTask.RecordCount; "records"

  If rsTask.RecordCount > 0 Then
    
    While Not rsTask.EOF
      Debug.Print "Checking ID:"; rsTask.Fields("ID")
      If FirstRec Then
        strSelected = Nz(rsTask.Fields("Task"), "")
        FirstRec = False
      Else
        strSelected = strSelected & vbNewLine & strNext _
        & vbNewLine & Nz(rsTask.Fields("Task"), "")
      End If

      'Get the frameworks associated with the selected records
      Dim rsFmk As Recordset
      Dim iFmk As Integer
      Set rsFmk = rsTask!FrameworkName.Value

      If rsFmk.RecordCount > 0 Then
        rsFmk.MoveLast
        rsFmk.MoveFirst
        Debug.Print "Frameworks assigned to the task:"; rsFmk.RecordCount
        While Not rsFmk.EOF
          Debug.Print "Framework ID:"; rsFmk(iFmk)
          'Build the Framework record source
          strSQL = strSQL & rsFmk(iFmk) & ", "
        rsFmk.MoveNext
        Wend
      End If

      rsFmk.Close
      Set rsFmk = Nothing
      rsTask.MoveNext
    Wend
    rsTask.Close
    Set rsTask = Nothing
    db.Close

    ' Check if the user entered any Description text; if not append the value
    If Nz(Forms!frmBuilder!fsubTaskDtl.Form!txtDescription, "") = "" Then
      Forms!frmBuilder!fsubTaskDtl.Form!txtDescription = strSelected
    Else
      Forms!frmBuilder!fsubTaskDtl.Form!txtDescription = _
      Forms!frmBuilder!fsubTaskDtl.Form!txtDescription & vbNewLine _
      & strNext & vbNewLine & strSelected
    End If
  
    'Finish the Framework filter
    strSQL = Left(strSQL, Len(strSQL) - 2) & ");"
    Debug.Print "SQL:"; strSQL
    Forms!frmBuilder!fsubTaskDtl.Form!cboFmkDtl.RowSource = strSQL
    Forms!frmBuilder!fsubTaskDtl.Form!cboFmkDtl.Requery
  
  End If ' Check if any Tasks are selected
  
  'Loop through the now-filtered values and select all
  Dim x As Integer  'row
  Dim y As String   'fmk ID value

  With Forms!frmBuilder!fsubTaskDtl.Form!cboFmkDtl
    Debug.Print "list count"; .ListCount
    Debug.Print "#selected"; .ItemsSelected.Count
    For x = 0 To .ListCount - 1
      y = .Column(1, x)
  
      'This only works with the .Dropdown
      Forms!frmBuilder!fsubTaskDtl.Form!cboFmkDtl.SetFocus
      Forms!frmBuilder!fsubTaskDtl.Form!cboFmkDtl.Dropdown
      Forms!frmBuilder!fsubTaskDtl.Form!cboFmkDtl.Selected(x) = True
    
      'Move the focus to close the Dropdown
      Forms!frmBuilder!fsubTaskDtl.Form!txtTask.SetFocus
      Forms!frmBuilder!fsubTaskDtl.Form!txtTask.SelStart = 0
  
    Next
  End With
  
  DoCmd.Close acForm, Me.FormName, acSaveNo
  CurrentDb.Execute "UPDATE tblProjectDtl set DraftCheckbox = 0", dbFailOnError
  
End Sub

One thing that has me very confused is the behavior of the .Selected command. I know the combobox has to have focus in order to set a value. Fine. But the .SetFocus command alone doesn't seem to do anything. If I use ".Selected(x) = True" without setting focus then I get an error; makes sense. But if I set focus, then select the value, it doesn't get selected. However, if I set focus, then .DropDown, and THEN set the value, it works. The dropdown seems to be the key to getting the value selected. But then it's stuck in the down position so I immediately follow that by setting focus to the text box. The .DropDown workaround is annoying because the user sees a flash of the dropdown. I would love to know why it only works with the .DropDown.

Also, I would welcome any pointers on making the code more efficient.

Thanks!
-Brian
Attached File(s)
Attached File  SetCboExample_v2.zip ( 262.34K )Number of downloads: 2
 
Go to the top of the page
 
theDBguy
post May 28 2020, 01:51 PM
Post#18


UA Moderator
Posts: 78,487
Joined: 19-June 07
From: SunnySandyEggo


Hi Brian. Glad to hear you got it sorted out. Good luck with your project.

--------------------
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
 
projecttoday
post May 28 2020, 04:01 PM
Post#19


UtterAccess VIP
Posts: 12,403
Joined: 10-February 04
From: South Charleston, WV


QUOTE
I would welcome any pointers on making the code more efficient.

Recap what the form is supposed to do.

--------------------
Robert Crouser
Go to the top of the page
 
B99
post May 28 2020, 04:42 PM
Post#20



Posts: 62
Joined: 15-April 20



QUOTE
Recap what the form is supposed to do.

When a user is adding a new record (Task record in this example), they search for similar records by using the continuous popup form. They scroll through the records and check a box next to the ones they want to copy; then they click a button to copy the results back to the form with the new record that they are working on. The copy button does 3 things:
1 - The copied record Description values are concatenated and appended to the Description field of the new record.
2 - There is also a MVF that stores 0:M Frameworks for each record. The combined values of all Framework values from the selected records are copied back to the new record to a combobox bound to the MVF. The Row Source is filtered so that the only choices are the Frameworks that are associated with the selected records.
3 - All values in the filtered MVF are selected by default

The copy button code is supposed to:
CODE
Find which records have been selected
Open a recordset of selected records
For each selected record
    Get the data in the Text field
    Append to a variable
    Get the data in the MVF field
    If the MVF has any values
        Open a recordset to get the MVF data
        Build a SQL Where clause to filter the MVF on the Detail Form
    Close the MVF recordset
Close the selected records recordset
Update the Description value on the new record
Clean up the Filter SQL and filter the Framework combobox
Loop through the values of the now-filtered combobox
    For each row in the .ListCount
    .Select the row
Close the form
Update the main table and set the value of all checkboxes back to 0


It works, I'm just not sure if I've used the most appropriate constructs, methods, etc. I also realize it's a pain to go through someone else's code and any pointers would be greatly appreciated!
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    13th July 2020 - 05:58 AM