Autonumber needed in form when new record opens

[Guest]

Access 2002
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext
This structure of code along with setting a couple of fields is what I am
trying to use to populate the key field of a table on a form. The field is
an autonumber and I keep getting error 2105 when attempting this. If I try
to save the keyfields I recieve a 170 error on the DoCmd.RunSQL stSQL, no
statement.
Help would be welcomed.

Thanks

 

[Dirk Goldgar]

Access 2002
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext
This structure of code along with setting a couple of fields is what
I am trying to use to populate the key field of a table on a form.
The field is an autonumber and I keep getting error 2105 when
attempting this. If I try to save the keyfields I recieve a 170
error on the DoCmd.RunSQL stSQL, no statement.

What exactly is your purpose here? If you simply assign some bound
control on the form to itself, the autonumber value will be generated --
in an MDB file, anyway. E.g.,

DoCmd.GoToRecord , , acNewRec
Forms!YourForm!SomeControl = Forms!YourForm!SomeControl

The record will be dirtied but not saved, and the autonumber will be
generated.

Are you trying to get the autonumber so that you can create related
records in other tables? If so, you won't be able to do that until the
record is actually saved.

What are you really trying to accomplish?

 

[Guest]

The autogenerated number goes throughout the application. It is generated on
this form and used to come up with the acPrevious and acNext code. The
client believes I have broken this area and nothing I seem to do changes it.
It always comes up with the Runtime error 2105. I need to have the number
come up as it is used as a filter to several other screens in the system.
I also tried saving a record in the file, but that got me a 170 runtime
error, which I believe is from some constraits in the file.
The number is one that I hide on the front page of the application and use
it to filter query Functions. They use to use the server filter,but I jammed
up a number of times.

 

[Guest]

The database is SQL Server

What exactly is your purpose here? If you simply assign some bound
control on the form to itself, the autonumber value will be generated --
in an MDB file, anyway. E.g.,

DoCmd.GoToRecord , , acNewRec
Forms!YourForm!SomeControl = Forms!YourForm!SomeControl

The record will be dirtied but not saved, and the autonumber will be
generated.

Are you trying to get the autonumber so that you can create related
records in other tables? If so, you won't be able to do that until the
record is actually saved.

What are you really trying to accomplish?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

The autogenerated number goes throughout the application. It is
generated on this form and used to come up with the acPrevious and
acNext code.

I don't follow what you're saying here..

The client believes I have broken this area and nothing
I seem to do changes it. It always comes up with the Runtime error
2105. I need to have the number come up as it is used as a filter to
several other screens in the system.
I also tried saving a record in the file, but that got me a 170
runtime error, which I believe is from some constraits in the file.
The number is one that I hide on the front page of the application
and use it to filter query Functions. They use to use the server
filter,but I jammed up a number of times.

Is this an ADP then? That makes a difference. In an MDB, Access
generates the autonumber immediately when you dirty the record. In an
ADP, if I remember, the autonumber is not generated until the record is
written.

So you have to force the record to be written. You say your previous
code was generating error 2105, which I make out to be "You can't go to
the specified record." Your idea, as I understand it, was to dirty the
current record by modifying some fields, then move to another record and
move back. Suppose we try it without actually moving the form to
another record.

Modify the fields that you can initially modify, then execute the
statement

RunCommand acCmdSaveRecord

*Then* see if the autonumber is available.

 

[Guest]

Record now does not save at all, even from the form. Is there something I am
missing?
SQL database with Access Project
Thanks!
Terry

 

[Dirk Goldgar]

Record now does not save at all, even from the form. Is there
something I am missing?
SQL database with Access Project

Is this something you just upsized? Can you save any record from the
form, even without using this code? If you open the table directly as a
datasheet, can you update records in it? If your form is based on a
query (view or SQL statement), can you update records in that query?
Make sure the table has a primary key, if you want it to be updatable.
I'm not sure about ADPs, but I know that in ODBC-linked MDBs, Access
really likes there to be a timestamp field in the table.

 

[Guest]

I have not changed anything in this area. That is what is frustrating. An
older version of the programs has no problems in this area. It does not save
records from the form either. It there a way to check a return code when the
record is attempted to be written.

Thanks!
Terry

 

[Dirk Goldgar]

I have not changed anything in this area. That is what is
frustrating. An older version of the programs has no problems in
this area. It does not save records from the form either. It there
a way to check a return code when the record is attempted to be
written.

Thanks!
Terry

Please answer the questions I asked. In what you posted above, when you
say, "It does not save records from the form either," I can't tell
whether you are referring to the "older version of the program", and
saying that old version can't save records from the form (implying
something about the back-end has changed), or whether are just sayiung
that this new version can't and the old version could.

What did you change between the old version and the new version?
Plainly you changed something, or there wouldn't *be* a new version.

 

[Guest]

I have not changed either the table or the basic code to this form between
versions. It will not save the record from the form at this time. I am able
to go directly to the table and insert a record and get the autonumber with
the 2 field filled in like in the code for the form. I had added a field to
the table a while back, but I believe that was before the issue began. I am
able to save already created records with no problem, either from the edit
form or on the record itself. The copy function works correctly and pulls in
a new number, but that copies the whole record into a new record and creates
the related records at the same time.
What I meant about old and new versions is that this started occuring about
a week ago, even though neither the form or query had been changed. Since
that time I have been trying to define how it is working, as the prior
developer had one screen for new and one screen for edit records in these
files. It is the new record screen I am having an issue with. I have found
that if I enter the fields in the new screen after it appears the record does
not save, though I need more testing in this area.
Do you want coding examples? I am just trying to force the key field to
generate and display a new record number before all the fields are entered,
so the customer may be able to move to other screens in the systems based on
the new keyfield.

Thanks!
Terry

 

[Guest]

Current status has changed. I was able to use the following code to insert a
new record and create the estimate number. Now I just need to retrieve and
display it on the form.
Current code is:
DoCmd.GoToRecord , , acNewRec

Me![e_part_ID] = "'!'"
Me!estimate_date = FormatDateTime(Now(), vbShortDate)
stSQL = "Insert into dbo.estimate (e_part_ID, estimate_date, e_m_cert)"
& _
" Values (" & [e_part_ID] & "," & [estimate_date] & "," & 0 & ")"
DoCmd.RunSQL stSQL, no
'DoCmd.GoToRecord , , acPrevious
'DoCmd.GoToRecord , , acNext
cboPart.SetFocus
The GotoRecord still receives the 2105 error - can not go to this record.
Please help in displaying the record with the estimate number. Part ID is a
combo box to select a part number.

Thanks in advance!
Terry

 

[Dirk Goldgar]

Current status has changed. I was able to use the following code to
insert a new record and create the estimate number. Now I just need
to retrieve and display it on the form.
Current code is:
DoCmd.GoToRecord , , acNewRec

Me![e_part_ID] = "'!'"
Me!estimate_date = FormatDateTime(Now(), vbShortDate)
stSQL = "Insert into dbo.estimate (e_part_ID, estimate_date,
e_m_cert)" & _
" Values (" & [e_part_ID] & "," & [estimate_date] & "," & 0 &
")" DoCmd.RunSQL stSQL, no
'DoCmd.GoToRecord , , acPrevious
'DoCmd.GoToRecord , , acNext
cboPart.SetFocus
The GotoRecord still receives the 2105 error - can not go to this
record. Please help in displaying the record with the estimate
number. Part ID is a combo box to select a part number.

What GoToRecord? You don't show an active GoToRecord call except the
one that goes to the new record at the top of the code. Is that the one
that is failing, or is it one that you are executing somewhere else,
possibly as part of the AfterUpdate code for cboPartNumber.

Your logic seems flawed to me. I'm assuming that the form is based on
dbo.estimate. You are dirtying a record on the form, then adding that
record via an append query. That will make it impossible to save the
form's record, and the form will not know about the record you added via
the append query. You'll need to requery both the form and combo box if
you want them to know about the record you added with the append query.

Did you ever try my suggestion of using RunCommand acCmdSaveRecord? Did
simple code like this work or not?

RunCommand acCmdRecordsGoToNew

Me![e_part_ID] = "'!'"
Me!estimate_date = FormatDateTime(Now(), vbShortDate)
Me!e_m_cert = 0

RunCommand acCmdSaveRecord

???

 

[Guest]

I tried the suggested code. I now have a run time error 30014. Data was
added , but cannot be displayed because it does not satify the critera in the
underlying record source.

Added the record with SQL worked. I should have commented out the
GotoRecord acnew. It would be perfect if I could display the record. Any
more suggestions would be wonderful.

Thanks!
Terry

Current status has changed. I was able to use the following code to
insert a new record and create the estimate number. Now I just need
to retrieve and display it on the form.
Current code is:
DoCmd.GoToRecord , , acNewRec

Me![e_part_ID] = "'!'"
Me!estimate_date = FormatDateTime(Now(), vbShortDate)
stSQL = "Insert into dbo.estimate (e_part_ID, estimate_date,
e_m_cert)" & _
" Values (" & [e_part_ID] & "," & [estimate_date] & "," & 0 &
")" DoCmd.RunSQL stSQL, no
'DoCmd.GoToRecord , , acPrevious
'DoCmd.GoToRecord , , acNext
cboPart.SetFocus
The GotoRecord still receives the 2105 error - can not go to this
record. Please help in displaying the record with the estimate
number. Part ID is a combo box to select a part number.

What GoToRecord? You don't show an active GoToRecord call except the
one that goes to the new record at the top of the code. Is that the one
that is failing, or is it one that you are executing somewhere else,
possibly as part of the AfterUpdate code for cboPartNumber.

Your logic seems flawed to me. I'm assuming that the form is based on
dbo.estimate. You are dirtying a record on the form, then adding that
record via an append query. That will make it impossible to save the
form's record, and the form will not know about the record you added via
the append query. You'll need to requery both the form and combo box if
you want them to know about the record you added with the append query.

Did you ever try my suggestion of using RunCommand acCmdSaveRecord? Did
simple code like this work or not?

RunCommand acCmdRecordsGoToNew

Me![e_part_ID] = "'!'"
Me!estimate_date = FormatDateTime(Now(), vbShortDate)
Me!e_m_cert = 0

RunCommand acCmdSaveRecord

???

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I tried the suggested code. I now have a run time error 30014. Data
was added , but cannot be displayed because it does not satify the
critera in the underlying record source.

Added the record with SQL worked. I should have commented out the
GotoRecord acnew. It would be perfect if I could display the
record. Any more suggestions would be wonderful.

Thanks!
Terry

Current status has changed. I was able to use the following code to
insert a new record and create the estimate number. Now I just need
to retrieve and display it on the form.
Current code is:
DoCmd.GoToRecord , , acNewRec

Me![e_part_ID] = "'!'"
Me!estimate_date = FormatDateTime(Now(), vbShortDate)
stSQL = "Insert into dbo.estimate (e_part_ID, estimate_date,
e_m_cert)" & _
" Values (" & [e_part_ID] & "," & [estimate_date] & "," & 0 &
")" DoCmd.RunSQL stSQL, no
'DoCmd.GoToRecord , , acPrevious
'DoCmd.GoToRecord , , acNext
cboPart.SetFocus
The GotoRecord still receives the 2105 error - can not go to this
record. Please help in displaying the record with the estimate
number. Part ID is a combo box to select a part number.

What GoToRecord? You don't show an active GoToRecord call except the
one that goes to the new record at the top of the code. Is that the
one that is failing, or is it one that you are executing somewhere
else, possibly as part of the AfterUpdate code for cboPartNumber.

Your logic seems flawed to me. I'm assuming that the form is based
on dbo.estimate. You are dirtying a record on the form, then adding
that record via an append query. That will make it impossible to
save the form's record, and the form will not know about the record
you added via the append query. You'll need to requery both the
form and combo box if you want them to know about the record you
added with the append query.

Did you ever try my suggestion of using RunCommand acCmdSaveRecord?
Did simple code like this work or not?

RunCommand acCmdRecordsGoToNew

Me![e_part_ID] = "'!'"
Me!estimate_date = FormatDateTime(Now(), vbShortDate)
Me!e_m_cert = 0

RunCommand acCmdSaveRecord

???

So what is the form's recordsource? From the message, I'd guess it's a
query or view that has some criteria for what records will be displayed
on the form, and the newly added record doesn't meet those criteria.

 

[Guest]

I got it to work. I finally added the dummy record with SQL and then did an
inverted sort on the file. This is a very low volume user system.

Terry

I tried the suggested code. I now have a run time error 30014. Data
was added , but cannot be displayed because it does not satify the
critera in the underlying record source.

Added the record with SQL worked. I should have commented out the
GotoRecord acnew. It would be perfect if I could display the
record. Any more suggestions would be wonderful.

Thanks!
Terry

message Current status has changed. I was able to use the following code to
insert a new record and create the estimate number. Now I just need
to retrieve and display it on the form.
Current code is:
DoCmd.GoToRecord , , acNewRec

Me![e_part_ID] = "'!'"
Me!estimate_date = FormatDateTime(Now(), vbShortDate)
stSQL = "Insert into dbo.estimate (e_part_ID, estimate_date,
e_m_cert)" & _
" Values (" & [e_part_ID] & "," & [estimate_date] & "," & 0 &
")" DoCmd.RunSQL stSQL, no
'DoCmd.GoToRecord , , acPrevious
'DoCmd.GoToRecord , , acNext
cboPart.SetFocus
The GotoRecord still receives the 2105 error - can not go to this
record. Please help in displaying the record with the estimate
number. Part ID is a combo box to select a part number.

What GoToRecord? You don't show an active GoToRecord call except the
one that goes to the new record at the top of the code. Is that the
one that is failing, or is it one that you are executing somewhere
else, possibly as part of the AfterUpdate code for cboPartNumber.

Your logic seems flawed to me. I'm assuming that the form is based
on dbo.estimate. You are dirtying a record on the form, then adding
that record via an append query. That will make it impossible to
save the form's record, and the form will not know about the record
you added via the append query. You'll need to requery both the
form and combo box if you want them to know about the record you
added with the append query.

Did you ever try my suggestion of using RunCommand acCmdSaveRecord?
Did simple code like this work or not?

RunCommand acCmdRecordsGoToNew

Me![e_part_ID] = "'!'"
Me!estimate_date = FormatDateTime(Now(), vbShortDate)
Me!e_m_cert = 0

RunCommand acCmdSaveRecord

???

So what is the form's recordsource? From the message, I'd guess it's a
query or view that has some criteria for what records will be displayed
on the form, and the newly added record doesn't meet those criteria.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I got it to work. I finally added the dummy record with SQL and then
did an inverted sort on the file. This is a very low volume user
system.

I'm glad you found a solution, Terry, though I'm still confused as to
exactly what was going on. All's well that ends well, I guess.