Update table from form using where clause (multiple conditions)

[SimoneRene]

Hi Guys,

I have this code behind a save button, I'm trying to automatically update my table from a form when a user up issues a drawing to a new rev.

If [LatestIssTxt] = "Latest Issue" Then
      DoCmd.SetWarnings False
      DoCmd.RunSQL "UPDATE DWGIssueControl SET DWGIssueControl.LatestRev ='Old Issue'" & "WHERE (((DWGIssueControl.DrawingNumber) = '" & Me.DwgNoCbo & "')) & ((DWGIssueControl.Issue)<> '" & Me.txtIssue & "')"  
'When drawing is up-issued status changed to old issue automatically changed from Latest Issue
            End If

I have created a column in my table to denote weather the issue is an 'old issue' or 'latest issue', this is what I'm trying to auto update. On the form there is a button to tick if its the latest issue which fills the text box LatestIssTxt= "Latest Issue".

On the form we have DwgNoCbo, txtIssue and LatestIssTxt which are linked to DrawingNumber, Issue and LatestRev respectively in the table.

Any help is greatly appreciated, I'm losing the will! haha:banghead:

Thank you.

 

[Minty]

I think you would be better to simply have a IssueDate and pick the last one - this retains when the drawing was updated and how old the previous ones where. Display or highlight the latest one by using Max(IssueDate) in your forms.

It also removes the need to try and store and update a (unnecessary?) text field at different points of your process.

 

[SimoneRene]

Hi Minty,

Great suggestion, I have an issue date already in my form/table.
As I have lots of different drawing numbers each with lots of different issues/dates how do I set the max Issue date for each set of drawing numbers.

I have a separate continuous form to search through all the drawings I was
wanting to highlight the most recent for each drawing in there. I guess I'm going about it a funny way!

Thank you!

 

[Minty]

You should only have one issue number / issue date per drawing?

If not can you post up your data layout as it sounds possibly a bit wrong, unless you have a fixed drawing number and then a separate table with the drawing number / issue no / issue date?

 

[SimoneRene]

You should only have one issue number / issue date per drawing?

If not can you post up your data layout as it sounds possibly a bit wrong, unless you have a fixed drawing number and then a separate table with the drawing number / issue no / issue date?

Hi Minty,

My column headings are ID, Drawing Number, Drawing Title, Issue and Issue Date. There are multiple records with the same drawing number and title but they have different issue numbers and dates.
The reason there are multiple drawings of the same name/number is as the drawing are modified and updated as the design matures the same drawing is up-issued to a higher revision. i.e drawing 1 -issue 1, drawing 1- issue 2 e.c.t

Thanks,

Simone.

 

[SimoneRene]

If [LatestIssTxt] = "Latest Issue" Then
            DoCmd.SetWarnings False
            DoCmd.RunSQL "UPDATE DWGIssueControl SET DWGIssueControl.LatestRev ='Old Issue'" & "WHERE (((DWGIssueControl.DrawingNumber) = '" & Me.DwgNoCbo & "')) AND((DWGIssueControl.Issue<'" & Me.Issue & "')) " '"  'When drawing is up-issued status changed to old issue automatically changed from Latest Issue
            End If

Got it to work, I used 'AND' instead of '&'. I also think I may have moved some brackets around.

My next step is to search through all the existing drawings in the database and set the max(issue) to 'Latest Issue'. Any ideas?
So I want to display the highest number in the issue column for each drawing.

Thank you!

Simone.

 

[Minty]

I wouldn't store it - just display it by calculating if it's the last one and bring that in in a query. As you have already discovered you will have to keep this up to date from any where it could be changed, much easier and reliable to simply calculate it.

Make a query that finds the last ID for each drawing. Then use that to create a calculated field that displays a 'Last Issue' text - bring that into your forms data. It will always be accurate and doesn't need any code to run at certain points in time.

 

[SimoneRene]

I wouldn't store it - just display it by calculating if it's the last one and bring that in in a query. As you have already discovered you will have to keep this up to date from any where it could be changed, much easier and reliable to simply calculate it.

Make a query that finds the last ID for each drawing. Then use that to create a calculated field that displays a 'Last Issue' text - bring that into your forms data. It will always be accurate and doesn't need any code to run at certain points in time.

Yes I was thinking a query was the way forward, I haven't had much practice with queries- if I make a query with the two fields drawing number how do I display the highest issue for each drawing? In criteria (data sheet view) I tried max(Issue) but get the error of having an aggregate in a where clause.

Thank you for your help!

Simone.

 

[SimoneRene]

Yes I was thinking a query was the way forward, I haven't had much practice with queries- if I make a query with the two fields drawing number how do I display the highest issue for each drawing? In criteria (data sheet view) I tried max(Issue) but get the error of having an aggregate in a where clause.

Thank you for your help!

Simone.

https://www.techonthenet.com/access/queries/max_query2_2007.php This is for older versions of Access the version I am on doesn't have a 'total' column otherwise this would have worked.

 

[Minty]

You do have a Totals button definitely! Which version of Access are you using ?

 

[SimoneRene]

You do have a Totals button definitely! Which version of Access are you using ?

Awesome that's good to know...now to find it I'm using 2016

Thanks,

Simone

UPDATE: I found it haha