Primary Key/AutoNumbering Problem

[Graybeard]

I have a field named ClientID as my Primary Key. It is an AutoNumber Field. I reset Access's Autonumber by creating a table named number. It has one field and one record- a Number field with the data entry 200401. I then created an append query named numberquery based on this table and appended the number to my clientid Primary Key in my clientInfo Table. This caused my clientID numbers to begin with the number 200401. It works great but I have a problem with my form for this table. When I open the form(I use docmd.openform "frmclientinfo" ,,,,formadd) the form will not automatically add the new number in the clientid field until I tab to another field and enter data in that field. It doesnt matter which field but until data is entered in another field the form will not add the new record number in the clientid field. How do I fix it so when the new record opens the new record number is added first? (Its not the Tab order. I checked.)

 

[WayneRyan]

Gray,

You really shouldn't care what your AutoNumber field is. In some sense,
you shouldn't even display it. It is just a unique identifier for each record.

When you are adding a record, Access does not acknowledge the new
record until at least one field is typed in. That's when you see the new
Autonumber.

If the user fills in one field, gets the AutoNumber, then deletes the new
record, you will "lose" the AutoNumber! There will be gaps in your
numbering sequence.

If you need different behaviour on entry (and also, no gaps), then you
will have to provide the numbers yourself. You can use the Search
facility here and look for "DMAX". That is normally the function used to
generate the new numbers.

Wayne

 

[Graybeard]

Wayne,
Ordinarily I would agree with you. The problem is the clientid number is not simply a unique number to prevent duplicate records. It is used for physical purposes independent of the db. The db is for a lawoffice. The clientid number is put on every physical client file. It is used to track copies made by the copier, long distance calls, postAge, etc. for billing purposes.
Thats why it is displayed and that is why I reset it to begin with 200401.
Gaps are not a problem because there are going to be gaps anyway. When the clients legal matter is concluded their record will be purged leaving a gap. I'm more interested in having the user see the the clientid number assigned to the new client when the newform first opens but it sounds like it can't be done. I really didn't want to but I guess I will have to make my first field the client's name then go to the client id field. When the name is typed in this will bring up the clientid.
Thanks for the explanation. You kept me from wasting a lot of time trying to figure out how to do something that can't be done.

 

[RallyXS]

Not sure if this will work, just an idea. How about on the formopen action, assigning a value to one of your fields and then removing that value, ie making that field null again. This should get you the autonumber.

 

[Graybeard]

Great suggestion, Rally. It partially works. I set my FName to populate when the form is opened. This gives me the my ClientID as you thought. However, I can not get the FName field to reset to null. I suspect this is because I am fairly new to Access and am not using the right code. Here is the code I am code I am using. This code is run off a button on my switchboard. The explanations in brackets are not in the actual code but added here to help explain.

Private Sub Label0_Click()
DoCmd.Close {this closes the switchboard}

DoCmd.OpenForm "frmClientInfo", , , , acFormAdd

Forms!frmClientInfo!Combo146.Visible = False {I have a combobox for when the form is opened in opened in edit mode which allows the user to select a specific record to edit. When the form is opened in addrecord mode the combobox is not visible}

Forms!frmClientInfo!FName = "John"

Forms!frmClientInfo!FName = "" {This is the line of code I am using to try and reset the FName field to null but it is not working. It leaves the field at the entry set by the previous line,"John"}

End Sub

Can anyone help with this line of code?

 

[Mile-O]

DoCmd.Close {this closes the switchboard}

Take more care with your code.

DoCmd.Close acForm, "MySwitchboard"

 

[Graybeard]

SJ,Thank You for your help. I think I did not make my problem clear. The line is not the the problem. The first 2 lines were given to me by Miles and works fine. Its the last line thatis my problem. I am trying to reset the fname field to its null state. All the other lines of the code work fine. The next to last line enters the value John in the field "FName"when the form opens. The last line then is suppose to reset the value back to null but it is not working. Can you tell me what is the correct code to reset the value of this field back to null?

 

[Mile-O]

The line is not the the problem.

I know. I'm just ensuring that line is more explicit.

The first 2 lines were given to me by Miles and works fine.

Mile-O would have given the exact same line as I did - we're the same person, you see.

Its the last line that is my problem. I am trying to reset the fname field to its null state.

Do you have the field's Allow Zero Length Values property set to Yes or No in the table design?

 

[Graybeard]

You are a genius. I checked my table and changed the setting to allow and it works perfectfectly now. Thank you. And I apologize if I sounded rude with my response re your suggestion on the first line of my code. I certainly didn't intend to be if it came across that way. Thank you again.

 

[Graybeard]

I do have one more queston. Where and how do I comment my code like I did in my post above so others can follow it in the future not to mention me understanding what I did a month from now.

 

[Mile-O]

Just put a ' after a line or on a line of it's own followed by your comment.

Try to comment everything in the development version of your database.



An example:

Private Function GetQuarter(ByVal dte As Date) As String

    ' Function: Get Quarter
    ' Scope: Local
    ' Return Value: String
    ' Passed Parameter(s):
    '           dte (Date) - for evaluating its month and year
    ' Author: SJ McAbney
    ' DateCreated: 12-Aug-2004
    ' Amendments:
    '           (None)
    ' Purpose:  To determine which quarter a date falls into.
    '           While it is possible, in normal circumstances,
    '           to say that Quarter 1 consists of the first three
    '           months of the year, Quarter 1 in this situation
    '           contains December of the previous year, and January
    '           and February of the current year.
    '           It is therefore important to increment the year of
    '           the date should the month be a December for the
    '           purposes of returning the correct Quarter for this
    '           situation. The year is then appended to the quarter
    '           for use within a search.
    ' Example:
    '           In: GetQuarter(#31/12/2004#)
    '           Out: Q1-2005
    
    On Error GoTo Err_GetQuarter
    
    Dim bytMonth As Byte ' month range (1 - 12)
    Dim intYear As Integer ' year
    
    bytMonth = Month(dte) ' get the month from the date passed
    intYear = Year(dte) ' get the year from the date passed
    
    ' If the month is December then it falls into Quarter 1 of
    ' the following year. The year should be incremented by 1.
    If bytMonth = 12 Then intYear = intYear + 1
    
    ' Determine which quarter the month falls into and append the year
    Select Case bytMonth
        Case Is = 12, 1, 2 ' Dec, Jan, Feb - Quarter 1
            GetQuarter = "Q1-" & intYear
        Case Is = 3, 4, 5 ' Mar, Apr, May - Quarter 2
            GetQuarter = "Q2-" & intYear
        Case Is = 6, 7, 8 ' Jun, Jul, Aug - Quarter 3
            GetQuarter = "Q3-" & intYear
        Case Is = 9, 10, 11 ' Sep, Oct, Nov - Quarter 4
            GetQuarter = "Q4-" & intYear
    End Select
    
    Exit Function ' value has been found
    
Err_GetQuarter:
    GetQuarter = vbNullString ' Unknown quarter
    
End Function

 

[Graybeard]

Thank you. I will