E
Emma
Hi Bruce,
Can you be more specific about which code to change after I change the
textbox name. I don't change the control source right? When I set it to
Me.AddressDate = Now it does something not exactly what I want though. I just
want it to be Now() when it's a new record (ie default right?).
Can you be more specific about which code to change after I change the
textbox name. I don't change the control source right? When I set it to
Me.AddressDate = Now it does something not exactly what I want though. I just
want it to be Now() when it's a new record (ie default right?).
BruceM said: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.
Emma said: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
BruceM said: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