Nov 30 2006, 12:55 PM
I know this should be simple, but for some reason, the logic is escaping me. I have 6 unbound text boxes in which I want to write text based on a selected check box. For example, I have 10 check box controls, and 6 unbound text box controls.
In the OnClick event for Check Box number 1 I put in code that looks to see if Text Box #1 is null, and if so, to write a value to it. If Text Box 1 is not null, it will then look to Text Box #2 and check for null and write a value if it is null, etc. Here is what I thought would work, but doesn't seem to:
Dim Name As String
Name = "John"
If txtName1 Is Null Then
Set txtName1 = Name
ElseIf txtName1 Is Not Null And txtName2 Is Null Then
txtName2 = Name
ElseIf txtName1 Is Not Null And txtName2 Is Not Null and txtName3 is null Then
txtName3 = Name
And so on through the 6 text box controls.
Nov 30 2006, 01:00 PM
the function is IsNull(Me.YourControl) or Not IsNull(Me.YourControl)...Furthermore, you might want to check for empty strings (there is a difference between a null value and an empty string) and use a loop to loop through the controls instead of using several different if then else statements.
You might also want to clarify why you are doing this in such a manner...seems kind of odd to me and it would be interesting to see your table structure.
Nov 30 2006, 01:01 PM
Use the afterupdate event of each checkbox to set the values of the textboxes. You can search on UA for "cascading comboboxes" to find similar code (though yours should be much simpler since you are not changing rowsources of comboboxes etc., but the concept should be the same).
Nov 30 2006, 01:15 PM
It was the IsNull(Control) syntax. Works great now.
I'll also now do a Loop now that I know it works.
Nov 30 2006, 01:39 PM
Good luck on future projects!
Nov 30 2006, 01:57 PM
I can't seem to figure out the logic for the Loop. I can make it work with the nested IsNull/Not IsNull statements, but would like to clean it up with the loop. I can't seem to figure out how to set up the loop to go through the 6 text boxes to find the first null and then write the value. For my example, the text boxes are called txtName1, txtName2, txtName3, etc. I presume I should be using an Until loop, since I'm looking until I find a null txtName...So what I am trying to do is
do until txtName(number) is null
when found txtName = Name
I'm not sure how to make txtName increment through the 6 text boxes so, for instance, if txtName3 is the first null, the "Name" is written to txtName3...
Nov 30 2006, 06:27 PM
you are again not using the IsNull function correctly...here is a sample of checking ALL textbox controls on a form for null and empty string values:
Dim ctl As Control
For Each ctl in Me.Controls
If Me.Dirty Then
If IsNull(ctl.value) or ctl.Value = "" Then
MsgBox("There is a null or empty string value")
This is untested, but should get you started.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here