By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,149 Members | 885 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,149 IT Pros & Developers. It's quick & easy.

Can't get value from Combo Box

PhilOfWalton
Expert 100+
P: 1,430
I have a subform with 14 combo boxes on it cbo1 to cbo14, and am trying to dynamically set the ControlSource, then display the value.

The code I am using is
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboABPeriodID_AfterUpdate()
  2.  
  3.     Dim MyDb As Database
  4.     Dim SubjectSet As Recordset
  5.     Dim StrSQL As String
  6.     Dim Inti As Integer
  7.  
  8.     ABPeriodID.SetFocus
  9.     DoCmd.FindRecord cboABPeriodID
  10.  
  11.     StrSQL = "SELECT TblSubjects.*, TblJoinPeriodSubject.ABPeriodID "
  12.     StrSQL = StrSQL & "FROM TblSubjects INNER JOIN TblJoinPeriodSubject "
  13.     StrSQL = StrSQL & "ON TblSubjects.SubjectID = TblJoinPeriodSubject.SubjectID "
  14.     StrSQL = StrSQL & "WHERE (TblJoinPeriodSubject.ABPeriodID = " & cboABPeriodID & ");"
  15.  
  16.     Set MyDb = CurrentDb
  17.     Set SubjectSet = MyDb.OpenRecordset(StrSQL)
  18.  
  19.     ' Hide all the Combo Boxes
  20.     For Inti = 1 To 14
  21.         Me!frmInstructionSubform.Form.Controls("cbo" & CStr(Inti)).Visible = False
  22.     Next Inti
  23.  
  24.     Inti = 1
  25.     With SubjectSet
  26.         Do Until .EOF
  27.             Me!frmInstructionSubform.Form.Controls("cbo" & CStr(Inti)).ControlSource = !SubjectName' Set the ControlSource
  28.             Me!frmInstructionSubform.Form.Controls("cbo" & CStr(Inti)).Visible = True  ' Make required Combo Boxes visible
  29.             Inti = Inti + 1
  30.             .MoveNext
  31.         Loop
  32.         .Close
  33.         Set SubjectSet = Nothing
  34.     End With
  35.  
  36. End Sub
all appears to work in that the Debug Window gives me the correct ControlSource, but if I type
Expand|Select|Wrap|Line Numbers
  1. ?Me!frmInstructionSubform.form.Controls("cbo1")
I get error 2424 "The expression you entered has a field, control or property name that MS Access cant find".

I know I am missing something obvious, but can's spot it.

Any thoughts please

Phil
Aug 19 '18 #1
Share this Question
Share on Google+
14 Replies


zmbd
Expert Mod 5K+
P: 5,397
PhilOfWalton:

Easier Syntax for the immediate pane: Me!Subform1.Form!ControlName
Me!frmInstructionSubform.form!cbo1
http://access.mvps.org/access/forms/frm0031.htm

LONG version
?forms.Item("ParentFormName").Controls("SubFormCon trolName").Form.controls.item("ControlName").value


Have you pulled ("cbo" & CStr(Inti)).
Out of the command and set it as a string value first?
This way we can make sure your strings are resolving correctly - for example:
Expand|Select|Wrap|Line Numbers
  1. Do Until .EOF
  2.   '<Debug>
  3.   Dim zStr As String
  4.   zStr = "cbo" & CStr(Inti)
  5.   Debug.Print zStr
  6.   '</Debug>
  7.   Me!frmInstructionSubform.Form.Controls(zStr).ControlSource = !SubjectName ' Set the ControlSource
  8.   Me!frmInstructionSubform.Form.Controls(zStr).Visible = True  ' Make required Combo Boxes visible
  9.   Inti = Inti + 1
  10.   .MoveNext
  11. Loop
(it's a pet-peeve of mine to build strings within the function that is calling them - the errors returned are often very unhelpful in determining the root cause)
Aug 19 '18 #2

PhilOfWalton
Expert 100+
P: 1,430
Thanks for coming back.

I have tried your suggestion

Here the modified code as suggested

Expand|Select|Wrap|Line Numbers
  1.     Inti = 1
  2.     With SubjectSet
  3.         Do Until .EOF
  4.             zStr = "cbo" & CStr(Inti)
  5.             Me!frmInstructionSubform.Form.Controls(zStr).ControlSource = !SubjectName     ' Set the ControlSource
  6.             Me!frmInstructionSubform.Form.Controls(zStr).Visible = True                   ' Make required Combo Boxes visible
  7.             Debug.Print Me!frmInstructionSubform.Form.Controls(zStr)
  8.             Me.Controls("cbo" & CStr(Inti)).Visible = True
  9.             Me.Controls("lblcbo" & CStr(Inti)).Caption = !LabelCaption                    ' Set the caption
  10.             Me.Controls("lblcbo" & CStr(Inti)).Visible = True
  11.             Inti = Inti + 1
  12.             .MoveNext
  13.         Loop
  14.         .Close
  15.         Set SubjectSet = Nothing
  16.     End With
I get the same error on the Debug Line.

Have also tried
Expand|Select|Wrap|Line Numbers
  1.     Dim Ctl as Control
  2.     ...
  3.     ...
  4.     Set Ctl = Me!frmInstructionSubform.Form.Controls(zStr)
  5.     Ctl.ControlSource = !SubjectName  
  6.     Debug.Print Ctl.Value
  7.  
Same error

Phil
Aug 19 '18 #3

zmbd
Expert Mod 5K+
P: 5,397
What does debug.print zStr give you for the resolved string?

Explicitly set Line7 .Item(zStr).value
I've had unusual things happen when referencing subform controls from the parent where you have to be explicit in order to get the result back to the parent - children!
Aug 19 '18 #4

PhilOfWalton
Expert 100+
P: 1,430
Hi Zmbd
zstr gives the correct result of cbo1

I have modified the code again as below
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboABPeriodID_AfterUpdate()
  2.  
  3.     Dim MyDb As Database
  4.     Dim SubjectSet As Recordset
  5.     Dim StrSQL As String
  6.     Dim Inti As Integer
  7.     Dim Intj As Integer
  8.     Dim Ctl As Control
  9.     Dim zStr As String
  10.     Dim SFrm As Form
  11.  
  12.     ABPeriodID.SetFocus
  13.     DoCmd.FindRecord cboABPeriodID
  14.  
  15.     StrSQL = "SELECT TblSubjects.*, TblJoinPeriodSubject.ABPeriodID "
  16.     StrSQL = StrSQL & "FROM TblSubjects INNER JOIN TblJoinPeriodSubject "
  17.     StrSQL = StrSQL & "ON TblSubjects.SubjectID = TblJoinPeriodSubject.SubjectID "
  18.     StrSQL = StrSQL & "WHERE (TblJoinPeriodSubject.ABPeriodID = " & cboABPeriodID & ");"
  19.  
  20.     Set MyDb = CurrentDb
  21.     Set SubjectSet = MyDb.OpenRecordset(StrSQL)
  22.  
  23.     Set SFrm = Me!frmInstructionSubform.Form
  24.  
  25.     ' Hide all the Combo Boxes
  26.     For Inti = 1 To 14
  27.         SFrm.Controls("cbo" & CStr(Inti)).Visible = False
  28.         Me.Controls("lblcbo" & CStr(Inti)).Visible = False
  29.         Me.Controls("cbo" & CStr(Inti)).Visible = False
  30.     Next Inti
  31.  
  32.     Inti = 1
  33.     With SubjectSet
  34.         Do Until .EOF
  35.             zStr = "cbo" & CStr(Inti)
  36.             SFrm.Controls.Item(zStr).ControlSource = !SubjectName     ' Set the ControlSource
  37.             SFrm.Controls(zStr).Visible = True                   ' Make required Combo Boxes visible
  38.             Set Ctl = SFrm.Controls(zStr)
  39.             Ctl.ControlSource = !SubjectName
  40.             If zStr = "cbo1" Then
  41.                 For Intj = 0 To Ctl.Properties.Count - 1
  42.                     On Error Resume Next
  43.                     Debug.Print Ctl.Properties(Intj).Name & ": " & Ctl.Properties(Intj)
  44.                 Next Intj
  45.             End If
  46.             'Debug.Print Ctl.Value
  47.            ' On Error GoTo 0
  48.             Debug.Print SFrm.Controls.Item(zStr).Value
  49.             Me.Controls("cbo" & CStr(Inti)).Visible = True
  50.             Me.Controls("lblcbo" & CStr(Inti)).Caption = !LabelCaption                    ' Set the caption
  51.             Me.Controls("lblcbo" & CStr(Inti)).Visible = True
  52.             Inti = Inti + 1
  53.             .MoveNext
  54.         Loop
  55.         .Close
  56.         Set SubjectSet = Nothing
  57.     End With
  58.  
  59. End Sub
As you can see. I have tried defining a subform.

Relevant lines in the Debug Window are
Expand|Select|Wrap|Line Numbers
  1. EventProcPrefix: cbo1
  2. Name: cbo1
  3. ControlType: 111
  4. ControlSource: InstrFolk1
  5. RowSource: 5;4;3;2;1
  6. RowSourceType: Value List
  7. Format: 
Indicating we have the correct name and ControlSource.

Still get the same error on line 48. I am guessing that as the Access error indicates that it can't find the property "Value", rather than not finding the field.

Phil
Aug 20 '18 #5

zmbd
Expert Mod 5K+
P: 5,397
Yes, something is goofy here... your code is setting the properties for the control such as .Visible=False; however, the .value isn't available... Is there a default set for the control or is it Null/zero-string - that shouldn't be an issue (hmmm).

+ Between lines 47 and 48 Stop
When the debugger opens:
Open the "Locals" pane
You should be able to find the SFrm object
Expand the tree on its node and see what is actually being set and what is available.
Aug 20 '18 #6

PhilOfWalton
Expert 100+
P: 1,430
Thanks zmbd.

I think the problem is that I have an incorrect ControlSource, but for the moments I am putting the project on hold, as I misinterpreted what was wanted.

But many thanks for your help. I may have to come back to this in due course, but I rather doubt it

Phil
Aug 20 '18 #7

Rabbit
Expert Mod 10K+
P: 12,366
I don't think the Immediate window has access to Me in the context of the Form module. You would need to replace Me with Forms("form name") if you want to refer to the form from the immediate window itself.

The immediate window is outside the scope of the form module and therefore does not know what Me is referring to.
Aug 20 '18 #8

NeoPa
Expert Mod 15k+
P: 31,494
Rabbit's absolutely correct. The Immediate Pane doesn't recognise Me - even though it recognises local variables perfectly fine.
Aug 21 '18 #9

zmbd
Expert Mod 5K+
P: 5,397
Hence why I doubled down by giving the "long" version for the call in Post#2 - wasn't sure if PhilOfWalton was in debug mode or entering the string with the form loaded and idle.

I may be wrong; however, it appears the issue went beyond the context of the static loaded form:
Post 3 Block 1 Line 7 was still giving PhilOfWalton the same error as in OP

Under some conditions the Me object is available from the immediate pane: if the form is loaded, event is running, and the VBA is in Debug mode either from STOP or Untrapped-Error.
Aug 21 '18 #10

PhilOfWalton
Expert 100+
P: 1,430
I am afraid I have abandoned the new project and reverted to the old.
the new project would require an updateable continuous subform based on a Crosstab query, and although there are ways round it, it would not simplify maintaining the Db

As to the subject of "Me" in the immediate window, if you are in break mode in a form or report module, the "Me" is recognised and intelliSence will prompt for a field or property and give the correct result, however, the Me word is not actually required.

Phil
Aug 21 '18 #11

NeoPa
Expert Mod 15k+
P: 31,494
You're right. I was mistaken. I must have misremembered something else :-(
Possibly it's the With X ... End With construct that isn't handled in the Immediate Pane.

Apologies for the incorrect advice.
Aug 21 '18 #12

PhilOfWalton
Expert 100+
P: 1,430
Even I make mistakes. What can I expect from lesser mortals? :)

Phil
Aug 21 '18 #13

Rabbit
Expert Mod 10K+
P: 12,366
Didn't know I could access it if I'm in break mode, good to know
Aug 21 '18 #14

NeoPa
Expert Mod 15k+
P: 31,494
Phil:
Even I make mistakes. What can I expect from lesser mortals? :)
Exactly! Be reasonable with your expectations! ;-)
Aug 22 '18 #15

Post your reply

Sign in to post your reply or Sign up for a free account.