Loading textbox value when a checkbox is clicked

S

Summer123

Registered User.
Local time
Yesterday, 21:20
Joined
Feb 9, 2011
Messages
216
Hello,
i have a question regarding checkboxes, is it possible to load textbox values when a checkbox is clicked? For example i have 2 checkboxes, if chbox1 is clicked i want to run qry1 and load txtbox1, txtbox2, txtbox3, if chkbox 2 is clicked i want to run qry2 and load txtbox1, txtbox2, txtbox3. If both chkbox 1 and 2 are clicked then run qry3 and load txtbox1, txtbox2, txtbox3
i have 4 different checkboxes and there can possibly be 14 different types of posibilities to load the 3 textboxes.. i was thinking of doing the following but i dont know how to say to load the text boxes that are on the form.. can someone please help? Also if there a better way to do this then to do 14 If Then statements?

Private Sub Command16_Click()
If chkbox1.Value = True Then
DoCmd.OpenQuery "qry1"
End If
If chkbox2.Value = True Then
DoCmd.OpenQuery "qry2"
End If
If chkbox1.value = True And chkbox2.Value = True Then
DoCmd.OpenQuery "qry3"
End If
End Sub
thank you,
Summer
 
It appears that the code you have would run the queries as you have described.

Are there values being returned by each query that should be used to populate the text boxes or what information do you want to populate the text boxes with?

There are several ways to populate controls on a form with data depending on where the data is and other considerations. For example, if your queries are returning the values that you want to be displayed on the form, you could manually bind each text box to the appropriate value in the query and then programatically set the Row Source of your form to be equal to the appropriate query. This would then display the data from the query in the form.

Please provide more details and someone will be able to provide more specific assistance.
 
yes sure.. sorry i shoul dhave done that earlier...
ok so yes my queries return values that need to populate in each text box. So value 1 will populate txtbox1, value2 in query will populate txtbox2 and so on... does that help?
 
First let me clear up something that I said in my previous post. I used the term "Row Source" when I should have referred to the "Record Source" property. Controls like combo boxes and list boxes have a "row source" property but a form has a "Record Source" property.

I am going to assume that all three of your queries return the same fields that will be used to populate the text boxes. Assuming that this is true, open your form in design view and set the row source of your form to be "qry1".

Set the "Control Source" of each of your text boxes to be one of the fields returned by your query and save your changes. This will make the form to be bound to the query and each of the text box controls to be bound to a field from the query.

Because the form is now bound to "qry1", when the form is opened it will have the text boxes populated with the values from the fields that you specified as the Control Source for each of the text boxes. In order to make the form and the check boxes to be in sync when the form opens, set the default value of "chkbox1" to "true" to indicate that the values returned by "qry1" should be displayed and set the default value of all of the other check boxes to "false". (I would suggest here that you rename your controls to some meaningful name so you can tell which one is which.) If you prefer some other query to be the default displayed information you can set that query to be the record source of the form and set the appropriate check box to be true.

Copy and past the code below into the forms module.
Code: 
Function DisplayData()
          If Me.chkbox1.Value = True Then
                       Me.RecordSource = "qry1"
          End If
          If Me.chkbox2.Value = True Then
                       Me.RecordSource = "qry2"
          End If
      If Me.chkbox1.value = True And Me.chkbox2.Value = True Then
            Me.RecordSource =  "qry3"
      End If
      'you would add any additional conditions here the same way as shown above
 End Function
This code is a user defined function that determines the query to be set as the record source of the current form. Setting the record source also refreshes the form to display the records and should be inserted just below the:
Code: 
Option Compare Database
Option Explicit

If your form module does not have the "Option Explicit" line, you should add it.

In the After Update event of each of your check boxes, please the following line of code:
Code: 
DisplayData
 
thank you Mr. B! this was real helpful. One question, is there a way to blank out the text boxes as the beginnig or is that not possible?
 
You could set the default value of all check boxes to false and remove the Record Source of your form. With these changes, there will be nothing displayed in the text boxes and only after you check a check box will anything be displayed.
 
THANK YOU! ok if i remove the record set it gives a value of "#Name?", which is not pretty. Is there a way to blank out the text box so it looks normal? I was thinking to create a query with no results and add that as the Record set of the form this way its blank at all times but is there a way to do this in the code itself?
 
I should have thought about that. I would suggest that you create a "qry0" that has the same fields in it but has criteria that will cause it to never return any records and set this new query as the initial Record Source for your form.
 
yeah i have done that already but was wondering if there was a diffrent way to do it in the code... but any who thank you sooooo much for your help!
 
ok if i remove the record set it gives a value of "#Name?", which is not pretty
Click to expand...
You could try this.
Leave the controls unbound and set the Control Source property of each control in the code that sets the query to be used.
 
1. Are the queries bound to the same data source (i.e. tables) and do they contain the same fields?
2. Do the queries return 1 record or several records?
3. What is the form already bound to? That is, what is the Record Source of your form?

It sounds to me that what you want is the DLookup() function, but the above questions need to be answered first.
 
Hi vbaInet,
to answer your questions
1. yes and yes
2. yes
3. record source is a temp query that i created off of my main table where i run each query to get the reuslts. But this query returns no results so that when the form loads its blank... hope that makes sense


So here is how it works
Maintable - has 5 fields
Field1 Field2 Field3 Field4 Field5
DBB 34 60 56 16
DBC 21 20 38 6
DBH 34 60 10 10
DBS 20 20 40 13

The form will display the following
Checkbox1 – DBD
Checkbox2 – DBC
Checkbox3 – DBH
Checkbox4 – DBS

Following textboxes are present on the form
Textbox1 – tied to field1 on the main table
Textbox2 - tied to field2 on the main table
Textbox3 - tied to field3 on the main table
Textbox4 - tied to field4 on the main table
Textbox5 - tied to field5 on the main table

When checkbox1 is clicked, it should display the query1 results. When checkbox2 is clicked then query 2 results will be displayed in the text boxes. See queries below. Does this help answer your questions?
QUERY 1 – FOR DBB only will return
DBB 34 60 56 16


Query2 – for DBC only will return
DBC 21 20 38 6


One thing I am stuck on now is, when both DBB and DBC are checked then I want the sum of the 2 to be displayed. I got the query working but how do I post the sum results in textbox2 – 5 ? See query below..

Query 3 – will return sum of DBB and DBC – I can do this with total query and it give me the result (see the SQL below) but I don’t know how to display that result on the form
SELECT Tbl1.field1, Sum(Tbl1.field2) AS SumOffield2, Sum(Tbl1.field3) AS SumOffield3, Sum(Tbl1.field4) AS SumOffield4, Sum(Tbl1.field5) AS SumOffield5
FROM Tbl1
GROUP BY Tbl1.field1, Tbl1.ID
HAVING (((Tbl1.field1)="DBD" Or (Tbl1.field1)="DCD"));
Field1 Field2 Field3 Field4 Field5
DB 34 60 56 16
DC 21 20 38 6
55 80 94 22
 
Last edited:
can someone please help? is there a way to display totals of the query on a form? see my layout in the above post.. maybe i need to change it entirely?
 
Alright. Mr. B has given you exactly what you need and bobfitz idea for your other question will work too. I was just clarifying what your objective was.

If you want to display the totals, you need to make the Default View of the form Continuous Form.
 
that did not work either.. any other suggestions on how to display the "Sum" results on a form?
 
its actually working but i have to keep closing the table and then reopening it... so what i did was added my main table as a subform on my form with the DSUM textbox. But how do i make it current so that when the checkbox is clicked it updates the text box? right now i have to close the form and reopen it to see it work.. any ideas?
 
me.refreash under both forms does not work.. please help!
 
I may be misunderstanding this but from you messages you indicate that the subform is linked to a copy of a table. Why is it linked to a copy?

If the checkbox is in the subform then you should first save the change to update the table then a me.requery will refresh the calculation.

Try adding to the after update event of your check box(s):
Code: 
If me.dirty then
     me.dirty = false
endif
me.requery
 
You must log in or register to reply here.

Similar threads

E
Generating report on a dynamic subform
Replies
6
Views
534
The_Doc_Man
The_Doc_Man
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
1,173
Mike Krailo
Mike Krailo
F
Solved Mask with sub-mask filtered by 2 combined drop-down boxes and a checkbox, opening two different reports depending on the checkbox
Replies
6
Views
830
Faoineag
F
A
Solved checkbox placed in frame by a query, not responding
Replies
1
Views
813
tvanstiphout
T
B
Solved ACCDE File On Click Error
Replies
9
Views
1,132
bonzitre
B

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom