Numerical in sequence

I created a log for cashier's check in MICROSOFT ACCESS PROGRAM - everyone is
inputting but sometime they forget so I have to go back and research the
missing information. My question is how can I format either in the "field
name" or in the "data type" to ensure that the check number entered is in
numerical order if is not, the system is to reject it (like a control).

On Mon, 12 May 2008 15:18:01 -0700, Maria (MAC) wrote:

I created a log for cashier's check in MICROSOFT ACCESS PROGRAM - everyone is
inputting but sometime they forget so I have to go back and research the
missing information. My question is how can I format either in the "field
name" or in the "data type" to ensure that the check number entered is in
numerical order if is not, the system is to reject it (like a control).

You're thinking spreadsheet, not Access.
It doesn't matter if the check number is ENTERED in numerical order.
Base your form on a query, sorted on the Check Number field.
All the records on the form will be displayed sorted correctly.
Enter the checks in any order.
When/if you print the log, also sort the report on the Check Number
field.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

fredg is correct. The check number order doesn't matter once the data is in
the tables, but if what you are asking is how to ensure the data entry people
enter check numbers in the correct order, that is a different thing. To give
you a correct answer (if this is the question), we need to know more about
the process. For example: In order related to what? The last check number
entered by the user +1, or the last check number entered by any user + 1?
--
Dave Hargis, Microsoft Access MVP


"Maria (MAC)" wrote:

I created a log for cashier's check in MICROSOFT ACCESS PROGRAM - everyone is
inputting but sometime they forget so I have to go back and research the
missing information. My question is how can I format either in the "field
name" or in the "data type" to ensure that the check number entered is in
numerical order if is not, the system is to reject it (like a control).

You mean that if anyone enters a cheque then it should always be 1 number
higher than the last check that was entered? Here is a possible, if you
don't have lots of people inputting at the same time.

In your form, in the After Update Event of the Cheque Number field have this
code.
(swap the field and table names for your own) (I'm presuming that your
ChequeNumbers are numbers and not text)



Dim LastNum as Long
LastNum = NZ(DMax("[CheckNumber]","TblOfCheckNumbers"),0)

If Me.CheckNumber LastNum + 1 Then
Me.CheckNumber.Undo
MsgBox "That was not the next check number. The previous check number was "
& LastNum
End If


Evi



"Maria (MAC)" Maria wrote in message
...
I created a log for cashier's check in MICROSOFT ACCESS PROGRAM - everyone
is
inputting but sometime they forget so I have to go back and research the
missing information. My question is how can I format either in the "field
name" or in the "data type" to ensure that the check number entered is in
numerical order if is not, the system is to reject it (like a control).

Thank you for your response (I apologize for the delay this is my first time
using this service and I was having some problems in getting through).

I have never taken a course in ACCESS, which I should, so what I know is by
asking questions and just experimenting. So I apoligize if I sound ignorant,
which I am.

You are correct the cheque number is always be 1 number higher than the last
check. I do not know what you mean about "After Update Event of the Cheque
Number field have this code (swap the .....)" is. Also, where should I place
the formula below in the "descripton" under TABLE?

The table has the following "main" information to complete (there are more):
Date (Text with Input Mask - short date)
Check Number (Number not text)
Amount (Currency)
Payee (Memo)
Issuance (Memo)
Address (Memo)

Thank you very much

"Evi" wrote:

You mean that if anyone enters a cheque then it should always be 1 number
higher than the last check that was entered? Here is a possible, if you
don't have lots of people inputting at the same time.

In your form, in the After Update Event of the Cheque Number field have this
code.
(swap the field and table names for your own) (I'm presuming that your
ChequeNumbers are numbers and not text)



Dim LastNum as Long
LastNum = NZ(DMax("[CheckNumber]","TblOfCheckNumbers"),0)

If Me.CheckNumber LastNum + 1 Then
Me.CheckNumber.Undo
MsgBox "That was not the next check number. The previous check number was "
& LastNum
End If


Evi



"Maria (MAC)" Maria wrote in message
...
I created a log for cashier's check in MICROSOFT ACCESS PROGRAM - everyone
is
inputting but sometime they forget so I have to go back and research the
missing information. My question is how can I format either in the "field
name" or in the "data type" to ensure that the check number entered is in
numerical order if is not, the system is to reject it (like a control).

Sorry about the jargon, Maria. I didn't know how much you knew.

Use a form to input your data, not a table.
Open the form in Design View.
Right Click on the Check Number control (text box)
Go to Properties
Click on the Event tab
Click next to AfterUpdate, choose Event Procedure, click just right of that
and open up a code page.

Just above where it says

End Sub

put the code

Dim LastNum as Long
LastNum = NZ(DMax("[Check Number]","TblOfCheckNumbers"),0)
If Me.[Check Number] LastNum + 1 Then
Me.[Check Number].Undo
MsgBox "That was not the next check number. The previous check number was "
& LastNum
End If


Replace TblOfCheckNumbers with the real name of the table that contains the
check number.

I'm assuming above that you don't want the code to automatically fill in the
next check number but if you do, open an 'After Update' code page for a
different control in your form, one you always fill in when you are entering
a new record (perhaps the date?) and enter:

Dim LastNum as Long
LastNum = NZ(DMax("[Check Number]","TblOfCheckNumbers"),0) + 1
If Me.[Check Number] =LastNum
End If

Put things in the After Update event of a control means that when you have
finished entering something into a text box, or choosing something with a
combo box, your code will happen.

One problem you will have though, if you really do have a field in your
table called Date, then before you do much else, you really ought to rename
it.

Look up Access Reserved Words field names to find out what may happen if you
don't. Call it XDate or anything else (and be prepared to spend time mending
queries, reports and forms)
You can always change labels in forms and reports to say Date so long as the
field doesn't

Evi






"Maria (MAC)" wrote in message
news
Thank you for your response (I apologize for the delay this is my first
time
using this service and I was having some problems in getting through).

I have never taken a course in ACCESS, which I should, so what I know is
by
asking questions and just experimenting. So I apoligize if I sound
ignorant,
which I am.

You are correct the cheque number is always be 1 number higher than the
last
check. I do not know what you mean about "After Update Event of the
Cheque
Number field have this code (swap the .....)" is. Also, where should I
place
the formula below in the "descripton" under TABLE?

The table has the following "main" information to complete (there are
more):
Date (Text with Input Mask - short date)
Check Number (Number not text)
Amount (Currency)
Payee (Memo)
Issuance (Memo)
Address (Memo)

Thank you very much

"Evi" wrote:

You mean that if anyone enters a cheque then it should always be 1
number
higher than the last check that was entered? Here is a possible, if you
don't have lots of people inputting at the same time.

In your form, in the After Update Event of the Cheque Number field have
this
code.
(swap the field and table names for your own) (I'm presuming that your
ChequeNumbers are numbers and not text)



Dim LastNum as Long
LastNum = NZ(DMax("[CheckNumber]","TblOfCheckNumbers"),0)

If Me.CheckNumber LastNum + 1 Then
Me.CheckNumber.Undo
MsgBox "That was not the next check number. The previous check number
was "
& LastNum
End If


Evi



"Maria (MAC)" Maria wrote in message
...
I created a log for cashier's check in MICROSOFT ACCESS PROGRAM -
everyone
is
inputting but sometime they forget so I have to go back and research
the
missing information. My question is how can I format either in the
"field
name" or in the "data type" to ensure that the check number entered is
in
numerical order if is not, the system is to reject it (like a
control).

Thank you so much for answering and for explaining it

"Evi" wrote:

Sorry about the jargon, Maria. I didn't know how much you knew.

Use a form to input your data, not a table.
Open the form in Design View.
Right Click on the Check Number control (text box)
Go to Properties
Click on the Event tab
Click next to AfterUpdate, choose Event Procedure, click just right of that
and open up a code page.

Just above where it says

End Sub

put the code

Dim LastNum as Long
LastNum = NZ(DMax("[Check Number]","TblOfCheckNumbers"),0)
If Me.[Check Number] LastNum + 1 Then
Me.[Check Number].Undo
MsgBox "That was not the next check number. The previous check number was "
& LastNum
End If


Replace TblOfCheckNumbers with the real name of the table that contains the
check number.

I'm assuming above that you don't want the code to automatically fill in the
next check number but if you do, open an 'After Update' code page for a
different control in your form, one you always fill in when you are entering
a new record (perhaps the date?) and enter:

Dim LastNum as Long
LastNum = NZ(DMax("[Check Number]","TblOfCheckNumbers"),0) + 1
If Me.[Check Number] =LastNum
End If

Put things in the After Update event of a control means that when you have
finished entering something into a text box, or choosing something with a
combo box, your code will happen.

One problem you will have though, if you really do have a field in your
table called Date, then before you do much else, you really ought to rename
it.

Look up Access Reserved Words field names to find out what may happen if you
don't. Call it XDate or anything else (and be prepared to spend time mending
queries, reports and forms)
You can always change labels in forms and reports to say Date so long as the
field doesn't

Evi






"Maria (MAC)" wrote in message
news
Thank you for your response (I apologize for the delay this is my first
time
using this service and I was having some problems in getting through).

I have never taken a course in ACCESS, which I should, so what I know is
by
asking questions and just experimenting. So I apoligize if I sound
ignorant,
which I am.

You are correct the cheque number is always be 1 number higher than the
last
check. I do not know what you mean about "After Update Event of the
Cheque
Number field have this code (swap the .....)" is. Also, where should I
place
the formula below in the "descripton" under TABLE?

The table has the following "main" information to complete (there are
more):
Date (Text with Input Mask - short date)
Check Number (Number not text)
Amount (Currency)
Payee (Memo)
Issuance (Memo)
Address (Memo)

Thank you very much

"Evi" wrote:

You mean that if anyone enters a cheque then it should always be 1
number
higher than the last check that was entered? Here is a possible, if you
don't have lots of people inputting at the same time.

In your form, in the After Update Event of the Cheque Number field have
this
code.
(swap the field and table names for your own) (I'm presuming that your
ChequeNumbers are numbers and not text)



Dim LastNum as Long
LastNum = NZ(DMax("[CheckNumber]","TblOfCheckNumbers"),0)

If Me.CheckNumber LastNum + 1 Then
Me.CheckNumber.Undo
MsgBox "That was not the next check number. The previous check number
was "
& LastNum
End If


Evi



"Maria (MAC)" Maria wrote in message
...
I created a log for cashier's check in MICROSOFT ACCESS PROGRAM -
everyone
is
inputting but sometime they forget so I have to go back and research
the
missing information. My question is how can I format either in the
"field
name" or in the "data type" to ensure that the check number entered is
in
numerical order if is not, the system is to reject it (like a
control).

Evi, I did the steps that you indicated below but I am getting the following
error Message:

THI IS MY INPUT:
Private Sub Check_No_AfterUpdate()

Dim LastNum As Long
LastNum = Nz(DMax("[Check_No]", "Loan Check Log - 2008"), 0)
If Me.[Check_No] = LastNum + 1 Then
Me.[Check_No].Undo
MsgBox "That was not the next check number. The previous check number was "
& LastNum
End Sub

When I enter "End If" - I get "Block Error - Expected End Sub"
So when I change it to "End Sub" - I get "Compile Error - Block If withough
End If"

and both times the first line "Private Sub Check ..." is highlighted in
yellow


"Evi" wrote:

Sorry about the jargon, Maria. I didn't know how much you knew.

Use a form to input your data, not a table.
Open the form in Design View.
Right Click on the Check Number control (text box)
Go to Properties
Click on the Event tab
Click next to AfterUpdate, choose Event Procedure, click just right of that
and open up a code page.

Just above where it says

End Sub

put the code

Dim LastNum as Long
LastNum = NZ(DMax("[Check Number]","TblOfCheckNumbers"),0)
If Me.[Check Number] LastNum + 1 Then
Me.[Check Number].Undo
MsgBox "That was not the next check number. The previous check number was "
& LastNum
End If


Replace TblOfCheckNumbers with the real name of the table that contains the
check number.

I'm assuming above that you don't want the code to automatically fill in the
next check number but if you do, open an 'After Update' code page for a
different control in your form, one you always fill in when you are entering
a new record (perhaps the date?) and enter:

Dim LastNum as Long
LastNum = NZ(DMax("[Check Number]","TblOfCheckNumbers"),0) + 1
If Me.[Check Number] =LastNum
End If

Put things in the After Update event of a control means that when you have
finished entering something into a text box, or choosing something with a
combo box, your code will happen.

One problem you will have though, if you really do have a field in your
table called Date, then before you do much else, you really ought to rename
it.

Look up Access Reserved Words field names to find out what may happen if you
don't. Call it XDate or anything else (and be prepared to spend time mending
queries, reports and forms)
You can always change labels in forms and reports to say Date so long as the
field doesn't

Evi






"Maria (MAC)" wrote in message
news
Thank you for your response (I apologize for the delay this is my first
time
using this service and I was having some problems in getting through).

I have never taken a course in ACCESS, which I should, so what I know is
by
asking questions and just experimenting. So I apoligize if I sound
ignorant,
which I am.

You are correct the cheque number is always be 1 number higher than the
last
check. I do not know what you mean about "After Update Event of the
Cheque
Number field have this code (swap the .....)" is. Also, where should I
place
the formula below in the "descripton" under TABLE?

The table has the following "main" information to complete (there are
more):
Date (Text with Input Mask - short date)
Check Number (Number not text)
Amount (Currency)
Payee (Memo)
Issuance (Memo)
Address (Memo)

Thank you very much

"Evi" wrote:

You mean that if anyone enters a cheque then it should always be 1
number
higher than the last check that was entered? Here is a possible, if you
don't have lots of people inputting at the same time.

In your form, in the After Update Event of the Cheque Number field have
this
code.
(swap the field and table names for your own) (I'm presuming that your
ChequeNumbers are numbers and not text)



Dim LastNum as Long
LastNum = NZ(DMax("[CheckNumber]","TblOfCheckNumbers"),0)

If Me.CheckNumber LastNum + 1 Then
Me.CheckNumber.Undo
MsgBox "That was not the next check number. The previous check number
was "
& LastNum
End If


Evi



"Maria (MAC)" Maria wrote in message
...
I created a log for cashier's check in MICROSOFT ACCESS PROGRAM -
everyone
is
inputting but sometime they forget so I have to go back and research
the
missing information. My question is how can I format either in the
"field
name" or in the "data type" to ensure that the check number entered is
in
numerical order if is not, the system is to reject it (like a
control).

Hi Maria
Put the End If just after the MsgBox line so that it now says

Dim LastNum As Long

LastNum = Nz(DMax("[Check_No]", "Loan Check Log - 2008"), 0)

If Me.[Check_No] = LastNum + 1 Then
Me.[Check_No].Undo
MsgBox "That was not the next check number. The previous check number
was " & LastNum
End if

End Sub

Evi


"Maria (MAC)" wrote in message
...
Evi, I did the steps that you indicated below but I am getting the
following
error Message:

THI IS MY INPUT:
Private Sub Check_No_AfterUpdate()

Dim LastNum As Long
LastNum = Nz(DMax("[Check_No]", "Loan Check Log - 2008"), 0)
If Me.[Check_No] = LastNum + 1 Then
Me.[Check_No].Undo
MsgBox "That was not the next check number. The previous check number was
"
& LastNum
End Sub

When I enter "End If" - I get "Block Error - Expected End Sub"
So when I change it to "End Sub" - I get "Compile Error - Block If
withough
End If"

and both times the first line "Private Sub Check ..." is highlighted in
yellow


"Evi" wrote:

Sorry about the jargon, Maria. I didn't know how much you knew.

Use a form to input your data, not a table.
Open the form in Design View.
Right Click on the Check Number control (text box)
Go to Properties
Click on the Event tab
Click next to AfterUpdate, choose Event Procedure, click just right of
that
and open up a code page.

Just above where it says

End Sub

put the code

Dim LastNum as Long
LastNum = NZ(DMax("[Check Number]","TblOfCheckNumbers"),0)
If Me.[Check Number] LastNum + 1 Then
Me.[Check Number].Undo
MsgBox "That was not the next check number. The previous check number
was "
& LastNum
End If


Replace TblOfCheckNumbers with the real name of the table that contains
the
check number.

I'm assuming above that you don't want the code to automatically fill in
the
next check number but if you do, open an 'After Update' code page for a
different control in your form, one you always fill in when you are
entering
a new record (perhaps the date?) and enter:

Dim LastNum as Long
LastNum = NZ(DMax("[Check Number]","TblOfCheckNumbers"),0) + 1
If Me.[Check Number] =LastNum
End If

Put things in the After Update event of a control means that when you
have
finished entering something into a text box, or choosing something with
a
combo box, your code will happen.

One problem you will have though, if you really do have a field in your
table called Date, then before you do much else, you really ought to
rename
it.

Look up Access Reserved Words field names to find out what may happen if
you
don't. Call it XDate or anything else (and be prepared to spend time
mending
queries, reports and forms)
You can always change labels in forms and reports to say Date so long as
the
field doesn't

Evi






"Maria (MAC)" wrote in message
news
Thank you for your response (I apologize for the delay this is my
first
time
using this service and I was having some problems in getting through).

I have never taken a course in ACCESS, which I should, so what I know
is
by
asking questions and just experimenting. So I apoligize if I sound
ignorant,
which I am.

You are correct the cheque number is always be 1 number higher than
the
last
check. I do not know what you mean about "After Update Event of the
Cheque
Number field have this code (swap the .....)" is. Also, where should
I
place
the formula below in the "descripton" under TABLE?

The table has the following "main" information to complete (there are
more):
Date (Text with Input Mask - short date)
Check Number (Number not text)
Amount (Currency)
Payee (Memo)
Issuance (Memo)
Address (Memo)

Thank you very much

"Evi" wrote:

You mean that if anyone enters a cheque then it should always be 1
number
higher than the last check that was entered? Here is a possible, if
you
don't have lots of people inputting at the same time.

In your form, in the After Update Event of the Cheque Number field
have
this
code.
(swap the field and table names for your own) (I'm presuming that
your
ChequeNumbers are numbers and not text)



Dim LastNum as Long
LastNum = NZ(DMax("[CheckNumber]","TblOfCheckNumbers"),0)

If Me.CheckNumber LastNum + 1 Then
Me.CheckNumber.Undo
MsgBox "That was not the next check number. The previous check
number
was "
& LastNum
End If


Evi



"Maria (MAC)" Maria wrote in
message
...
I created a log for cashier's check in MICROSOFT ACCESS PROGRAM -
everyone
is
inputting but sometime they forget so I have to go back and
research
the
missing information. My question is how can I format either in
the
"field
name" or in the "data type" to ensure that the check number
entered is
in
numerical order if is not, the system is to reject it (like a
control).