Combo box auto-prompt # text boxes

[deanndra]

Hi. I am trying to do something that I'm not actually sure is possible. I am attempting to take a combo box [c_box1] and somehow have it produce textboxes. Further explanation, [c_box1] contains numbers 1-5. When a user clicks on a number within that combobox, I want the result to be that the coordinating number of textboxes will br produced under another field. I have not yet produced the combobox, so perhaps that's why this is so confusing. Allow me to go more into detail.

Positions will be the field that the combobox [c_box1] is contained within. If a user clicks the number "4" in that field, I want it to produce four textboxes under the "Line #" field.

Is this possible???

[BradHodge]

Hi. I am trying to do something that I'm not actually sure is possible. I am attempting to take a combo box [c_box1] and somehow have it produce textboxes. Further explanation, [c_box1] contains numbers 1-5. When a user clicks on a number within that combobox, I want the result to be that the coordinating number of textboxes will br produced under another field. I have not yet produced the combobox, so perhaps that's why this is so confusing. Allow me to go more into detail.

Positions will be the field that the combobox [c_box1] is contained within. If a user clicks the number "4" in that field, I want it to produce four textboxes under the "Line #" field.

Is this possible???

It would seem like it would be possible to do this using the CreateControl command in VBA along with using your combo box output to dictate the number of repeats. However, the CreateControl command requires that the form is in design view, so I'm not sure you could do this in RunTime. You can also use the CreateControl command to create Text Boxes on a new form, so maybe you could some how modify your process to do something like this.

Just a couple of thoughts.

Brad.

[missinglinq]

How about creating the maximum number of textboxes, set their visibilty as No by Default, and then, in the AfterUpdate event of the combobox, make the appropriate number of boxes visible?

Then, in the Form Current event, makes the text boxes visible if they contain data.

Linq ;)>

[deanndra]

How about creating the maximum number of textboxes, set their visibilty as No by Default, and then, in the AfterUpdate event of the combobox, make the appropriate number of boxes visible?

Then, in the Form Current event, makes the text boxes visible if they contain data.

Linq ;)>

I'm new to this so please be patient. :) If this is a new database that I am making, would the "Form Current" event you spoke of be visible to those who will later be using this form on this particular database? I don't want them to be able to change anything to do with the database itself, only with the records and information contained in those records.

Sorry if that's a dumb question. :)

Thanks for the responses *that's to both of you!!!*

[missinglinq]

We understand that everyone has to start somewhere, but you're really talking about the very basic ABCs of Access/VBA programming here. If you're really going to do this, you'll need to get a decent primer on MS Access (by preference, one for your particular version) that includes at least the very basics of writing code, i.e. how to access the code window, etc. We're happy to answer questions here, that's what we do, but this is not a suitable forum for teaching Access to someone from the ground up.

The Form_Current() event (which you may also see referred to as the On Current event) resides in the Code Module for a form, and shouldn't be available to the end users. It will always look similar to this

Private Sub Form_Current()
'Your code goes here
End Sub

The code in this sub fires whenever a record is loaded. When the form itself loads (because the first record is loaded) and anytime you move from one record to another. Because of this, you place code here that you want executed each time a new record is accessed. One example of this would be code to make your textboxes visible, but only if they contain data.

If you have, for example, three textboxes that may or may not need to be displayed, depending on whether they contain data or not, you'd do something like this:

Private Sub Form_Current()
 
If  IsNull(TextBox1) Then  
 TextBox1.Visible = False
Else
 TextBox1.Visible =  True
End If
 
If  IsNull(TextBox2) Then  
 TextBox2.Visible = False
Else
 TextBox2.Visible =  True
End If
 
If  IsNull(TextBox3) Then  
 TextBox3.Visible = False
Else
 TextBox3.Visible =  True
End If
 
End Sub

If IsNull(TextBox1)

asks if the box named TextBox1 IsNull (is empty)

If it is null, or empty, it's not displayed

TextBox1.Visible = False

Else (if it's not null, it has data) it is displayed

TextBox1.Visible = True

BTW, the line numbersyou see in the code above are for display purposes here in the forum, and should not appear in the actual code.
Linq ;0)>

[deanndra]

We understand that everyone has to start somewhere, but you're really talking about the very basic ABCs of Access/VBA programming here. If you're really going to do this, you'll need to get a decent primer on MS Access (by preference, one for your particular version) that includes at least the very basics of writing code, i.e. how to access the code window, etc. We're happy to answer questions here, that's what we do, but this is not a suitable forum for teaching Access to someone from the ground up.

The Form_Current() event (which you may also see referred to as the On Current event) resides in the Code Module for a form, and shouldn't be available to the end users. It will always look similar to this

Private Sub Form_Current()
'Your code goes here
End Sub

The code in this sub fires whenever a record is loaded. When the form itself loads (because the first record is loaded) and anytime you move from one record to another. Because of this, you place code here that you want executed each time a new record is accessed. One example of this would be code to make your textboxes visible, but only if they contain data.

If you have, for example, three textboxes that may or may not need to be displayed, depending on whether they contain data or not, you'd do something like this:

Private Sub Form_Current()
 
If  IsNull(TextBox1) Then  
 TextBox1.Visible = False
Else
 TextBox1.Visible =  True
End If
 
If  IsNull(TextBox2) Then  
 TextBox2.Visible = False
Else
 TextBox2.Visible =  True
End If
 
If  IsNull(TextBox3) Then  
 TextBox3.Visible = False
Else
 TextBox3.Visible =  True
End If
 
End Sub

If IsNull(TextBox1)

asks if the box named TextBox1 IsNull (is empty)

If it is null, or empty, it's not displayed

TextBox1.Visible = False

Else (if it's not null, it has data) it is displayed

TextBox1.Visible = True

BTW, the line numbersyou see in the code above are for display purposes here in the forum, and should not appear in the actual code.
Linq ;0)>

Many thanks for your reply Linq as well as my sincerest apologies that you feel I'm entirely learning Access via the advice given on this forum. I have worked in Access, and am familiar with some things, others are foreign to me. I can clearly see why you thought me a total novice based on the answer you gave me. You gave examples and code for simply displaying textboxes, which was not what I was wanting. In fact, given the examples I provided and the correspondence elsewhere in this post, you'll easily find I was asking how to provide blank textboxes in correlation with a combo box. I was aware of the codes you provided and their meanings, just not exactly how to tailor it to my needs.

I appreciate the help/guidance other users on this forum have offered, and hope the little guidance I have offered to others was beneficial. That being said, I would never feel comfortable seeking voluntarily offered advice where it is clearly not offered to "novices" such as myself. Thank you for your time. Have a great day. :)

[MMcCarthy]

I'm new to this so please be patient. :) If this is a new database that I am making, would the "Form Current" event you spoke of be visible to those who will later be using this form on this particular database? I don't want them to be able to change anything to do with the database itself, only with the records and information contained in those records.

Sorry if that's a dumb question. :)

Thanks for the responses *that's to both of you!!!*

deanndra

missinglinq was not trying to insult you but trying to be helpful. Being familiar with Access structures and understanding VBA (which is what the Forms On Current event is about) are two different things. Putting code in the On Current event of the form or any other event is standard VBA coding. You spoke of that being "available" to the user. This led missinglinq to understand that you weren't familiar with VBA event coding.

Hiding the structure of the database which would include code from the user requires you to create an mde file. If you want help with that we are happy to provide it.

Again as I say missinglinq was not trying to insult you. Many Access users are unfamiliar with VBA. However, it is an integral part of creating a modern user friendly Access database. Getting a good Access/VBA book is a great place to start learning this stuff.

Mary

[deanndra]

Hi. I am trying to do something that I'm not actually sure is possible. I am attempting to take a combo box [c_box1] and somehow have it produce textboxes. Further explanation, [c_box1] contains numbers 1-5. When a user clicks on a number within that combobox, I want the result to be that the coordinating number of textboxes will br produced under another field. I have not yet produced the combobox, so perhaps that's why this is so confusing. Allow me to go more into detail.

Positions will be the field that the combobox [c_box1] is contained within. If a user clicks the number "4" in that field, I want it to produce four textboxes under the "Line #" field.

Is this possible???

I'm still working on this. So far, I have a combo box named CBPos bound to tbl_PositionsAir, and 10 textboxes bound to separate fields within tbl_ParaAir. I have placed the following code in VBA for CBPos:

Private Sub CBPos_AfterUpdate()
  If IsNull(Me.CBPos) Then
    Me!Para1.Visible = False
    Me!Para2.Visible = False
    Me!Para3.Visible = False
    Me!Para4.Visible = False
    Me!Para5.Visible = False
    Me!Para6.Visible = False
    Me!Para7.Visible = False
    Me!Para8.Visible = False
    Me!Para9.Visible = False
    Me!Para10.Visible = False
  Else
    Me.CBPos = "1"
    Me!Para1.Visible = True
    Me!Para2.Visible = False
    Me!Para3.Visible = False
    Me!Para4.Visible = False
    Me!Para5.Visible = False
    Me!Para6.Visible = False
    Me!Para7.Visible = False
    Me!Para8.Visible = False
    Me!Para9.Visible = False
    Me!Para10.Visible = False
  End If
End Sub

Now my understanding was that this would be quite tedious having to enter all that for each Pos possibility (values 1-10), but I decided in the end it would probably be easier. However, now the problem I am having is not with hiding the textboxes...all textboxes (Para1-10) are set to invisible. And the numbers 1-10 do appear in CBPos as they should. However, if I go to click on "1" in CBPos, first of all it won't even let me click on it. The cursor simply highlights it and does nothing, so obviously this will not allow any textboxes to appear. And I can't for the life of me understand this. I have changed the code in the AfterUpdate procedure a dozen times...to no avail. I did see something online abotu a Tag property, as well as something for a Function, and wondered if perhaps I wasn't setting this up all wrong. Clueless. Any ideas on what I've coded wrong?

[deanndra]

Ooops, I forgot to put in my last reply that the following code is also located with the other code in CBPos:

Private Sub Form_Current()
  Me!Para1.Visible = False
  Me!Para2.Visible = False
  Me!Para3.Visible = False
  Me!Para4.Visible = False
  Me!Para5.Visible = False
  Me!Para6.Visible = False
  Me!Para7.Visible = False
  Me!Para8.Visible = False
  Me!Para9.Visible = False
  Me!Para10.Visible = False
End Sub

[NeoPa]

If you leave your OnOpen code as it is, you may find the following code a less tedious replacement for the AfterUpdate event.

Private Sub CBPos_AfterUpdate()
  Dim intX As Integer
 
  For intX = 1 To 10
    Me.Controls("Para" & intX).Visible = (CBPos >= intX)
  Next intX
End Sub