Multiple forms

[Fabian1675]

Hello; I have two forms one MainForm and StepChildForm and I have the following content on each form:

MainForm has a: ResultTextbox1 and ResultTextbox2 that I need to keep track of the entries from the StepChildForm.

StepChildForm has a: Textbox1; Textbox2; Textbox3; and Textbox4

base on user input I would like for what ever value (numeric) happens to be in Textbox3 to be outputed to either ResultTextbox1 Or ResultTextbox2

so if the user entered the following:

If Textbox1 = a and Textbox2 = a and Textbox3 = 2 and Textbox4 = a Then

ResultTexbox1 = Textbox3

Else:

ResultTextbox2 = Textbox3



So far I'm able able to accomphlish this as long as the user did not go back and changed the previous input.

The problem I encountered is when:

1) the user goes back and changed one of the Textbox input to say b the ResultTextbox2 will now = Textbox3, but the ResultTextbox1 still retains the previous data. What I would like is; since the If Statement is no longer True I want the ResultTextbox1 = 0

2) the other problem I encounter is if the user decided to add a second entry I would like the entries on both Textbox3 to be added and output the result to either ResultTextbox1 or ResultTextbox2

This is what I did to get the result to add up but It's not right:

ResultTextbox1 = (TextBox3) + (ResultTextbox1)

I guess my question is there a way to continously monitor the user input and reflect the changes into my ResultTextboxe(s) accordingly.

Thanks

 

[Beetle]

I'm not sure I follow your logic, but generally speaking;

1) the user goes back and changed one of the Textbox input to say b the ResultTextbox2 will now = Textbox3, but the ResultTextbox1 still retains the previous data. What I would like is; since the If Statement is no longer True I want the ResultTextbox1 = 0

You can use the After Update event of each text box on the subform to check the logic after a user changes a value, then update values on the main form accordingly.

2) the other problem I encounter is if the user decided to add a second entry I would like the entries on both Textbox3 to be added and output the result to either ResultTextbox1 or ResultTextbox2

If you want to sum the results of one of the text boxes on the subform, then put a calculated control (text box) in the subform footer, then reference that calculated control for the values in your main form controls (text boxes).

However, I'm not sure how that will jive with your logic if one of the records in the subform satisfies the logic and the other one doesn't.

 

[Fabian1675]

If you want to sum the results of one of the text boxes on the subform, then put a calculated control (text box) in the subform footer, then reference that calculated control for the values in your main form controls (text boxes).

However, I'm not sure how that will jive with your logic if one of the records in the subform satisfies the logic and the other one doesn't.

Hey thanks I never tthought about adding a summing function at the bottom of the form. Would I be able to sum up each Textbox based on it's input? for instance:

Textbox1 = a And Textbox2 = a And Textbox3 = 2 and Textbox4 = a

Textbox1 = b And Textbox2 = a And Textbox3 = 3 and Textbox4 = b


Then I can just tell the ResultTextbox to look at the result at the bottom of the form? I'm not very good at trying to explain things I hope this conveys of what I would like to achieve.

Also how do I tell access to only sum up if the input on the Textbox is = to a?

 

[Beetle]

If you want to do that it would be more efficient to use a calculated field in the query that is used as the record source for the subform. The calculated field would look like;

Alias: IIf([Field1]="a" And [Field2]="a" And [Field4]="a",[Field3],0)

Replace Alias with whatever you want to use for the calculated field name, and of course replace Field1, Field2, etc. with the actual names of your table fields.

Then, in your subform, sum this calculated query field. Any field that does not meet the criteria will have 0 in this field so it won't affect your sum. That way you don't need to worry about checking the value in a control event, it will automatically calculate anytime a record is updated.

 

[Fabian1675]

If you want to do that it would be more efficient to use a calculated field in the query that is used as the record source for the subform. The calculated field would look like;

Alias: IIf([Field1]="a" And [Field2]="a" And [Field4]="a",[Field3],0)

Replace Alias with whatever you want to use for the calculated field name, and of course replace Field1, Field2, etc. with the actual names of your table fields.

Then, in your subform, sum this calculated query field. Any field that does not meet the criteria will have 0 in this field so it won't affect your sum. That way you don't need to worry about checking the value in a control event, it will automatically calculate anytime a record is updated.

Thanks, the subform currently gets it's source directly from a table can I apply this function into the table source or is it better to just create a query?

 

[Beetle]

Create a query that includes the fields you need from the table, then add the calculated field mentioned above. Then use that query as the record source of your form in place of the table.

Personally, I always use queries as form or report record sources, because you can add your own calculated fields easily and you have better control over sorting.

 

[Fabian1675]

Create a query that includes the fields you need from the table, then add the calculated field mentioned above. Then use that query as the record source of your form in place of the table.

Personally, I always use queries as form or report record sources, because you can add your own calculated fields easily and you have better control over sorting.

Thanks, creating a query and doing all the manipulation from there is a much better choice. It works just like how I expected it to be and with the criteria that I want. The only thing I would like to do now is to filter the sum for the current day from 8:00AM current day to 8:00AM the next day. Right now I'm able to filter using the date() and date()+1 but I also would like to tell access at what time to start and what time to end

I have a field named Date in my querry and that's where I'm doing the date filtering in the criteria section

Thanks