DoCmd OpenForm Advice

[dkmoreland]

Hi everyone,

This is what I am trying to make happen:

User enters data in [Input Table].[Evalby] field - in the After Update event, I open a form, asking the user to indicate whether the job is complete. If the user clicks the YES button, the job status update form opens and allows the user to update the fields on this status update form, which has a different data source.

All that works just fine - but there is one thing I am unsure of how to code. There is a checkbox on the original calling form that has a value of false when the After Update event is kicked off. Once the user clicks the YES button, I need to change that checkbox value on the original form to true, then allow the user to update the status form.

Can anyone offer insight on the best way to code this?

Thanks very much.

 

[isladogs]

If the checkbox is bound, use an update query or sql statement to update the underlying field and modify it's status.

If it's unbound, create a public sub for the checkbox to include the required code using a suitable event e.g. OnClick. You'll need to include an If clause to check its status. By making it public you can call that procedure from your other form.

Hope that's enough to get you started

 

[dkmoreland]

Thanks for your suggestion. I'm running into a little trouble getting the checkbox to update - maybe my query syntax is wrong. I still have warnings turned on so I can see if it is actually updating the row - so far, no luck.

Here's the sql view for my saved update query:

UPDATE [Input] SET [Input].[Job Complete] = -1
WHERE ((("id")=[Forms]![Input Form]![ID]));

Here's the code for the On Click event for the Yes button:

Private Sub CmdJobComplete_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb

       DoCmd.OpenQuery "JobCompleteUpdate", acViewNormal
 
       DoCmd.OpenForm "Update Job Status form", acNormal
      
End Sub

The only part that does not work is the update query. I am missing something. Thoughts?

 

[isladogs]

Remove the quotes around ID
I've also removed unnecessary brackets below

UPDATE Input SET Input.[Job Complete] = True
WHERE ID=Forms![Input Form].ID;

 

[dkmoreland]

I'm still finding 0 rows to update, even though debug.print shows that the table is on the correct record. What is an alternative method to using OpenQuery?

Any and all suggestions are welcome.

Thanks

Private Sub CmdJobComplete_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim jobid As String
jobid = [Forms]![Input Form]![ID]

    Debug.Print jobid
    
    DoCmd.OpenQuery "JobCompleteUpdate", acViewNormal
    DoCmd.OpenForm "Update Job Status form", acNormal
       
End Sub

Here's my saved query:

UPDATE [Input] SET [Input].[Job Complete] = True
WHERE ID=Forms![Input Form].[ID];

 

[isladogs]

Ok let's go back a few steps.

Is the form open? Is ID the correct name for that control on the form?

Next find an ID value for which a record exists.
Create a SELECT query with that ID value. Does it give the correct record(s).
Now open the form to that record and filter the query using the forms syntax.
Still OK? Next change to an update query and click run. Does it say it will update any records?

Assuming it does then that query should work
However you can use a sql statement with either DoCmd.RunSQL or CurrentDB.Execute.
If you do that the code will need slight modification
Assuming ID is a number then

CurrentDB.Execute "UPDATE Input SET Input.[Job Complete] = True WHERE ID= " & Forms![Input Form].ID & ";"