Numerical in sequence

[Maria (MAC)]

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).

 

[fredg]

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.

 

[Klatuu]

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?

 

[Evi]

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)]

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

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

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]

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

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

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

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).

 

[Maria (MAC)]

Thank you so much for answering and for explaining it

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

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

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



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).

 

[Maria (MAC)]

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

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

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

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



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]

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

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

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

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

:

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



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).