Adding new entry with specific value

  • Thread starter Thread starter Edward H via AccessMonster.com
  • Start date Start date
E

Edward H via AccessMonster.com

Hi;

I am trying to create an ADD only form that would automatically find the
ID# of the last record add 1 to it and place it in the ID# field. So that
when it adds the record, the ID will add progressively.
I cannot auto number the ID#.

example:

Last ID: 4500

New ID:
 
Sorry I hit the wrong button!

Example;

Last ID: 4500
New ID: 4501 (automatically becomes this number when you hit add new record
button)
 
Do you want to add one to the last one entered or add one to the highest
existing value? For the secone scenario try this.

Create a TOP 1 query that is sorted descending on the ID Ffield. Then as you
start each new record do a DLookup of that query to find the value of the
highest existing record and then add one to that. A query named
"qryID_Top1" might look like this

SELECT TOP 1 IDField FROM TableName
ORDER BY IDField DESC;

Code might look something like this.

Private Sub Form_BeforeInsert
Me.txtIDField = Dlookup("[ID]", "qryID_Top1") + 1
end sub

Assuminf IDField is required to be unique then the query will always return
just one record so you shouldn't need any criteria in the Dlookup. Of course
you might want to do it at a different time other then BeforeInsert. You
will have to judge that yourself.

Good luck,
Tony V
 
Thank you Tony!
YOU ARE DA MAN!!

This worked out great!

Tony said:
Do you want to add one to the last one entered or add one to the highest
existing value? For the secone scenario try this.

Create a TOP 1 query that is sorted descending on the ID Ffield. Then as you
start each new record do a DLookup of that query to find the value of the
highest existing record and then add one to that. A query named
"qryID_Top1" might look like this

SELECT TOP 1 IDField FROM TableName
ORDER BY IDField DESC;

Code might look something like this.

Private Sub Form_BeforeInsert
Me.txtIDField = Dlookup("[ID]", "qryID_Top1") + 1
end sub

Assuminf IDField is required to be unique then the query will always return
just one record so you shouldn't need any criteria in the Dlookup. Of course
you might want to do it at a different time other then BeforeInsert. You
will have to judge that yourself.

Good luck,
Tony V
Hi;
Click to expand...
[quoted text clipped - 8 lines]
New ID:
Click to expand...
Click to expand...
 
Glad to help. Thanks for inflating my ego. Now I will go home so my three
sons can humble me.

Tony


Edward H via AccessMonster.com said:
Thank you Tony!
YOU ARE DA MAN!!

This worked out great!

Tony said:
Do you want to add one to the last one entered or add one to the highest
existing value? For the secone scenario try this.

Create a TOP 1 query that is sorted descending on the ID Ffield. Then as
you
start each new record do a DLookup of that query to find the value of the
highest existing record and then add one to that. A query named
"qryID_Top1" might look like this

SELECT TOP 1 IDField FROM TableName
ORDER BY IDField DESC;

Code might look something like this.

Private Sub Form_BeforeInsert
Me.txtIDField = Dlookup("[ID]", "qryID_Top1") + 1
end sub

Assuminf IDField is required to be unique then the query will always
return
just one record so you shouldn't need any criteria in the Dlookup. Of
course
you might want to do it at a different time other then BeforeInsert. You
will have to judge that yourself.

Good luck,
Tony V
Hi;
Click to expand...
[quoted text clipped - 8 lines]
New ID:
Click to expand...
Click to expand...
Click to expand...
 
One more question!

If I don't hit update, how do I make sure the entry is not added to the
database?
I have it set to On Enter.
 
Do you mean an update button? I don't see in the previous posts about an
update button. It depends on what action is set to occur when the Update
Button is clicked or how the form is setup.

I am surmisng that there is an Update button that saves the record and you
are wondering if the button is not clicked how to prevent the ID from being
saved. By default in access, leaving a form or moving to another record save
the previous one. So this would require some workaround to prevent the
record from saving.

Can you provide more details about the question and the form design.
 
Actually, I think I figured the problem out.
I added the code you provided me in front of the Add New Record button
generated by Access.
Now, the entry is only created when I hit the Add New Record button. Not very
fancy, but it works.

Now I just want to be able to display the value of TOP_1 query as a
reference.
I can't seem to get this thing to spit out 1 stinking value.
 
If you add the code the be BeforeInsert event of the the code will execute
immediately when the users starts typing in a new record. You could just
lock the ID field to revent them from typing in it directly.

How are you trying to display the results of the TOP query? If you don't
mind a message box you could change the code like below. This assumes the ID
is a whole number.

Private Sub Form_BeforeInsert
Dim ID as Long
ID = Dlookup("[ID]", "qryID_Top1")
MsgBox "Previous ID number is " & ID
Me.txtIDField = ID + 1
End Sub

Tony
 
:

Now I just want to be able to display the value of TOP_1 query as a
reference.
I can't seem to get this thing to spit out 1 stinking value.
Click to expand...

Just like before, but don't add one to it.


Me.Somecontrol = Dlookup("[ID]", "qryID_Top1")


You could also use a label (so there is no chance of it being mistaken for
an entry field):

Me.LabelName.Caption = "Last ID = " & Dlookup("[ID]", "qryID_Top1")

(**Change "LabelName" to the name of the label)


HTH
 
Back
Top