Resetting Sequence Number

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fyrebryte - 02 Mar 2007 19:17 GMT

I have a database that I am building (I am very new to VBA, so please be
patient) and I figured out how to create a number for each different type of
record based on the starting letter of the file. For example:

Medical = M
Insurance = I

The file number would read M07-0001 for the first medical record (I have the
last two digits of the year inserted also).

What I have though is this number is assigned when you tab out of the record
type combo box. If someone mistakenly hit the wrong type and then tabs, the
number gets assigned and the next record would be M07-0002.

I would like to create some code that actually deletes the number that is not
wanted so that the next record would be assigned properly.

I have a field that basically keeps count of the last number that was
assigned and every new record adds 1 to it.

I hope this make sense. If I can clarify in any way, please let me know.

Sincerly,

Fyre

Reply to this Message

MikeJohnB - 03 Mar 2007 16:49 GMT

Have you tried adding the number generating code to the after update event of
the form rather than the control. that way the number does not get generated
until the last record is saved.

Please let me know if I have misunderstood your question?

Signature

Advice to Posters.
Mark as answered when completed.

Check your post for replies or request for more information.

Have the COURTESY to send an ending note even if the answer didn''t work.

> I have a database that I am building (I am very new to VBA, so please be
> patient) and I figured out how to create a number for each different type of
[quoted text clipped - 21 lines]
>
> Fyre

Reply to this Message

Tim Ferguson - 05 Mar 2007 13:53 GMT

> Medical = M
> Insurance = I
>
> The file number would read M07-0001 for the first medical record (I
> have the last two digits of the year inserted also).

You really need three fields to carry these separate bits of
information...

RecordType Text(1) // M or I
YearNumber Integer // please, it's better to use all four digits;
<sigh>
SerialNumber Integer // unless you plan to go past 65000 in a year

It's easy to combine these when you want to display them on a form or on
a report:

= RecordType & Format(YearNumber mod 100, "00\-") & _
Format(SerialNumber, "0000")

It's easy to calculate the next available number (as long as you are in
single-user mode; multi-user safety takes a little bit more doing):

dim newSerialNumber As Variant ' in case it's null

' get the current highest number for this record type
' and the current year
newSerialNumber = DMax("SerialNumber", _
"MyTable", _
"RecordType=""" & thisRecordType & _
" AND YearNumber=" & currentYearNumber _
)

if isnull(newSerialNumber) Then
' first one of the year
newSerialNumber = 1

else
' have to bump up to the next one
newSerialNumber = newSerialNumber + 1
end if

You _can_ do this all in one line, but it's easier to read broken down.

Hope that helps

Tim F

Reply to this Message

Fyrebryte - 13 Mar 2007 15:04 GMT

Thank you so much for your replies.

I am trying my best to make sense of everything... hehe like I said I am very
new to this.

Basically, I am creating a database and thinking of how I would like to try
things, then trying to find code that makes it possible. I did buy a book,
but it doesn't seem specific enough. I may need an actual VB book.

I have another question if you don't mind.

I am trying out option groups so that I could show/hide subforms on my main
background dependant on which radio button is selected. I have three options
currently...

1. Sign In Log
2. Petition Entry
3. Subdivision Entry

Now, through trial and error, I found that if I just insert a radio button
(no option group), then I can use the AfterUpdate event of the button to use
the following code:

If Me.Active = True Then
Me.<NameOfForm>.Visible = True
Else
Me.<NameOfForm>.Visible = False
End If

Also, the same code goes into the On Curent event on the main form.

This works nicely at showing and hiding the form if the button is selected or
deselected, but it is only one choice.

So I tried creating an option group so that only one value is selected at any
given time. I noticed that the AfterUpdate event is not available.

So my question is this... Is it possible to create what I am going after? In
an option group, when choice 1 is selected, choice 2 and 3 are not visible.
Then when I choose 2, 1 is hidden. Then if I reselect choice 1, it reappears
and choice 2 disappears?

This is Access 2003, if that helps any.

I guess overall I am trying to use one background and a bunch of forms that
are actually stacked on each other, but only one is visible at a time.

If I missed anything, please let me know.

Thank you.

Reply to this Message

Tim Ferguson - 13 Mar 2007 22:37 GMT

In general it's better to post a new question in a new thread because
more people will read it.

Also: please could you quote relevant text from previous postings as I
(nor do most of us, I guess) don't keep copies of historic interchanges
available.

> So I tried creating an option group so that only one value is selected
> at any given time. I noticed that the AfterUpdate event is not
> available.

It's the AfterUpdate event of the option group, not the individual
buttons. Don't forget to hide the current form as well as unhiding the
one you do want.

> I guess overall I am trying to use one background and a bunch of forms
> that are actually stacked on each other, but only one is visible at a
> time.

Okay: I use a tab control for this because it's less amenable to being
messed up by the user, but it's your call.

Hope that helps

Tim F

Reply to this Message

Fyrebryte - 19 Mar 2007 12:33 GMT

Yes, it helps alot. Thank you for your replies. I did get the subforms to
work correctly. I will also take your suggestion to post new questions in a
new posting.

Thank you all very much.

Reply to this Message