470,626 Members | 2,139 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,626 developers. It's quick & easy.

Dependency autoincrement

I need to make the key field on a subform autoincrement differently dependent on a seletion made in the main form.

On the main form, there is a drop-down with a limited set of values, for example:

Type-A
Type-B
Type-C
Type-D

When a selection is made, I would like to add a new record to the table attached to the subform, look up the highest value in a range dependent on the selection, for example:

Type-A 10000-19999
Type-B 20000-29999
Type-C 30000-39999
Type-D 40000-49999

and add 1 to make the key for the new record.

Obviously making the field autoincrement by default in the table will not work for this need.

I know what I need to do, just not how to implement it in Access/VBA, as I am not really a VB programmer at all, I am a C/C++/C# and SQL (Oracle/MS-SQL Server) programmer.

Help please!
Oct 12 '06 #1
1 1330
jimatqsi
1,263 Expert 1GB
Here is the code I use whenever I want to increment a keyfield, like next invoice number, next order number, next whatever. So in your case, you would add something like this:

dim strNextSomething as String
strNextSomething = "qsel_NextAvailableTypeA"
if Me!nameofdropdownlistbox="TypeB" then strNextSomething="qsel_NextAvailableTypeB"
if Me!nameofdropdownlistbox="TypeC" then strNextSomething="qsel_NextAvailableTypeC"
and so on until you have
Set rs = db.OpenRecordset(strNextSomething) which will open the appropriate query depending on the value chosen in your list box.

Of course to support this, you have to have these queries against some table (tblParameters, in my case) with the NextAvailableNumber data item.

Hope this helps.
Jim

Public Function GetNextOrderNumber() As Long

Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb

Set rs = db.OpenRecordset("qsel_NextAvailableOrderNumber")

If rs.RecordCount = 0 Then
MsgBox "Please build 'Order' counter in table tblParameters"
Else
rs.MoveFirst
GetNextOrderNumber = rs!NextAvailableNumber + 1
rs.Edit
rs!NextAvailableNumber = rs!NextAvailableNumber + 1
rs.Update
End If
rs.Close
Set rs = Nothing

End Function
Oct 14 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Neil | last post: by
6 posts views Thread by Dennis | last post: by
5 posts views Thread by JC Voon | last post: by
4 posts views Thread by Tim | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.