Discussion Locked

VB6 Code with a data base problem

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have written code for a simple form to write to and collect data from. The problem is that I want the code to look up the last number in the data base, add one to it and display the next number on my form?
The data base is written in Access.
I apprecaite the help.
Thanks
Andrew

7 replies

by sl-campbell

Assuming the field with the is a true number field:

The recordset variable is: rs

The Sql string is:
sql="SELECT Max(MyNumberFld) As MaxNumber FROM myTable"

The variable to hold and calculate the number is:
Dim dLastNumber As Double

Get the record with the highest number:
dLastNumber=rs("MaxNumber")

Add 1 to it:
dLastNumber=dLastNumber+1

Display it in a text box:
Text1.Text=dLastNumber

by john_wills

Private Sub number_the_new_Umbrella()
Dim IDCounter As Integer
Dim navCounter As Long
IDCounter = 1
Do While DCount("[UmbrellaID]", "Umbrella", "UmbrellaID = '" & IDCounter & "'") > 0 _
And IDCounter < 100000 IDCounter = IDCounter + 1
Loop
If IDCounter = 100000 _
Then
MsgBox "There is no more room in the table of Umbrella Organizations - please tell I.S."
Else
Me.UmbrellaID = IDCounter
lastUpdated = Date
MsgBox Me![name] & " will have Umbrella ID " & Me.UmbrellaID
Me.DataEntry = False
navCounter = DCount("[UmbrellaID]", "Umbrella", "UmbrellaID <= '" & IDCounter & "'")
DoCmd.GoToRecord , , acGoTo, navCounter End If
End Sub

by pancras

place a textbox with
control source value
=DMax("productid";"products")+1
where productid is the lookupvalue and products the table
You may have to use , instead of ;

This discussion is closed to new replies.