Form loading incorrectly

[stevenblanc]

Hi folks,

So I solved an earlier problem loading a specific record and now found myself with a new issue. While my code opens the correct form on the correct record I find the form opens without properly drawing boxes, lines, and the record navigation pane.

If I move drag the form off the screen and back on, the portion of the form moved off the screen comes back properly drawn. The form source is a query which is recreated just before the form is opened.

I already tried adding after the requery to no avail:

DoCmd.RepaintObject acForm "frmMachines"

The code:

Private Sub lstMachines_DblClick(Cancel As Integer)
    If ObjectExists("Query", "qryCustomerMachines") Then
        DoCmd.DeleteObject acQuery, "qryCustomerMachines"
    End If
    
    CurrentDb.CreateQueryDef "qryCustomerMachines", "SELECT * FROM tblMachines WHERE CUST_ID = Forms!frmCustomerRecord.CUST_ID"

    DoCmd.OpenForm "frmMachines", acNormal
    DoCmd.Requery
    
    With Forms!frmMachines.Recordset
        .FindFirst "MACH_ID = " & lstMachines.Column(0)
        If .NoMatch Then MsgBox "The selected machine does not exist in the system."
    End With

End Sub

Any ideas?

 

[stevenblanc]

1) I suppose I could bind the form to the query, but now that you have me thinking, I could really just bind it to the table and open form with the CUST_ID as the criteria and get rid of the query altogether.

2) My intention in using .FindFirst as opposed to MACH_ID as criteria in this case is to allow the form access to all the records for quick navigation. If I were to use MACH_ID in the criteria the form would only have access to the specific record.

Does this make sense?

UPDATE: I have adjusted the code as mentioned and it solved the problem. I'd appreciate it if you have additional advice on stream lining it:

Private Sub lstMachines_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmMachines", acNormal, , "CUST_ID = Forms!frmcustomerrecord.CUST_ID
        
    With Forms!frmMachines.Recordset
        .FindFirst "MACH_ID = " & lstMachines.Column(0)
        If .NoMatch Then MsgBox "The selected machine does not exist in the system."
    End With
End Sub

 

[stevenblanc]

1) The system is designed primarily to track service history, parts and alternate manufacturer part numbers, and notes on customer machines. I can see the need for queries when it comes to running summary reports, however, for viewing or printing individual records there will be no filtering other than single "where" clauses to isolate the customer or machine ID. Creating queries to handle these simple filters would cause more clutter than its worth in my opinion.

Do you expect to see significant resource saving by using queries to handle single where filters?

2) In 90 percent of cases the number of an individual customer's machines will not cross 5. In 100 percent of cases this number will never cross 100.

The form this code exists displays the customer record with the relative list of machines. With the where clause, the number of displayed records will never exceed 100. As it is, frmMachines displays only one record at a time. I've left the navigation controls at the bottom so that the user can scroll through the 2-4 records on average per customer.

This should be more than manageable.