Loop through controls on form

[RWood]

I have a database for storing quality control results. A form is used for data entry, with text boxes (txtAbc, txtDef, etc) for entering the results of each type of test. Each has a corresponding textbox to display the lower and upper limits of the acceptable range for each test.

A For Each...Next routine fetches the range for each value, and assigns it to txtRange. So far, so good.

Within this routine, I want to put txtRange into the appropriate text box. I have given the pairs of boxes related names, ie txtAbc and txtAbcR, txtDef and txtDefR. Then I've used this:

Dim txtCR as Control, txtC as String, txtRange as String
  For Each txtCR In Me
       If txtCR.Name = "txtC & 'R'" Then
            txtCR = txtRange
       End If
   Next

The problem is with txtCR = txtRange, as txtCR is a variable, not a control name.

Any ideas?

 

[Jack Cowley]

Hmmm. Am I correct in assuming that the Upper and Lower limits for each type of test are constant? If that is so then why not just have the Upper and Lower limits as labels on the form? You can use code in each text boxes Before Update event to verify that the value entered is between the Upper and Lower limits to be sure the user is entering valid data.

If your Upper and Lower limits can change then create a related table with the Upper and Lower limits and use a query (combine the lookup limits table with your data entry table) to display the limits on your form rather than looking them up with code.

hth,
Jack

 

[WayneRyan]

R,

Dim txtCR as Control
Dim txtC as String
Dim txtRange as String
  For Each txtCR In Me
       If txtCR.Name = "txtC & 'R'" Then
            txtCR = txtRange
       End If
   Next

It's always going to be txC'R'

If you are trying to enforce some data entry rules, you can use
the Before or After Update events on each control to put code
like:

If Me.txtSomeField < 0 And Me.txtSomeField > 127 Then
   MsgBox("That value is not allowed.")
End If

Wayne

 

[Pat Hartman]

"txtC & 'R'" is not doing what I think you intend for it to do. Try "txtC" & "R" instead

 

[WayneRyan]

R,

Dim txtCR as Control, txtC as String, txtRange as String
  For Each txtCR In Me
    If txtCR.Name = "txtC & 'R'" Then
       txtCR = txtRange
    End If
    Next

The above code will loop through all of your controls
and when it comes to one named "txtCR" it will set it
equal to txtRange.

Regardless of the syntax (txtC & "R"), I don't think
that the code means what you want.

If txtRange has a value of say 100 then:

Me.txtCR = Me.txtRange

Does the same thing.

As in the earlier post, if you want to enforce some
rules on specific fields, then use the Before or After
Update event of that field and:

If Me.txtCR < 0 Or Me.txtCR > 100 then
   MsgBox("You must enter a value between 0 and 100.")
End If

Wayne

 

[RWood]

Thanks for all the comments and suggestions.

I have considered putting the limits on the form, but the same values are also used on reports. I guess I could get the report to fetch them from the form. I would like to make a form for editing the limits when needed, and having them in a table seems to be all-round the best setup.

This isn't a data validation problem; the database is to record the values obtained, whether they are 'correct' or not. The form will eventually have a flag if the results are high or low, and a text box that must be filled in showing what action has been taken to correct the error that caused the out-of-range results.

The problem is that each time program runs through the loop, the text box to put txtRange in will be different, and I'm trying to make the name of the text box displaying the range related to the name of the text box that has the result entered, by adding 'R'. But I can't see how to specify that in the code:

  For Each txtCR In Me
    If txtCR.Name = "txtC & 'R'" Then
       txtCR = txtRange
    End If
  Next

txtCR is the appropriate text box. The program is looking for a text box labelled, literally, txtCR, and I want to tell it that the name it's looking for is "txtC & 'R'"

Rosemary

BTW, I've found all sorts of tips and ideas on this site, it's really helpful

 

[WayneRyan]

Rosemary,

If you know that its name is TxtCR, then you don't have
to loop through all your controls looking for it.

You can reference its validation rule by:

SomeString = Me.TxtCR.ValidationRule

If you had many of these like TxtCR1 ... TxtCRn:

For i = 0 to Me.Controls.Count - 1
   SomeString = Me.Controls("TxtCR" & i).ValidationRule
   Next i

Just an idea,
Wayne

 

[RWood]

Hi Wayne,

I'm not sure what you mean by .ValidationRule;
I don't need to validate anything, just get the right range in the right box.

I want to use the contents of the variable txtC, and add 'R' to the contents, and make this the name of the control. So when the variable is the string txtAbc, txtAbcR will be the name of the control to recieve txtRange.