Link to home
Start Free TrialLog in
Avatar of Yaniv Schiff
Yaniv SchiffFlag for United States of America

asked on

rerun a query after on change event in form combobox

I have a form with a combo box where a user can select a project id. After selecting the project id the user can click on 1 of 3 buttons that takes the project id selected and queries 1 of 3 tables for all responsive records. This all works fine. The selection form stays open so that the user doesn't have to reopen it to rerun a query. The problem i running into though is the user can't change the selected project id and rerun the queries. I can't figure out where to put the code to rerun the query, is it under "On Change" or "AfterUpdate" and is it associated with the combo box or the query buttons? Below is the code for the three query buttons and the sql for one of the queries run.
Private Sub SearchIntake_Click()
DoCmd.OpenQuery "PhysicalItem Query By ProjectID", acViewNormal, acEdit
 
End Sub
 
 
Private Sub SearchTransferIN_Click()
DoCmd.OpenQuery "TransferIN Query By ProjectID", acViewNormal, acEdit
End Sub
 
Private Sub SearchTransferOUT_Click()
DoCmd.OpenQuery "TransferOUT Query By ProjectID", acViewNormal, acEdit
End Sub
 
=-----------------------------------------------------------------------------
PhysicalItem Query By ProjectID
 
SELECT PhysicalItem.PhysicalItemGUID, PhysicalItem.ProjectID, PhysicalItem.ItemNumber, PhysicalItem.Type, PhysicalItem.Make, PhysicalItem.Model, PhysicalItem.SerialNumber, PhysicalItem.Notes, PhysicalItem.Custodian, PhysicalItem.Description, PhysicalItem.Location, PhysicalItem.ActiveUse
WHERE (((PhysicalItem.ProjectID)=[Forms]![_SearchByProjectID]![cboProjectID]));

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I wrote this:

"Queries should be used ONLY to build forms/reports"

which isn't really accurate. You also use queries to manipulate data, and to group data correctly and such. What queries should NOT be used for is User Interfaces. Use forms and reports instead.
Avatar of Yaniv Schiff

ASKER

ok here's what i have. I'm getting a syntax error:

Run-Time error '3075'
Syntax error in query expression
'ProjectID=_EXCEPTION'


NOTE: _EXCEPTION is the name of the project id i selected
Private Sub SearchIntake_Click()
If CurrentProject.AllForms("PhysicalItem Query By ProjectID").IsLoaded Then DoCmd.Close acForm, "PhysicalItem Query By ProjectID"
DoCmd.OpenForm "PhysicalItem Query By ProjectID", acFormDS, , "ProjectID=" & Me.cboProjectID
End Sub

Open in new window

Ok, i got it to run without the syntax error but it's not pulling up any records, it just pulls up the form with a new record entry.
Private Sub SearchIntake_Click()
If CurrentProject.AllForms("PhysicalItem Query By ProjectID").IsLoaded Then DoCmd.Close acForm, "PhysicalItem Query By ProjectID"
DoCmd.OpenForm "PhysicalItem Query By ProjectID", acNormal, , ProjectID = "& cboProjectID"
MsgBox "you selected" & cboProjectID & "", vbOKOnly
 
 
End Sub

Open in new window

So is ProjectID a text field, rather than a number?
it is a text field, i changed the where expressions to read ProjectID Like "& cboProjectID" with the same result
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That did it! Thanks.
Thanks for the help experts.