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]));
Microsoft AccessSQL
Last Comment
Yaniv Schiff
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
ASKER
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
So is ProjectID a text field, rather than a number?
ASKER
it is a text field, i changed the where expressions to read ProjectID Like "& cboProjectID" with the same result
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
That did it! Thanks.
ASKER
Thanks for the help experts.
"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.