By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,915 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Run Query On Form To Populate A Field On A Table

P: 37
HI

I have a database with one Main Table.

That table has 4 Fields:
Age
Type
Specialty
Cost

A Form has been created to allow data entry.

The age, type, specialty are combo boxes. The idea is that when the user has inputted into the age, type, specialty fields, a query can be executed that will populate the cost field (on the form - and likewise to the Main Table).

Currently, I have a Run Query Command Box.

Query =

SELECT tbl_OP_Tariffs.Cost
FROM tbl_OP_Tariffs
WHERE (((tbl_OP_Tariffs.Age)=[Forms]![OP_Information]![AgeGroup1]) AND ((tbl_OP_Tariffs.Type)=[Forms]![OP_Information]![Type1]) AND ((tbl_OP_Tariffs.spec)=[Forms]![OP_Information]![Specialty1]));

When I hit the Command to run the query, the result is shown as a separate window.

Thats not what I want. What I need is for the result to automatically be placed into the cost field.

Been working on this all day, and am losing the plot......

Any help appreciated........
Dec 21 '06 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
HI

I have a database with one Main Table.

That table has 4 Fields:
Age
Type
Specialty
Cost

A Form has been created to allow data entry.

The age, type, specialty are combo boxes. The idea is that when the user has inputted into the age, type, specialty fields, a query can be executed that will populate the cost field (on the form - and likewise to the Main Table).

Currently, I have a Run Query Command Box.

Query =

SELECT tbl_OP_Tariffs.Cost
FROM tbl_OP_Tariffs
WHERE (((tbl_OP_Tariffs.Age)=[Forms]![OP_Information]![AgeGroup1]) AND ((tbl_OP_Tariffs.Type)=[Forms]![OP_Information]![Type1]) AND ((tbl_OP_Tariffs.spec)=[Forms]![OP_Information]![Specialty1]));

When I hit the Command to run the query, the result is shown as a separate window.

Thats not what I want. What I need is for the result to automatically be placed into the cost field.

Been working on this all day, and am losing the plot......

Any help appreciated........

Can you post a reply to this thread on Wednesday/Thursday to bump it up the list. It may not get much attention over Christmas.

Happy Holidays.

Mary
Dec 25 '06 #2

P: 37
Hi

Can anyone help with this.

I have created the subform within the main form that shows the results when the comman buttn is hit.

Heres the code:

Private Sub RunIPCost_Click()
On Error GoTo Err_RunIPCost_Click

Dim stDocName As String

stDocName = "qry_ip_cost"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Forms![IP Information]![qry_ip_cost SubForm].Requery
Exit_RunIPCost_Click:
Exit Sub

Err_RunIPCost_Click:
MsgBox Err.Description
Resume Exit_RunIPCost_Click

End Sub

---- But the query will still open in a new window, i need to stop it from doing that
Dec 29 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi

Can anyone help with this.

I have created the subform within the main form that shows the results when the comman buttn is hit.

Heres the code:

Private Sub RunIPCost_Click()
On Error GoTo Err_RunIPCost_Click

Dim stDocName As String

stDocName = "qry_ip_cost"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Forms![IP Information]![qry_ip_cost SubForm].Requery
Exit_RunIPCost_Click:
Exit Sub

Err_RunIPCost_Click:
MsgBox Err.Description
Resume Exit_RunIPCost_Click

End Sub

---- But the query will still open in a new window, i need to stop it from doing that
Why are you opening the query at all. If it is the record source for the subform then the subform requery command is sufficient. Comment out the open query line and see if it does what you want.

Mary
Jan 1 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.