Create/Update a field in a table that is opened via a sql qry on form?

[GreyHounds]

I have searched the forum for information and I have tried events on multiple forms to the point that I have confused myself inside out. I know it has to be easier than what I have been doing which is sub forms, on load events, on open events, closing the main form and opening a duplicate temp form…I imagine that any one of these things alone might work. My problem is that I am trying too many things. So I need to get back to basic square one – what is the best way to update a field in a table that is opened via a sql qry on the current form.

I have a form where in the properties data tab/record source is a sql statement based on a qry with two tables.

Table 1= tblDefect pk=tblDefect.ROW_ID
Left outer join on
Table2 = tblUpdateClass pk=tblUpdateClass.DEFECT_ROW_ID


Table 1 contains a list of defects with two columns classification and rank which may or may not be populated

Table 2 contains the recommended classification and rank for the defect in Table 1 not every defect in Table 1 has a record in Table 2

The form has the defect number, current classification, current rank, recommended classification (if it exists), recommended rank (if it exists).

Eventually, once all defects in the collection are reviewed the recommended classification and recommended rank willprocessed become the actual classification and rank in Table1

Issues I need to solve:

1. click “the button” and update the correct record in Table 2 to a different rank or classification – however since the table is in use it will not let me update
2. click “the button” and add a new record (if one does not exist) with the Defect Row_ID to Table 2 with a new rank and classification – as above the table is in use so it will not let me update
3. return to the original form with the same record (tblDefect.Row_ID) with the values updated
4. p.s. if i use a sub-form in the main form it is blank if there is no record

What is the correct way to do this, the correct sequence of events, and the correct places to do these things?

I am really poor with Macros but do have a little experience with VB and access.

Thanks in advance for any help you can offer.

Puzzled, perplexed and down-right confused… ?

 

[ZiggyS1]

it is hard to visualize, but my gut is telling me you may not have your tables structured properly.

Shouldn't table 2 just contain classifications, and then from table one you select the one that applies ( from a combo)?

Remember you need to think about how to "Relate" data not copy it to another table...yes there are times when you do this, but just want to make sure you aren't duplicating data when it doesn't need to be.

 

[GreyHounds]

Thanks so much for your response. I need to have the second table as we are comparing old values with new values.

Some background: we have a magic number of 25 defects that can be addressed in a bi-monthly rollout. There will always be 25 Active defects that are being worked on and 25 open defects on deck for the next rollout.

There are currently 200+ defects to be addressed (yes read and weep) not all of these defects are a high priority - many are nice to haves or obscure ideas. There is a board of business stake holders who make a group decision on which 25 to throw into the next rollout.

The individual Board members can take any unprioritized (new) defect or any B priority and make it an A priority. And take any open A priority (on deck for the next rollout) and make it a B priority if something more important has come up. There are 9 business units represented.

They mark as many A priorities as they see fit - then they come together and make decisions to whittled it down to 25. So in my first form it opens as a datasheet of everything that is either currently Open and A-Priority (Table 1) as well as any of their recommended A-Prioritys (Table 2)that they have added.

They then compare all and decide which are the final 25. The others become B priorities.

The 2nd table is what is used to update a remote defect tracking database that is not available to the Board but only to the developers. It becomes the shortlist of new A priorities or new B Priorities

I put it in a separate table so they can see both old and new priorities and make a decision.

If I have missed something in your post let me know.

Thanks again.

still ruminating...

 

[ZiggyS1]

I missed your reply, I'll take a look on Monday.

 

[Fretwalker]

Seems like your process is to make modifications to the "temp" table and then write the applicable changes (top 25) back to the "perm" table. Is that the size of it?

If I'm not off base here, I'm curious as to why your temp table won't update with new values? Do you have it set as read only on the form or what? By allowing "edits, adds..." in the properties window of the form you should be able to make those changes readily available. Will need to handle possible duplicate errors as you could wind up with two items with the same ranking (perhaps that's not a problem). You may need to write something that reranks everything down the list after an update has been made.

To commit top 25 to the perm table is merely a matter of truncate and reload top 25. Which can be done a number of ways.

Will check back to see if there's additonal clarification, thanks.

 

[ZiggyS1]

still seems to me that Table one should be for Review work, where you will select the Class and Rank, when done you can change the status of those records and use that to control who can see them based on criteria, or if you want to move to another table, use Append/Delete queries to update.

working within the one table will still show you all the records to review.

 

[GreyHounds]

Thanks ZiggyS1 and Fretwalker for your responses. Sorry for the delay getting back as i actually took the weekend off. Miracle of miracles.

The original form for review is locked. However I am closing that form when the user clicks the update button and pulling up table 2 in a form that is unbound and has no locks. I have added the code i have tried so far below. You will see there are two approaches. the first seemed most logical but won't edit it is remmmed out - the second I am closing everything the form the table so that it shouldn't be opened exclusively by anyone. it isn't bound to anything when i try to update it. Sorry if it's confusing. I can try to separate it out if need be. As you can see i have tried all sorts of approaches...

the form that is originally opened during the review is called
AdminOpenAReview - and is locked for editing.
I grab the value of the defect then close it.

The 2nd form for updating is called InterestedGroupsAdmin and is unlocked and at this point completely unbound to anything. I removed all the onload events etc. because the tblUpdateClass is uneditable. But that is neither here nor there as i can't get the table to update... when this runs i get a table can not be updated as it is already opened exclusively...

Thanks so much for your help.

Private Sub UpdateClassRank_Click()
On Error GoTo Err_UpdateClassRank_Click
Dim sRowID As String
Dim stdocname As String
Dim rs As Recordset

stdocname = "InterestedGroupsAdmin"
sRowID = FORMS!AdminOpenAReview.DEFROWID
MsgBox sRowID
DoCmd.Close acForm, "AdminOpenAReview"

'Set rs = CurrentDb.OpenRecordset("select * from tblUpdateClass where tblUpdateClass!DEFROWID='" & sRowID & "';")
'If rs.RecordCount = 0 Then

'rs.AddNew
'rs!DEFROWID = sRowID
'rs!NewRank = sRANK
'rs!NewClass = sCLASS
'rs!Group = sGroup
'rs!Processed = sTrack

'Else
'rs.Edit
'rs!NewRank = sRANK
'rs!NewClass = sCLASS
'rs!Group = sGroup
'rs!Processed = sTrack
'End If

'rs.Update


Set rs = CurrentDb.OpenRecordset("tblUpdateClass")
MsgBox rs.RecordCount
rs.FindFirst "DEFROWID = '" & sRowID & "'"

If rs.NoMatch Then
rs.Close
CurrentDb.Execute "INSERT INTO tblUpdateClass ([DEFROWID],[NEWRANK])" _
 & " VALUES ('" & sRowID & "'," & 0 & ")"

'Me.Refresh
DoCmd.OpenForm stdocname, acNormal, , "defrowid='" & sRowID & "'", acFormEdit
'[FORMS]![InterestedGroupsAdmin]![Rank] = 0
Else

DoCmd.OpenForm stdocname, acNormal, , "defrowid='" & sRowID & "'", acFormEdit
'[FORMS]![InterestedGroupsAdmin]![DEFROWID] = sRowID
'[FORMS]![InterestedGroupsAdmin]![CLASS] = rs!NewClass
'[FORMS]![InterestedGroupsAdmin]![Rank] = rs!NewRank
'[FORMS]![InterestedGroupsAdmin]![Group] = rs!Group
'[FORMS]![interestedgroups]![Processed] = rs!Processed



End If
'DoCmd.OpenForm stDocname, acNormal, , "DEFROWID = '" & sRowID & "'", acFormAdd
'DoCmd.OpenForm stDocname, acNormal, , , , , sRowID



Exit_UpdateClassRank_Click:
    Exit Sub

Err_UpdateClassRank_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_UpdateClassRank_Click

End Sub

 

[GreyHounds]

I will add something else, because it does seem unusual that the table won't allow me to edit when I have closed everything.

In the database I also have a 'dashboard' if you will of the different items that need to be reviewed. It is a series of labels formatted to look like hyperlinks (I know amateur hour but it was easy, quick and worked)

To open the AdminOpenAReview form - you click a text label and the on click event runs the open form cmd. Could this be locking my table even after i have closed the AdminOpenAReview form?

Private Sub AdminOpenAReviewSiebel_Click()
DoCmd.OpenForm "AdminOpenAReview", acFormDS, AdminOpenA, "AREA= 'Magellan'", , acWindowNormal
End Sub

 

[GreyHounds]

Another update. I changed the form to lock on the edited record and it didn't give me an error. so now i can access the table. Will try to untangle the spaghetti to get something cleaner.

Also - i closed the adminMain form from my question above. and found that even with all the forms closed - I couldn't open the table. So something is holding on to it, although when you click on the menu item Window nothing is opened.

 

[GreyHounds]

Okay - last update. I have been able to get it to work without closing the form. I don't know why when I closed the form it didn't free up the table, but I believe that was the issue. I had the records locked on the form - and even though I closed everything it held the table in the backend somewhere.

What is happening now - the AdminOpenAReview form is opened. I click the update button which launches the InterestedGroupsAdmin form which queries the second table for a match. If it finds it it displays the new item. If it doesn't it displays the row_id and the other fields are blank. The person can update those fields and save it.

The only issue i have now is getting the AdminOpenAReview to requery/refresh to show the new values. Requery with the row_id only returns the one record, requey without takes me back to the first record. I am going to look at the Bookmark stuff to get back to the record.

Also will search the forum on how to get to a specific record - I am pretty sure I have seen it before. My first attempt at the go to record method didn't work. And maybe that's my new tagline - My first attempt never works or I am shocked when it works the first time!

Anyway - Thank you very much for your help. I have posted the code I am using below just to bring this full circle

Laura

here is the on open code for the InterestedGroupsAdmin

Private Sub Form_Open(Cancel As Integer)

Dim sRowID As String
sRowID = FORMS!AdminOpenAReview.DEFROWID
  Set rs = CurrentDb.OpenRecordset("select * from tblUpdateClass where tblUpdateClass!DEFROWID='" & sRowID & "';")

If rs.RecordCount > 0 Then
[FORMS]![interestedgroupsadmin]![DEFROWID] = sRowID
[FORMS]![interestedgroupsadmin]![CLASS] = rs!NewClass
[FORMS]![interestedgroupsadmin]![Rank] = rs!NewRank
[FORMS]![interestedgroupsadmin]![Group] = rs!Group
[FORMS]![interestedgroupsadmin]![Processed] = rs!Processed

Else
[FORMS]![interestedgroupsadmin]![DEFROWID] = sRowID
[FORMS]![interestedgroupsadmin]![Rank] = 0
End If

End Sub

here is what is behind the update button which is still a little messy haven't had a chance to redo it yet...

Private Sub UpdateIG_Click()
On Error GoTo Err_UpdateIG_Click
Dim sRowID As String
Dim sRANK As String
Dim sCLASS As String
Dim sProcessed As Boolean
Dim sGroup As String
Dim rs As Recordset

   With FORMS!interestedgroupsadmin
   If Me![DEFROWID] <> "" Then
   sRowID = Me![DEFROWID]
   Else
   sRowID = ""
   End If
   
   If Me!Rank <> 0 Then
   sRANK = Me!Rank
   Else
   sRANK = 0
   End If

    If Me!CLASS <> "" Then
   sCLASS = Me!CLASS
   Else
   sCLASS = ""
   End If
   
   If Me!Processed <> 0 Then
   sTrack = Me!Processed
   Else
   sTrack = 0
   End If
   
   If Me!Group <> "" Then
   sGroup = Me!Group
   Else
   sGroup = ""
   End If
  End With


Set rs = CurrentDb.OpenRecordset("select * from tblUpdateClass where tblUpdateClass!DEFROWID='" & sRowID & "';")
If rs.RecordCount = 0 Then

rs.AddNew
rs!DEFROWID = sRowID
rs!NewRank = sRANK
rs!NewClass = sCLASS
rs!Group = sGroup
rs!Processed = sTrack

Else
rs.Edit
rs!NewRank = sRANK
rs!NewClass = sCLASS
rs!Group = sGroup
rs!Processed = sTrack
End If

rs.Update

DoCmd.Close acForm, "InterestedGroupsAdmin", acSaveYes
DoCmd.Close acTable, "tblUpdateClass", acSaveYes
FORMS!AdminOpenAReview.SetFocus
FORMS!AdminOpenAReview.Requery



Exit_UpdateIG_Click:
    Exit Sub

Err_UpdateIG_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_UpdateIG_Click
    
End Sub