How to autonumber based on another field

[mutiger83]

Here is what I am trying to accomplish and have tried and tried without resolve to create....

On an Access Form I will have a drop down of 9 different options (for example, 1 Year, Blanket, Legal, etc...). Then I want to have text field 'smart number' based on this selection. In other words if I select "1 Year" from the drop down, I want it to place into the text field 1xxx, where xxx represents the next unique number in the sequence. Likewise, if I select "Blanket" from the drop down, I want it to place into the text field 2xxx, where xxx is the next number is the 2xxx sequence. Any thoughts on how this could be accomplished???

Thanks in advance!

 

[snyperx3]

Are you familiar with VBA? I'm asking so I can know how much help you need without giving it all away.

This is my suggestion:
Have a textbox on the form to contain the number.
on the event for the combo box Change put the vba code.
create a recordset of the table in which this information is stored.
move to the last record.
get the number without the first char and add 1. intSmartNum = CInt(Right(SmartNumber,Len(SmartNumber-1))) + 1

make the new smart number and put it in the textbox. (im assuming the first number is the listindex) txtSmartNum.Value = cboOptions.ListIndex & intSmartNum

-Pete

 

[mutiger83]

Thank you for your help. I am familiar with VBA and know exactly what you mean. My only thought, though, is that I belive this will number as follows...

First Record -- listindex = 1 -- textsmartnumber: 1100
Secoond Record -- listindex = 2 -- texsmartnumber: 2101
Third Record -- listindex = 1 -- textsmartnumber: 1102

Correct?

I would prefer if instead it knew where it was at within each sequence. In otherwords, the above scenario would play out as follows:

First Record -- listindex = 1 -- textsmartnumber: 1100
Secoond Record -- listindex = 2 -- texsmartnumber: 2100
Third Record -- listindex = 1 -- textsmartnumber: 1101

Any thoughts on how this might be done?

Thanks!!

 

[snyperx3]

ah, i see. Yes my method will do it the first way you have shown. I thought thats what you wanted =] You can alter your recordset to get it the way you ACTUALLY wanted it.

"SELECT * FROM tblTable WHERE Left(SmartNumber,1) = " & cboOptions.ListIndex & ";"
rst.movelast
.
.
.
you know the rest

Hope this helps.

-Pete

 

[mutiger83]

Hey,

Sorry to bother you with this again, but despite my knowledge of VBA I have not worked with recordsets in quite some time. Hoping you can help me out a bit more towards my goal here...

I have the following...

Dim rst As Recordset
Dim intSmartNum As Integer

Set rst = db.OpenRecordset("SELECT * FROM BD WHERE Left(BidReqNbr,1)=" & Combo62.ListIndex & ";")
rst.MoveLast

How do I get the value out from the recordset in order to get to a point where I can use the formula you referenced earlier (intSmartNum = CInt(Right(SmartNumber,Len(SmartNumber-1))) + 1)?

Note in the above BD is my table with all my records
BidReqNbr is the filed where my 1xxx number needs to end up
combo62 is my listbox where I make the selection

Thanks so much for your help...sorry I wasn't able to get it from what you had replied with...

Thank you!

 

[snyperx3]

np

First off i would suggest using DAO in your declaration
Dim rst as DAO.Recordset

im not sure, but you may have to include a reference to Microsoft DAO Object library. make sure the dim of db is DAO.Database as well.

next, you would get the value with:
intSmartNum = CInt(Right(rst![BidReqNbr],Len(rst![BidReqNbr]-1))) + 1)

thats it =]

-Pete

 

[mutiger83]

Thanks so much....

The problem I has having is gone, and here is my code, however, now I keep getting this Run-time error '91':
Object variable or With block variable not set

Private Sub Combo62_Change()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intSmartNum As Integer

Set rst = db.OpenRecordset("SELECT * FROM BD WHERE Left(BidReqNbr,1)=" & Combo62.ListIndex & ";")
rst.MoveLast
intSmartNum = CInt((Right(rst![BidReqNbr], Len(rst![BidReqNbr] - 1))) + 1)


End Sub

Do you see anything wrong or have any idea what might be causing this?

You have been a lot of help...thanks so much for your time!

 

[snyperx3]

add
Set db = CurrentDb()
before you set the rst

-Pete

 

[mutiger83]

YOU ARE AWESOME!!! Thank you so very much!