[Last Call] Learn how to a build a cloud-first strategyRegister Now


Increment field in form not working

Posted on 2012-08-12
Medium Priority
Last Modified: 2012-08-17
I'm trying to get a form to increment a field when someone enters a new record for a property. So for below, on the last one I want the code to fill in "3" for the UnitID when a person enters that record.

Ppty_ID      UnitID
1                      1
1                       2

I tried using the code suggested in an earlier answer about how to do this on the "BeforeInsert".  But it keeps bombing.  I know it's something simple, but I'm not seeing the problem.

Here's the code I used:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!UnitId = Nz(DMax("[UnitID]", "tblVacyUnits", "[UnitID]=" & Me!UnitId), 0) + 1
Me.Dirty = False
End Sub

Got the error: Syntax error (missing operator in query exression "[UnitID]="
when I do the above.

Please let me know what I'm doing wrong.
Thanks,  Patty
Question by:Patty01Access
  • 5
  • 2
LVL 77

Expert Comment

ID: 38285498
You should be looking for the propertyid.

Me!UnitId = Nz(DMax("[UnitID]", "tblVacyUnits", "[ppty_ID]=" & Me!ppty_Id), 0) + 1

Author Comment

ID: 38285516
Thanks Peter.  

But I get the exact same error when trying to do that, except it says "Ppty_ID" vrs UnitID
LVL 77

Expert Comment

ID: 38285523
Where exactly is propertyid coming from?  I don't quite see how it is available to be used in the beforeinsert event.

You can't look up the max UnitId until the propertyid has been entered.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 38285546
Color me stupid on this one Peter.

I don't know how, but prior to entering that code, when I would add a record, the ppty_ID from the Property form would automatically be inserted into the tblVacyUnits along with the other info.

Do I need to go about this another way?

Author Comment

ID: 38285566
Sorry, I screwed up the last attachment. This one is hopefully correct.

I see that I should have had the Ppty_ID in the code.

I've revised it to show that, and it's in the "Before Insert" in the subfrmVacyUnits.

The main form for inputting the data is the frmProperty.

Now when I go to add a new record, same error.

So should the code go somewhere else?

Thanks again.

Accepted Solution

Patty01Access earned 0 total points
ID: 38285765
Finally got it.  It was in the wrong event place on the subform.

I removed it from "Before Insert"  and put it in "Before Update"

If IsNull(Me.UnitId) Then
    Me!UnitId = Nz(DMax("[UnitID]", "[tblVacyUnits]", "[Ppty_ID]=" & Me!PPTY_ID), 0) + 1
    Cancel = True
End If

Works fine now.

Author Closing Comment

ID: 38304074
I didn't understand what Peter was saying about the Ppty_Id, so kept trying to put the code in different places until it finally worked.

This may not be the most elegant solution, but it worked for me.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question