It is not an auto-incrementing field, and I am not able to modify the
structure of this table to make it so. I did not develop teh
application.
I am not sure of the exact purpose for this field in this table, but this
is a multi-purpose table, supplying "notes" to a variety of other tables,
AR, AP, Inventory Control, etc. All I know is that it is coded this way,
and I am stuck with it. I DO know that this number is not visible to
anyone within the application, although it does effect sorting.
The sad fact is that I have to increment this field manually, there is no
way around it. In order to determine what the last number is, and
therefore what the next number should be, I am using DMax(). I have been
told by someone on this forum, (someone who has been quite helpful in the
past and seems to know his stuff,) that using DMAX() in an append query
is problematic, as each record added will increase the note_id required
for the next record to be added, but in an append query, the DMAX()
function will not reliably return the new higher number. I suspected
something like this, which is why I posed the question.
So I need to do these record appends in VB, and I do not remember all of
the opening and closing junk for accessing a table. My Access VB game is
really rusty.
Phil
Jeff Boyce wrote:
Phil
Are you saying that the unique ID field in the notes table is not an
"auto-incrementing" field? Can it be converted to one if it isn't
already? That way, you wouldn't have to be messing with setting the
unique ID, the db would do that.
The use of ID #s between 19000 and 25000 (or greater than 25000, or any
other attempt to define an "automatic" ID number) suggests that you are
using the "auto-number" as a human-visible identifier. If so, why? If
you were working in Access, using an Access Autonumber field, the
general consensus in the tablesdbdesign newsgroup is that Autonumbers
are not designed to be fit for human consumption...
If you can create a query that returns all the fields/values you wish to
have added to a table (i.e., your notes table), I'm not understanding
why you cannot use an append query to append those records?
Once again, more info, please...
Jeff Boyce
Microsoft Office/Access MVP
The query itself, (if it would work) would do what I want. I have
slimmed it down a bit so it is easier for people to deal with. For
Instance, The note will actually read "retired" along with the current
date, and there are other fields in the notes table that need values as
well.
Basicly, I have a MYSQL database from another application that I am
updating. Note_ID is a unique number, generated by the application,
with a a table holding the "next available" number. Due to the way
that application works, I can not use that "next available" number
reliably. If My updates come during that system's updates, there would
be duplicates. So I have opened up a window, giving me between 19000
and 25000 that the system will never use, so I can use it myself. MY
goal in updating the Notes table is simply to find the next available
number below 25000, to assign to the record. I asked on this forum,
and was answered that doing what I want to do with an append query will
not work. The DMAX() will not update between each row being added. I
sort of suspected this, as Access does not like to do things the wya
you would expect, but has to make things difficult wherever possible.
The suggestion was made that this be done with code, which would allow
for the DMAX() to recalculate after each row.
The last time I had to such things was in Access 2.0.
Phil
You've described how you're trying to do something. What you are
trying to do isn't entirely clear.
Where did "25000" come from? What are you trying to do with the "+1"?
What datatype is the [note_id] field in the "notes" table?
Are you trying to create a collection of note records that include the
text "Retired" when something matches something in a list of special
somethings?<g>
Your SQL doesn't mention anything about "to whom" the note relates ...
do you really want to add 50 note records that only say "Retired", but
not who?
More info, please...!
Jeff Boyce
Microsoft Office/Access MVP
INSERT INTO notes ( note_id, link_table, [note] )
SELECT DMax("[note_id]","notes","[note_id] >25000")+1 AS note_id,
"item" AS link_table, "Retired" AS [note]
FROM [Fifty special items in a list];
I am told, as I suspected, this will not work, as DMAX is not updated
between each record when run as a query.
Now, I am not looking for someone to write the code for me, (Not that
I would turn it down), just a reasonable, preferably online resource
to help me out.
I have a basic understanding of VB, a rusty understanding of
table.field references in VB, and almost no understanding of the
basic needs going into and out of such a routine, like what needs to
be declared, dimensioned, etc. how to open the table, how to close
it.
I have an Access 2.0 book which I will hit if I have to, (the last
time I had to do such things was in Access 2.0), but I expect much of
it would be obsolete, as I am using Access 2002 right now.
thanx
