strings in commands?

  • Thread starter Thread starter vircalendar via AccessMonster.com
  • Start date Start date
V

vircalendar via AccessMonster.com

Is there any way to put a string into the command lines below that would
allow me to replace the "1" in each line with a 2 or a 3 or a 4 as needed? In
other words, can one single character in these lines be made into a variable
that I can manipulate?

If [field1] = "Technologist" Then
Me.txt1.RowSourceType = "table/query"
Me.txt1.RowSource = "tbl_techname"
 
This kind of thing:

Dim strControl As String
Dim i As Integer

If Me.[Field1] = "Technologist" Then
For i = 1 to 4
strControl = "txt" & i
Me(strControl).RowSource = ...
Next
End If
 
Thanks, but that won't really do what I need.
The lines I posted are part of about 20 lines of code that live in a
subroutine. When field1 changes, it triggers this routine, all of which
relates to combo box 1. When field 2 changes, it triggers the routine for cb
2. What I really want to do is code the subroutine with fieldx and cbx, and
then pass the variable 1, 2, 3, etc to x as the routine is initiated.


Allen said:
This kind of thing:

Dim strControl As String
Dim i As Integer

If Me.[Field1] = "Technologist" Then
For i = 1 to 4
strControl = "txt" & i
Me(strControl).RowSource = ...
Next
End If
Is there any way to put a string into the command lines below that would
allow me to replace the "1" in each line with a 2 or a 3 or a 4 as needed?
Click to expand...
[quoted text clipped - 6 lines]
Me.txt1.RowSourceType = "table/query"
Me.txt1.RowSource = "tbl_techname"
Click to expand...
Click to expand...
 
I'm not really clear about what you are doing, but you could create a
routine and call it in the AfterUpdate of each control that requires the
change to be made.

It would probably contain a Select Case like this:

Private Function SetRowSource(cbo As Combobox)
Dim strCtl As String
Dim i As integer
strCtl = "txt" & right(cbo.Name,1)
Select Case cbo.Value
Case "Technologist"
Me(strCtl).RowSource = "tbl_techname"
Case ...

Case Else
If Not IsNull(cbo.Value) Then
MsgBox "Failed to handle value " & cbo
End If
End Select
End Function

Remember, we can't see your form or what you have to achieve, so this can
only be an example for you to adapt. Your example doesn't make sense to me:
if I had a control named txt1, it would be a text box (not a combo), so it
would not have a RowSource property to set (as your original example did.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

vircalendar via AccessMonster.com said:
Thanks, but that won't really do what I need.
The lines I posted are part of about 20 lines of code that live in a
subroutine. When field1 changes, it triggers this routine, all of which
relates to combo box 1. When field 2 changes, it triggers the routine for
cb
2. What I really want to do is code the subroutine with fieldx and cbx,
and
then pass the variable 1, 2, 3, etc to x as the routine is initiated.


Allen said:
This kind of thing:

Dim strControl As String
Dim i As Integer

If Me.[Field1] = "Technologist" Then
For i = 1 to 4
strControl = "txt" & i
Me(strControl).RowSource = ...
Next
End If
Is there any way to put a string into the command lines below that would
allow me to replace the "1" in each line with a 2 or a 3 or a 4 as
needed?
Click to expand...
[quoted text clipped - 6 lines]
Me.txt1.RowSourceType = "table/query"
Me.txt1.RowSource = "tbl_techname"
Click to expand...
Click to expand...
Click to expand...
 
The form has five rows and two columns of combo boxes. For each row, the
first combo box is used to set the rowsource for the second. The sequence of
logical steps used is the same for each row, so there's a subroutine -
select_combo_data() - that I would like to initiate over and over, row by
row, with the only difference in the subroutine being the controls to which
it refers. That's where I think I need the variable. The code below works
fine for the first row of boxes (based on cb_choose1), but I want to make it
work for each of the rows. If I could refer to cb_chooseX and cb_criteriaX,
with the X changing to match the number of the calling control, it would do
what I want.


Private Sub cb_choose1_Change()
select_combo_data
End Sub
Private Sub cb_choose2_Change()
select_combo_data
End Sub
Private Sub cb_choose3_Change()
select_combo_data
End Sub

Private Sub select_combo_data()
If cb_choose1 = "Ordered by" Then
Me.cb_criteria1.RowSourceType = "table/query"
Me.cb_criteria1.RowSource = "ordering"
ElseIf cb_choose1 = "Attending" Then
Me.cb_criteria1.RowSourceType = "table/query"
Me.cb_criteria1.RowSource = "physician_IRattending"
ElseIf cb_choose1 = "Category" Then
Me.cb_criteria1.RowSourceType = "value list"
Me.cb_criteria1.RowSource = "'People';'Places;'Things'"

...there are eight more elseifs based on the options in cb_choose

endif
endsub


Allen said:
I'm not really clear about what you are doing, but you could create a
routine and call it in the AfterUpdate of each control that requires the
change to be made.

It would probably contain a Select Case like this:

Private Function SetRowSource(cbo As Combobox)
Dim strCtl As String
Dim i As integer
strCtl = "txt" & right(cbo.Name,1)
Select Case cbo.Value
Case "Technologist"
Me(strCtl).RowSource = "tbl_techname"
Case ...

Case Else
If Not IsNull(cbo.Value) Then
MsgBox "Failed to handle value " & cbo
End If
End Select
End Function

Remember, we can't see your form or what you have to achieve, so this can
only be an example for you to adapt. Your example doesn't make sense to me:
if I had a control named txt1, it would be a text box (not a combo), so it
would not have a RowSource property to set (as your original example did.)
Thanks, but that won't really do what I need.
The lines I posted are part of about 20 lines of code that live in a
Click to expand...
[quoted text clipped - 23 lines]
Click to expand...
 
This works:

Dim strsearch As String
Dim strcriteria As String
Option Compare Database

Private Sub cb_searchfield1_Change()
strcriteria = Me.txt_criteria1.Name
strsearch = Me.cb_searchfield1.Name
select_combo_data
End Sub

Private Sub cb_searchfield2_Change()
strcriteria = Me.txt_criteria2.Name
strsearch = Me.cb_searchfield2.Name
select_combo_data

...etc

Private Sub select_combo_data()
If Me(strsearch) = "Ordered by" Then
Me(strcriteria).RowSourceType = "table/query"
Me(strcriteria).RowSource = "ordered by"
ElseIf Me(strsearch) = "Attending" Then
Me(strcriteria).RowSourceType = "table/query"
Me(strcriteria).RowSource = "attending"
ElseIf Me(strsearch) = "Category" Then
Me(strcriteria).RowSourceType = "value list"
Me(strcriteria).RowSource = "'people','place','thing''"

... etc

End If
End Sub



The form has five rows and two columns of combo boxes. For each row, the
first combo box is used to set the rowsource for the second. The sequence of
logical steps used is the same for each row, so there's a subroutine -
select_combo_data() - that I would like to initiate over and over, row by
row, with the only difference in the subroutine being the controls to which
it refers. That's where I think I need the variable. The code below works
fine for the first row of boxes (based on cb_choose1), but I want to make it
work for each of the rows. If I could refer to cb_chooseX and cb_criteriaX,
with the X changing to match the number of the calling control, it would do
what I want.

Private Sub cb_choose1_Change()
select_combo_data
End Sub
Private Sub cb_choose2_Change()
select_combo_data
End Sub
Private Sub cb_choose3_Change()
select_combo_data
End Sub

Private Sub select_combo_data()
If cb_choose1 = "Ordered by" Then
Me.cb_criteria1.RowSourceType = "table/query"
Me.cb_criteria1.RowSource = "ordering"
ElseIf cb_choose1 = "Attending" Then
Me.cb_criteria1.RowSourceType = "table/query"
Me.cb_criteria1.RowSource = "physician_IRattending"
ElseIf cb_choose1 = "Category" Then
Me.cb_criteria1.RowSourceType = "value list"
Me.cb_criteria1.RowSource = "'People';'Places;'Things'"

...there are eight more elseifs based on the options in cb_choose

endif
endsub
I'm not really clear about what you are doing, but you could create a
routine and call it in the AfterUpdate of each control that requires the
Click to expand...
[quoted text clipped - 27 lines]
Click to expand...
 
Back
Top