"I think the problem is that when you open the form, Access opens the whole table and all the records are available. Am I correct in this assumption?"
My bet is that you are absolutely right.
If you open the property sheet for the form, select the "Data" tab and look at the value "Record Source", do you see the name of one of your tables?
Or a query without a WHERE clause, perhaps like "SELECT * FROM tblInvoice;"?
If your answer is yes to any of these questions, Access will have to pull each record from the table through the wire.
So don't do this ! :-) The key to decent performance is limiting the Record Source form to a reasonable subset of strings.
Choose some criterion that makes sense in your situation, perhaps the invoice date and build a query that includes this criterion in your WHERE clause. Basic criteria in an indexed field --- add an index if your table does not already have one in this field. You can experiment by creating a new query in the query designer ... maybe SQL View will look like this:
SELECT Invoice_ID, Customer_ID, invoice_date FROM tblInvoice WHERE invoice_date = Date();
This will only retrieve rows where invoice_date matches today's date.
(If you have already loaded the form by pulling all the lines to the local cache, you will not get a true indication of the speed of this request. It is better to check the request from a new access session without first loading the form.)
So, give the user a method to choose a different invoice. For example, a text box control named txtSelectDate. And in the After Update event of this control, you can write an updated SELECT statement that you use as the Record Source form.
Private Sub txtSelectDate_AfterUpdate() Dim strSql As String If Len(Me.txtSelectDate & "") > 0 Then strSql = "SELECT Invoice_ID, Customer_ID, invoice_date" & vbCrLf & _ "FROM tblInvoice" & vbCrLf & _ "WHERE invoice_date = " & Format(Me.txtSelectDate, "\#yyyy-md\#") Debug.Print strSql Me.RecordSource = strSql End If End Sub
The Debug.Print line will print the SELECT statement in the Immediate window, so if something goes wrong, you can view the completed statement and copy and paste it into SQL View for a new query for testing. Changing the Record Source property automatically results in access to the data source. And you can check the user entry in the "Before upgrade" text box to make sure that this is a valid date.
Another possibility is to have the first form load with a single non-editable dummy record ( 1 ). Then display the records only after the user selects the invoice. For this, you can use something like this as a Record Source record:
SELECT TOP 1 Null AS Invoice_ID, Null AS Customer_ID, Null AS invoice_date FROM tblInvoice;