Last Question

[dunkingman]

I am going to make the changes I've made to a database live tomorrow, so this might be the last question(s) I ever ask:

1. If something is selected in a combo box, how do I get a text box to autofill based on the selection?

2. I have a check box that, when checked, clears the contents in every control on the form. When I uncheck it, I would like for the contents that were cleared to reappear. Does anyone know how to do this?

Thanks in Advance, you've all helped me very much

*************************************
D-Fresh and Charityg, I finally have internet access, thanks for the help you gave me, it worked perfectly.
*************************************

 

[D-Fresh]

Well, for question #1... I'm assuming you want to fill the text box with a different piece of data that's related to what you selected from the combo box... If so, set the combo box to have 2 columns, or however many you need, keep the bound column the same as it was. Then you have to set the column widths of the extra columns to 0. So if you have 2 columns, and the first one is the bound column, use 4;0 as your widths, so it will hide the second column. Then in the record source, select the table, or query, that has all the data for your combo box and select the pertinent columns... Then in the AfterUpdate event of the combo box, put this code...

me![TextBoxName] = me![ComboBoxName].column(1).value

You can replace 1 with whatever column you need to go to the text box(it's just an array of the columns, and it starts at 0)

Now for #2, this is a bit tougher, but what I would do, is used hidden text boxes to store all the values.. When you check the box, it will put the values of the visible controls to the hidden controls. Then when you uncheck it, it will put the values from the hidden controls back to the visible controls. I hope this helps you. Let me know if you need more help.

Doug

 

[dunkingman]

D-Fresh, a few more questions:

In the column count property of the combo box, it stated "1", so I changed it to "2." The bound column stated "1" and I didn't change it. Also, I changed the column widths that initially weren't displayed to 4;0. In the AfterUpdate Event of the combo box I tried using Me![textbox] = Me![ComboBox].column(1).Value, but the ".Value" wasn't recognized, and I couldn't manipulate the expression to get it to work. Did I actually need to change the columns in the combo, and am I doing something wrong that you can tell?

Also, on the second problem, I created extra controls that are not visible. In the AfterUpdate Event of the check box that clears the form I put the following before the code that clears the form controls:
******************************
If Not TextBox.value = Null Then
NewTextBoxThatIsnotvisible.Value = TextBox.value
End if
******************************
This way, after the form is cleared, it should not update the Controls that are not visible since the cleared controls will be null; however, the values don't even transfer to the invisible text boxes. I also have check boxes on the form, and since the following works for them,
***********************************
If Not chkBox = False Then
chkNewCheckBoxthatisnotvisible.Value = chkBox.Value
End If
***********************************
why wont the previous code work for the text boxes?


Thank you very much with your help thus far on a lot of my questions.

[This message has been edited by dunkingman (edited 07-17-2001).]

[This message has been edited by dunkingman (edited 07-17-2001).]

[This message has been edited by dunkingman (edited 07-17-2001).]

 

[D-Fresh]

I think that the syntax is wrong for the textboxes... Try this..

If Not isnull(me!TextBox)Then
me!NewTextBoxThatIsnotvisible = me!TextBox
else
me!NewTextBoxThatIsnotvisible = null
End if

That should do it for you.. You don't need the .value on textboxes... If nothing is specified for a control, then the default is taken, which is value for a textbox(and most others). Also, you should throw that else statement in there. If you fill out the form, then click the checkbox, then it will transfer the data to the hidden controls, when you uncheck it, it will transfer them back. If you delete a field's entry, and recheck the box, it will keep the old value. With the else statement, it will clear that old value. Hope that isn't too confusing.

A little tip too for the checkbox... Use this...

If me!chkBox = true Then
me!chkNewCheckBoxthatisnotvisible = true
else
me!chkNewCheckBoxthatisnotvisible = false
End If

It's a little easier to read and same thing applies to the use of the else statement.

Now for the combo box... Did you add the second field to the Row Source? In the properties sheet, click the builder (...) next to the Row Source field and make sure that there are two columns sitting there, the one you want the users to see first, and then the hidden values in the second column. The syntax looks, right, again, you don't really need the .value. Use...

me![ComboBox].column(1)

I hope this all works out for you and let me know if you need anything clarified or need more help.

Doug

 

[KDg]

Just to post an alternative. You could read it all into an array

    Dim myAry(200) As String' works fine but variant is probably more correct
    Dim ctl As Control
    Dim x As Integer
    
    x = 0
    For Each ctl In frm
        If ctl.ControlType = acTextBox Or ctl.ControlType = acCheckBox Or ctl.ControlType = acComboBox Then
            myAry(x) = Nz(ctl.Value, "")
        End If
        x = x + 1
    Next

and then read it back out when the box is unchecked

HTH

Drew

 

[dunkingman]

Doug, I have one last question, which is regarding the expression that transfers the code to the hiden textbox/checkbox:

I have a question regarding the following code you gave me:
*******************************************
If Not isnull(me!TextBox)Then
me!NewTextBoxThatIsnotvisible = me!TextBox
else
me!NewTextBoxThatIsnotvisible = null
End if
*******************************************
If the check box is checked by the user, the controls empty and the values are transferred to the controls that are not visible; however, when the user unchecks the box, the original entries (which are stored in the invisible controls) are not transferred back to the original controls. The reason I didn't include the else statment in my original expression was that it checks to see if the control is null (which it will be since the check box clears the controls), and if so changes the invisible control to null as well. This expression is evaluated before it reaches the statement that would transfer the value in the invisible control back to the original control, so nothing shows up in the original. . . But, then again, it seems like the else statement has to be there since (as you stated) the user might delete the contents of a field manually. Can you think of a different way to do this? . . . I can't thank you enough for your help so far, I probably never would have figured out how to get any of my two questions to work if you didn't take the time to help.

 

[D-Fresh]

Wouldn't you have to do the opposite of what you had to put the values back in the visible controls... Like so..


If Not isnull(me!NewTextBoxThatIsnotvisible)Then
me!TextBox = me!NewTextBoxThatIsnotvisible
else
me!Textbox = null
End if

I think this is what you're looking for... By the way, how many controls do you have? I have another Idea of what to do, stemming off of Drew's idea, but it depends if you have many controls or not... Let me know..

Doug

 

[dunkingman]

I have 14 text boxes and 2 check boxes, not including the check box to clear the form. Sorry that I'm such a layman with this subject. Thanks again for your help.

 

[D-Fresh]

Alright, Here goes the modified attempt...

Private Sub ChkBox_Click()
    Dim ctl As Control
    
    If chkbox Then
        For Each ctl In Controls
            If ctl.Tag = "ChangeOver" Then
                Me(ctl.Name & "i") = ctl.Value
                ctl.Value = Null
            End If
        Next
    Else
        For Each ctl In Controls
            If ctl.Tag = "ChangeOver" Then
                ctl.Value = Me(ctl.Name & "i")
                Me(ctl.Name & "i") = Null
            End If
        Next
    End If
    
End Sub

What you have to do, is name the invisible control the same name as it's corresponding visible control except with an "i" at the end. For example, Your text box is called "txtBox1" then the invisible control should be called "txtBox1i". What you also have to do, is put the word "ChangeOver" as the tag of the visible controls that you need to copy over, the 14 text boxes and the 2 checkboxes. This code should do everything for you. It will check for nulls in itself and everything. This will be a lot easier than writing 16 pieces of code. I hope this is understandable and let me know how it works out.

Doug

 

[dunkingman]

Doug, I am getting an error message with the folowing code (Formerclient is the checkbox name):
*********************************************
Private Sub Formerclient_Click()
Dim ctl As Control

If Formerclient Then
For Each ctl In Controls
If ctl.Tag = "ChangeOver" Then
Me(ctl.Name & "i") = ctl.Value
ctl.Value = Null
End If
Next
Else
For Each ctl In Controls
If ctl.Tag = "ChangeOver" Then
ctl.Value = Me(ctl.Name & "i")
Me(ctl.Name & "i") = Null
End If
Next
End If
End Sub
********************************************

The control.Value = Null produces a run-time error stating "The value you entered isn't valid for this field." I've made the invisible controls visible for the moment, and I can see a lot of contents being transferred to those controls (about half), but the others aren't transferred at all. I also get the same message when I uncheck the box to transfer the contents back to the original controls. I don't know if this is important, but I actuall have 11 text boxes, 2 check boxes, and 3 combo boxes. I'm not sure I completely understand the code you gave me as your knowledge is far superior to mine, but could it have something to do with the check boxes not noticing the ctrl.value = Null. Would they have to be set to ctrl.value = False? I really am sorry I haven't got this to work yet with all the help you've been giving me. Also thanks to Drew for the initial code.

[This message has been edited by dunkingman (edited 07-18-2001).]

[This message has been edited by dunkingman (edited 07-18-2001).]

 

[D-Fresh]

Alright, I'm not sure why it's not working for you... Do you have any validation rules or anything for any of the boxes? When it does bomb out, what control is it on? To get this, when the error pops up, hit debug.. Then go to the intermediate window(ctrl+g) and type "? ctl.name" without the quotes. I tested this code for combo boxes, textboxes, and checkboxes and it worked fine for me, so there has to be something small we're missing here... Try setting the value to "" instead of null... (ctl.value = "") Maybe that'll do something.. Good luck and let me know what happens.

 

[dunkingman]

Thanks Doug, I doubt this will help, but . . .

1. Everything works until it reaches the first check box. At this point is where I get the error message. It actually tranfers the check into the invisible check box, but the original check box stays checked.

2. I changed the name of the second check box (as it was the only one getting an error message at first when I pressed ctrl+g, ...) even though it was initially correct, then instead of stating the second check box had the error, it stated (when I pressed ctrl+g, . . .) that the first check box had the error.

3. None of the checkboxes are required.

[This message has been edited by dunkingman (edited 07-18-2001).]

[This message has been edited by dunkingman (edited 07-18-2001).]

 

[D-Fresh]

Okay then try this... This has to work eventually...

for the checking of the box use this, replace the line "ctl.value = null" with the below code...

if ctl.controltype = acCheckBox then
ctl.Value = false
else
ctl.value = null
end if

and for the unchecking of the box use this, replace the line "me(ctl.name & 'i') = null" with the below code...

if ctl.ControlType = acCheckBox then
Me(ctl.Name & "i") = false
else
Me(ctl.Name & "i") = null
end if

Hope this finally works...

Doug

 

[dunkingman]

Doug, first off, no more need to help me as I (actually you) figured it out. I just thought that since the ".ControlType" didn't appear in the properties dropdown list, that I couldn't use it, but obviously that's wrong. I can't believe you actually took the time to keep replying to my questions considering how much of a layman I am. I never would have figured this out without your help. If I knew you I'd say if you ever need any help with anything, just let me know (except with Access, of course). I am now able to make the changes live to the database, and I wasn't sure I'd be able to since I didn't know how to do either of the first two questions I posted. Thanks again

[This message has been edited by dunkingman (edited 07-18-2001).]

 

[D-Fresh]

Hey no problem... I started once too... I had no idea what I was doing with Access when I first started my job 3 years ago... But with a good background for programming and a boss who's strict for deadlines, I was able to pick up on Access pretty well... Just keep on building databases and writing code.. This board is a great source of info.. Keep on visiting even if you don't have any questions.. And don't worry about posting questions.. That's what the board is for... You don't have to apologize. Best of luck with the updates.

Doug