I have an Access database which was built from the Northwind template, which is referenced here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases
I am new to Access and VBA.
The screenshots I show are from the Northwind Access template, not the database I am working on.
From what I can tell, the Home form's New Customer Order button uses an embedded macro with "1=0" (like, FALSE) in the where condition to retrieve zero rows from the Order Details form data and this allows for data entry. You can see in these settings how this is accomplished:
This is the full query used in "Record Source":
SELECT Orders.*, [Orders Status].[Status Name], [Customers Extended].[E-mail Address] FROM [Customers Extended] INNER JOIN ([Orders Status] INNER JOIN Orders ON [Orders Status].[Status ID]=Orders.[Status ID]) ON [Customers Extended].ID=Orders.[Customer ID];
Notice "Data Entry" is set to "No". More commonly, for data entry forms, this would be set to "Yes", but it seems "1=0" in the previous macro works to open a blank form.
If I open the Order Details form without using the New Customer Order button, the form will open fixed on the first order in the table, where I cannot change any of the values. This will also happen if I delete the "Where Condition" or set it to something like "1=1", then click the New Customer Order button.
I can click on the New (blank) record button at the bottom to get a blank form and enter new data.
In the current database, when I click on the New Customer Order button, I will get error 2105 ("You can't go to the specified record."), unless I remove the "1=0" condition. If I click "debug" on the error dialog box, I will be taken into the VBA editor for the Order Details form. Within the SetFormState procedure, the line,
If fChangeFocus Then Me.Customer_ID.SetFocus, is highlighted as the point of failure. (
fChangeFocus is True.) I can troubleshoot several things, but trying to set the focus to the Customer_ID field will always cause a 2105 error if the macro uses the "1=0" condition.
I cannot find a visible difference between the database I am using and the original template. I believe the difference must be in the tables the data is being pulled from.
I would like to find a solution, not necessarily using the "1=0" clause, that will allow the effects of clicking the button to be similar to the template and preserve the general formatting.
The current live database is working as intended, but after copying the tables to a new test database, the error occurs. This is with both computers and users who have tried this, and in different ways: Linking to SQL Server, or simply importing the tables into the test Access database copy. The live database uses links to tables in other Access database files on the server.
I am interested in knowing if others have seen this before and know how to resolve it.
I'll provide extra details if I can, but cannot share the data.
Here is the macro used when setting focus to the Customer_ID field:
I think that the issue on setting the focus is a symptom, rather than a cause; but I could be wrong.
Update: I've realized that the New (blank) record button is grayed out in the working database. This could be an important clue about why opening on a new record isn't working. I've also removed the "1=0" and added the "GoToRecord" action to the New Customer Order "On Click" macro with "Record" set to "New" and other fields blank. This results in the 2105 error, so I think I'm on to something. I have changed the "Data Entry" option of the Order Details form to "Yes".