Im trying to make a button that will take the record selected in a combo box and use that in a query as the criteria then run, and save that query then update it in the form then I would like to do some calculations based on the information sorted in the query. How do I go about doing this? Is there a better way?
Make a command button in a form run a query
- Thread starter sljensen
- Start date
Isskint
Slowly Developing
- Local time
- Today, 19:46
- Joined
- Apr 25, 2012
- Messages
- 1,302
Using a value on a form as criteria in a query is done by referencing the control of that form in the query field criteria section of the query builder.EG On a form, Form1, you have a combobox, cmbName. On the query in the criteria section under the Name field you would put in =Forms!Form1.cmbName.
What do you mean by run, and save that query then update it in the form? If you want to save the query as a new query, you could use the CreateQueryDef method. To update it in the form wouldl mean changing the forms RecordSource to the name of the new query.
Calculations can be done in the query. Just select an empty column in the Query Builder, and click the Build icon on the toolbar to bring up the Expression builder box.
What do you mean by run, and save that query then update it in the form? If you want to save the query as a new query, you could use the CreateQueryDef method. To update it in the form wouldl mean changing the forms RecordSource to the name of the new query.
Calculations can be done in the query. Just select an empty column in the Query Builder, and click the Build icon on the toolbar to bring up the Expression builder box.
Sounds like all you want to do is a search form. So after selecting a value in the combo box, the related record in the form should be displayed right? Use the combo box wizard and on the first page select the third option then follow the instructions on the remaining screens.
Thanks Isskint that should work but here is another problem. This query that I will be using will be querying from two different tables with the same criteria which is the lot number and the corresponding weight. In one table this lot number is specific to only one record whereas in the other table there could be multiple records with the same lot number. Ill also need to sum the multiple records into a total and then take the difference between the total weight (from the table with only one record) and the summed weight of the multiple records from the other table. Then this value I'd like to be returned to the form with the combo box. How do I do this?
Last edited:
Isskint
Slowly Developing
- Local time
- Today, 19:46
- Joined
- Apr 25, 2012
- Messages
- 1,302
On the table structure first, the LotNo fields in the 2 tables should be related to each other. The unique field should be a PK in that table and the table with multiple LotNos should be an FK. This will assist your query work. If you are not sure about relationships just shout.
Next, Calculations
Use the DSUM() function.
You use the DUM() like this:
Dsum("[Field]","
") to get the total sum of EVERY record
Or add a criteria to reduce the number of records summed
Dsum("[Field]","
","[FieldB]=" & [Something])
So you would have a query based on the table with Unique Lot Nos, with a criteria to reduce the results according to the value form your form and an added calculated field to sum the weights in the other table where the lot nos match. You would then use this query as the rowsource for your combo box.
If you have problems just shout and could you provide relevant table details.
Next, Calculations
Use the DSUM() function.
You use the DUM() like this:
Dsum("[Field]","
Or add a criteria to reduce the number of records summed
Dsum("[Field]","
So you would have a query based on the table with Unique Lot Nos, with a criteria to reduce the results according to the value form your form and an added calculated field to sum the weights in the other table where the lot nos match. You would then use this query as the rowsource for your combo box.
If you have problems just shout and could you provide relevant table details.
