Pulling the next avail number

  • Thread starter Thread starter Iram
  • Start date Start date
I

Iram

Hello.
I am having a hard time getting some code to work that is supposed to auto
fill a field with the next available number. I have a button on a data entry
form and on the "On Click" of the button I have =NextNWNumber(), which points
to a Module called "mdl_NextNWNumber". In this Module I have the following
code...

Option Compare Database
Option Explicit

Public Function NextNWNumber() As Long

Dim myrecs As Recordset
Set myrecs = CurrentDb.OpenRecordset("tbl_NWNumbersUsed")
NextNWNumber = myrecs!NWNumber
myrecs.Edit
myrecs!NWNumber = myrecs!NWNumber + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function


Here is a summary of the tables, fields and Modules of this db.

*Main table for data entry "tbl_Questionnaire". This table has many fields
however the main field that needs to be autofilled with the next available
number via the button on the form is "CalwinNumb" . This is a Text field
because we some times need to type in our own number with letters when we
don't use the auto fill button.
*Seperate table to store all of the next available numbers used
"tbl_NWNumbersUsed". There is only one field in this table called "NWNumber".
And there is only one record in this table which is 5000. This is the number
that I want the autofill number to start with, then sequence +1 from here on
all new data entry records. This is a Number field.


Can you help me figure out why my code doesn't produce the next avail number
starting from 5000? When I click on the button nothing happens, no errors.


Thanks.
Iram
 
Iram,

From the looks of it, you have created the next number but you don't tell
the form to use it.

I tired your code out and it works fine. The next step is to call that
number. After your function runs, you need to set the value of the text box
equal to the new number.

It should look something like this:
Sub OnClick_NewNumber()

mdl_NextNWNumber

Me.CalwinNumber = Me.CalwinNumber = DMax("NWNumber", "tbl_NWNumbersUsed")

End Sub

Try that.

PJ
 
Trust me I am a real dork at this kind of stuff. Can you tell me step by step
how to do what you just instructed me to do?
I tried changing the On Click to an [Event Procedure] then copied this into it

Private Sub Btn_NeedAnNWNumber_Click()
mdl_NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

But when I run the compiler I get "mdl_NextNWNumber" becomes greyed out then
an error appears "Expected variable or procedure, not module".

Can you help me with this? Your help is greatly appreciated!

Thanks.
Iram
 
I think I gave you incorrect code. The mdl_ should not have been there.
Try the below:

Private Sub Btn_NeedAnNWNumber_Click()
NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

PJ

Iram said:
Trust me I am a real dork at this kind of stuff. Can you tell me step by step
how to do what you just instructed me to do?
I tried changing the On Click to an [Event Procedure] then copied this into it

Private Sub Btn_NeedAnNWNumber_Click()
mdl_NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

But when I run the compiler I get "mdl_NextNWNumber" becomes greyed out then
an error appears "Expected variable or procedure, not module".

Can you help me with this? Your help is greatly appreciated!

Thanks.
Iram








PJFry said:
Iram,

From the looks of it, you have created the next number but you don't tell
the form to use it.

I tired your code out and it works fine. The next step is to call that
number. After your function runs, you need to set the value of the text box
equal to the new number.

It should look something like this:
Sub OnClick_NewNumber()

mdl_NextNWNumber

Me.CalwinNumber = Me.CalwinNumber = DMax("NWNumber", "tbl_NWNumbersUsed")

End Sub

Try that.

PJ
Click to expand...
Click to expand...
 
Ok, I changed it to the below however now when I click on the button I get an
error Run-Time error '-2147352567 (80020009)'
The field 'tblQuestionnaire.CalwinNumb' cannot contain a Null value because
the Required property for this field is set to True. Enter a value in this
field.
End or Debug

Then this area becomes yellow highlighted...
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")


'This is what the code looks like altogether...
Option Compare Database
Option Explicit

Private Sub Btn_NeedAnNWNumber_Click()
NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub


Iram


PJFry said:
I think I gave you incorrect code. The mdl_ should not have been there.
Try the below:

Private Sub Btn_NeedAnNWNumber_Click()
NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

PJ

Iram said:
Trust me I am a real dork at this kind of stuff. Can you tell me step by step
how to do what you just instructed me to do?
I tried changing the On Click to an [Event Procedure] then copied this into it

Private Sub Btn_NeedAnNWNumber_Click()
mdl_NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

But when I run the compiler I get "mdl_NextNWNumber" becomes greyed out then
an error appears "Expected variable or procedure, not module".

Can you help me with this? Your help is greatly appreciated!

Thanks.
Iram








PJFry said:
Iram,

From the looks of it, you have created the next number but you don't tell
the form to use it.

I tired your code out and it works fine. The next step is to call that
number. After your function runs, you need to set the value of the text box
equal to the new number.

It should look something like this:
Sub OnClick_NewNumber()

mdl_NextNWNumber

Me.CalwinNumber = Me.CalwinNumber = DMax("NWNumber", "tbl_NWNumbersUsed")

End Sub

Try that.

PJ

:

Hello.
I am having a hard time getting some code to work that is supposed to auto
fill a field with the next available number. I have a button on a data entry
form and on the "On Click" of the button I have =NextNWNumber(), which points
to a Module called "mdl_NextNWNumber". In this Module I have the following
code...

Option Compare Database
Option Explicit

Public Function NextNWNumber() As Long

Dim myrecs As Recordset
Set myrecs = CurrentDb.OpenRecordset("tbl_NWNumbersUsed")
NextNWNumber = myrecs!NWNumber
myrecs.Edit
myrecs!NWNumber = myrecs!NWNumber + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function


Here is a summary of the tables, fields and Modules of this db.

*Main table for data entry "tbl_Questionnaire". This table has many fields
however the main field that needs to be autofilled with the next available
number via the button on the form is "CalwinNumb" . This is a Text field
because we some times need to type in our own number with letters when we
don't use the auto fill button.
*Seperate table to store all of the next available numbers used
"tbl_NWNumbersUsed". There is only one field in this table called "NWNumber".
And there is only one record in this table which is 5000. This is the number
that I want the autofill number to start with, then sequence +1 from here on
all new data entry records. This is a Number field.


Can you help me figure out why my code doesn't produce the next avail number
starting from 5000? When I click on the button nothing happens, no errors.


Thanks.
Iram
Click to expand...
Click to expand...
Click to expand...
 
Btw, I have all of this code in the Data Entry form internal vba area and I
don't know if it is interfering with the code you gave me...

Option Compare Database
Option Explicit

Private Sub Add_an_Interview_Click()
On Error GoTo Err_Add_an_Interview_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuestionnaireAdd"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Add_an_Interview_Click:
Exit Sub

Err_Add_an_Interview_Click:
MsgBox Err.Description
Resume Exit_Add_an_Interview_Click

End Sub



Private Sub ModifyInterview_Click()
On Error GoTo Err_ModifyInterview_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuestionnaireModify"

stLinkCriteria = "[IntQuestID]=" & Me![IntQuestID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ModifyInterview_Click:
Exit Sub

Err_ModifyInterview_Click:
MsgBox Err.Description
Resume Exit_ModifyInterview_Click

End Sub
Private Sub NonActiveInterviews_Click()
On Error GoTo Err_NonActiveInterviews_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAllNonActive10DayInterviews"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_NonActiveInterviews_Click:
Exit Sub

Err_NonActiveInterviews_Click:
MsgBox Err.Description
Resume Exit_NonActiveInterviews_Click

End Sub
Private Sub Btn_OKSearchButton_Click()
On Error GoTo Err_Btn_OKSearchButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSearchAll10DayInterviews"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Btn_OKSearchButton_Click:
Exit Sub

Err_Btn_OKSearchButton_Click:
MsgBox Err.Description
Resume Exit_Btn_OKSearchButton_Click

End Sub
Private Sub Btn_OpenCompletedSRs_Click()
On Error GoTo Err_Btn_OpenCompletedSRs_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompletedSRs"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Btn_OpenCompletedSRs_Click:
Exit Sub

Err_Btn_OpenCompletedSRs_Click:
MsgBox Err.Description
Resume Exit_Btn_OpenCompletedSRs_Click

End Sub

Private Sub SearchTextBox_AfterUpdate()
On Error GoTo Err_Btn_OKSearchButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSearchAll10DayInterviews"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Btn_OKSearchButton_Click:
Exit Sub

Err_Btn_OKSearchButton_Click:
MsgBox Err.Description
Resume Exit_Btn_OKSearchButton_Click

End Sub
Private Sub Tools_Click()
On Error GoTo Err_Tools_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Tools_Click:
Exit Sub

Err_Tools_Click:
MsgBox Err.Description
Resume Exit_Tools_Click

End Sub
Private Sub CloseInterviewHistoryFromAddForm_Click()
On Error GoTo Err_CloseInterviewHistoryFromAddForm_Click


DoCmd.Close

Exit_CloseInterviewHistoryFromAddForm_Cl:
Exit Sub

Err_CloseInterviewHistoryFromAddForm_Click:
MsgBox Err.Description
Resume Exit_CloseInterviewHistoryFromAddForm_Cl

End Sub









PJFry said:
I think I gave you incorrect code. The mdl_ should not have been there.
Try the below:

Private Sub Btn_NeedAnNWNumber_Click()
NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

PJ

Iram said:
Trust me I am a real dork at this kind of stuff. Can you tell me step by step
how to do what you just instructed me to do?
I tried changing the On Click to an [Event Procedure] then copied this into it

Private Sub Btn_NeedAnNWNumber_Click()
mdl_NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

But when I run the compiler I get "mdl_NextNWNumber" becomes greyed out then
an error appears "Expected variable or procedure, not module".

Can you help me with this? Your help is greatly appreciated!

Thanks.
Iram








PJFry said:
Iram,

From the looks of it, you have created the next number but you don't tell
the form to use it.

I tired your code out and it works fine. The next step is to call that
number. After your function runs, you need to set the value of the text box
equal to the new number.

It should look something like this:
Sub OnClick_NewNumber()

mdl_NextNWNumber

Me.CalwinNumber = Me.CalwinNumber = DMax("NWNumber", "tbl_NWNumbersUsed")

End Sub

Try that.

PJ

:

Hello.
I am having a hard time getting some code to work that is supposed to auto
fill a field with the next available number. I have a button on a data entry
form and on the "On Click" of the button I have =NextNWNumber(), which points
to a Module called "mdl_NextNWNumber". In this Module I have the following
code...

Option Compare Database
Option Explicit

Public Function NextNWNumber() As Long

Dim myrecs As Recordset
Set myrecs = CurrentDb.OpenRecordset("tbl_NWNumbersUsed")
NextNWNumber = myrecs!NWNumber
myrecs.Edit
myrecs!NWNumber = myrecs!NWNumber + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function


Here is a summary of the tables, fields and Modules of this db.

*Main table for data entry "tbl_Questionnaire". This table has many fields
however the main field that needs to be autofilled with the next available
number via the button on the form is "CalwinNumb" . This is a Text field
because we some times need to type in our own number with letters when we
don't use the auto fill button.
*Seperate table to store all of the next available numbers used
"tbl_NWNumbersUsed". There is only one field in this table called "NWNumber".
And there is only one record in this table which is 5000. This is the number
that I want the autofill number to start with, then sequence +1 from here on
all new data entry records. This is a Number field.


Can you help me figure out why my code doesn't produce the next avail number
starting from 5000? When I click on the button nothing happens, no errors.


Thanks.
Iram
Click to expand...
Click to expand...
Click to expand...
 
I seem to be making a habit of this...

There was an extra Me.CalwinNumb =

Private Sub Btn_NeedAnNWNumber_Click()
NextNWNumber
Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

That should clear up any operator type error (i.e. me) and we can see what
the real errors are...

PJ

Iram said:
Btw, I have all of this code in the Data Entry form internal vba area and I
don't know if it is interfering with the code you gave me...

Option Compare Database
Option Explicit

Private Sub Add_an_Interview_Click()
On Error GoTo Err_Add_an_Interview_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuestionnaireAdd"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Add_an_Interview_Click:
Exit Sub

Err_Add_an_Interview_Click:
MsgBox Err.Description
Resume Exit_Add_an_Interview_Click

End Sub



Private Sub ModifyInterview_Click()
On Error GoTo Err_ModifyInterview_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuestionnaireModify"

stLinkCriteria = "[IntQuestID]=" & Me![IntQuestID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ModifyInterview_Click:
Exit Sub

Err_ModifyInterview_Click:
MsgBox Err.Description
Resume Exit_ModifyInterview_Click

End Sub
Private Sub NonActiveInterviews_Click()
On Error GoTo Err_NonActiveInterviews_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAllNonActive10DayInterviews"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_NonActiveInterviews_Click:
Exit Sub

Err_NonActiveInterviews_Click:
MsgBox Err.Description
Resume Exit_NonActiveInterviews_Click

End Sub
Private Sub Btn_OKSearchButton_Click()
On Error GoTo Err_Btn_OKSearchButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSearchAll10DayInterviews"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Btn_OKSearchButton_Click:
Exit Sub

Err_Btn_OKSearchButton_Click:
MsgBox Err.Description
Resume Exit_Btn_OKSearchButton_Click

End Sub
Private Sub Btn_OpenCompletedSRs_Click()
On Error GoTo Err_Btn_OpenCompletedSRs_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompletedSRs"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Btn_OpenCompletedSRs_Click:
Exit Sub

Err_Btn_OpenCompletedSRs_Click:
MsgBox Err.Description
Resume Exit_Btn_OpenCompletedSRs_Click

End Sub

Private Sub SearchTextBox_AfterUpdate()
On Error GoTo Err_Btn_OKSearchButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSearchAll10DayInterviews"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Btn_OKSearchButton_Click:
Exit Sub

Err_Btn_OKSearchButton_Click:
MsgBox Err.Description
Resume Exit_Btn_OKSearchButton_Click

End Sub
Private Sub Tools_Click()
On Error GoTo Err_Tools_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Tools_Click:
Exit Sub

Err_Tools_Click:
MsgBox Err.Description
Resume Exit_Tools_Click

End Sub
Private Sub CloseInterviewHistoryFromAddForm_Click()
On Error GoTo Err_CloseInterviewHistoryFromAddForm_Click


DoCmd.Close

Exit_CloseInterviewHistoryFromAddForm_Cl:
Exit Sub

Err_CloseInterviewHistoryFromAddForm_Click:
MsgBox Err.Description
Resume Exit_CloseInterviewHistoryFromAddForm_Cl

End Sub









PJFry said:
I think I gave you incorrect code. The mdl_ should not have been there.
Try the below:

Private Sub Btn_NeedAnNWNumber_Click()
NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

PJ

Iram said:
Trust me I am a real dork at this kind of stuff. Can you tell me step by step
how to do what you just instructed me to do?
I tried changing the On Click to an [Event Procedure] then copied this into it

Private Sub Btn_NeedAnNWNumber_Click()
mdl_NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

But when I run the compiler I get "mdl_NextNWNumber" becomes greyed out then
an error appears "Expected variable or procedure, not module".

Can you help me with this? Your help is greatly appreciated!

Thanks.
Iram








:

Iram,

From the looks of it, you have created the next number but you don't tell
the form to use it.

I tired your code out and it works fine. The next step is to call that
number. After your function runs, you need to set the value of the text box
equal to the new number.

It should look something like this:
Sub OnClick_NewNumber()

mdl_NextNWNumber

Me.CalwinNumber = Me.CalwinNumber = DMax("NWNumber", "tbl_NWNumbersUsed")

End Sub

Try that.

PJ

:

Hello.
I am having a hard time getting some code to work that is supposed to auto
fill a field with the next available number. I have a button on a data entry
form and on the "On Click" of the button I have =NextNWNumber(), which points
to a Module called "mdl_NextNWNumber". In this Module I have the following
code...

Option Compare Database
Option Explicit

Public Function NextNWNumber() As Long

Dim myrecs As Recordset
Set myrecs = CurrentDb.OpenRecordset("tbl_NWNumbersUsed")
NextNWNumber = myrecs!NWNumber
myrecs.Edit
myrecs!NWNumber = myrecs!NWNumber + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function


Here is a summary of the tables, fields and Modules of this db.

*Main table for data entry "tbl_Questionnaire". This table has many fields
however the main field that needs to be autofilled with the next available
number via the button on the form is "CalwinNumb" . This is a Text field
because we some times need to type in our own number with letters when we
don't use the auto fill button.
*Seperate table to store all of the next available numbers used
"tbl_NWNumbersUsed". There is only one field in this table called "NWNumber".
And there is only one record in this table which is 5000. This is the number
that I want the autofill number to start with, then sequence +1 from here on
all new data entry records. This is a Number field.


Can you help me figure out why my code doesn't produce the next avail number
starting from 5000? When I click on the button nothing happens, no errors.


Thanks.
Iram
Click to expand...
Click to expand...
Click to expand...
 
You are the MAN!!!!!!!!!!

Thanks.
I am not sure how Microsoft compensates you guys but I sure wish they sent
you guys on some awesome cruises or big bucks!!!!

Iram



PJFry said:
I seem to be making a habit of this...

There was an extra Me.CalwinNumb =

Private Sub Btn_NeedAnNWNumber_Click()
NextNWNumber
Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

That should clear up any operator type error (i.e. me) and we can see what
the real errors are...

PJ

Iram said:
Btw, I have all of this code in the Data Entry form internal vba area and I
don't know if it is interfering with the code you gave me...

Option Compare Database
Option Explicit

Private Sub Add_an_Interview_Click()
On Error GoTo Err_Add_an_Interview_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuestionnaireAdd"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Add_an_Interview_Click:
Exit Sub

Err_Add_an_Interview_Click:
MsgBox Err.Description
Resume Exit_Add_an_Interview_Click

End Sub



Private Sub ModifyInterview_Click()
On Error GoTo Err_ModifyInterview_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuestionnaireModify"

stLinkCriteria = "[IntQuestID]=" & Me![IntQuestID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ModifyInterview_Click:
Exit Sub

Err_ModifyInterview_Click:
MsgBox Err.Description
Resume Exit_ModifyInterview_Click

End Sub
Private Sub NonActiveInterviews_Click()
On Error GoTo Err_NonActiveInterviews_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAllNonActive10DayInterviews"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_NonActiveInterviews_Click:
Exit Sub

Err_NonActiveInterviews_Click:
MsgBox Err.Description
Resume Exit_NonActiveInterviews_Click

End Sub
Private Sub Btn_OKSearchButton_Click()
On Error GoTo Err_Btn_OKSearchButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSearchAll10DayInterviews"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Btn_OKSearchButton_Click:
Exit Sub

Err_Btn_OKSearchButton_Click:
MsgBox Err.Description
Resume Exit_Btn_OKSearchButton_Click

End Sub
Private Sub Btn_OpenCompletedSRs_Click()
On Error GoTo Err_Btn_OpenCompletedSRs_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompletedSRs"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Btn_OpenCompletedSRs_Click:
Exit Sub

Err_Btn_OpenCompletedSRs_Click:
MsgBox Err.Description
Resume Exit_Btn_OpenCompletedSRs_Click

End Sub

Private Sub SearchTextBox_AfterUpdate()
On Error GoTo Err_Btn_OKSearchButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSearchAll10DayInterviews"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Btn_OKSearchButton_Click:
Exit Sub

Err_Btn_OKSearchButton_Click:
MsgBox Err.Description
Resume Exit_Btn_OKSearchButton_Click

End Sub
Private Sub Tools_Click()
On Error GoTo Err_Tools_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Tools_Click:
Exit Sub

Err_Tools_Click:
MsgBox Err.Description
Resume Exit_Tools_Click

End Sub
Private Sub CloseInterviewHistoryFromAddForm_Click()
On Error GoTo Err_CloseInterviewHistoryFromAddForm_Click


DoCmd.Close

Exit_CloseInterviewHistoryFromAddForm_Cl:
Exit Sub

Err_CloseInterviewHistoryFromAddForm_Click:
MsgBox Err.Description
Resume Exit_CloseInterviewHistoryFromAddForm_Cl

End Sub









PJFry said:
I think I gave you incorrect code. The mdl_ should not have been there.
Try the below:

Private Sub Btn_NeedAnNWNumber_Click()
NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

PJ

:

Trust me I am a real dork at this kind of stuff. Can you tell me step by step
how to do what you just instructed me to do?
I tried changing the On Click to an [Event Procedure] then copied this into it

Private Sub Btn_NeedAnNWNumber_Click()
mdl_NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

But when I run the compiler I get "mdl_NextNWNumber" becomes greyed out then
an error appears "Expected variable or procedure, not module".

Can you help me with this? Your help is greatly appreciated!

Thanks.
Iram








:

Iram,

From the looks of it, you have created the next number but you don't tell
the form to use it.

I tired your code out and it works fine. The next step is to call that
number. After your function runs, you need to set the value of the text box
equal to the new number.

It should look something like this:
Sub OnClick_NewNumber()

mdl_NextNWNumber

Me.CalwinNumber = Me.CalwinNumber = DMax("NWNumber", "tbl_NWNumbersUsed")

End Sub

Try that.

PJ

:

Hello.
I am having a hard time getting some code to work that is supposed to auto
fill a field with the next available number. I have a button on a data entry
form and on the "On Click" of the button I have =NextNWNumber(), which points
to a Module called "mdl_NextNWNumber". In this Module I have the following
code...

Option Compare Database
Option Explicit

Public Function NextNWNumber() As Long

Dim myrecs As Recordset
Set myrecs = CurrentDb.OpenRecordset("tbl_NWNumbersUsed")
NextNWNumber = myrecs!NWNumber
myrecs.Edit
myrecs!NWNumber = myrecs!NWNumber + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function


Here is a summary of the tables, fields and Modules of this db.

*Main table for data entry "tbl_Questionnaire". This table has many fields
however the main field that needs to be autofilled with the next available
number via the button on the form is "CalwinNumb" . This is a Text field
because we some times need to type in our own number with letters when we
don't use the auto fill button.
*Seperate table to store all of the next available numbers used
"tbl_NWNumbersUsed". There is only one field in this table called "NWNumber".
And there is only one record in this table which is 5000. This is the number
that I want the autofill number to start with, then sequence +1 from here on
all new data entry records. This is a Number field.


Can you help me figure out why my code doesn't produce the next avail number
starting from 5000? When I click on the button nothing happens, no errors.


Thanks.
Iram
Click to expand...
Click to expand...
Click to expand...
 
I am not sure how Microsoft compensates you guys but I sure wish they sent
you guys on some awesome cruises or big bucks!!!!
Click to expand...

So do we!

Actually, the people who respond to questions here are just volunteering our
time to help others. Most of us have no connection whatsoever to MS
(other than the fact that we have experience with Access). There are a few
who have some connection to MS, but nobody gets compensated for
answering questions here.

However, don't let me stop you from trying to convince MS to send us
all on a cruise, if that's what you want to do :-)

_________

Sean Bailey


Iram said:
You are the MAN!!!!!!!!!!

Thanks.
I am not sure how Microsoft compensates you guys but I sure wish they sent
you guys on some awesome cruises or big bucks!!!!

Iram



PJFry said:
I seem to be making a habit of this...

There was an extra Me.CalwinNumb =

Private Sub Btn_NeedAnNWNumber_Click()
NextNWNumber
Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

That should clear up any operator type error (i.e. me) and we can see
what
the real errors are...

PJ

Iram said:
Btw, I have all of this code in the Data Entry form internal vba area
and I
don't know if it is interfering with the code you gave me...

Option Compare Database
Option Explicit

Private Sub Add_an_Interview_Click()
On Error GoTo Err_Add_an_Interview_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuestionnaireAdd"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Add_an_Interview_Click:
Exit Sub

Err_Add_an_Interview_Click:
MsgBox Err.Description
Resume Exit_Add_an_Interview_Click

End Sub



Private Sub ModifyInterview_Click()
On Error GoTo Err_ModifyInterview_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuestionnaireModify"

stLinkCriteria = "[IntQuestID]=" & Me![IntQuestID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ModifyInterview_Click:
Exit Sub

Err_ModifyInterview_Click:
MsgBox Err.Description
Resume Exit_ModifyInterview_Click

End Sub
Private Sub NonActiveInterviews_Click()
On Error GoTo Err_NonActiveInterviews_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAllNonActive10DayInterviews"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_NonActiveInterviews_Click:
Exit Sub

Err_NonActiveInterviews_Click:
MsgBox Err.Description
Resume Exit_NonActiveInterviews_Click

End Sub
Private Sub Btn_OKSearchButton_Click()
On Error GoTo Err_Btn_OKSearchButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSearchAll10DayInterviews"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Btn_OKSearchButton_Click:
Exit Sub

Err_Btn_OKSearchButton_Click:
MsgBox Err.Description
Resume Exit_Btn_OKSearchButton_Click

End Sub
Private Sub Btn_OpenCompletedSRs_Click()
On Error GoTo Err_Btn_OpenCompletedSRs_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompletedSRs"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Btn_OpenCompletedSRs_Click:
Exit Sub

Err_Btn_OpenCompletedSRs_Click:
MsgBox Err.Description
Resume Exit_Btn_OpenCompletedSRs_Click

End Sub

Private Sub SearchTextBox_AfterUpdate()
On Error GoTo Err_Btn_OKSearchButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSearchAll10DayInterviews"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Btn_OKSearchButton_Click:
Exit Sub

Err_Btn_OKSearchButton_Click:
MsgBox Err.Description
Resume Exit_Btn_OKSearchButton_Click

End Sub
Private Sub Tools_Click()
On Error GoTo Err_Tools_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Tools_Click:
Exit Sub

Err_Tools_Click:
MsgBox Err.Description
Resume Exit_Tools_Click

End Sub
Private Sub CloseInterviewHistoryFromAddForm_Click()
On Error GoTo Err_CloseInterviewHistoryFromAddForm_Click


DoCmd.Close

Exit_CloseInterviewHistoryFromAddForm_Cl:
Exit Sub

Err_CloseInterviewHistoryFromAddForm_Click:
MsgBox Err.Description
Resume Exit_CloseInterviewHistoryFromAddForm_Cl

End Sub









:

I think I gave you incorrect code. The mdl_ should not have been
there.
Try the below:

Private Sub Btn_NeedAnNWNumber_Click()
NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber", "tbl_NWNumbersUsed")
End Sub

PJ

:

Trust me I am a real dork at this kind of stuff. Can you tell me
step by step
how to do what you just instructed me to do?
I tried changing the On Click to an [Event Procedure] then copied
this into it

Private Sub Btn_NeedAnNWNumber_Click()
mdl_NextNWNumber
Me.CalwinNumb = Me.CalwinNumb = DMax("NWNumber",
"tbl_NWNumbersUsed")
End Sub

But when I run the compiler I get "mdl_NextNWNumber" becomes greyed
out then
an error appears "Expected variable or procedure, not module".

Can you help me with this? Your help is greatly appreciated!

Thanks.
Iram








:

Iram,

From the looks of it, you have created the next number but you
don't tell
the form to use it.

I tired your code out and it works fine. The next step is to
call that
number. After your function runs, you need to set the value of
the text box
equal to the new number.

It should look something like this:
Sub OnClick_NewNumber()

mdl_NextNWNumber

Me.CalwinNumber = Me.CalwinNumber = DMax("NWNumber",
"tbl_NWNumbersUsed")

End Sub

Try that.

PJ

:

Hello.
I am having a hard time getting some code to work that is
supposed to auto
fill a field with the next available number. I have a button on
a data entry
form and on the "On Click" of the button I have
=NextNWNumber(), which points
to a Module called "mdl_NextNWNumber". In this Module I have
the following
code...

Option Compare Database
Option Explicit

Public Function NextNWNumber() As Long

Dim myrecs As Recordset
Set myrecs = CurrentDb.OpenRecordset("tbl_NWNumbersUsed")
NextNWNumber = myrecs!NWNumber
myrecs.Edit
myrecs!NWNumber = myrecs!NWNumber + 1
myrecs.Update
myrecs.Close
Set myrecs = Nothing

End Function


Here is a summary of the tables, fields and Modules of this db.

*Main table for data entry "tbl_Questionnaire". This table has
many fields
however the main field that needs to be autofilled with the
next available
number via the button on the form is "CalwinNumb" . This is a
Text field
because we some times need to type in our own number with
letters when we
don't use the auto fill button.
*Seperate table to store all of the next available numbers used
"tbl_NWNumbersUsed". There is only one field in this table
called "NWNumber".
And there is only one record in this table which is 5000. This
is the number
that I want the autofill number to start with, then sequence +1
from here on
all new data entry records. This is a Number field.


Can you help me figure out why my code doesn't produce the next
avail number
starting from 5000? When I click on the button nothing happens,
no errors.


Thanks.
Iram
Click to expand...
Click to expand...
Click to expand...
 
By the way, that function is designed to return the next number from that
table. So, the needed code is:

Private Sub Btn_NeedAnNWNumber_Click()
Me.CalwinNumb = NextNWNumber()
End Sub

If the user does not call the code as per above...then the value will not
increment....
 
Back
Top