The order of things

[Emma]

I know this is a hard one to explain. Right now I have an address table which
allows the users to enter more then one address per client as they move
around. So on the form address is a subform allowing the user to scroll
through all the address to get to the current one. The problem is I need to
have the current address visible or "on top". Anything's possible right?

 

[BruceM]

If you mean the addresses are in a related table, start by making a query
where you sort them in whatever order will put the current one on top. In
query design view, click View >> SQL. You will see something like:
SELECT [SomeField].SomeTable ...
Change this to:
SELECT TOP 1 [SomeField].SomeTable ...

Make a subform based on this query. Make it just tall enough for one
record. Make another subform based on a query that includes all addresses.
Put that subform below the first one.

This is just to give you an idea of how this can be done. I don't know
exactly what you mean, so my answer will have to be a little vague. If the
three addresses are 100 Aspen ST, 200 Birch ST, and 300 Cherry ST, with 200
Birch ST the most recent, followed by 100 Aspen and 300 Cherry, what exactly
do you want to see on the form? When you add a new address, how do you want
that to happen? At the top of the list?

 

[Emma]

Actually this sounds very complicated. I hope you don't think I have a list
of address going down in a form like a datasheet because actually what I have
is one address showing up in a single form. So the user clicks the arrow keys
in the record selector region and the next address shows up. so I just want
the user to see 200 Birch st then when they click a new button or something
the window goes blank and the first record is the most recent. can I do this
without including a date in it?

If you mean the addresses are in a related table, start by making a query
where you sort them in whatever order will put the current one on top. In
query design view, click View >> SQL. You will see something like:
SELECT [SomeField].SomeTable ...
Change this to:
SELECT TOP 1 [SomeField].SomeTable ...

Make a subform based on this query. Make it just tall enough for one
record. Make another subform based on a query that includes all addresses.
Put that subform below the first one.

This is just to give you an idea of how this can be done. I don't know
exactly what you mean, so my answer will have to be a little vague. If the
three addresses are 100 Aspen ST, 200 Birch ST, and 300 Cherry ST, with 200
Birch ST the most recent, followed by 100 Aspen and 300 Cherry, what exactly
do you want to see on the form? When you add a new address, how do you want
that to happen? At the top of the list?

I know this is a hard one to explain. Right now I have an address table
which
allows the users to enter more then one address per client as they move
around. So on the form address is a subform allowing the user to scroll
through all the address to get to the current one. The problem is I need
to
have the current address visible or "on top". Anything's possible right?

 

[Damon Heron]

If we can assume that the last address entered is the current address, then
sort your sql source based on the ID field in descending order. This will
work in most cases, although ID autonumbers can get scrambled on occasion.

Damon

Actually this sounds very complicated. I hope you don't think I have a
list
of address going down in a form like a datasheet because actually what I
have
is one address showing up in a single form. So the user clicks the arrow
keys
in the record selector region and the next address shows up. so I just
want
the user to see 200 Birch st then when they click a new button or
something
the window goes blank and the first record is the most recent. can I do
this
without including a date in it?

If you mean the addresses are in a related table, start by making a query
where you sort them in whatever order will put the current one on top.
In
query design view, click View >> SQL. You will see something like:
SELECT [SomeField].SomeTable ...
Change this to:
SELECT TOP 1 [SomeField].SomeTable ...

Make a subform based on this query. Make it just tall enough for one
record. Make another subform based on a query that includes all
addresses.
Put that subform below the first one.

This is just to give you an idea of how this can be done. I don't know
exactly what you mean, so my answer will have to be a little vague. If
the
three addresses are 100 Aspen ST, 200 Birch ST, and 300 Cherry ST, with
200
Birch ST the most recent, followed by 100 Aspen and 300 Cherry, what
exactly
do you want to see on the form? When you add a new address, how do you
want
that to happen? At the top of the list?

I know this is a hard one to explain. Right now I have an address table
which
allows the users to enter more then one address per client as they move
around. So on the form address is a subform allowing the user to scroll
through all the address to get to the current one. The problem is I
need
to
have the current address visible or "on top". Anything's possible
right?

 

[Jeff Boyce]

Concepts like "the last" and "the most recent" generally don't have meaning
in the "bucket o' data" Access uses to store table data.

If you don't have a field that stores date/time data, how would YOU know
which one was "the last"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Actually this sounds very complicated. I hope you don't think I have a
list
of address going down in a form like a datasheet because actually what I
have
is one address showing up in a single form. So the user clicks the arrow
keys
in the record selector region and the next address shows up. so I just
want
the user to see 200 Birch st then when they click a new button or
something
the window goes blank and the first record is the most recent. can I do
this
without including a date in it?

If you mean the addresses are in a related table, start by making a query
where you sort them in whatever order will put the current one on top.
In
query design view, click View >> SQL. You will see something like:
SELECT [SomeField].SomeTable ...
Change this to:
SELECT TOP 1 [SomeField].SomeTable ...

Make a subform based on this query. Make it just tall enough for one
record. Make another subform based on a query that includes all
addresses.
Put that subform below the first one.

This is just to give you an idea of how this can be done. I don't know
exactly what you mean, so my answer will have to be a little vague. If
the
three addresses are 100 Aspen ST, 200 Birch ST, and 300 Cherry ST, with
200
Birch ST the most recent, followed by 100 Aspen and 300 Cherry, what
exactly
do you want to see on the form? When you add a new address, how do you
want
that to happen? At the top of the list?

I know this is a hard one to explain. Right now I have an address table
which
allows the users to enter more then one address per client as they move
around. So on the form address is a subform allowing the user to scroll
through all the address to get to the current one. The problem is I
need
to
have the current address visible or "on top". Anything's possible
right?

 

[Ken Sheridan]

You really do need a date column for the reason's Jeff's described. Tables
are sets and sets have no intrinsic order. You cannot reliably use an
incrementing autonumber for this. With a date column you can base the
subform on a query which returns the latest address per client, e.g.

SELECT *
FROM Addresses AS A1
WHERE AddressDate =
(SELECT MAX(AddressDate)
FROM Addresses AS A2
WHERE A2.ClientID = A1.ClientID);

Ken Sheridan
Stafford, England

Actually this sounds very complicated. I hope you don't think I have a list
of address going down in a form like a datasheet because actually what I have
is one address showing up in a single form. So the user clicks the arrow keys
in the record selector region and the next address shows up. so I just want
the user to see 200 Birch st then when they click a new button or something
the window goes blank and the first record is the most recent. can I do this
without including a date in it?

If you mean the addresses are in a related table, start by making a query
where you sort them in whatever order will put the current one on top. In
query design view, click View >> SQL. You will see something like:
SELECT [SomeField].SomeTable ...
Change this to:
SELECT TOP 1 [SomeField].SomeTable ...

Make a subform based on this query. Make it just tall enough for one
record. Make another subform based on a query that includes all addresses.
Put that subform below the first one.

This is just to give you an idea of how this can be done. I don't know
exactly what you mean, so my answer will have to be a little vague. If the
three addresses are 100 Aspen ST, 200 Birch ST, and 300 Cherry ST, with 200
Birch ST the most recent, followed by 100 Aspen and 300 Cherry, what exactly
do you want to see on the form? When you add a new address, how do you want
that to happen? At the top of the list?

I know this is a hard one to explain. Right now I have an address table
which
allows the users to enter more then one address per client as they move
around. So on the form address is a subform allowing the user to scroll
through all the address to get to the current one. The problem is I need
to
have the current address visible or "on top". Anything's possible right?

 

[BruceM]

In addition to what has been written, you can include a date field into
which the default value Date() is inserted. The default value applies only
to new records, so when a new record is created the date is inserted
automatically. You can do this in the table, or you can add a text box
(txtDateField) to the form, bound to the table field, with the Default
Value:
=Date()
as the Control Source.
You can also use VBA code in the form's Current event:
Me.txtDateField.DefaultValue = Date

The text box can be made very small (.01 x .01) with its forecolor and
backcolor properties set to match the background.

You can also use the form's NewRecord property to add the value directly to
a table field (in the Before Insert event, for instance):

If Me.NewRecord Then
Me.DateField = Date
End If

If you use numbers you should increment the number using code rather than
relying on Autonumber. This one takes a bit more explanation, so I will
wait to see if you are interested before going into the details.

Actually this sounds very complicated. I hope you don't think I have a
list
of address going down in a form like a datasheet because actually what I
have
is one address showing up in a single form. So the user clicks the arrow
keys
in the record selector region and the next address shows up. so I just
want
the user to see 200 Birch st then when they click a new button or
something
the window goes blank and the first record is the most recent. can I do
this
without including a date in it?

If you mean the addresses are in a related table, start by making a query
where you sort them in whatever order will put the current one on top.
In
query design view, click View >> SQL. You will see something like:
SELECT [SomeField].SomeTable ...
Change this to:
SELECT TOP 1 [SomeField].SomeTable ...

Make a subform based on this query. Make it just tall enough for one
record. Make another subform based on a query that includes all
addresses.
Put that subform below the first one.

This is just to give you an idea of how this can be done. I don't know
exactly what you mean, so my answer will have to be a little vague. If
the
three addresses are 100 Aspen ST, 200 Birch ST, and 300 Cherry ST, with
200
Birch ST the most recent, followed by 100 Aspen and 300 Cherry, what
exactly
do you want to see on the form? When you add a new address, how do you
want
that to happen? At the top of the list?

I know this is a hard one to explain. Right now I have an address table
which
allows the users to enter more then one address per client as they move
around. So on the form address is a subform allowing the user to scroll
through all the address to get to the current one. The problem is I
need
to
have the current address visible or "on top". Anything's possible
right?

 

[Emma]

The reason I asked about the date column is because unfortunately I already
have the database up and running on the server so alot of data has already
been entered; do I just add a default date to these records say Jan 1, 2009?
Also I don't think the date will work as the user will probably enter more
then one address on a given day. I really want this to work, so would I have
to add time?

 

[BruceM]

You can use the Now function, which includes the time of day, instead of the
Date function. For instance, =Now() instead of = Date().

You cannot add a Default value to an existing record, as the Default Value
only applies to new records. However, you can use an update query to change
all existing dates to the same value. If you are reasonably confident the
existing numbers represent the order of Address entry you could sort by the
date field (in which new records show the current date and time, and
existing records show the Jan. 1, 2009 or whatever you chose), then the
number field.

Another option is to increment the ID number yourself. First, back up the
database. You should do this whenever you get it to a point where a change
works correctly.

Did I mention backing up the database?

Change the autonumber ID field (the one you are using for sorting) to a
number field (Long Integer). Add a text box to the form with its Default
Value set to:
=DMax("[ID_Field]","[TableName]") + 1

This will find the largest value in the number field, and add one to it. In
a multi-user database you will want to add something to guard against two
users entering records at the same time. One way is to put something like
this in the form's Error event:

************
Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

************

Add the following code to the form's code module (it can be at the top, just
under Option Compare Database and Option Explicit, or wherever you choose:

************
Function IncrementField(DataErr)

If DataErr = 3022 Then
Me!ID_Field = DMax("[ID_Field]", "TableName") + 1
IncrementField = acDataErrContinue
End If

End Function

************

I got this from the following source:

http://www.rogersaccesslibrary.com/...?TID=395&SID=9954bc4949z9616c4cf797addfz5799d

The link is all on one line.

 

[Emma]

I was just wondering what the 3022 value is for, is that the limit on the
number of records you can have in a database?

You can use the Now function, which includes the time of day, instead of the
Date function. For instance, =Now() instead of = Date().

You cannot add a Default value to an existing record, as the Default Value
only applies to new records. However, you can use an update query to change
all existing dates to the same value. If you are reasonably confident the
existing numbers represent the order of Address entry you could sort by the
date field (in which new records show the current date and time, and
existing records show the Jan. 1, 2009 or whatever you chose), then the
number field.

Another option is to increment the ID number yourself. First, back up the
database. You should do this whenever you get it to a point where a change
works correctly.

Did I mention backing up the database?

Change the autonumber ID field (the one you are using for sorting) to a
number field (Long Integer). Add a text box to the form with its Default
Value set to:
=DMax("[ID_Field]","[TableName]") + 1

This will find the largest value in the number field, and add one to it. In
a multi-user database you will want to add something to guard against two
users entering records at the same time. One way is to put something like
this in the form's Error event:

************
Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

************

Add the following code to the form's code module (it can be at the top, just
under Option Compare Database and Option Explicit, or wherever you choose:

************
Function IncrementField(DataErr)

If DataErr = 3022 Then
Me!ID_Field = DMax("[ID_Field]", "TableName") + 1
IncrementField = acDataErrContinue
End If

End Function

************

I got this from the following source:

http://www.rogersaccesslibrary.com/...?TID=395&SID=9954bc4949z9616c4cf797addfz5799d

The link is all on one line.

The reason I asked about the date column is because unfortunately I
already
have the database up and running on the server so alot of data has already
been entered; do I just add a default date to these records say Jan 1,
2009?
Also I don't think the date will work as the user will probably enter more
then one address on a given day. I really want this to work, so would I
have
to add time?

 

[BruceM]

It's just an error number. Error 3022 is the ID number for the error about
duplicated primary key information. If two users on different machines
attempt at the same approximate to enter a record with a primary key
incremented by code, they will both get the same number. When the first
user saves the record (by navigating to another record, for instance) the
second user is shut out from using that number, so they get an error 3022
whenthey attempt to save the record. The error event code goes back and
increments a new number. I only have a minute right now, but I will explain
more fully tomorrow.

I was just wondering what the 3022 value is for, is that the limit on the
number of records you can have in a database?

You can use the Now function, which includes the time of day, instead of
the
Date function. For instance, =Now() instead of = Date().

You cannot add a Default value to an existing record, as the Default
Value
only applies to new records. However, you can use an update query to
change
all existing dates to the same value. If you are reasonably confident
the
existing numbers represent the order of Address entry you could sort by
the
date field (in which new records show the current date and time, and
existing records show the Jan. 1, 2009 or whatever you chose), then the
number field.

Another option is to increment the ID number yourself. First, back up
the
database. You should do this whenever you get it to a point where a
change
works correctly.

Did I mention backing up the database?

Change the autonumber ID field (the one you are using for sorting) to a
number field (Long Integer). Add a text box to the form with its Default
Value set to:
=DMax("[ID_Field]","[TableName]") + 1

This will find the largest value in the number field, and add one to it.
In
a multi-user database you will want to add something to guard against two
users entering records at the same time. One way is to put something
like
this in the form's Error event:

************
Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

************

Add the following code to the form's code module (it can be at the top,
just
under Option Compare Database and Option Explicit, or wherever you
choose:

************
Function IncrementField(DataErr)

If DataErr = 3022 Then
Me!ID_Field = DMax("[ID_Field]", "TableName") + 1
IncrementField = acDataErrContinue
End If

End Function

************

I got this from the following source:

http://www.rogersaccesslibrary.com/...?TID=395&SID=9954bc4949z9616c4cf797addfz5799d

The link is all on one line.

The reason I asked about the date column is because unfortunately I
already
have the database up and running on the server so alot of data has
already
been entered; do I just add a default date to these records say Jan 1,
2009?
Also I don't think the date will work as the user will probably enter
more
then one address on a given day. I really want this to work, so would I
have
to add time?

 

[Emma]

I've started trying to implement Ken's version not sure what A1 and A2 are I
thought they were addresses, do I need to declare them?

I'm getting the following parameters popping up A2.ClientID? A1.ClientID?
twice.

It's just an error number. Error 3022 is the ID number for the error about
duplicated primary key information. If two users on different machines
attempt at the same approximate to enter a record with a primary key
incremented by code, they will both get the same number. When the first
user saves the record (by navigating to another record, for instance) the
second user is shut out from using that number, so they get an error 3022
whenthey attempt to save the record. The error event code goes back and
increments a new number. I only have a minute right now, but I will explain
more fully tomorrow.

I was just wondering what the 3022 value is for, is that the limit on the
number of records you can have in a database?

You can use the Now function, which includes the time of day, instead of
the
Date function. For instance, =Now() instead of = Date().

You cannot add a Default value to an existing record, as the Default
Value
only applies to new records. However, you can use an update query to
change
all existing dates to the same value. If you are reasonably confident
the
existing numbers represent the order of Address entry you could sort by
the
date field (in which new records show the current date and time, and
existing records show the Jan. 1, 2009 or whatever you chose), then the
number field.

Another option is to increment the ID number yourself. First, back up
the
database. You should do this whenever you get it to a point where a
change
works correctly.

Did I mention backing up the database?

Change the autonumber ID field (the one you are using for sorting) to a
number field (Long Integer). Add a text box to the form with its Default
Value set to:
=DMax("[ID_Field]","[TableName]") + 1

This will find the largest value in the number field, and add one to it.
In
a multi-user database you will want to add something to guard against two
users entering records at the same time. One way is to put something
like
this in the form's Error event:

************
Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

************

Add the following code to the form's code module (it can be at the top,
just
under Option Compare Database and Option Explicit, or wherever you
choose:

************
Function IncrementField(DataErr)

If DataErr = 3022 Then
Me!ID_Field = DMax("[ID_Field]", "TableName") + 1
IncrementField = acDataErrContinue
End If

End Function

************

I got this from the following source:

http://www.rogersaccesslibrary.com/...?TID=395&SID=9954bc4949z9616c4cf797addfz5799d

The link is all on one line.

The reason I asked about the date column is because unfortunately I
already
have the database up and running on the server so alot of data has
already
been entered; do I just add a default date to these records say Jan 1,
2009?
Also I don't think the date will work as the user will probably enter
more
then one address on a given day. I really want this to work, so would I
have
to add time?

 

[Emma]

There's an A1 table in query design it contains everything an Address table
contains.

It's just an error number. Error 3022 is the ID number for the error about
duplicated primary key information. If two users on different machines
attempt at the same approximate to enter a record with a primary key
incremented by code, they will both get the same number. When the first
user saves the record (by navigating to another record, for instance) the
second user is shut out from using that number, so they get an error 3022
whenthey attempt to save the record. The error event code goes back and
increments a new number. I only have a minute right now, but I will explain
more fully tomorrow.

I was just wondering what the 3022 value is for, is that the limit on the
number of records you can have in a database?

You can use the Now function, which includes the time of day, instead of
the
Date function. For instance, =Now() instead of = Date().

You cannot add a Default value to an existing record, as the Default
Value
only applies to new records. However, you can use an update query to
change
all existing dates to the same value. If you are reasonably confident
the
existing numbers represent the order of Address entry you could sort by
the
date field (in which new records show the current date and time, and
existing records show the Jan. 1, 2009 or whatever you chose), then the
number field.

Another option is to increment the ID number yourself. First, back up
the
database. You should do this whenever you get it to a point where a
change
works correctly.

Did I mention backing up the database?

Change the autonumber ID field (the one you are using for sorting) to a
number field (Long Integer). Add a text box to the form with its Default
Value set to:
=DMax("[ID_Field]","[TableName]") + 1

This will find the largest value in the number field, and add one to it.
In
a multi-user database you will want to add something to guard against two
users entering records at the same time. One way is to put something
like
this in the form's Error event:

************
Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

************

Add the following code to the form's code module (it can be at the top,
just
under Option Compare Database and Option Explicit, or wherever you
choose:

************
Function IncrementField(DataErr)

If DataErr = 3022 Then
Me!ID_Field = DMax("[ID_Field]", "TableName") + 1
IncrementField = acDataErrContinue
End If

End Function

************

I got this from the following source:

http://www.rogersaccesslibrary.com/...?TID=395&SID=9954bc4949z9616c4cf797addfz5799d

The link is all on one line.

The reason I asked about the date column is because unfortunately I
already
have the database up and running on the server so alot of data has
already
been entered; do I just add a default date to these records say Jan 1,
2009?
Also I don't think the date will work as the user will probably enter
more
then one address on a given day. I really want this to work, so would I
have
to add time?

 

[Emma]

Ok I Implemented Ken's version and it's doing something bringing up the first
address but no other address as AddressDate is always coming up with 12:00:07
a time instead of a date and time:

Here's my code:

Private Sub Form_Current()
Me.AddressDate.DefaultValue = Date
End Sub

Please note I don't know how to use the Now() in this situation

There's an A1 table in query design it contains everything an Address table
contains.

It's just an error number. Error 3022 is the ID number for the error about
duplicated primary key information. If two users on different machines
attempt at the same approximate to enter a record with a primary key
incremented by code, they will both get the same number. When the first
user saves the record (by navigating to another record, for instance) the
second user is shut out from using that number, so they get an error 3022
whenthey attempt to save the record. The error event code goes back and
increments a new number. I only have a minute right now, but I will explain
more fully tomorrow.

I was just wondering what the 3022 value is for, is that the limit on the
number of records you can have in a database?

:

You can use the Now function, which includes the time of day, instead of
the
Date function. For instance, =Now() instead of = Date().

You cannot add a Default value to an existing record, as the Default
Value
only applies to new records. However, you can use an update query to
change
all existing dates to the same value. If you are reasonably confident
the
existing numbers represent the order of Address entry you could sort by
the
date field (in which new records show the current date and time, and
existing records show the Jan. 1, 2009 or whatever you chose), then the
number field.

Another option is to increment the ID number yourself. First, back up
the
database. You should do this whenever you get it to a point where a
change
works correctly.

Did I mention backing up the database?

Change the autonumber ID field (the one you are using for sorting) to a
number field (Long Integer). Add a text box to the form with its Default
Value set to:
=DMax("[ID_Field]","[TableName]") + 1

This will find the largest value in the number field, and add one to it.
In
a multi-user database you will want to add something to guard against two
users entering records at the same time. One way is to put something
like
this in the form's Error event:

************
Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

************

Add the following code to the form's code module (it can be at the top,
just
under Option Compare Database and Option Explicit, or wherever you
choose:

************
Function IncrementField(DataErr)

If DataErr = 3022 Then
Me!ID_Field = DMax("[ID_Field]", "TableName") + 1
IncrementField = acDataErrContinue
End If

End Function

************

I got this from the following source:

http://www.rogersaccesslibrary.com/...?TID=395&SID=9954bc4949z9616c4cf797addfz5799d

The link is all on one line.

The reason I asked about the date column is because unfortunately I
already
have the database up and running on the server so alot of data has
already
been entered; do I just add a default date to these records say Jan 1,
2009?
Also I don't think the date will work as the user will probably enter
more
then one address on a given day. I really want this to work, so would I
have
to add time?

 

[BruceM]

This link contains among other things some good basic information about SQL:

http://allenbrowne.com/casu-22.html

This is Ken's SQL:

SELECT *
FROM Addresses AS A1
WHERE AddressDate =
(SELECT MAX(AddressDate)
FROM Addresses AS A2
WHERE A2.ClientID = A1.ClientID);

The first part means to select everything (*) from the Addresses table
(which is assigned the alias A1) where AddressDate matches the address date
in a subquery. The subquery is the SELECT in parentheses, which says to
select the maximum AddressDate from the Addresses table (assigned the alias
A2) from among the records in which ClientID matches ClientID in A1 (all
records). Modify the SQL if necessary so that Addresses matches the name of
the table containing addresses, and ClientID matches the ID field for the
client in your table. Open a new query in design view, select no tables,
and click View >> SQL. Paste the SQL, then switch to datasheet view. You
should see a row showing the maximum value (latest record) for each
ClientID. It sounds like this is what is happening. You can use this query
as the row source for a subform that will show only the latest address. The
aliases are because there are two instances of Addresses, so Access needs a
way to tell them apart. You can use an alias in any query, which sometimes
makes it easier to read the SQL. Just right click the table in query design
view, click Properties, and assign an alias.

To see all of the addresses, use a query based on all records as the Record
Source for another subform. It may be something like this:

SELECT *
FROM Addresses
ORDER BY AddressDate;

Use ClientID as the linking field betweent he main form and the subform.

If you are seeing the time rather than the date in query datasheet view,
check the formatting for AddressDate in your table.

Use Now just as you would Date:

Me.AddressDate.DefaultValue = Now

Note that in VBA you do not use the parentheses after Date() or Now(). You
can put them in, but the VBA editor will probably remove them. In a text
box or query expression you need the parentheses.

Date() actually includes the time portion of the date, but it is always
midnight. Now() includes the time and the date.

Ok I Implemented Ken's version and it's doing something bringing up the
first
address but no other address as AddressDate is always coming up with
12:00:07
a time instead of a date and time:

Here's my code:

Private Sub Form_Current()
Me.AddressDate.DefaultValue = Date
End Sub

Please note I don't know how to use the Now() in this situation

There's an A1 table in query design it contains everything an Address
table
contains.

It's just an error number. Error 3022 is the ID number for the error
about
duplicated primary key information. If two users on different machines
attempt at the same approximate to enter a record with a primary key
incremented by code, they will both get the same number. When the
first
user saves the record (by navigating to another record, for instance)
the
second user is shut out from using that number, so they get an error
3022
whenthey attempt to save the record. The error event code goes back
and
increments a new number. I only have a minute right now, but I will
explain
more fully tomorrow.

I was just wondering what the 3022 value is for, is that the limit on
the
number of records you can have in a database?

:

You can use the Now function, which includes the time of day,
instead of
the
Date function. For instance, =Now() instead of = Date().

You cannot add a Default value to an existing record, as the Default
Value
only applies to new records. However, you can use an update query
to
change
all existing dates to the same value. If you are reasonably
confident
the
existing numbers represent the order of Address entry you could sort
by
the
date field (in which new records show the current date and time, and
existing records show the Jan. 1, 2009 or whatever you chose), then
the
number field.

Another option is to increment the ID number yourself. First, back
up
the
database. You should do this whenever you get it to a point where a
change
works correctly.

Did I mention backing up the database?

Change the autonumber ID field (the one you are using for sorting)
to a
number field (Long Integer). Add a text box to the form with its
Default
Value set to:
=DMax("[ID_Field]","[TableName]") + 1

This will find the largest value in the number field, and add one to
it.
In
a multi-user database you will want to add something to guard
against two
users entering records at the same time. One way is to put
something
like
this in the form's Error event:

************
Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

************

Add the following code to the form's code module (it can be at the
top,
just
under Option Compare Database and Option Explicit, or wherever you
choose:

************
Function IncrementField(DataErr)

If DataErr = 3022 Then
Me!ID_Field = DMax("[ID_Field]", "TableName") + 1
IncrementField = acDataErrContinue
End If

End Function

************

I got this from the following source:

http://www.rogersaccesslibrary.com/...?TID=395&SID=9954bc4949z9616c4cf797addfz5799d

The link is all on one line.

The reason I asked about the date column is because unfortunately
I
already
have the database up and running on the server so alot of data has
already
been entered; do I just add a default date to these records say
Jan 1,
2009?
Also I don't think the date will work as the user will probably
enter
more
then one address on a given day. I really want this to work, so
would I
have
to add time?

 

[Emma]

When I use the Now statement it comes up as #Name?. I don't understand what
is suppose to happen right now the first record appears in the subform and a
record appears in the first form, two different records but the subform when
I scroll through using the record selectors goes in the default order oldest
to newest.

This link contains among other things some good basic information about SQL:

http://allenbrowne.com/casu-22.html

This is Ken's SQL:

SELECT *
FROM Addresses AS A1
WHERE AddressDate =
(SELECT MAX(AddressDate)
FROM Addresses AS A2
WHERE A2.ClientID = A1.ClientID);

The first part means to select everything (*) from the Addresses table
(which is assigned the alias A1) where AddressDate matches the address date
in a subquery. The subquery is the SELECT in parentheses, which says to
select the maximum AddressDate from the Addresses table (assigned the alias
A2) from among the records in which ClientID matches ClientID in A1 (all
records). Modify the SQL if necessary so that Addresses matches the name of
the table containing addresses, and ClientID matches the ID field for the
client in your table. Open a new query in design view, select no tables,
and click View >> SQL. Paste the SQL, then switch to datasheet view. You
should see a row showing the maximum value (latest record) for each
ClientID. It sounds like this is what is happening. You can use this query
as the row source for a subform that will show only the latest address. The
aliases are because there are two instances of Addresses, so Access needs a
way to tell them apart. You can use an alias in any query, which sometimes
makes it easier to read the SQL. Just right click the table in query design
view, click Properties, and assign an alias.

To see all of the addresses, use a query based on all records as the Record
Source for another subform. It may be something like this:

SELECT *
FROM Addresses
ORDER BY AddressDate;

Use ClientID as the linking field betweent he main form and the subform.

If you are seeing the time rather than the date in query datasheet view,
check the formatting for AddressDate in your table.

Use Now just as you would Date:

Me.AddressDate.DefaultValue = Now

Note that in VBA you do not use the parentheses after Date() or Now(). You
can put them in, but the VBA editor will probably remove them. In a text
box or query expression you need the parentheses.

Date() actually includes the time portion of the date, but it is always
midnight. Now() includes the time and the date.

Ok I Implemented Ken's version and it's doing something bringing up the
first
address but no other address as AddressDate is always coming up with
12:00:07
a time instead of a date and time:

Here's my code:

Private Sub Form_Current()
Me.AddressDate.DefaultValue = Date
End Sub

Please note I don't know how to use the Now() in this situation

There's an A1 table in query design it contains everything an Address
table
contains.

:

It's just an error number. Error 3022 is the ID number for the error
about
duplicated primary key information. If two users on different machines
attempt at the same approximate to enter a record with a primary key
incremented by code, they will both get the same number. When the
first
user saves the record (by navigating to another record, for instance)
the
second user is shut out from using that number, so they get an error
3022
whenthey attempt to save the record. The error event code goes back
and
increments a new number. I only have a minute right now, but I will
explain
more fully tomorrow.

I was just wondering what the 3022 value is for, is that the limit on
the
number of records you can have in a database?

:

You can use the Now function, which includes the time of day,
instead of
the
Date function. For instance, =Now() instead of = Date().

You cannot add a Default value to an existing record, as the Default
Value
only applies to new records. However, you can use an update query
to
change
all existing dates to the same value. If you are reasonably
confident
the
existing numbers represent the order of Address entry you could sort
by
the
date field (in which new records show the current date and time, and
existing records show the Jan. 1, 2009 or whatever you chose), then
the
number field.

Another option is to increment the ID number yourself. First, back
up
the
database. You should do this whenever you get it to a point where a
change
works correctly.

Did I mention backing up the database?

Change the autonumber ID field (the one you are using for sorting)
to a
number field (Long Integer). Add a text box to the form with its
Default
Value set to:
=DMax("[ID_Field]","[TableName]") + 1

This will find the largest value in the number field, and add one to
it.
In
a multi-user database you will want to add something to guard
against two
users entering records at the same time. One way is to put
something
like
this in the form's Error event:

************
Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

************

Add the following code to the form's code module (it can be at the
top,
just
under Option Compare Database and Option Explicit, or wherever you
choose:

************
Function IncrementField(DataErr)

If DataErr = 3022 Then
Me!ID_Field = DMax("[ID_Field]", "TableName") + 1
IncrementField = acDataErrContinue
End If

End Function

************

I got this from the following source:

http://www.rogersaccesslibrary.com/...?TID=395&SID=9954bc4949z9616c4cf797addfz5799d

The link is all on one line.

The reason I asked about the date column is because unfortunately
I
already
have the database up and running on the server so alot of data has
already
been entered; do I just add a default date to these records say
Jan 1,
2009?
Also I don't think the date will work as the user will probably
enter
more
then one address on a given day. I really want this to work, so
would I
have
to add time?

 

[Emma]

Ok I got it working using Descending instead of Ascending
and

SELECT *
FROM Addresses
ORDER BY AddressDate;

now I would like to know how to get the Now working?

When I use the Now statement it comes up as #Name?. I don't understand what
is suppose to happen right now the first record appears in the subform and a
record appears in the first form, two different records but the subform when
I scroll through using the record selectors goes in the default order oldest
to newest.

This link contains among other things some good basic information about SQL:

http://allenbrowne.com/casu-22.html

This is Ken's SQL:

SELECT *
FROM Addresses AS A1
WHERE AddressDate =
(SELECT MAX(AddressDate)
FROM Addresses AS A2
WHERE A2.ClientID = A1.ClientID);

The first part means to select everything (*) from the Addresses table
(which is assigned the alias A1) where AddressDate matches the address date
in a subquery. The subquery is the SELECT in parentheses, which says to
select the maximum AddressDate from the Addresses table (assigned the alias
A2) from among the records in which ClientID matches ClientID in A1 (all
records). Modify the SQL if necessary so that Addresses matches the name of
the table containing addresses, and ClientID matches the ID field for the
client in your table. Open a new query in design view, select no tables,
and click View >> SQL. Paste the SQL, then switch to datasheet view. You
should see a row showing the maximum value (latest record) for each
ClientID. It sounds like this is what is happening. You can use this query
as the row source for a subform that will show only the latest address. The
aliases are because there are two instances of Addresses, so Access needs a
way to tell them apart. You can use an alias in any query, which sometimes
makes it easier to read the SQL. Just right click the table in query design
view, click Properties, and assign an alias.

To see all of the addresses, use a query based on all records as the Record
Source for another subform. It may be something like this:

SELECT *
FROM Addresses
ORDER BY AddressDate;

Use ClientID as the linking field betweent he main form and the subform.

If you are seeing the time rather than the date in query datasheet view,
check the formatting for AddressDate in your table.

Use Now just as you would Date:

Me.AddressDate.DefaultValue = Now

Note that in VBA you do not use the parentheses after Date() or Now(). You
can put them in, but the VBA editor will probably remove them. In a text
box or query expression you need the parentheses.

Date() actually includes the time portion of the date, but it is always
midnight. Now() includes the time and the date.

Ok I Implemented Ken's version and it's doing something bringing up the
first
address but no other address as AddressDate is always coming up with
12:00:07
a time instead of a date and time:

Here's my code:

Private Sub Form_Current()
Me.AddressDate.DefaultValue = Date
End Sub

Please note I don't know how to use the Now() in this situation

:


There's an A1 table in query design it contains everything an Address
table
contains.

:

It's just an error number. Error 3022 is the ID number for the error
about
duplicated primary key information. If two users on different machines
attempt at the same approximate to enter a record with a primary key
incremented by code, they will both get the same number. When the
first
user saves the record (by navigating to another record, for instance)
the
second user is shut out from using that number, so they get an error
3022
whenthey attempt to save the record. The error event code goes back
and
increments a new number. I only have a minute right now, but I will
explain
more fully tomorrow.

I was just wondering what the 3022 value is for, is that the limit on
the
number of records you can have in a database?

:

You can use the Now function, which includes the time of day,
instead of
the
Date function. For instance, =Now() instead of = Date().

You cannot add a Default value to an existing record, as the Default
Value
only applies to new records. However, you can use an update query
to
change
all existing dates to the same value. If you are reasonably
confident
the
existing numbers represent the order of Address entry you could sort
by
the
date field (in which new records show the current date and time, and
existing records show the Jan. 1, 2009 or whatever you chose), then
the
number field.

Another option is to increment the ID number yourself. First, back
up
the
database. You should do this whenever you get it to a point where a
change
works correctly.

Did I mention backing up the database?

Change the autonumber ID field (the one you are using for sorting)
to a
number field (Long Integer). Add a text box to the form with its
Default
Value set to:
=DMax("[ID_Field]","[TableName]") + 1

This will find the largest value in the number field, and add one to
it.
In
a multi-user database you will want to add something to guard
against two
users entering records at the same time. One way is to put
something
like
this in the form's Error event:

************
Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

************

Add the following code to the form's code module (it can be at the
top,
just
under Option Compare Database and Option Explicit, or wherever you
choose:

************
Function IncrementField(DataErr)

If DataErr = 3022 Then
Me!ID_Field = DMax("[ID_Field]", "TableName") + 1
IncrementField = acDataErrContinue
End If

End Function

************

I got this from the following source:

http://www.rogersaccesslibrary.com/...?TID=395&SID=9954bc4949z9616c4cf797addfz5799d

The link is all on one line.

The reason I asked about the date column is because unfortunately
I
already
have the database up and running on the server so alot of data has
already
been entered; do I just add a default date to these records say
Jan 1,
2009?
Also I don't think the date will work as the user will probably
enter
more
then one address on a given day. I really want this to work, so
would I
have
to add time?

 

[BruceM]

Open the database and press Ctrl + G. This should open the VBA editor to
the Immediate window. In the Immediate window (it's labeled) type this:
?Now()
Type it exactly as I have entered it, and press Enter. If Now is working
properly you should see the current date and time. If you do, the problem
could be that you are using Now as a name somewhere(for a field or control,
maybe), and Access is confused. If you still get the #Name error in the
immediate window, it could be a problem with References. More information
here:

http://allenbrowne.com/ser-38.html

Ok I got it working using Descending instead of Ascending
and

SELECT *
FROM Addresses
ORDER BY AddressDate;

now I would like to know how to get the Now working?

When I use the Now statement it comes up as #Name?. I don't understand
what
is suppose to happen right now the first record appears in the subform
and a
record appears in the first form, two different records but the subform
when
I scroll through using the record selectors goes in the default order
oldest
to newest.

This link contains among other things some good basic information about
SQL:

http://allenbrowne.com/casu-22.html

This is Ken's SQL:

SELECT *
FROM Addresses AS A1
WHERE AddressDate =
(SELECT MAX(AddressDate)
FROM Addresses AS A2
WHERE A2.ClientID = A1.ClientID);

The first part means to select everything (*) from the Addresses table
(which is assigned the alias A1) where AddressDate matches the address
date
in a subquery. The subquery is the SELECT in parentheses, which says
to
select the maximum AddressDate from the Addresses table (assigned the
alias
A2) from among the records in which ClientID matches ClientID in A1
(all
records). Modify the SQL if necessary so that Addresses matches the
name of
the table containing addresses, and ClientID matches the ID field for
the
client in your table. Open a new query in design view, select no
tables,
and click View >> SQL. Paste the SQL, then switch to datasheet view.
You
should see a row showing the maximum value (latest record) for each
ClientID. It sounds like this is what is happening. You can use this
query
as the row source for a subform that will show only the latest address.
The
aliases are because there are two instances of Addresses, so Access
needs a
way to tell them apart. You can use an alias in any query, which
sometimes
makes it easier to read the SQL. Just right click the table in query
design
view, click Properties, and assign an alias.

To see all of the addresses, use a query based on all records as the
Record
Source for another subform. It may be something like this:

SELECT *
FROM Addresses
ORDER BY AddressDate;

Use ClientID as the linking field betweent he main form and the
subform.

If you are seeing the time rather than the date in query datasheet
view,
check the formatting for AddressDate in your table.

Use Now just as you would Date:

Me.AddressDate.DefaultValue = Now

Note that in VBA you do not use the parentheses after Date() or Now().
You
can put them in, but the VBA editor will probably remove them. In a
text
box or query expression you need the parentheses.

Date() actually includes the time portion of the date, but it is always
midnight. Now() includes the time and the date.

Ok I Implemented Ken's version and it's doing something bringing up
the
first
address but no other address as AddressDate is always coming up with
12:00:07
a time instead of a date and time:

Here's my code:

Private Sub Form_Current()
Me.AddressDate.DefaultValue = Date
End Sub

Please note I don't know how to use the Now() in this situation

:


There's an A1 table in query design it contains everything an
Address
table
contains.

:

It's just an error number. Error 3022 is the ID number for the
error
about
duplicated primary key information. If two users on different
machines
attempt at the same approximate to enter a record with a primary
key
incremented by code, they will both get the same number. When the
first
user saves the record (by navigating to another record, for
instance)
the
second user is shut out from using that number, so they get an
error
3022
whenthey attempt to save the record. The error event code goes
back
and
increments a new number. I only have a minute right now, but I
will
explain
more fully tomorrow.

I was just wondering what the 3022 value is for, is that the
limit on
the
number of records you can have in a database?

:

You can use the Now function, which includes the time of day,
instead of
the
Date function. For instance, =Now() instead of = Date().

You cannot add a Default value to an existing record, as the
Default
Value
only applies to new records. However, you can use an update
query
to
change
all existing dates to the same value. If you are reasonably
confident
the
existing numbers represent the order of Address entry you could
sort
by
the
date field (in which new records show the current date and
time, and
existing records show the Jan. 1, 2009 or whatever you chose),
then
the
number field.

Another option is to increment the ID number yourself. First,
back
up
the
database. You should do this whenever you get it to a point
where a
change
works correctly.

Did I mention backing up the database?

Change the autonumber ID field (the one you are using for
sorting)
to a
number field (Long Integer). Add a text box to the form with
its
Default
Value set to:
=DMax("[ID_Field]","[TableName]") + 1

This will find the largest value in the number field, and add
one to
it.
In
a multi-user database you will want to add something to guard
against two
users entering records at the same time. One way is to put
something
like
this in the form's Error event:

************
Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

************

Add the following code to the form's code module (it can be at
the
top,
just
under Option Compare Database and Option Explicit, or wherever
you
choose:

************
Function IncrementField(DataErr)

If DataErr = 3022 Then
Me!ID_Field = DMax("[ID_Field]", "TableName") + 1
IncrementField = acDataErrContinue
End If

End Function

************

I got this from the following source:

http://www.rogersaccesslibrary.com/...?TID=395&SID=9954bc4949z9616c4cf797addfz5799d

The link is all on one line.

The reason I asked about the date column is because
unfortunately
I
already
have the database up and running on the server so alot of
data has
already
been entered; do I just add a default date to these records
say
Jan 1,
2009?
Also I don't think the date will work as the user will
probably
enter
more
then one address on a given day. I really want this to work,
so
would I
have
to add time?

 

[Emma]

Yes I am getting the correct date and time in the immediate window.
Here's your code that I'm using:
Private Sub Form_Current()
Me.AddressDate.DefaultValue = Now
End Sub

Open the database and press Ctrl + G. This should open the VBA editor to
the Immediate window. In the Immediate window (it's labeled) type this:
?Now()
Type it exactly as I have entered it, and press Enter. If Now is working
properly you should see the current date and time. If you do, the problem
could be that you are using Now as a name somewhere(for a field or control,
maybe), and Access is confused. If you still get the #Name error in the
immediate window, it could be a problem with References. More information
here:

http://allenbrowne.com/ser-38.html

Ok I got it working using Descending instead of Ascending
and

SELECT *
FROM Addresses
ORDER BY AddressDate;

now I would like to know how to get the Now working?

When I use the Now statement it comes up as #Name?. I don't understand
what
is suppose to happen right now the first record appears in the subform
and a
record appears in the first form, two different records but the subform
when
I scroll through using the record selectors goes in the default order
oldest
to newest.

:

This link contains among other things some good basic information about
SQL:

http://allenbrowne.com/casu-22.html

This is Ken's SQL:

SELECT *
FROM Addresses AS A1
WHERE AddressDate =
(SELECT MAX(AddressDate)
FROM Addresses AS A2
WHERE A2.ClientID = A1.ClientID);

The first part means to select everything (*) from the Addresses table
(which is assigned the alias A1) where AddressDate matches the address
date
in a subquery. The subquery is the SELECT in parentheses, which says
to
select the maximum AddressDate from the Addresses table (assigned the
alias
A2) from among the records in which ClientID matches ClientID in A1
(all
records). Modify the SQL if necessary so that Addresses matches the
name of
the table containing addresses, and ClientID matches the ID field for
the
client in your table. Open a new query in design view, select no
tables,
and click View >> SQL. Paste the SQL, then switch to datasheet view.
You
should see a row showing the maximum value (latest record) for each
ClientID. It sounds like this is what is happening. You can use this
query
as the row source for a subform that will show only the latest address.
The
aliases are because there are two instances of Addresses, so Access
needs a
way to tell them apart. You can use an alias in any query, which
sometimes
makes it easier to read the SQL. Just right click the table in query
design
view, click Properties, and assign an alias.

To see all of the addresses, use a query based on all records as the
Record
Source for another subform. It may be something like this:

SELECT *
FROM Addresses
ORDER BY AddressDate;

Use ClientID as the linking field betweent he main form and the
subform.

If you are seeing the time rather than the date in query datasheet
view,
check the formatting for AddressDate in your table.

Use Now just as you would Date:

Me.AddressDate.DefaultValue = Now

Note that in VBA you do not use the parentheses after Date() or Now().
You
can put them in, but the VBA editor will probably remove them. In a
text
box or query expression you need the parentheses.

Date() actually includes the time portion of the date, but it is always
midnight. Now() includes the time and the date.

Ok I Implemented Ken's version and it's doing something bringing up
the
first
address but no other address as AddressDate is always coming up with
12:00:07
a time instead of a date and time:

Here's my code:

Private Sub Form_Current()
Me.AddressDate.DefaultValue = Date
End Sub

Please note I don't know how to use the Now() in this situation

:


There's an A1 table in query design it contains everything an
Address
table
contains.

:

It's just an error number. Error 3022 is the ID number for the
error
about
duplicated primary key information. If two users on different
machines
attempt at the same approximate to enter a record with a primary
key
incremented by code, they will both get the same number. When the
first
user saves the record (by navigating to another record, for
instance)
the
second user is shut out from using that number, so they get an
error
3022
whenthey attempt to save the record. The error event code goes
back
and
increments a new number. I only have a minute right now, but I
will
explain
more fully tomorrow.

I was just wondering what the 3022 value is for, is that the
limit on
the
number of records you can have in a database?

:

You can use the Now function, which includes the time of day,
instead of
the
Date function. For instance, =Now() instead of = Date().

You cannot add a Default value to an existing record, as the
Default
Value
only applies to new records. However, you can use an update
query
to
change
all existing dates to the same value. If you are reasonably
confident
the
existing numbers represent the order of Address entry you could
sort
by
the
date field (in which new records show the current date and
time, and
existing records show the Jan. 1, 2009 or whatever you chose),
then
the
number field.

Another option is to increment the ID number yourself. First,
back
up
the
database. You should do this whenever you get it to a point
where a
change
works correctly.

Did I mention backing up the database?

Change the autonumber ID field (the one you are using for
sorting)
to a
number field (Long Integer). Add a text box to the form with
its
Default
Value set to:
=DMax("[ID_Field]","[TableName]") + 1

This will find the largest value in the number field, and add
one to
it.
In
a multi-user database you will want to add something to guard
against two
users entering records at the same time. One way is to put
something
like
this in the form's Error event:

************
Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

************

Add the following code to the form's code module (it can be at
the
top,
just
under Option Compare Database and Option Explicit, or wherever
you
choose:

************
Function IncrementField(DataErr)

If DataErr = 3022 Then
Me!ID_Field = DMax("[ID_Field]", "TableName") + 1
IncrementField = acDataErrContinue
End If

End Function

************

I got this from the following source:

http://www.rogersaccesslibrary.com/...?TID=395&SID=9954bc4949z9616c4cf797addfz5799d

The link is all on one line.

The reason I asked about the date column is because
unfortunately
I
already
have the database up and running on the server so alot of
data has
already
been entered; do I just add a default date to these records
say
Jan 1,
2009?
Also I don't think the date will work as the user will
probably
enter
more
then one address on a given day. I really want this to work,
so
would I
have
to add time?

 

[BruceM]

That works with Date, but not with Now? Hmmm. Do you have Option Explicit
at the top of the code window, just below Option Compare Database. If not,
add it. In any case, click Debug >> Compile.

If you are setting a default value in a form you need to refer to the text
box, not the field. If they both have the same name it could cause Access
to be confused. Better to name the text box something like txtAddressDate,
and to change the code accordingly. Better yet, rewrite the line of code.
Sometimes Access seems to get confused by edited code. Also, I would be
sure you are not setting the default value in the table, and again in the
form. I don't know if that could cause the problem, but I doubt it would
help. Pick one or the other.

Yes I am getting the correct date and time in the immediate window.
Here's your code that I'm using:
Private Sub Form_Current()
Me.AddressDate.DefaultValue = Now
End Sub

Open the database and press Ctrl + G. This should open the VBA editor to
the Immediate window. In the Immediate window (it's labeled) type this:
?Now()
Type it exactly as I have entered it, and press Enter. If Now is working
properly you should see the current date and time. If you do, the
problem
could be that you are using Now as a name somewhere(for a field or
control,
maybe), and Access is confused. If you still get the #Name error in the
immediate window, it could be a problem with References. More
information
here:

http://allenbrowne.com/ser-38.html

Ok I got it working using Descending instead of Ascending
and
SELECT *
FROM Addresses
ORDER BY AddressDate;

now I would like to know how to get the Now working?

:

When I use the Now statement it comes up as #Name?. I don't understand
what
is suppose to happen right now the first record appears in the subform
and a
record appears in the first form, two different records but the
subform
when
I scroll through using the record selectors goes in the default order
oldest
to newest.

:

This link contains among other things some good basic information
about
SQL:

http://allenbrowne.com/casu-22.html

This is Ken's SQL:

SELECT *
FROM Addresses AS A1
WHERE AddressDate =
(SELECT MAX(AddressDate)
FROM Addresses AS A2
WHERE A2.ClientID = A1.ClientID);

The first part means to select everything (*) from the Addresses
table
(which is assigned the alias A1) where AddressDate matches the
address
date
in a subquery. The subquery is the SELECT in parentheses, which
says
to
select the maximum AddressDate from the Addresses table (assigned
the
alias
A2) from among the records in which ClientID matches ClientID in A1
(all
records). Modify the SQL if necessary so that Addresses matches the
name of
the table containing addresses, and ClientID matches the ID field
for
the
client in your table. Open a new query in design view, select no
tables,
and click View >> SQL. Paste the SQL, then switch to datasheet
view.
You
should see a row showing the maximum value (latest record) for each
ClientID. It sounds like this is what is happening. You can use
this
query
as the row source for a subform that will show only the latest
address.
The
aliases are because there are two instances of Addresses, so Access
needs a
way to tell them apart. You can use an alias in any query, which
sometimes
makes it easier to read the SQL. Just right click the table in
query
design
view, click Properties, and assign an alias.

To see all of the addresses, use a query based on all records as the
Record
Source for another subform. It may be something like this:

SELECT *
FROM Addresses
ORDER BY AddressDate;

Use ClientID as the linking field betweent he main form and the
subform.

If you are seeing the time rather than the date in query datasheet
view,
check the formatting for AddressDate in your table.

Use Now just as you would Date:

Me.AddressDate.DefaultValue = Now

Note that in VBA you do not use the parentheses after Date() or
Now().
You
can put them in, but the VBA editor will probably remove them. In a
text
box or query expression you need the parentheses.

Date() actually includes the time portion of the date, but it is
always
midnight. Now() includes the time and the date.

Ok I Implemented Ken's version and it's doing something bringing
up
the
first
address but no other address as AddressDate is always coming up
with
12:00:07
a time instead of a date and time:

Here's my code:

Private Sub Form_Current()
Me.AddressDate.DefaultValue = Date
End Sub

Please note I don't know how to use the Now() in this situation

:


There's an A1 table in query design it contains everything an
Address
table
contains.

:

It's just an error number. Error 3022 is the ID number for the
error
about
duplicated primary key information. If two users on different
machines
attempt at the same approximate to enter a record with a
primary
key
incremented by code, they will both get the same number. When
the
first
user saves the record (by navigating to another record, for
instance)
the
second user is shut out from using that number, so they get an
error
3022
whenthey attempt to save the record. The error event code goes
back
and
increments a new number. I only have a minute right now, but I
will
explain
more fully tomorrow.

I was just wondering what the 3022 value is for, is that the
limit on
the
number of records you can have in a database?

:

You can use the Now function, which includes the time of
day,
instead of
the
Date function. For instance, =Now() instead of = Date().

You cannot add a Default value to an existing record, as the
Default
Value
only applies to new records. However, you can use an update
query
to
change
all existing dates to the same value. If you are reasonably
confident
the
existing numbers represent the order of Address entry you
could
sort
by
the
date field (in which new records show the current date and
time, and
existing records show the Jan. 1, 2009 or whatever you
chose),
then
the
number field.

Another option is to increment the ID number yourself.
First,
back
up
the
database. You should do this whenever you get it to a point
where a
change
works correctly.

Did I mention backing up the database?

Change the autonumber ID field (the one you are using for
sorting)
to a
number field (Long Integer). Add a text box to the form
with
its
Default
Value set to:
=DMax("[ID_Field]","[TableName]") + 1

This will find the largest value in the number field, and
add
one to
it.
In
a multi-user database you will want to add something to
guard
against two
users entering records at the same time. One way is to put
something
like
this in the form's Error event:

************
Private Sub Form_Error(DataErr As Integer, Response As
Integer)
On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

************

Add the following code to the form's code module (it can be
at
the
top,
just
under Option Compare Database and Option Explicit, or
wherever
you
choose:

************
Function IncrementField(DataErr)

If DataErr = 3022 Then
Me!ID_Field = DMax("[ID_Field]", "TableName") + 1
IncrementField = acDataErrContinue
End If

End Function

************

I got this from the following source:

http://www.rogersaccesslibrary.com/...?TID=395&SID=9954bc4949z9616c4cf797addfz5799d

The link is all on one line.

The reason I asked about the date column is because
unfortunately
I
already
have the database up and running on the server so alot of
data has
already
been entered; do I just add a default date to these
records
say
Jan 1,
2009?
Also I don't think the date will work as the user will
probably
enter
more
then one address on a given day. I really want this to
work,
so
would I
have
to add time?