Auto populate increment value in textbox. (1 Viewer)

[mCool]

Hi guys

Just a quick questions guys, I want to populate a textbox (txtA) in a subform with an increment of numbers starting from "1".

2 concerns; I don't want txtA to be filled in the unless other textboxes are filled in (can I make the code sorta like a dependant to these textboxes ?). Second concern, I have existing data on the table that is link to the same subform.

So the baseline here is if there is no prior record under the same ID, txtA would get a "1" in it else if there is a prior record (say "2"), txtA would have to be 3 and so on.

I apologise firsthand for being such a n00b on Access. I have no prior knowledge of coding VB. I would really appreciate any help/hints given.

mCool

 

[vbaInet]

Some ways have been discussed in these threads:

http://www.access-programmers.co.uk/forums/showthread.php?t=91574&highlight=sequential+numbering

http://www.access-programmers.co.uk/forums/showthread.php?t=190485&highlight=sequential+numbering

Take note of the caveats.

 

[mCool]

Hello vbaInet

Firstly thanks for posting the links ! I am learning from the links...

Secondly I apologise if I come across as a kid who needs his hands held all the time but I am trying my best to understand this.

I have incorporated the code on the first link which looks like it would work but I do not understand a little bit of the code in general. Would you mind explaining those bits ?

Private Sub AppointmentID_BeforeUpdate(Cancel As Integer)
    Me.NoAppointment = Nz(DMax("[Sequence]", _
        "Appointments2010/2011", _
        "[StudentID] = " & Me.StudentID), 0) + 1
End Sub

I know the Nz function returns a value when variant is null and Dmax returns a maximum value. What I do not understand is the part inside. I am using StudentID as a query on the Appointment table. I am pretty sure I have done something wrong somewhere because the code does not work in my form.

I would really appreciate any helps/hints !

mCool

 

[vbaInet]

I would advise you explore all the approaches and reading further down the threads.

 

[mCool]

Hi again

I manage to get the auto number working with a query built. Thanks for the guidance !

But here is the second part of my nutty idea. The subform that the auto-filled text box resides in is linked to a main form which basically splits the data appearing in the subform according to the StudentID.

To see the picture I have to personally mail it to you because this forum does not allow me to post any links/pictures until i hit 10 posts...

The thing is, I just need to auto fill in the red highlighed text box as the user adds more record. Any idea how I could begin to tackle this problem ?

Is it possible to return the integer from the bar "Record" at the bottom of the page ?

mCool

 

[vbaInet]

You're only three posts away from 10. But before you count on that. Explain further.

 

[mCool]

It's basic stuff. I just need autofill on a textbox that says "Appointment Number". The number of records are affected by the StudentID. Which means if you type in say 00000001 you will be able to see say 3 records on the Appointment table that is linked to the same ID.

Record 1 should show "1" in the "Appointment Number" textbox so on and so forth. I don't want the user to do it because I know they would definitely either forget or skip a number. What I need is if the user request for a new record by clicking on the next button on the bar at the bottom, the "Appointment Number" should show the last number + 1.

I just saw the bottom display where it shows all the button to navigate through the records and it shows the number of record. Is it possible to just retrieve that number and use it to put into the text box ?

 

[vbaInet]

Look into the DMax() function:

=DMax([AppointNumber]) + 1