Incrementing Values? (1 Viewer)

[AccessNewb]

I have a Family ID, which is my primary key, and it increments for each new record entered into the database.
I want the records to start with Family ID = 100 and increment by 10 for all records entered after.

example:
Family ID = 100
Family ID = 110
Family ID = 120
Family ID = 130
Family ID = 140
Family ID = 150
Family ID = 160

I have the records starting at 100, but How do i get them to increment by 10?
As of the moment this is what I have:
Family ID = 101
Family ID = 102
Family ID = 103
Family ID = 104

 

[Jack Cowley]

Hmmm. I suppose you have code to increment your ID and it must be something like FamilyID = FamilyID + 1. Just change it to FamilyID = FamilyID + 10. If that is not how you are doing it then post your code and someone here will be able to help you.

hth,
Jack

 

[AccessNewb]

HHMMM, I do not have any code.
I created a table with a Number attribute, created a query, and then appended this table to the table containing the Family ID.

I followed this procedure because my table were already populated. I found this procedure in the Help section of ACCESS 2000. (search Auto increment)

thanks

 

[Jerry Stoner]

Use DMax

Me.YourTXTBOX = DMax("YourTblField", "YourTblName") + 10

 

[AccessNewb]

WHere would I put this?
This would not change the the Family ID in the table, right?
If not then this would occur:
Family ID = 100 TextBox = 110
Family ID = 101 TextBox = 111
Family ID = 102 TextBox = 112
Family ID = 103 TextBox = 113

This is not what I am looking for.
OR AM I JUST MISSING SOMETHING.
I am sleepy.

 

[Jerry Stoner]

OK Assuming you are using a form for data entry and that FamilyID IS NOT an auto number field and your txtBox's control source is FamilyID in the table or query you are using as the record source for the form then you can put the code in the Current event of the form like this:
If Me.NewRecord Then
Me.YourTXTBOX = DMax("YourTblField", "YourTblName") + 10
End If

Or alternatly use a command button new record then use code like:

DoCmd.GoToRecord,,acNewRec
Me.YourTXTBOX = DMax("YourTblField", "YourTblName") + 10


If FamilyID is an auto number then this wont work. What I would do is make a new number field FamilyNum for example and use that as the visible ID, you can keep the auto number (I would) just dont show it.

Hope that helps.
Jerry

 

[AccessNewb]

Thanks for the replies.
I have not made it work yet, but I am still working on it.

The IF Then End If seems to keep things from working. If I take that out of code then I get feedback. It is incorrect feedback, but none the less, feedback.
Is there something wrong with the IF Then code?
I will be looking at VB later today and try to figure it out.

Still working on it but if any more ideas let me know
Thanks

 

[Jack Cowley]

The If Then code posted by Jerry is correct. What error message are you getting? Where are you using the code and post your code as well. Hard to know what is going on without some specific details and what happens when you try to use the code.

hth,
Jack

 

[MikeAngelastro]

Hi,

Why don't you create a SQL statement in your code that will return the last ID in your family table? You can increment it by 10 and then append it with the new record.

Mike

 

[AccessNewb]

OK, I will try to be very detailed.

My original problem was that I needed to know how to increment one of my fields, in a table, by 10 for every record entered.
For example, My table has a primary key called Family ID.
I need for the Family ID to start at 100 (which it currently does) and increment by 10 for every record after.
Family ID = 100
Family ID = 110
Family ID = 120
Family ID = 130

Now what is the easiest way to achieve this?
From the previous posts, I am assuming that there is no way to do this with directly in my table. From what I have read I need to created a text box on my form and manipulate the text box containing the Family ID.

Well, I have created the text box. One problem I have come across is with the IF Then solution that was posted previously.
This is what I have done.
In design view I right clicked on the text box and selected properties. I then clicked the Event Tab and inserted the following code in the "Before Update" portion.
Me.Text66 = (DMax("Family ID", "Family Information") + 10)

Is this correct?
I just do not know where this code goes.
Also, where would the "IF, THEN, END IF" code (from previous post) go?

please try to be detailed and again thanks alot for all your help

 

[MikeAngelastro]

Now I'm assuming that your table contains other info besides the family ID, for example a father and a mother. After the user enters the name of the father and the name of the mother, he clicks a button to store the new record. The following sub will run.

Private Sub Button_Click

Dim FamID as long
Dim strINSERT as string
Dim strSELECT as string
Dim strSQL as string
Dim QUOTES as string

QUOTES = """"

strINSERT = "INSERT INTO tblFamily ( [Family ID], Father, Mother ) "

FamID = (DMax("Family ID", "Family Information") + 10)

strSELECT = "SELECT " & FamID & ", " & QUOTES & txtFather & QUOTES & ", " & QUOTES & txtMother & QUOTES & ";"

strSQL = strINSERT & strSELECT

docmd.SetWarnings False
docmd.runSQL (strSQL)
docmd.SetWarnings True

End Sub

The strSQL variable will = "INSERT INTO tblFamily ( [Family ID], Father, Mother ) SELECT 140, "Frank", "Maria";"

This will automatically store each new record with Family IDs incremented by 10.

Mike

 

[The_Doc_Man]

See my direct reply to RGreene regarding the "What does this mean?" question.