Dear Sir, I am using Access 2002, and I would like to return the next 
number from a table and place it on my form. I have a buffer table which 
I use to enter the information, then I 'Append' it to the main table.

I get an Error: 
Compile Error:
              User-Defined Type not defined..


Here is my code:(WHat am I doing wrong....

Public Function getCommonID() As Long
Dim strSQL As String
Dim dbs As Database
Dim rst As Recordset
Dim tmp As Long
Dim tmp2 As Long
 Set dbs = CurrentDb()

strSQL = "SELECT Max(NewID) as LastIDAdded FROM NewMain"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
 tmp = Nz(rst.Fields(0), 0)

strSQL = "SELECT Max(NewID) as LastIDAdded FROM NewMain_buff "
 Set rst = dbs.OpenRecordset(strSQL)
 rst.MoveFirst
 tmp2 = Nz(rst.Fields(0), 0)

If tmp >= tmp2 Then
    getNewID = tmp + 1
Else
    getNewID = tmp2 + 1
End If

End Function

Is there any one out there who can help me with this Module that I call 
from an Input form - to return the next un-used number.

Thank you

Where is the code?
 
 KennethMungwira <KennethMungwira@y...> wrote:Is there any one out there who can help me with this Module that I call 
from an Input form - to return the next un-used number.

Thank you


Lonnie Johnson
ProDev, Professional Development of MS Access Databases
http://www.prodev.us
Let me build your next MS Access database application. 




 



---------------------------------
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day

This code is in a module. The Table which the NewID is located only holds 
a number, not Autonumber. So in my input form on the load event I 
call 'getNewID' and place the next un-used number and place it in the 
buffer table, so when I paste the info the new number is place in the 
main table. This code worked in Access 97, but now does not in 2002.


Public Function getNewID() As Long

Dim strSQL As String
Dim dbs As Database
Dim rst As Recordset
Dim tmp As Long
Dim tmp2 As Long
 Set dbs = CurrentDb()

strSQL = "SELECT Max(NewID) as LastIDAdded FROM NewMain"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
 tmp = Nz(rst.Fields(0), 0)

strSQL = "SELECT Max(NewID) as LastIDAdded FROM NewMain_buff "
 Set rst = dbs.OpenRecordset(strSQL)
 rst.MoveFirst
 tmp2 = Nz(rst.Fields(0), 0)

If tmp >= tmp2 Then
    getNewID = tmp + 1
Else
    getNewID = tmp2 + 1
End If

End Function

If its a numerical field. I'm not sure why you're going through this much work to increase the number by one. I would put a short
line in my before update event of the form to say 
IDField = DMax("IDField","MyTable")
That way the number is not created until just before the record is saved. This way you don't get two users trying to use the same
number.  Also if one aborts processing there are no holes or gaps in your numbers.
Any reason you are not using the autonum? Just curious.
 
 
 KennethMungwira <KennethMungwira@y...> wrote:This code is in a module. The Table which the NewID is located only holds 
a number, not Autonumber. So in my input form on the load event I 
call 'getNewID' and place the next un-used number and place it in the 
buffer table, so when I paste the info the new number is place in the 
main table. This code worked in Access 97, but now does not in 2002.


Public Function getNewID() As Long

Dim strSQL As String
Dim dbs As Database
Dim rst As Recordset
Dim tmp As Long
Dim tmp2 As Long
Set dbs = CurrentDb()

strSQL = "SELECT Max(NewID) as LastIDAdded FROM NewMain"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
tmp = Nz(rst.Fields(0), 0)

strSQL = "SELECT Max(NewID) as LastIDAdded FROM NewMain_buff "
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
tmp2 = Nz(rst.Fields(0), 0)

If tmp >= tmp2 Then
getNewID = tmp + 1
Else
getNewID = tmp2 + 1
End If

End Function


Lonnie Johnson
ProDev, Professional Development of MS Access Databases
http://www.prodev.us
Let me build your next MS Access database application. 




 



---------------------------------
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day

Dear L,

Thank you for your suggestion, I will try that now. The reason I am not 
using Autonumber is that the Buffer Form that I load everything in, 
before asking the user if all the information is correct, then Autonumber 
is activated when the form is opened. Meaning if the form is opened then 
closed again the number goes up,since the Buffer table is just a table 
with not actual information. I actually append the data from the buffer 
table to the main table.

Here is the code behind the form:


Option Compare Database

Public Sub AppendData()
'
's is the common ID
'p is plant ID
'
'
Dim strSQL As String
Dim s As String

s = Me.txtNewID.Value


'Dim p As String
'p = Me.txtPlantID.Value

' Turn Warnings off
DoCmd.SetWarnings False

'
' Append data to main table
'
'MsgBox "Main"
strSQL = "INSERT INTO [NewMain] "
strSQL = strSQL + "SELECT * "
strSQL = strSQL + "FROM [NewMain_Buff] "
strSQL = strSQL + "WHERE NewID=" + s
DoCmd.RunSQL strSQL

'MsgBox "Acct"
strSQL = "INSERT INTO SubComment "
strSQL = strSQL + "SELECT * "
strSQL = strSQL + "FROM SubComment_Buff "
strSQL = strSQL + "WHERE NewID=" + s
DoCmd.RunSQL strSQL

' Turn Warnings on
DoCmd.SetWarnings True


End Sub


Private Sub CmdClose1_Click()
On Error GoTo Err_CmdClose1_Click


    DoCmd.Close
    
Exit_CmdClose1_Click:
    Exit Sub

Err_CmdClose1_Click:
    MsgBox Err.Description
    Resume Exit_CmdClose1_Click
    
End Sub

Private Sub CmdSaveRec_Click()
Me.SubComment.SetFocus
AppendData
Me.CmdNextRec.Enabled = True
Me.CmdNextRec.SetFocus
Me.CmdSaveRec.Enabled = False

End Sub

Private Sub Form_Close()
    clearBuff (Me.txtNTID.Value)
End Sub

Private Sub Form_Current()
Me.CmdSaveRec.Enabled = True
Me.CmdNextRec.Enabled = False
Me.NTID.Value = Me.txtNTID.Value


'Me.txtNewID.Value = getNewID

'x = Me.Text102.Value

End Sub


Private Sub CmdNextRec_Click()
On Error GoTo Err_CmdNextRec_Click


    DoCmd.GoToRecord , , acNext

Exit_CmdNextRec_Click:
    Exit Sub

Err_CmdNextRec_Click:
    MsgBox Err.Description
    Resume Exit_CmdNextRec_Click
    
End Sub

Dear L,

IDField = DMax("IDField","MyTable")

This formula seems to work and I added + 1 that makes the user use the 
next Max Number, unfortuately my number start at 0, which creates another 
problem, the result of 0 + 1 = 0 how do i get around that problem. Do not 
want to start with Zero..

Thank you all for your help, I have another problem:

On my input form I have a Combo Box which pulls down info from a main 
table, which is not behind the form. But I would like to return some of 
that info from the Combo box to Individual Textboxs is that possible.

Thank you

As long as you include the column (field) in your combo box you can
retrieve the value on the field by using the combobox.column property.
After the .column put inside a pair of parenthesis the number of the
column keeping in mind that the first column is 0 not 1. So if you want
to put a value of the second column into a text box it would be
NameOfYourTextBox = NameOfYourComboBox.Column(1).
Incidentally if you don't want all those extra columns showing in the
drop down, just set their column widths to 0.

Richard Lobel
President
NoClassroom.com
Live Software training 
Right over the Internet 
richard@n...
Tel:  (xxx) xxx-xxxx
Fax:  (xxx) xxx-xxxx

*****ORIGINAL MESSAGE*****
On my input form I have a Combo Box which pulls down info from a main 
table, which is not behind the form. But I would like to return some of 
that info from the Combo box to Individual Textboxs is that possible.

>
If its a numerical field. I'm not sure why you're going through this
much work to increase the number by one. I would put a short line in my
before update event of the form to say 
IDField = DMax("IDField","MyTable")
That way the number is not created until just before the record is
saved. This way you don't get two users trying to use the same number.
Also if one aborts processing there are no holes or gaps in your
numbers. Any reason you are not using the autonum? Just curious.
<
One efficiency improvement I might suggest to this is to create a global
variable that can hold the result of the DMax function mentioned above.
Running the function every time a new added can slow things down. I
would run it once when the program is started, put the return value in a
variable and then simply increment the variable each time a record is
successfully added.

Richard Lobel
President
NoClassroom.com
Live Software training 
Right over the Internet 
richard@n...
Tel:  (xxx) xxx-xxxx
Fax:  (xxx) xxx-xxxx

*****ORIGINAL MESSAGE*****