Resetting Sequence Number

[Fyrebryte]

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

 

[MikeJohnB]

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?
--
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.

 

[Tim Ferguson]

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

 

[Fyrebryte via AccessMonster.com]

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.

 

[Tim Ferguson]

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

 

[Fyrebryte via AccessMonster.com]

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.