HELP: What is wrong with my For loop

S

sam

Hi All,

I have an excel userfom with a text box that accepts Student_ID. Once a
student inputs ID in this textbox, other form fields like Name, Address,
Ph_No will auto populate.
The button to launch this form is on Sheet1 and the students data that auto
populates is in sheet2.

NOW, There are instances where new student data is not available in Sheet2
until I manually update Sheet2 with their info. So even if students are
enrolled and they input their ID they dont see their Name, Address and Ph_No
autopopulate in the fields. Is there a way where a student inputs the ID and
if his or hers data is not present in Sheet2 the student can manually input
the data which will then populate the Sheet2.

Initially I have kept the Name, Address and Ph_No fields disabled as they
are autopopulated, But for new students if it doesnt autopopulate, I want
these fields to be enabled once a student inputs the ID for 3 times and the
Name, Address, Ph_No fields dont autopopulate.

SO for eg, IF a student inputs "123ef" in the ID fields and the Name,
Address, Ph_No doesnt populate, the student will see a msgbox saying please
input your ID again, This will happen for a total of 3 times, and the fourth
time if the student inputs the Id and the fields dont autopopulate then the
Name, Address and Ph_No fields should be enabled and editable

With Me.Student_ID
Dim i As Integer

If Me.Name.Value = "" Then
For i = 1 To 3
MsgBox ("please re-enter Student ID number")
Next i
End If

Me.Name.Enabled = True
Me.Address.Enabled = True
Me.Ph_No.Enabled = True

End With

Hope I made it clear

Thanks in Advance
 
J

JLGWhiz

Is the text box for the student's name named Name? There's a confusing
question. I assumed that the input method is through text boxes for all the
data.
 
J

JLGWhiz

I think I would use a Do ..Loop for this:

ctr = 0
Do
'Code her for entering the student ID
If Me.Name.Value = "" Then
MsgBox "Please re-enter Studernt-ID number"
Else
Exit Do
End If
ctr = ctr + 1
Loop Until ctr = 3
If ctr = 3 Then
Me.Name.Enabled = True
Me.Address.Enabled = True
Me.Ph_No.Enabled = True
End If


This way if the name populates, then it goes on its merry way. If not, it
will cycle the three times and enable the other text boxes.
 
J

JLGWhiz

I think I would modify the Name textbox name to StdtName or something other
than Name. Name is a reserved word and can confuse the compiler in a case
like Me.Name. If Me refers to a UserForm then you could get the name of the
UserForm. It can screw up your code and cause all kinds of debugging
headaches. I try to avoid using reserved words as variables altogether by
using abbreviations, prefixes or suffixes to modify them so the compiler
knows they are variables and not built in constants.
 
K

ker_01

Sam-

Try the following in your code:
With Me.Student_ID
Dim i As Integer
Msgbox Me.Name.Value
If Me.Name.Value = "" Then
For i = 1 To 3

There isn't enough information here to be sure, but if "me" is the control,
then it has a codename that will never be blank, so your loop would never
occur.

As an aside, it isn't clear why you would make a user enter an ID 3 times.
If their data isn't there, it isn't there- and I'd go straight to having them
enter it as soon as it isn't found.

HTH,
Keith
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top