Issue between Form and Table

[randallst]

Hi Everyone,

I have been working on an Audit Database for a while now and basically got it going...until I found a flaw in one of my designs.

I have a Form which allows you to update records in a table, and the issue I have is the following;

When an isssue has been completed, you enter a date into the 'Actual Closure Date' field. This changes the status on the form from Active to Closed. Issue is, this isn't changing on the Table.

I have attached a slimmed down version of the database, removing all data and input test data. 2 of the examples are closed on the form, but still show as active on the Table.

I am a beginner at Access so appologies if it's a stupid issue, this project was something I took on to try and teach myself how to use Access so would be over the moon to find out what I have missed/done wrong. I have been pulling my hair out for too long now!

Thanks in advance
Stu

 

[boerbende]

In your query "master" you have not linked "status" to the issue table. So it will never write the results in the table
You have to solve this different. For example in the afterupdate event of the Actual Closer date

Ben

 

[randallst]

In your query "master" you have not linked "status" to the issue table. So it will never write the results in the table
You have to solve this different. For example in the afterupdate event of the Actual Closer date

Ben

Thanks for the feedback Ben, if I change the Master Query to link Status to Issues, what should I do as an 'After Update' event to get the database to automatically chance Active to Closed when a date is inputted?

Again, thanks in advance

 

[boerbende]

replace the macro in the actual closure date with an event (code).

click on actual closure date
go to properties, events, afterupdate
delete the macro
Right mouse click on actual closure date and select build event
in "choose builder" form, choose "code builder"

Private Sub Actual_Closure_Date_AfterUpdate()

If IsNull(Me.[Actual_Closure_Date]) Then
Me.[Status] = "Active"
Else
Me.[Status] = "Closed"
Me.Dirty = False ' Save the record

End Sub

Think this will work

Ben

 

[randallst]

replace the macro in the actual closure date with an event (code).

click on actual closure date
go to properties, events, afterupdate
delete the macro
Right mouse click on actual closure date and select build event
in "choose builder" form, choose "code builder"

Private Sub Actual_Closure_Date_AfterUpdate()

If IsNull(Me.[Actual_Closure_Date]) Then
Me.[Status] = "Active"
Else
Me.[Status] = "Closed"
Me.Dirty = False ' Save the record

End Sub

Think this will work

Ben

You sir are an absolute legend. I am gradually getting the hang of VBA but keep confussing myself. I was writing something basically to do what you wrote, but looks nothing like it haha! Once again, thank you, it's been doing my head in for a while :banghead:

 

[boerbende]

Glad to help...Have had some headbangers myself (and knows it hurts) and have experienced several times thanks to this forum how much relief an answer can give