UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Another Subform Requery That Isn't Working, Access 2016    
 
   
JayM
post Jun 2 2020, 10:06 PM
Post#1



Posts: 10
Joined: 2-June 20



I have a database that contains systems (finished products). Systems have Components. Components may have one or more Machines that are used in the manufacturing process. Machines have an order of operation that varies by Component. Machines have Operations that depend on the Component.

I developed an Access form to input/edit Components and the Machines used. The form requests a System selection from a list box, and then displays the Components data form (in Form view). If the Component has Machines assigned, it displays them in a read-only list in a subform. There is an Edit/Add button that pops-up a form to edit or add Machines and specify their order. The main form has the Systems table as its Recordsource. The subform is unbound. Its listbox is bound to a view that Joins fields from three tables.

The form and subform work correctly for the first component of the system. When I move to the next component record, the subform doesn't update correctly. When I check the view, it has the correct component-machine records. If I exit the form and re-open it, the previous record's component-machine records appear in the subform list (after debugging, that section will be initially hidden, until the user selects a system to work on). The suspect code is below.

I must admit that I'm an old-fashioned programmer, who is very out of practice. The last time I did database programming was with PHP and mySQL, where I had to brute force things that Access does automatically. Needless to say, Access has stumped me more than once, but this one has really gotten me. I've tried many of the solutions I found for this problem on this forum and others, and none have worked for me. Sorry for the long write-up, but I thought it would be helpful. I'm still not comfortable with the differences between ! and . notations, so I've tried both for the Requery (shown as comments). The Requery does nothing, and none of the forms shown throw an error.

Code:

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("viewCompMachineOrder")

strWhereClause = "WHERE Components.[System ID] = " & varSystemID & " "

If varComponentID <> "" Then
strWhereClause = strWhereClause & " AND Components.[Component ID] = " & varComponentID

strSQL = "SELECT [Comp-MachineOrder].CompMachID, [Comp-MachineOrder].[System ID] As SystemID, [Comp-MachineOrder].[Component ID] As ComponentID, " _
& "[Comp-MachineOrder].[Machine ID] As MachineID, Machines.MachineNumber AS [Machine Number], " _
& "Machines.MachineDescription AS Description, [Comp-MachineOrder].MachineOrder AS [Machine Order] " _
& "FROM Components INNER JOIN (Machines INNER JOIN [Comp-MachineOrder] ON Machines.[Machine ID] = [Comp-MachineOrder].[Machine ID]) " _
& "ON (Components.[Component ID] = [Comp-MachineOrder].[Component ID]) AND (Components.[Component ID] = [Comp-MachineOrder].[Component ID] AND " _
& "Components.[System ID] = [Comp-MachineOrder].[System ID]) " _
& strWhereClause & " " _
& "ORDER BY [Comp-MachineOrder].MachineOrder;"

qdf.SQL = strSQL

Me![frmCompMachineOrder Subform].Form.Controls("lstCompMachOrder").Requery
' Me.[frmCompMachineOrder Subform].Form!lstCompMachOrder.Requery
' Me![frmCompMachineOrder Subform].Form.Controls("lstCompMachOrder").Requery

.....

End If
Go to the top of the page
 
June7
post Jun 3 2020, 01:32 AM
Post#2



Posts: 1,547
Joined: 25-January 16
From: The Great Land


Not sure what you mean by "subform doesn't update correctly". You follow that statement with "it has the correct component-machine records". So what exactly is issue?

Do you need to requery a form or a listbox?

If you want to provide db for analysis, follow instructions at bottom of my post.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
projecttoday
post Jun 3 2020, 05:25 AM
Post#3


UtterAccess VIP
Posts: 12,416
Joined: 10-February 04
From: South Charleston, WV


Is there a listbox whose record source is viewCompMachineOrder?
Have you stepped through this code?

--------------------
Robert Crouser
Go to the top of the page
 
JayM
post Jun 3 2020, 11:41 AM
Post#4



Posts: 10
Joined: 2-June 20



When I MoveNext to a new component, the previous component's records remain in the subform's listbox. I then opened the view (double-clicked in the list of queries), and it shows the correct records in a list form.

I assumed that I only needed to requery the listbox, but I've tried just the subform, and both the subform and listbox.

I do have some client confidential information in the db along with my test data, so it isn't appropriate for me to upload it. If necessary, I can delete the confidential info, but I'd like to see if I can get the necessary assistance without doing that.

Thanks for your request for clarification.

Jay
Go to the top of the page
 
JayM
post Jun 3 2020, 11:42 AM
Post#5



Posts: 10
Joined: 2-June 20



Yes to both questions, Projecttoday.
Go to the top of the page
 
June7
post Jun 3 2020, 12:03 PM
Post#6



Posts: 1,547
Joined: 25-January 16
From: The Great Land


What event is code in? Perhaps you need to run in OnCurrent event.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
projecttoday
post Jun 3 2020, 12:08 PM
Post#7


UtterAccess VIP
Posts: 12,416
Joined: 10-February 04
From: South Charleston, WV


Have you confirmed that viewCompMachineOrder gets updated when you run this code? If so, then
CODE
Me.[frmCompMachineOrder Subform].Form.lstCompMachOrder.Requery

looks correct to me if frmCompMachineOrder Subform is a subform on the current form. (I changed ! to .). If you use a datasheet or a continuous form you can filter it instead of replacing the entire recordsource.


--------------------
Robert Crouser
Go to the top of the page
 
JayM
post Jun 4 2020, 12:21 PM
Post#8



Posts: 10
Joined: 2-June 20



Yes, I've confirmed that vieCompMachineOrder gets updated, and that is the code I started with. I tried it again, and I still have the same problem. The new view record(s) appear on the next displayed component. I haven't tried using either a datasheet or continuous form. I'll try that next.
Go to the top of the page
 
JayM
post Jun 4 2020, 12:25 PM
Post#9



Posts: 10
Joined: 2-June 20



The code is shared for several events: OnLoad, MoveNext (an OnClick event), MovePrevious (an OnClick event), and Exit button (OnClick). I used it for OnCurrent, but I had trouble with when OnCurrent got executed, so I stopped using that event. Perhaps I need to simplify my other events and go back to OnCurrent. I'll look at that.
Go to the top of the page
 
projecttoday
post Jun 4 2020, 02:41 PM
Post#10


UtterAccess VIP
Posts: 12,416
Joined: 10-February 04
From: South Charleston, WV


You might post it here.

--------------------
Robert Crouser
Go to the top of the page
 
JayM
post Jun 5 2020, 04:35 PM
Post#11



Posts: 10
Joined: 2-June 20



Well, I'm no longer sure why I thought I had to use a subform. I put the listbox and button from the subform on the main form, and Me.lstCompMachOrder.Requery works. Now, I can move forward and back through the components without issue until I do an AddNew. Then my previous button doesn't work (I'm using custom Next, Previous, and Exit buttons rather than the standard navigation buttons). I can't get it to bring up the last component.

I think the non-standard thing I'm doing is using two different queries and a view to fill fields on one form.

Go to the top of the page
 
projecttoday
post Jun 5 2020, 04:47 PM
Post#12


UtterAccess VIP
Posts: 12,416
Joined: 10-February 04
From: South Charleston, WV


What's a view?
Type in the table layouts and explain what you want to do.

--------------------
Robert Crouser
Go to the top of the page
 
June7
post Jun 5 2020, 05:31 PM
Post#13



Posts: 1,547
Joined: 25-January 16
From: The Great Land


How do you do AddNew?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
JayM
post Jun 8 2020, 09:03 AM
Post#14



Posts: 10
Joined: 2-June 20



I've attached the table definitions in a Word document, and I've included a relationships picture as an attachment. The qry versions in the relationships diagram are used to output structured XML, which is then used with an XSLT to create an XML version that is input to a cool formatting program I found (deserved kudo to MiramoXML), which outputs reports in PDF format. The reports are totally data-driven from the database to show entire systems or individual components with their machines and operations listed in order. I created the database specifically to generate these reports, and I entered the initial data directly into underlying tables. I'm currently working on the data entry application to let the manufacturing engineers enter data using forms without worrying about the underlying database structure.

When a user clicks the Next button to display the next component (OnClick event), I execute the following code:

CODE
    Me!PreviousButton.Visible = True
    On Error GoTo MoveNextError
    CheckForUpdate                                     ' a function
    Me.Recordset.MoveNext

    If Me.Recordset.EOF Then
        boolEOF = True
        Me.ExitButton.SetFocus
        Me!NextButton.Visible = False
        On Error Resume Next
        Me.Recordset.AddNew
        varComponentID = ""
        Fill_Comp_MachineOrder                      ' a function that fills a read-only list of machines used by the component in machine order
        Me.EditListButton.Enabled = False         ' disable the Edit button until a new component is created
        Me.FinishedPartNo.Value = "(New)"      ' the first field that the user would enter
        Me.FinishedPartNo.SetFocus
        GoTo GoToNext_Click_Exit
    Else
        Me!NextButton.Visible = True
        varComponentID = Me.Recordset.[Component ID].Value
        Fill_Comp_MachineOrder
    End If

A view is simply a query. In this case the view includes multiple joins. After my last post on June 5, I realized that I could drive the entire form with the one query simply by including more fields in the select. Here is the current "view" query:

SELECT [Comp-MachineOrder].CompMachID, Components.[System ID] AS CompSysID, [Comp-MachineOrder].[System ID] AS SystemID, Components.[Component ID] AS ComponentID, [Comp-MachineOrder].[Component ID] AS CompID, Components.FinishedPartNo, Components.CompRev, Components.RawPartNo, Components.CompPicture, [Comp-MachineOrder].[Machine ID] AS MachineID, Machines.MachineNumber AS [Machine Number], Machines.MachineDescription AS Description, [Comp-MachineOrder].MachineOrder AS [Machine Order]

FROM Components INNER JOIN (Machines INNER JOIN [Comp-MachineOrder] ON Machines.[Machine ID] = [Comp-MachineOrder].[Machine ID]) ON (Components.[Component ID] = [Comp-MachineOrder].[Component ID] AND Components.[System ID] = [Comp-MachineOrder].[System ID]) AND (Components.[Component ID] = [Comp-MachineOrder].[Component ID])

ORDER BY [Comp-MachineOrder].MachineOrder;


I use a different Order By for the initial list box from which the user selects the desired system. I programmatically change the Where clause to populate the form for the component and the list box for the machines.

I'm thinking I'll be able to get this to work, but no joy so far.




Attached File(s)
Attached File  DatabaseDesign.pdf ( 199.77K )Number of downloads: 2
 
Go to the top of the page
 
projecttoday
post Jun 8 2020, 09:25 AM
Post#15


UtterAccess VIP
Posts: 12,416
Joined: 10-February 04
From: South Charleston, WV


Hmmm. That Me.Recordset.EOF code get actually gets triggered? What does it do?
So it works except when you try to go back to the previous record? What happens then? Can you post it?

--------------------
Robert Crouser
Go to the top of the page
 
JayM
post Jun 8 2020, 11:26 AM
Post#16



Posts: 10
Joined: 2-June 20



I was wrong about using one query to drive the entire form. I've gone back to my previous design. by the way, I don't expect someone to click the previous button after seeing the (New) component form, but this is all about bulletproofing my code.

In the attached images:
  1. ToolBookComponentInputOnLoad.JPG shows the form when first loaded
  2. ToolBookComponentInputFirstRec.JPG shows the form after a System was selected.
  3. ToolBookComponentInput2ndRec.JPG After Next button clicked. (This is the last component for this system, and it doesn't have any machines defined yet.)
  4. ToolBookComponentInputNewRec.JPG After Next button clicked again, this shows the form ready for a new component input
  5. AfterPreviousButtonClicked.JPG shows the form after the previous button clicked from (New). The list of machines is from the first component not the second one.


Note: Fields labeled System ID, Components.Sys ID, Component ID, and Machines are for debugging purposes only
Attached File(s)
Attached File  ToolBookComponentInputOnLoad.JPG ( 30.21K )Number of downloads: 3
Attached File  ToolBookComponentInputFirstRec.JPG ( 78.38K )Number of downloads: 3
Attached File  ToolBookComponentInput2ndRec.JPG ( 75.37K )Number of downloads: 2
Attached File  ToolBookComponentInputNewRec.JPG ( 72.11K )Number of downloads: 2
Attached File  AfterPreviousButtonClicked.JPG ( 76.79K )Number of downloads: 2
 
Go to the top of the page
 
projecttoday
post Jun 8 2020, 11:47 AM
Post#17


UtterAccess VIP
Posts: 12,416
Joined: 10-February 04
From: South Charleston, WV


They all show HD01. What do you mean it changes back to the first component? In any case when you navigate through a form if the records were entered correctly and their relationships are intact then each record should show the same every time you pass through it. And in the same order. These are all new records, right? An OrderBy property is a good idea.
I think if they click Next when you're already on a new record then they should receive an error message.

--------------------
Robert Crouser
Go to the top of the page
 
JayM
post Jun 8 2020, 12:37 PM
Post#18



Posts: 10
Joined: 2-June 20



Sorry, I should have explained what I was showing more clearly. After the initial load screen, here is what is happening:
  1. The user (me) select the HD01 system.
  2. The form displays the first component of that system sorted by Finished Part No. (HD01-0003). Its component ID is 13. (The Previous button is not visible because there is no previous record.)
  3. The user clicks the Next component button. The next component, in Finished Part No order (HD01-0004), is Component ID 12.
  4. The user clicks the Next button again. An empty form displays, in which I programmatically add the "(New)" to reinforce that the user must enter data. (The Next button is not visible because there is no next record.)
  5. The user click Previous (who knows why. Perhaps because they were just browsing.
  6. That's when the wrong component's machines appear in the Machines list, and the component data isn't refreshed. (Notice that the Component ID shows 13.)
  7. Note: If I click Previous again, the BOF message appears, and the record doesn't change. Moving forward and backward though components works fine as long as I don't get the the newly added record.


I've tried this with Access' navigation buttons instead of my programmed buttons, and the component navigation works fine, but the machine list isn't getting updated. Perhaps I should work on that, forget my navigation buttons, and see if I can get the machine list updated correctly with an OnCurrent event (I had tried that before and ran into weird issues when I was using a subform for the list).
Go to the top of the page
 
projecttoday
post Jun 8 2020, 01:01 PM
Post#19


UtterAccess VIP
Posts: 12,416
Joined: 10-February 04
From: South Charleston, WV


Have you checked the table? Was the table updated correctly? Take a look at the underlying data. You're saying that when you back up 1 record, you're hitting the wrong record? Or is the value in the record wrong? You can show the record id for this (for debugging purposes, as before.)
I agree that you should use the built-in navigation if possible.
You're doing a process where you're creating new records buy pulling some data in from existing records. Can you make a form that does this without any referencing any componets table? It should navigate smoothly if you do it right, it seems.

--------------------
Robert Crouser
Go to the top of the page
 
JayM
post Jun 8 2020, 05:10 PM
Post#20



Posts: 10
Joined: 2-June 20



The underlying table gets updated correctly when I modify component data. When I back up from the added record, I get the first of the three current records rather than the third. Sure, we can create data directly in tables. Relationships nest related table rows, but that mean the user needs to know related data simply by knowing a record ID. For example, if I want to enter the Machine used in a Component, I'd need to know the Machine ID, which is some arbitrary number rather than a Machine Number or Machine Description. I'm still hoping to create a system that makes it easier for users to add and update data.
Attached File(s)
Attached File  StructuredTableData.JPG ( 110.43K )Number of downloads: 1
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2020 - 07:52 AM