Solved Add Record VBA Not working

E9-Tech

Member
Local time
Today, 12:24
Joined
Apr 28, 2021
Messages
46
I have various forms displaying a list of records the record sourse is set to a query. I have a Add Record button to add a new record and these has been working perfectly fine on all various forms I have apart from one form that I cannot figure out why when launching the Add button it will open the form on the very first record and not a new record.

Code:
Private Sub CmdAdd_Click()
    DoCmd.OpenForm "frmSalary", acNormal, "", "", acAdd
    Forms!frmSalary!CmdEdit.Visible = False
    Forms!frmSalary!DateValid.SetFocus
End Sub

This is the code I have always been using in all forms and I change the name of the form, in this case "frm Salary", and the SetFocus for the relevant field in the form.

Any thoughts of why it is not working on this form and what I should be looking at?

Thanks
 
1. Does the query used itself allow records to be added?
2. If frmsalary is open on the first record, can you simply go to the new record?
3. The docmd is used to open the form frmsalary and move to the new record. Is it executed from a different form, with the intent it is only used to add new records? however you arebgoing to the fitrst record - perhaps because ...
4. acAdd does not appear to be the correct enumeration:
OpenForm (FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs).
DataMode:
acFormAdd0The user can add new records but can't edit existing records.
 
I changed the code to
Code:
Private Sub CmdAdd_Click()
    DoCmd.OpenForm "frmSalary", acNormal, , , acFormAdd
End Sub

Still no luck

The query is
Code:
SELECT
    tblSalary.IDSalary,
    tblSalary.DateValid,
    tblSalary.SalaryAnnual,
    tblSalary.SalaryBasic,
    tblSalary.[EmployerPensionContributions%],
    tblSalary.[EmployeePensionContributions%],
    tblSalary.EmployerPensionContributions,
    tblSalary.EmployeePensionContributions,
    tblSalary.TotPensionContributions,
    tblSalary.TaxCode,
    tblSalary.TBF,
    tblSalary.OvertimePay,
    tblSalary.BankHolidayPay,
    (
        SELECT
            TOP 1 A.SalaryAnnual
        FROM
            tblSalary AS A
        WHERE
            A.IDSalary < tblSalary.IDSalary
        ORDER BY
            A.IDSalary DESC
    ) AS PreviousValue,
    [SalaryAnnual] - [previousvalue] AS Difference,
    [difference] / [previousvalue] AS PercentChange
FROM
    tblSalary;

Yes the Add button is on another form which displays the list of all records with record set the query.

Yes the form is only to be used to add a record, or edit it when the Edit button is selected
 
When in doubt, check the syntax
You posted #4 just as I posted this.

What code is in frmSalary?

Upload enough of the db to see the issue. Can you add a record in the query directly?
 
And in regards to points 1 and 2?
Do you have the form properties for allow additions set? or for Data Entry to Yes?
 
4. acAdd does not appear to be the correct enumeration:
OpenForm (FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs).
DataMode:

acFormAdd0The user can add new records but can't edit existing records.
Strangely enough that still works though?
 
Ive always assumed by using acAdd you were temporarily assigning DataEntry properties to the form.

Question for the OP, in the form's properties, is Additions set to True?
 
If the query for your form is not updateable, it won't allow you to add a new record, or edit an existing one.
Is this a new/modified form?
 
I would try remove this section of the query
SQL:
  (
        SELECT
            TOP 1 A.SalaryAnnual
        FROM
            tblSalary AS A
        WHERE
            A.IDSalary < tblSalary.IDSalary
        ORDER BY
            A.IDSalary DESC
    ) AS PreviousValue,
    [SalaryAnnual] - [previousvalue] AS Difference,
    [difference] / [previousvalue] AS PercentChange
 
The "TOP 1" clause makes the query not updatable. You can also see this when pasting the entire statement into a new query in SQL view, and running it. The * line for new record will not be there.
 
The "TOP 1" clause makes the query not updatable. You can also see this when pasting the entire statement into a new query in SQL view, and running it. The * line for new record will not be there.
Could that be a seperate query and be joined by IDSalary ?
 
Could that be a seperate query and be joined by IDSalary ?
That won't help.
What would help is to replace the subquery with a call to DMax or other domain aggregate functions. Those keep the query updatable.
 
Last edited:
I expect the subquery with the TOP 1 could be replaced by DMax(). If there are a large number of records, this would be very inefficient.
 

Users who are viewing this thread

Back
Top Bottom