URGENT! - Autonumbering Problem

  • Thread starter Thread starter Jody
  • Start date Start date
J

Jody

I posted my question earlier but I need a response ASAP --
my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It may be
the way I have my data entry forms set up.

When the user first opens the data entry screen they are
prompted to enter their name from a drop down box. They
then click a "next" button to take them to the main data
entry screen. This screen will show all their
travel/activity entries in a list box at the bottom of the
screen. When they click on any item in the list box, they
can edit it the form at the top of the screen. I have a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the next
autonumber based on the records in the listbox rather than
the entire table. How do I get the "add new activity" to
work properly by taking out the next autonumber in the
table yet still show only the selected users activities in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub
 
Jody,

Did you get that problem fixed last week? I hope I was
able to help!

It sounds to me like when they enter their name, the form
is trying to create a new record. Not sure why without
more information.

Is the form bound? If so, you need another form that will
allow the user to enter their name, then open the form
filtered or apply a filter if it's already open based on
the choice made in the other form.

I have also handled this by use of a hidden form. In the
after update event I set the value of a field on a hidden
form to the vaslue of the field and apply the filter based
on the field on the hidden form.

If the form is not bound, I would need more information on
how your retrieving records.

Hope that helps!

Kevin
-----Original Message-----
I posted my question earlier but I need a response ASAP --
Click to expand...
my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It may be
the way I have my data entry forms set up.

When the user first opens the data entry screen they are
prompted to enter their name from a drop down box. They
then click a "next" button to take them to the main data
entry screen. This screen will show all their
travel/activity entries in a list box at the bottom of the
screen. When they click on any item in the list box, they
can edit it the form at the top of the screen. I have a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the next
autonumber based on the records in the listbox rather than
the entire table. How do I get the "add new activity" to
work properly by taking out the next autonumber in the
table yet still show only the selected users activities in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub

-----Original Message-----
Sounds like a corruption problem to me.
Make a copy of your back end database just in case,
then run compact/repair on the original.
Chances are good that will take care of it, but if
Click to expand...
it
doesn't, post
back.

HTH
- Turtle
Click to expand...


.
Click to expand...
 
Hi Jody,

First be sure that you've tried compacting and repairing the Backend
Database. Next, take a look at the following KB article which may explain
why the problem is occuring and how to fix it.

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and
Repair a Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;257408&Product=acc

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I posted my question earlier but I need a response ASAP --
my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It may be
the way I have my data entry forms set up.

When the user first opens the data entry screen they are
prompted to enter their name from a drop down box. They
then click a "next" button to take them to the main data
entry screen. This screen will show all their
travel/activity entries in a list box at the bottom of the
screen. When they click on any item in the list box, they
can edit it the form at the top of the screen. I have a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the next
autonumber based on the records in the listbox rather than
the entire table. How do I get the "add new activity" to
work properly by taking out the next autonumber in the
table yet still show only the selected users activities in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub

-----Original Message-----
Sounds like a corruption problem to me.
Make a copy of your back end database just in case,
then run compact/repair on the original.
Chances are good that will take care of it, but if it doesn't,
post back.

HTH
- Turtle
Click to expand...
Click to expand...
 
No I never did get the "copy" option to work. Then the
autonumber problem occurred.

I have two forms: the first form called SelectName
contains only an unbound combo box called SalesRep. The
user selects their name from the combo box then clicks
a "Continue" button. The "On Click" event on my "continue"
button runs a macro. This macro opens a form
called "Schedule Data Entry" where SlsRepID = [Forms]!
[Schedule Data Entry]![SalesRep].

The list box located at the bottom of form "Schedule Data
Entry" lists all the travel/activities for the user. If
the user clicks on any one of the records listed in the
listbox called "list53" the individual record can be
edited on the form (upper half of form). The user has
command buttons on this form that add a record, delete a
record, update/refresh, cancel.

The next autonumber in the Activity table is 513; however,
the "add new activity" button is trying to assign an
available autonumber (270) to the record based on the
filtered records in the listbox. I need it to assign the
next autonumber (513) based on the table NOT the listbox.

My users like the way the form works - ability to see all
their entries in the list box, edit, update and add
records all on one screen.

Here's my code associated with this form:

Private Sub cboDate_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
With Calendar1
.Visible = Not .Visible
If .Visible Then
.SetFocus
.Value = Date
Else
cboDate.SetFocus
End If
End With
End Sub
---------------------------------
Private Sub Calendar1_Click()
With Calendar1
cboDate.Value = .Value
cboDate.SetFocus
.Visible = False
End With
End Sub
-----------------------------------
Private Sub cmdUndo_Click()
' same action as clicking Undo from the Edit menu
DoCmd.RunCommand acCmdUndo
Requery
End Sub
Private Sub Form_Current()
Me!CmdUndo.Enabled = False
End Sub
Private Sub Form_Dirty(Cancel As Integer)
Me!CmdUndo.Enabled = True
End Sub
-----------------------------
Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub
-------------------------
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

----------------------------
Private Sub CmdDelActivity_Click()
On Error GoTo Err_CmdDelActivity_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70

Exit_CmdDelActivity_Click:
Exit Sub

Err_CmdDelActivity_Click:
MsgBox Err.Description
Resume Exit_CmdDelActivity_Click

Requery

End Sub
-------------------------
Private Sub cmdUpdateScreen_Click()
On Error GoTo Err_cmdUpdateScreen_Click

DoCmd.GoToRecord , , acLast
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdUpdateScreen_Click:
Exit Sub

Err_cmdUpdateScreen_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateScreen_Click

End Sub


-----Original Message-----
Jody,

Did you get that problem fixed last week? I hope I was
able to help!

It sounds to me like when they enter their name, the form
is trying to create a new record. Not sure why without
more information.

Is the form bound? If so, you need another form that will
allow the user to enter their name, then open the form
filtered or apply a filter if it's already open based on
the choice made in the other form.

I have also handled this by use of a hidden form. In the
after update event I set the value of a field on a hidden
form to the vaslue of the field and apply the filter based
on the field on the hidden form.

If the form is not bound, I would need more information on
how your retrieving records.

Hope that helps!

Kevin
-----Original Message-----
I posted my question earlier but I need a response ASAP -
Click to expand...
-
my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It may be
the way I have my data entry forms set up.

When the user first opens the data entry screen they are
prompted to enter their name from a drop down box. They
then click a "next" button to take them to the main data
entry screen. This screen will show all their
travel/activity entries in a list box at the bottom of the
screen. When they click on any item in the list box, they
can edit it the form at the top of the screen. I have a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the next
autonumber based on the records in the listbox rather than
the entire table. How do I get the "add new activity" to
work properly by taking out the next autonumber in the
table yet still show only the selected users activities in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub

-----Original Message-----
Sounds like a corruption problem to me.
Make a copy of your back end database just in case,
then run compact/repair on the original.
Chances are good that will take care of it, but if
Click to expand...
it
doesn't, post
back.

HTH
- Turtle

My database has linked tables. I have 10 users that have
a copy of the database on each of their computers. The
main tables reside in a shared department area on our
network. We "split" the database in order to allow more
than one individual to have the data entry forms open at a
time.

It seemed to be working but I recently noticed that when a
new record is added, instead of the system assigning the
next autonumber in the table (Activity_ID is my primary
key and autonumber field) which in this case should be
513, it tries to assign a 270 which already exists
Click to expand...
Click to expand...
and
I number
on


.
Click to expand...
.
Click to expand...
 
I tried your suggestion and it still does not work.

I added a new record and it inserted an autonumber 284
instead of 513 -- the next autonumber in the database
table. I think perhaps my database has been doing this
all along and I didn't notice it until now.

I still say it has something to do with my listbox. Does
my code look correct?
-----Original Message-----
Hi Jody,

First be sure that you've tried compacting and repairing the Backend
Database. Next, take a look at the following KB article which may explain
why the problem is occuring and how to fix it.

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and
Repair a Database
http://support.microsoft.com/default.aspx?scid=kb;en- us;257408&Product=acc

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I posted my question earlier but I need a response ASAP --
my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It may be
the way I have my data entry forms set up.

When the user first opens the data entry screen they are
prompted to enter their name from a drop down box. They
then click a "next" button to take them to the main data
entry screen. This screen will show all their
travel/activity entries in a list box at the bottom of the
screen. When they click on any item in the list box, they
can edit it the form at the top of the screen. I have a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the next
autonumber based on the records in the listbox rather than
the entire table. How do I get the "add new activity" to
work properly by taking out the next autonumber in the
table yet still show only the selected users activities in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub

-----Original Message-----
Sounds like a corruption problem to me.
Make a copy of your back end database just in case,
then run compact/repair on the original.
Chances are good that will take care of it, but if it doesn't,
post back.

HTH
- Turtle

My database has linked tables. I have 10 users that have
a copy of the database on each of their computers. The
main tables reside in a shared department area on our
network. We "split" the database in order to allow more
than one individual to have the data entry forms open at a
time.

It seemed to be working but I recently noticed that when a
new record is added, instead of the system assigning the
next autonumber in the table (Activity_ID is my primary
key and autonumber field) which in this case should be
513, it tries to assign a 270 which already exists and I
get the following error message:

"The changes you requested to the tabe were no successful
because they would create duplicate values in the index,
primary key or relationship...."


I thought it should automatically put the next number on
the record.

Here's my code to add the new record.
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

I fairly new to VB and not sure what's happening .... any
suggestions?
Click to expand...
Click to expand...

.
Click to expand...
 
Jody,

What is the value being stored in the listbox field? You
display the users name, but is the field actually storing
an ID number? Could 270 be some id value corresponding to
the users name? The autonumber field, is that an (I am
guessing now) activity ID? I am not real clear on your
database structure.

Answer back tonight if you get this (Monday)and let me
study on it a little more.

Kevin
-----Original Message-----
No I never did get the "copy" option to work. Then the
autonumber problem occurred.

I have two forms: the first form called SelectName
contains only an unbound combo box called SalesRep. The
user selects their name from the combo box then clicks
a "Continue" button. The "On Click" event on my "continue"
button runs a macro. This macro opens a form
called "Schedule Data Entry" where SlsRepID = [Forms]!
[Schedule Data Entry]![SalesRep].

The list box located at the bottom of form "Schedule Data
Entry" lists all the travel/activities for the user. If
the user clicks on any one of the records listed in the
listbox called "list53" the individual record can be
edited on the form (upper half of form). The user has
command buttons on this form that add a record, delete a
record, update/refresh, cancel.

The next autonumber in the Activity table is 513; however,
the "add new activity" button is trying to assign an
available autonumber (270) to the record based on the
filtered records in the listbox. I need it to assign the
next autonumber (513) based on the table NOT the listbox.

My users like the way the form works - ability to see all
their entries in the list box, edit, update and add
records all on one screen.

Here's my code associated with this form:

Private Sub cboDate_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
With Calendar1
.Visible = Not .Visible
If .Visible Then
.SetFocus
.Value = Date
Else
cboDate.SetFocus
End If
End With
End Sub
---------------------------------
Private Sub Calendar1_Click()
With Calendar1
cboDate.Value = .Value
cboDate.SetFocus
.Visible = False
End With
End Sub
-----------------------------------
Private Sub cmdUndo_Click()
' same action as clicking Undo from the Edit menu
DoCmd.RunCommand acCmdUndo
Requery
End Sub
Private Sub Form_Current()
Me!CmdUndo.Enabled = False
End Sub
Private Sub Form_Dirty(Cancel As Integer)
Me!CmdUndo.Enabled = True
End Sub
-----------------------------
Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub
-------------------------
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

----------------------------
Private Sub CmdDelActivity_Click()
On Error GoTo Err_CmdDelActivity_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70

Exit_CmdDelActivity_Click:
Exit Sub

Err_CmdDelActivity_Click:
MsgBox Err.Description
Resume Exit_CmdDelActivity_Click

Requery

End Sub
-------------------------
Private Sub cmdUpdateScreen_Click()
On Error GoTo Err_cmdUpdateScreen_Click

DoCmd.GoToRecord , , acLast
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdUpdateScreen_Click:
Exit Sub

Err_cmdUpdateScreen_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateScreen_Click

End Sub


-----Original Message-----
Jody,

Did you get that problem fixed last week? I hope I was
able to help!

It sounds to me like when they enter their name, the form
is trying to create a new record. Not sure why without
more information.

Is the form bound? If so, you need another form that will
allow the user to enter their name, then open the form
filtered or apply a filter if it's already open based on
the choice made in the other form.

I have also handled this by use of a hidden form. In the
after update event I set the value of a field on a hidden
form to the vaslue of the field and apply the filter based
on the field on the hidden form.

If the form is not bound, I would need more information on
how your retrieving records.

Hope that helps!

Kevin
Click to expand...
ASAP -
-
my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It may be
the way I have my data entry forms set up.

When the user first opens the data entry screen they are
prompted to enter their name from a drop down box. They
then click a "next" button to take them to the main data
entry screen. This screen will show all their
travel/activity entries in a list box at the bottom of the
screen. When they click on any item in the list box, they
can edit it the form at the top of the screen. I have a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the next
autonumber based on the records in the listbox rather than
the entire table. How do I get the "add new activity" to
work properly by taking out the next autonumber in the
table yet still show only the selected users activities in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub


-----Original Message-----
Sounds like a corruption problem to me.
Make a copy of your back end database just in case,
then run compact/repair on the original.
Chances are good that will take care of it, but if it
doesn't, post
back.

HTH
- Turtle

message
My database has linked tables. I have 10 users that
have
a copy of the database on each of their computers. The
main tables reside in a shared department area on our
network. We "split" the database in order to allow more
than one individual to have the data entry forms open
at a
time.

It seemed to be working but I recently noticed that
when a
new record is added, instead of the system assigning the
next autonumber in the table (Activity_ID is my primary
key and autonumber field) which in this case should be
513, it tries to assign a 270 which already exists
Click to expand...
and
I
get the following error message:

"The changes you requested to the tabe were no
successful
because they would create duplicate values in the index,
primary key or relationship...."


I thought it should automatically put the next number
on
the record.

Here's my code to add the new record.
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

I fairly new to VB and not sure what's happening ....
any
suggestions?


.
Click to expand...
.
Click to expand...
.
Click to expand...
 
The value being stored in the listbox is ActivityID
(autonumber).

The "Schedule Data Entry" form shows all the activities
listed in the listbox based on the user's selection on
the "SelectName" form [SlsRepID]. The user can cycle
through each activity by clicking on the item in the
listbox revealing all details for that record in the data
entry form located directly above the listbox. By
clicking on the "add activity" button a new data entry
screen is revealed to allow the user to enter data. When
completed the user clicks the "update screen" button to
add the new record to the top of the list in the listbox.

What is very strange is that sometimes the record is added
and I notice the ActivityID (autonumber) is a number 340
when it should be 513. I'll try to add another new record
for the salesrep and then I'll get the following error
message:

"The changes you requested to the table were not
successful because they would create duplicate entries in
the index, primary key or relationship...."

When I look at my Activity table, I notice that the
ActivityID (autonumber) has huge gaps in the numerical
order of the numbers. I'm not sure why this is occurring.
I have 272 records in the table. My highest ActivityID is
512 but when I go to add a record in the raw data table,
it assigns 316 not 513; however, 316 is already assigned
and hence the above error message. Is there some sort of
error handling code I need included?

I'm about ready to go crazy ..... especially when I'm new
at this.
-----Original Message-----
Jody,

What is the value being stored in the listbox field? You
display the users name, but is the field actually storing
an ID number? Could 270 be some id value corresponding to
the users name? The autonumber field, is that an (I am
guessing now) activity ID? I am not real clear on your
database structure.

Answer back tonight if you get this (Monday)and let me
study on it a little more.

Kevin
-----Original Message-----
No I never did get the "copy" option to work. Then the
autonumber problem occurred.

I have two forms: the first form called SelectName
contains only an unbound combo box called SalesRep. The
user selects their name from the combo box then clicks
a "Continue" button. The "On Click" event on my "continue"
button runs a macro. This macro opens a form
called "Schedule Data Entry" where SlsRepID = [Forms]!
[Schedule Data Entry]![SalesRep].

The list box located at the bottom of form "Schedule Data
Entry" lists all the travel/activities for the user. If
the user clicks on any one of the records listed in the
listbox called "list53" the individual record can be
edited on the form (upper half of form). The user has
command buttons on this form that add a record, delete a
record, update/refresh, cancel.

The next autonumber in the Activity table is 513; however,
the "add new activity" button is trying to assign an
available autonumber (270) to the record based on the
filtered records in the listbox. I need it to assign the
next autonumber (513) based on the table NOT the listbox.

My users like the way the form works - ability to see all
their entries in the list box, edit, update and add
records all on one screen.

Here's my code associated with this form:

Private Sub cboDate_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
With Calendar1
.Visible = Not .Visible
If .Visible Then
.SetFocus
.Value = Date
Else
cboDate.SetFocus
End If
End With
End Sub
---------------------------------
Private Sub Calendar1_Click()
With Calendar1
cboDate.Value = .Value
cboDate.SetFocus
.Visible = False
End With
End Sub
-----------------------------------
Private Sub cmdUndo_Click()
' same action as clicking Undo from the Edit menu
DoCmd.RunCommand acCmdUndo
Requery
End Sub
Private Sub Form_Current()
Me!CmdUndo.Enabled = False
End Sub
Private Sub Form_Dirty(Cancel As Integer)
Me!CmdUndo.Enabled = True
End Sub
-----------------------------
Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub
-------------------------
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

----------------------------
Private Sub CmdDelActivity_Click()
On Error GoTo Err_CmdDelActivity_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70

Exit_CmdDelActivity_Click:
Exit Sub

Err_CmdDelActivity_Click:
MsgBox Err.Description
Resume Exit_CmdDelActivity_Click

Requery

End Sub
-------------------------
Private Sub cmdUpdateScreen_Click()
On Error GoTo Err_cmdUpdateScreen_Click

DoCmd.GoToRecord , , acLast
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdUpdateScreen_Click:
Exit Sub

Err_cmdUpdateScreen_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateScreen_Click

End Sub


-----Original Message-----
Jody,

Did you get that problem fixed last week? I hope I was
able to help!

It sounds to me like when they enter their name, the form
is trying to create a new record. Not sure why without
more information.

Is the form bound? If so, you need another form that will
allow the user to enter their name, then open the form
filtered or apply a filter if it's already open based on
the choice made in the other form.

I have also handled this by use of a hidden form. In the
after update event I set the value of a field on a hidden
form to the vaslue of the field and apply the filter based
on the field on the hidden form.

If the form is not bound, I would need more information on
how your retrieving records.

Hope that helps!

Kevin
-----Original Message-----
I posted my question earlier but I need a response
Click to expand...
ASAP -
-
my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It may
be
the way I have my data entry forms set up.

When the user first opens the data entry screen they are
prompted to enter their name from a drop down box. They
then click a "next" button to take them to the main data
entry screen. This screen will show all their
travel/activity entries in a list box at the bottom of
the
screen. When they click on any item in the list box,
they
can edit it the form at the top of the screen. I have a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the next
autonumber based on the records in the listbox rather
than
the entire table. How do I get the "add new activity" to
work properly by taking out the next autonumber in the
table yet still show only the selected users activities
in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub


-----Original Message-----
Sounds like a corruption problem to me.
Make a copy of your back end database just in case,
then run compact/repair on the original.
Chances are good that will take care of it, but if
it
doesn't, post
back.

HTH
- Turtle

message
My database has linked tables. I have 10 users that
have
a copy of the database on each of their computers. The
main tables reside in a shared department area on our
network. We "split" the database in order to allow
more
than one individual to have the data entry forms open
at a
time.

It seemed to be working but I recently noticed that
when a
new record is added, instead of the system assigning
the
next autonumber in the table (Activity_ID is my primary
key and autonumber field) which in this case should be
513, it tries to assign a 270 which already exists and
I
get the following error message:

"The changes you requested to the tabe were no
successful
because they would create duplicate values in the
index,
primary key or relationship...."


I thought it should automatically put the next number
on
the record.

Here's my code to add the new record.
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

I fairly new to VB and not sure what's happening ....
any
suggestions?


.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Can you compact and send me the database. Remove any data
you feel is proprietary, but it would be helpful if a few
records were left in the database. Change anything you
feel could compromise anything. Send it to
(e-mail address removed)

Just remove the NoSpam tag on the end

If there is a seperate access backend, I will need both
files. If there is an MS SQL Server(or something similar)
backend, make an access equivalent using make table
queires and I will use that. I expect that will meet most
of my needs. I just need to look at how things are
structured. When you send it, if the application has a
backend, send the front end and backend seperately.

I will need instructions on how to get to the problem
code and how to duplicate the problem.

Also, I can look at the problem we discussed last week if
you can give me information on what the queries were
again and how you want to execute the code.

Kevin
-----Original Message-----
The value being stored in the listbox is ActivityID
(autonumber).

The "Schedule Data Entry" form shows all the activities
listed in the listbox based on the user's selection on
the "SelectName" form [SlsRepID]. The user can cycle
through each activity by clicking on the item in the
listbox revealing all details for that record in the data
entry form located directly above the listbox. By
clicking on the "add activity" button a new data entry
screen is revealed to allow the user to enter data. When
completed the user clicks the "update screen" button to
add the new record to the top of the list in the listbox.

What is very strange is that sometimes the record is added
and I notice the ActivityID (autonumber) is a number 340
when it should be 513. I'll try to add another new record
for the salesrep and then I'll get the following error
message:

"The changes you requested to the table were not
successful because they would create duplicate entries in
the index, primary key or relationship...."

When I look at my Activity table, I notice that the
ActivityID (autonumber) has huge gaps in the numerical
order of the numbers. I'm not sure why this is occurring.
I have 272 records in the table. My highest ActivityID is
512 but when I go to add a record in the raw data table,
it assigns 316 not 513; however, 316 is already assigned
and hence the above error message. Is there some sort of
error handling code I need included?

I'm about ready to go crazy ..... especially when I'm new
at this.
-----Original Message-----
Jody,

What is the value being stored in the listbox field? You
display the users name, but is the field actually storing
an ID number? Could 270 be some id value corresponding to
the users name? The autonumber field, is that an (I am
guessing now) activity ID? I am not real clear on your
database structure.

Answer back tonight if you get this (Monday)and let me
study on it a little more.

Kevin
-----Original Message-----
No I never did get the "copy" option to work. Then the
autonumber problem occurred.

I have two forms: the first form called SelectName
contains only an unbound combo box called SalesRep. The
user selects their name from the combo box then clicks
a "Continue" button. The "On Click" event on my "continue"
button runs a macro. This macro opens a form
called "Schedule Data Entry" where SlsRepID = [Forms]!
[Schedule Data Entry]![SalesRep].

The list box located at the bottom of form "Schedule Data
Entry" lists all the travel/activities for the user. If
the user clicks on any one of the records listed in the
listbox called "list53" the individual record can be
edited on the form (upper half of form). The user has
command buttons on this form that add a record, delete a
record, update/refresh, cancel.

The next autonumber in the Activity table is 513; however,
the "add new activity" button is trying to assign an
available autonumber (270) to the record based on the
filtered records in the listbox. I need it to assign the
next autonumber (513) based on the table NOT the listbox.

My users like the way the form works - ability to see all
their entries in the list box, edit, update and add
records all on one screen.

Here's my code associated with this form:

Private Sub cboDate_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
With Calendar1
.Visible = Not .Visible
If .Visible Then
.SetFocus
.Value = Date
Else
cboDate.SetFocus
End If
End With
End Sub
---------------------------------
Private Sub Calendar1_Click()
With Calendar1
cboDate.Value = .Value
cboDate.SetFocus
.Visible = False
End With
End Sub
-----------------------------------
Private Sub cmdUndo_Click()
' same action as clicking Undo from the Edit menu
DoCmd.RunCommand acCmdUndo
Requery
End Sub
Private Sub Form_Current()
Me!CmdUndo.Enabled = False
End Sub
Private Sub Form_Dirty(Cancel As Integer)
Me!CmdUndo.Enabled = True
End Sub
-----------------------------
Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub
-------------------------
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

----------------------------
Private Sub CmdDelActivity_Click()
On Error GoTo Err_CmdDelActivity_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70

Exit_CmdDelActivity_Click:
Exit Sub

Err_CmdDelActivity_Click:
MsgBox Err.Description
Resume Exit_CmdDelActivity_Click

Requery

End Sub
-------------------------
Private Sub cmdUpdateScreen_Click()
On Error GoTo Err_cmdUpdateScreen_Click

DoCmd.GoToRecord , , acLast
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdUpdateScreen_Click:
Exit Sub

Err_cmdUpdateScreen_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateScreen_Click

End Sub



-----Original Message-----
Jody,

Did you get that problem fixed last week? I hope I was
able to help!

It sounds to me like when they enter their name, the form
is trying to create a new record. Not sure why without
more information.

Is the form bound? If so, you need another form that will
allow the user to enter their name, then open the form
filtered or apply a filter if it's already open based on
the choice made in the other form.

I have also handled this by use of a hidden form. In the
after update event I set the value of a field on a hidden
form to the vaslue of the field and apply the filter
based
on the field on the hidden form.

If the form is not bound, I would need more information
on
how your retrieving records.

Hope that helps!

Kevin
-----Original Message-----
I posted my question earlier but I need a response ASAP -
-

my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It may
be
the way I have my data entry forms set up.

When the user first opens the data entry screen they are
prompted to enter their name from a drop down box. They
then click a "next" button to take them to the main data
entry screen. This screen will show all their
travel/activity entries in a list box at the bottom of
the
screen. When they click on any item in the list box,
they
can edit it the form at the top of the screen. I
Click to expand...
have
a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the
next
autonumber based on the records in the listbox rather
than
the entire table. How do I get the "add new activity"
to
work properly by taking out the next autonumber in the
table yet still show only the selected users activities
in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me! [List53])
Me.Bookmark = RS.Bookmark
End Sub


-----Original Message-----
Sounds like a corruption problem to me.
Make a copy of your back end database just in case,
then run compact/repair on the original.
Chances are good that will take care of it, but if
it
doesn't, post
back.

HTH
- Turtle

message
My database has linked tables. I have 10 users that
have
a copy of the database on each of their computers.
The
main tables reside in a shared department area on our
network. We "split" the database in order to allow
more
than one individual to have the data entry forms open
at a
time.

It seemed to be working but I recently noticed that
when a
new record is added, instead of the system assigning
the
next autonumber in the table (Activity_ID is my
primary
key and autonumber field) which in this case
Click to expand...
should
be
513, it tries to assign a 270 which already exists
and
I
get the following error message:

"The changes you requested to the tabe were no
successful
because they would create duplicate values in the
index,
primary key or relationship...."


I thought it should automatically put the next
number
on
the record.

Here's my code to add the new record.
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

I fairly new to VB and not sure what's happening ....
any
suggestions?


.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Kevin,

I rebuilt my tables this morning and created new
autonumbers. It seems to be working now. I may
have "corrupted" the tables when I was trying to program
the "copy" option last week. However, I would still like
to know how how to program a "copy" button so I may go
ahead and send you my db just to get your code
suggestions. This is something all my users are requesting.

Thanks -- I really appreciate all your help!
-----Original Message-----
Can you compact and send me the database. Remove any data
you feel is proprietary, but it would be helpful if a few
records were left in the database. Change anything you
feel could compromise anything. Send it to
(e-mail address removed)

Just remove the NoSpam tag on the end

If there is a seperate access backend, I will need both
files. If there is an MS SQL Server(or something similar)
backend, make an access equivalent using make table
queires and I will use that. I expect that will meet most
of my needs. I just need to look at how things are
structured. When you send it, if the application has a
backend, send the front end and backend seperately.

I will need instructions on how to get to the problem
code and how to duplicate the problem.

Also, I can look at the problem we discussed last week if
you can give me information on what the queries were
again and how you want to execute the code.

Kevin
-----Original Message-----
The value being stored in the listbox is ActivityID
(autonumber).

The "Schedule Data Entry" form shows all the activities
listed in the listbox based on the user's selection on
the "SelectName" form [SlsRepID]. The user can cycle
through each activity by clicking on the item in the
listbox revealing all details for that record in the data
entry form located directly above the listbox. By
clicking on the "add activity" button a new data entry
screen is revealed to allow the user to enter data. When
completed the user clicks the "update screen" button to
add the new record to the top of the list in the listbox.

What is very strange is that sometimes the record is added
and I notice the ActivityID (autonumber) is a number 340
when it should be 513. I'll try to add another new record
for the salesrep and then I'll get the following error
message:

"The changes you requested to the table were not
successful because they would create duplicate entries in
the index, primary key or relationship...."

When I look at my Activity table, I notice that the
ActivityID (autonumber) has huge gaps in the numerical
order of the numbers. I'm not sure why this is occurring.
I have 272 records in the table. My highest ActivityID is
512 but when I go to add a record in the raw data table,
it assigns 316 not 513; however, 316 is already assigned
and hence the above error message. Is there some sort of
error handling code I need included?

I'm about ready to go crazy ..... especially when I'm new
at this.
-----Original Message-----
Jody,

What is the value being stored in the listbox field? You
display the users name, but is the field actually storing
an ID number? Could 270 be some id value corresponding to
the users name? The autonumber field, is that an (I am
guessing now) activity ID? I am not real clear on your
database structure.

Answer back tonight if you get this (Monday)and let me
study on it a little more.

Kevin

-----Original Message-----
No I never did get the "copy" option to work. Then the
autonumber problem occurred.

I have two forms: the first form called SelectName
contains only an unbound combo box called SalesRep. The
user selects their name from the combo box then clicks
a "Continue" button. The "On Click" event on
my "continue"
button runs a macro. This macro opens a form
called "Schedule Data Entry" where SlsRepID = [Forms]!
[Schedule Data Entry]![SalesRep].

The list box located at the bottom of form "Schedule Data
Entry" lists all the travel/activities for the user. If
the user clicks on any one of the records listed in the
listbox called "list53" the individual record can be
edited on the form (upper half of form). The user has
command buttons on this form that add a record, delete a
record, update/refresh, cancel.

The next autonumber in the Activity table is 513;
however,
the "add new activity" button is trying to assign an
available autonumber (270) to the record based on the
filtered records in the listbox. I need it to assign the
next autonumber (513) based on the table NOT the listbox.

My users like the way the form works - ability to see all
their entries in the list box, edit, update and add
records all on one screen.

Here's my code associated with this form:

Private Sub cboDate_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
With Calendar1
.Visible = Not .Visible
If .Visible Then
.SetFocus
.Value = Date
Else
cboDate.SetFocus
End If
End With
End Sub
---------------------------------
Private Sub Calendar1_Click()
With Calendar1
cboDate.Value = .Value
cboDate.SetFocus
.Visible = False
End With
End Sub
-----------------------------------
Private Sub cmdUndo_Click()
' same action as clicking Undo from the Edit menu
DoCmd.RunCommand acCmdUndo
Requery
End Sub
Private Sub Form_Current()
Me!CmdUndo.Enabled = False
End Sub
Private Sub Form_Dirty(Cancel As Integer)
Me!CmdUndo.Enabled = True
End Sub
-----------------------------
Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub
-------------------------
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

----------------------------
Private Sub CmdDelActivity_Click()
On Error GoTo Err_CmdDelActivity_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70

Exit_CmdDelActivity_Click:
Exit Sub

Err_CmdDelActivity_Click:
MsgBox Err.Description
Resume Exit_CmdDelActivity_Click

Requery

End Sub
-------------------------
Private Sub cmdUpdateScreen_Click()
On Error GoTo Err_cmdUpdateScreen_Click

DoCmd.GoToRecord , , acLast
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdUpdateScreen_Click:
Exit Sub

Err_cmdUpdateScreen_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateScreen_Click

End Sub



-----Original Message-----
Jody,

Did you get that problem fixed last week? I hope I was
able to help!

It sounds to me like when they enter their name, the
form
is trying to create a new record. Not sure why without
more information.

Is the form bound? If so, you need another form that
will
allow the user to enter their name, then open the form
filtered or apply a filter if it's already open based on
the choice made in the other form.

I have also handled this by use of a hidden form. In the
after update event I set the value of a field on a
hidden
form to the vaslue of the field and apply the filter
based
on the field on the hidden form.

If the form is not bound, I would need more information
on
how your retrieving records.

Hope that helps!

Kevin
-----Original Message-----
I posted my question earlier but I need a response
ASAP -
-

my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It may
be
the way I have my data entry forms set up.

When the user first opens the data entry screen they
are
prompted to enter their name from a drop down box.
They
then click a "next" button to take them to the main
data
entry screen. This screen will show all their
travel/activity entries in a list box at the bottom of
the
screen. When they click on any item in the list box,
they
can edit it the form at the top of the screen. I have
a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the
next
autonumber based on the records in the listbox rather
than
the entire table. How do I get the "add new activity"
to
work properly by taking out the next autonumber in the
table yet still show only the selected users activities
in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me! [List53])
Me.Bookmark = RS.Bookmark
End Sub


-----Original Message-----
Sounds like a corruption problem to me.
Make a copy of your back end database just in case,
then run compact/repair on the original.
Chances are good that will take care of it, but if
it
doesn't, post
back.

HTH
- Turtle

message
My database has linked tables. I have 10 users that
have
a copy of the database on each of their computers.
The
main tables reside in a shared department area on our
network. We "split" the database in order to allow
more
than one individual to have the data entry forms
open
at a
time.

It seemed to be working but I recently noticed that
when a
new record is added, instead of the system assigning
the
next autonumber in the table (Activity_ID is my
primary
key and autonumber field) which in this case should
be
513, it tries to assign a 270 which already exists
and
I
get the following error message:

"The changes you requested to the tabe were no
successful
because they would create duplicate values in the
index,
primary key or relationship...."


I thought it should automatically put the next
number
on
the record.

Here's my code to add the new record.
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

I fairly new to VB and not sure what's
happening ....
any
suggestions?


.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
It will be this evening before I can do anything with it.

Kevin
-----Original Message-----
Kevin,

I rebuilt my tables this morning and created new
autonumbers. It seems to be working now. I may
have "corrupted" the tables when I was trying to program
the "copy" option last week. However, I would still like
to know how how to program a "copy" button so I may go
ahead and send you my db just to get your code
suggestions. This is something all my users are requesting.

Thanks -- I really appreciate all your help!
-----Original Message-----
Can you compact and send me the database. Remove any data
you feel is proprietary, but it would be helpful if a few
records were left in the database. Change anything you
feel could compromise anything. Send it to
(e-mail address removed)

Just remove the NoSpam tag on the end

If there is a seperate access backend, I will need both
files. If there is an MS SQL Server(or something similar)
backend, make an access equivalent using make table
queires and I will use that. I expect that will meet most
of my needs. I just need to look at how things are
structured. When you send it, if the application has a
backend, send the front end and backend seperately.

I will need instructions on how to get to the problem
code and how to duplicate the problem.

Also, I can look at the problem we discussed last week if
you can give me information on what the queries were
again and how you want to execute the code.

Kevin
-----Original Message-----
The value being stored in the listbox is ActivityID
(autonumber).

The "Schedule Data Entry" form shows all the activities
listed in the listbox based on the user's selection on
the "SelectName" form [SlsRepID]. The user can cycle
through each activity by clicking on the item in the
listbox revealing all details for that record in the data
entry form located directly above the listbox. By
clicking on the "add activity" button a new data entry
screen is revealed to allow the user to enter data. When
completed the user clicks the "update screen" button to
add the new record to the top of the list in the listbox.

What is very strange is that sometimes the record is added
and I notice the ActivityID (autonumber) is a number 340
when it should be 513. I'll try to add another new record
for the salesrep and then I'll get the following error
message:

"The changes you requested to the table were not
successful because they would create duplicate entries in
the index, primary key or relationship...."

When I look at my Activity table, I notice that the
ActivityID (autonumber) has huge gaps in the numerical
order of the numbers. I'm not sure why this is occurring.
I have 272 records in the table. My highest ActivityID is
512 but when I go to add a record in the raw data table,
it assigns 316 not 513; however, 316 is already assigned
and hence the above error message. Is there some sort of
error handling code I need included?

I'm about ready to go crazy ..... especially when I'm new
at this.

-----Original Message-----
Jody,

What is the value being stored in the listbox field? You
display the users name, but is the field actually storing
an ID number? Could 270 be some id value corresponding to
the users name? The autonumber field, is that an (I am
guessing now) activity ID? I am not real clear on your
database structure.

Answer back tonight if you get this (Monday)and let me
study on it a little more.

Kevin

-----Original Message-----
No I never did get the "copy" option to work. Then the
autonumber problem occurred.

I have two forms: the first form called SelectName
contains only an unbound combo box called SalesRep. The
user selects their name from the combo box then clicks
a "Continue" button. The "On Click" event on
my "continue"
button runs a macro. This macro opens a form
called "Schedule Data Entry" where SlsRepID = [Forms]!
[Schedule Data Entry]![SalesRep].

The list box located at the bottom of form "Schedule
Data
Entry" lists all the travel/activities for the user.
If
the user clicks on any one of the records listed in the
listbox called "list53" the individual record can be
edited on the form (upper half of form). The user has
command buttons on this form that add a record,
Click to expand...
delete
a
record, update/refresh, cancel.

The next autonumber in the Activity table is 513;
however,
the "add new activity" button is trying to assign an
available autonumber (270) to the record based on the
filtered records in the listbox. I need it to assign
the
next autonumber (513) based on the table NOT the listbox.

My users like the way the form works - ability to see
all
their entries in the list box, edit, update and add
records all on one screen.

Here's my code associated with this form:

Private Sub cboDate_MouseDown(Button As Integer, Shift
As
Integer, X As Single, Y As Single)
With Calendar1
.Visible = Not .Visible
If .Visible Then
.SetFocus
.Value = Date
Else
cboDate.SetFocus
End If
End With
End Sub
---------------------------------
Private Sub Calendar1_Click()
With Calendar1
cboDate.Value = .Value
cboDate.SetFocus
.Visible = False
End With
End Sub
-----------------------------------
Private Sub cmdUndo_Click()
' same action as clicking Undo from the Edit menu
DoCmd.RunCommand acCmdUndo
Requery
End Sub
Private Sub Form_Current()
Me!CmdUndo.Enabled = False
End Sub
Private Sub Form_Dirty(Cancel As Integer)
Me!CmdUndo.Enabled = True
End Sub
-----------------------------
Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me![List53])
Me.Bookmark = RS.Bookmark
End Sub
-------------------------
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

----------------------------
Private Sub CmdDelActivity_Click()
On Error GoTo Err_CmdDelActivity_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70

Exit_CmdDelActivity_Click:
Exit Sub

Err_CmdDelActivity_Click:
MsgBox Err.Description
Resume Exit_CmdDelActivity_Click

Requery

End Sub
-------------------------
Private Sub cmdUpdateScreen_Click()
On Error GoTo Err_cmdUpdateScreen_Click

DoCmd.GoToRecord , , acLast
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdUpdateScreen_Click:
Exit Sub

Err_cmdUpdateScreen_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateScreen_Click

End Sub



-----Original Message-----
Jody,

Did you get that problem fixed last week? I hope I was
able to help!

It sounds to me like when they enter their name, the
form
is trying to create a new record. Not sure why without
more information.

Is the form bound? If so, you need another form that
will
allow the user to enter their name, then open the form
filtered or apply a filter if it's already open based
on
the choice made in the other form.

I have also handled this by use of a hidden form. In
the
after update event I set the value of a field on a
hidden
form to the vaslue of the field and apply the filter
based
on the field on the hidden form.

If the form is not bound, I would need more information
on
how your retrieving records.

Hope that helps!

Kevin
-----Original Message-----
I posted my question earlier but I need a response
ASAP -
-

my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It
may
be
the way I have my data entry forms set up.

When the user first opens the data entry screen they
are
prompted to enter their name from a drop down box.
They
then click a "next" button to take them to the main
data
entry screen. This screen will show all their
travel/activity entries in a list box at the bottom of
the
screen. When they click on any item in the list box,
they
can edit it the form at the top of the screen. I have
a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the
next
autonumber based on the records in the listbox rather
than
the entire table. How do I get the "add new activity"
to
work properly by taking out the next autonumber in the
table yet still show only the selected users
activities
in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me! [List53])
Me.Bookmark = RS.Bookmark
End Sub


-----Original Message-----
Sounds like a corruption problem to me.
Make a copy of your back end database just in case,
then run compact/repair on the original.
Chances are good that will take care of it, but
if
it
doesn't, post
back.

HTH
- Turtle

message
My database has linked tables. I have 10 users
that
have
a copy of the database on each of their computers.
The
main tables reside in a shared department area on
our
network. We "split" the database in order to allow
more
than one individual to have the data entry forms
open
at a
time.

It seemed to be working but I recently noticed that
when a
new record is added, instead of the system
assigning
the
next autonumber in the table (Activity_ID is my
primary
key and autonumber field) which in this case should
be
513, it tries to assign a 270 which already exists
and
I
get the following error message:

"The changes you requested to the tabe were no
successful
because they would create duplicate values in the
index,
primary key or relationship...."


I thought it should automatically put the next
number
on
the record.

Here's my code to add the new record.
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

I fairly new to VB and not sure what's
happening ....
any
suggestions?


.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Jody,

Did you send it? I have not received anything yet??

Kevin
-----Original Message-----
Kevin,

I rebuilt my tables this morning and created new
autonumbers. It seems to be working now. I may
have "corrupted" the tables when I was trying to program
the "copy" option last week. However, I would still like
to know how how to program a "copy" button so I may go
ahead and send you my db just to get your code
suggestions. This is something all my users are requesting.

Thanks -- I really appreciate all your help!
-----Original Message-----
Can you compact and send me the database. Remove any data
you feel is proprietary, but it would be helpful if a few
records were left in the database. Change anything you
feel could compromise anything. Send it to
(e-mail address removed)

Just remove the NoSpam tag on the end

If there is a seperate access backend, I will need both
files. If there is an MS SQL Server(or something similar)
backend, make an access equivalent using make table
queires and I will use that. I expect that will meet most
of my needs. I just need to look at how things are
structured. When you send it, if the application has a
backend, send the front end and backend seperately.

I will need instructions on how to get to the problem
code and how to duplicate the problem.

Also, I can look at the problem we discussed last week if
you can give me information on what the queries were
again and how you want to execute the code.

Kevin
-----Original Message-----
The value being stored in the listbox is ActivityID
(autonumber).

The "Schedule Data Entry" form shows all the activities
listed in the listbox based on the user's selection on
the "SelectName" form [SlsRepID]. The user can cycle
through each activity by clicking on the item in the
listbox revealing all details for that record in the data
entry form located directly above the listbox. By
clicking on the "add activity" button a new data entry
screen is revealed to allow the user to enter data. When
completed the user clicks the "update screen" button to
add the new record to the top of the list in the listbox.

What is very strange is that sometimes the record is added
and I notice the ActivityID (autonumber) is a number 340
when it should be 513. I'll try to add another new record
for the salesrep and then I'll get the following error
message:

"The changes you requested to the table were not
successful because they would create duplicate entries in
the index, primary key or relationship...."

When I look at my Activity table, I notice that the
ActivityID (autonumber) has huge gaps in the numerical
order of the numbers. I'm not sure why this is occurring.
I have 272 records in the table. My highest
Click to expand...
ActivityID
is
512 but when I go to add a record in the raw data table,
it assigns 316 not 513; however, 316 is already assigned
and hence the above error message. Is there some sort of
error handling code I need included?

I'm about ready to go crazy ..... especially when I'm new
at this.

-----Original Message-----
Jody,

What is the value being stored in the listbox field? You
display the users name, but is the field actually storing
an ID number? Could 270 be some id value
Click to expand...
corresponding
to
the users name? The autonumber field, is that an (I am
guessing now) activity ID? I am not real clear on your
database structure.

Answer back tonight if you get this (Monday)and let me
study on it a little more.

Kevin

-----Original Message-----
No I never did get the "copy" option to work. Then the
autonumber problem occurred.

I have two forms: the first form called SelectName
contains only an unbound combo box called SalesRep. The
user selects their name from the combo box then clicks
a "Continue" button. The "On Click" event on
my "continue"
button runs a macro. This macro opens a form
called "Schedule Data Entry" where SlsRepID = [Forms]!
[Schedule Data Entry]![SalesRep].

The list box located at the bottom of form "Schedule
Data
Entry" lists all the travel/activities for the user.
If
the user clicks on any one of the records listed in the
listbox called "list53" the individual record can be
edited on the form (upper half of form). The user has
command buttons on this form that add a record,
Click to expand...
delete
a
record, update/refresh, cancel.

The next autonumber in the Activity table is 513;
however,
the "add new activity" button is trying to assign an
available autonumber (270) to the record based on the
filtered records in the listbox. I need it to assign
the
next autonumber (513) based on the table NOT the listbox.

My users like the way the form works - ability to see
all
their entries in the list box, edit, update and add
records all on one screen.

Here's my code associated with this form:

Private Sub cboDate_MouseDown(Button As Integer, Shift
As
Integer, X As Single, Y As Single)
With Calendar1
.Visible = Not .Visible
If .Visible Then
.SetFocus
.Value = Date
Else
cboDate.SetFocus
End If
End With
End Sub
---------------------------------
Private Sub Calendar1_Click()
With Calendar1
cboDate.Value = .Value
cboDate.SetFocus
.Visible = False
End With
End Sub
-----------------------------------
Private Sub cmdUndo_Click()
' same action as clicking Undo from the Edit menu
DoCmd.RunCommand acCmdUndo
Requery
End Sub
Private Sub Form_Current()
Me!CmdUndo.Enabled = False
End Sub
Private Sub Form_Dirty(Cancel As Integer)
Me!CmdUndo.Enabled = True
End Sub
-----------------------------
Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me! [List53])
Me.Bookmark = RS.Bookmark
End Sub
-------------------------
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

----------------------------
Private Sub CmdDelActivity_Click()
On Error GoTo Err_CmdDelActivity_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70

Exit_CmdDelActivity_Click:
Exit Sub

Err_CmdDelActivity_Click:
MsgBox Err.Description
Resume Exit_CmdDelActivity_Click

Requery

End Sub
-------------------------
Private Sub cmdUpdateScreen_Click()
On Error GoTo Err_cmdUpdateScreen_Click

DoCmd.GoToRecord , , acLast
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdUpdateScreen_Click:
Exit Sub

Err_cmdUpdateScreen_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateScreen_Click

End Sub



-----Original Message-----
Jody,

Did you get that problem fixed last week? I hope I was
able to help!

It sounds to me like when they enter their name, the
form
is trying to create a new record. Not sure why without
more information.

Is the form bound? If so, you need another form that
will
allow the user to enter their name, then open the form
filtered or apply a filter if it's already open based
on
the choice made in the other form.

I have also handled this by use of a hidden form. In
the
after update event I set the value of a field on a
hidden
form to the vaslue of the field and apply the filter
based
on the field on the hidden form.

If the form is not bound, I would need more information
on
how your retrieving records.

Hope that helps!

Kevin
-----Original Message-----
I posted my question earlier but I need a response
ASAP -
-

my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It
may
be
the way I have my data entry forms set up.

When the user first opens the data entry screen they
are
prompted to enter their name from a drop down box.
They
then click a "next" button to take them to the main
data
entry screen. This screen will show all their
travel/activity entries in a list box at the
Click to expand...
bottom
of
the
screen. When they click on any item in the list box,
they
can edit it the form at the top of the screen. I have
a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the
next
autonumber based on the records in the listbox rather
than
the entire table. How do I get the "add new activity"
to
work properly by taking out the next autonumber in the
table yet still show only the selected users
activities
in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me! [List53])
Me.Bookmark = RS.Bookmark
End Sub


-----Original Message-----
Sounds like a corruption problem to me.
Make a copy of your back end database just in case,
then run compact/repair on the original.
Chances are good that will take care of it, but
if
it
doesn't, post
back.

HTH
- Turtle

"Jody" <[email protected]>
Click to expand...
wrote
in
message
My database has linked tables. I have 10 users
that
have
a copy of the database on each of their computers.
The
main tables reside in a shared department area on
our
network. We "split" the database in order to allow
more
than one individual to have the data entry forms
open
at a
time.

It seemed to be working but I recently noticed that
when a
new record is added, instead of the system
assigning
the
next autonumber in the table (Activity_ID is my
primary
key and autonumber field) which in this case should
be
513, it tries to assign a 270 which already exists
and
I
get the following error message:

"The changes you requested to the tabe were no
successful
because they would create duplicate values in the
index,
primary key or relationship...."


I thought it should automatically put the next
number
on
the record.

Here's my code to add the new record.
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

I fairly new to VB and not sure what's
happening ....
any
suggestions?


.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
I am getting ready to send it now. I stripped out most of
the records and changed salesman names and customer info.

Jody
-----Original Message-----
Jody,

Did you send it? I have not received anything yet??

Kevin
-----Original Message-----
Kevin,

I rebuilt my tables this morning and created new
autonumbers. It seems to be working now. I may
have "corrupted" the tables when I was trying to program
the "copy" option last week. However, I would still like
to know how how to program a "copy" button so I may go
ahead and send you my db just to get your code
suggestions. This is something all my users are requesting.

Thanks -- I really appreciate all your help!
-----Original Message-----
Can you compact and send me the database. Remove any data
you feel is proprietary, but it would be helpful if a few
records were left in the database. Change anything you
feel could compromise anything. Send it to
(e-mail address removed)

Just remove the NoSpam tag on the end

If there is a seperate access backend, I will need both
files. If there is an MS SQL Server(or something similar)
backend, make an access equivalent using make table
queires and I will use that. I expect that will meet most
of my needs. I just need to look at how things are
structured. When you send it, if the application has a
backend, send the front end and backend seperately.

I will need instructions on how to get to the problem
code and how to duplicate the problem.

Also, I can look at the problem we discussed last week if
you can give me information on what the queries were
again and how you want to execute the code.

Kevin
-----Original Message-----
The value being stored in the listbox is ActivityID
(autonumber).

The "Schedule Data Entry" form shows all the activities
listed in the listbox based on the user's selection on
the "SelectName" form [SlsRepID]. The user can cycle
through each activity by clicking on the item in the
listbox revealing all details for that record in the
data
entry form located directly above the listbox. By
clicking on the "add activity" button a new data entry
screen is revealed to allow the user to enter data.
When
completed the user clicks the "update screen" button to
add the new record to the top of the list in the
listbox.

What is very strange is that sometimes the record is
added
and I notice the ActivityID (autonumber) is a number 340
when it should be 513. I'll try to add another new
record
for the salesrep and then I'll get the following error
message:

"The changes you requested to the table were not
successful because they would create duplicate entries
in
the index, primary key or relationship...."

When I look at my Activity table, I notice that the
ActivityID (autonumber) has huge gaps in the numerical
order of the numbers. I'm not sure why this is
occurring.
I have 272 records in the table. My highest ActivityID
is
512 but when I go to add a record in the raw data table,
it assigns 316 not 513; however, 316 is already assigned
and hence the above error message. Is there some sort
of
error handling code I need included?

I'm about ready to go crazy ..... especially when I'm
new
at this.

-----Original Message-----
Jody,

What is the value being stored in the listbox field?
You
display the users name, but is the field actually
storing
an ID number? Could 270 be some id value corresponding
to
the users name? The autonumber field, is that an (I am
guessing now) activity ID? I am not real clear on your
database structure.

Answer back tonight if you get this (Monday)and let me
study on it a little more.

Kevin

-----Original Message-----
No I never did get the "copy" option to work. Then
the
autonumber problem occurred.

I have two forms: the first form called SelectName
contains only an unbound combo box called SalesRep.
The
user selects their name from the combo box then clicks
a "Continue" button. The "On Click" event on
my "continue"
button runs a macro. This macro opens a form
called "Schedule Data Entry" where SlsRepID = [Forms]!
[Schedule Data Entry]![SalesRep].

The list box located at the bottom of form "Schedule
Data
Entry" lists all the travel/activities for the user.
If
the user clicks on any one of the records listed in
the
listbox called "list53" the individual record can be
edited on the form (upper half of form). The user has
command buttons on this form that add a record, delete
a
record, update/refresh, cancel.

The next autonumber in the Activity table is 513;
however,
the "add new activity" button is trying to assign an
available autonumber (270) to the record based on the
filtered records in the listbox. I need it to assign
the
next autonumber (513) based on the table NOT the
listbox.

My users like the way the form works - ability to see
all
their entries in the list box, edit, update and add
records all on one screen.

Here's my code associated with this form:

Private Sub cboDate_MouseDown(Button As Integer, Shift
As
Integer, X As Single, Y As Single)
With Calendar1
.Visible = Not .Visible
If .Visible Then
.SetFocus
.Value = Date
Else
cboDate.SetFocus
End If
End With
End Sub
---------------------------------
Private Sub Calendar1_Click()
With Calendar1
cboDate.Value = .Value
cboDate.SetFocus
.Visible = False
End With
End Sub
-----------------------------------
Private Sub cmdUndo_Click()
' same action as clicking Undo from the Edit menu
DoCmd.RunCommand acCmdUndo
Requery
End Sub
Private Sub Form_Current()
Me!CmdUndo.Enabled = False
End Sub
Private Sub Form_Dirty(Cancel As Integer)
Me!CmdUndo.Enabled = True
End Sub
-----------------------------
Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me! [List53])
Me.Bookmark = RS.Bookmark
End Sub
-------------------------
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

----------------------------
Private Sub CmdDelActivity_Click()
On Error GoTo Err_CmdDelActivity_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70

Exit_CmdDelActivity_Click:
Exit Sub

Err_CmdDelActivity_Click:
MsgBox Err.Description
Resume Exit_CmdDelActivity_Click

Requery

End Sub
-------------------------
Private Sub cmdUpdateScreen_Click()
On Error GoTo Err_cmdUpdateScreen_Click

DoCmd.GoToRecord , , acLast
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdUpdateScreen_Click:
Exit Sub

Err_cmdUpdateScreen_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateScreen_Click

End Sub



-----Original Message-----
Jody,

Did you get that problem fixed last week? I hope I
was
able to help!

It sounds to me like when they enter their name, the
form
is trying to create a new record. Not sure why
without
more information.

Is the form bound? If so, you need another form that
will
allow the user to enter their name, then open the
form
filtered or apply a filter if it's already open based
on
the choice made in the other form.

I have also handled this by use of a hidden form. In
the
after update event I set the value of a field on a
hidden
form to the vaslue of the field and apply the filter
based
on the field on the hidden form.

If the form is not bound, I would need more
information
on
how your retrieving records.

Hope that helps!

Kevin
-----Original Message-----
I posted my question earlier but I need a response
ASAP -
-

my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It
may
be
the way I have my data entry forms set up.

When the user first opens the data entry screen they
are
prompted to enter their name from a drop down box.
They
then click a "next" button to take them to the main
data
entry screen. This screen will show all their
travel/activity entries in a list box at the bottom
of
the
screen. When they click on any item in the list
box,
they
can edit it the form at the top of the screen. I
have
a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the
next
autonumber based on the records in the listbox
rather
than
the entire table. How do I get the "add new
activity"
to
work properly by taking out the next autonumber in
the
table yet still show only the selected users
activities
in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me!
[List53])
Me.Bookmark = RS.Bookmark
End Sub


-----Original Message-----
Sounds like a corruption problem to me.
Make a copy of your back end database just in case,
then run compact/repair on the original.
Chances are good that will take care of it, but
if
it
doesn't, post
back.

HTH
- Turtle

in
message
My database has linked tables. I have 10 users
that
have
a copy of the database on each of their
computers.
The
main tables reside in a shared department area on
our
network. We "split" the database in order to
allow
more
than one individual to have the data entry forms
open
at a
time.

It seemed to be working but I recently noticed
that
when a
new record is added, instead of the system
assigning
the
next autonumber in the table (Activity_ID is my
primary
key and autonumber field) which in this case
should
be
513, it tries to assign a 270 which already
exists
and
I
get the following error message:

"The changes you requested to the tabe were no
successful
because they would create duplicate values in the
index,
primary key or relationship...."


I thought it should automatically put the next
number
on
the record.

Here's my code to add the new record.
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

I fairly new to VB and not sure what's
happening ....
any
suggestions?


.

.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
I'm unable to send you the files. Our company has put a
block on our outgoing email. I will send it to you
tonight from home.
-----Original Message-----
I am getting ready to send it now. I stripped out most of
the records and changed salesman names and customer info.

Jody
-----Original Message-----
Jody,

Did you send it? I have not received anything yet??

Kevin
-----Original Message-----
Kevin,

I rebuilt my tables this morning and created new
autonumbers. It seems to be working now. I may
have "corrupted" the tables when I was trying to program
the "copy" option last week. However, I would still like
to know how how to program a "copy" button so I may go
ahead and send you my db just to get your code
suggestions. This is something all my users are requesting.

Thanks -- I really appreciate all your help!

-----Original Message-----
Can you compact and send me the database. Remove any data
you feel is proprietary, but it would be helpful if a few
records were left in the database. Change anything you
feel could compromise anything. Send it to
(e-mail address removed)

Just remove the NoSpam tag on the end

If there is a seperate access backend, I will need both
files. If there is an MS SQL Server(or something similar)
backend, make an access equivalent using make table
queires and I will use that. I expect that will meet most
of my needs. I just need to look at how things are
structured. When you send it, if the application has a
backend, send the front end and backend seperately.

I will need instructions on how to get to the problem
code and how to duplicate the problem.

Also, I can look at the problem we discussed last week if
you can give me information on what the queries were
again and how you want to execute the code.

Kevin
-----Original Message-----
The value being stored in the listbox is ActivityID
(autonumber).

The "Schedule Data Entry" form shows all the activities
listed in the listbox based on the user's selection on
the "SelectName" form [SlsRepID]. The user can cycle
through each activity by clicking on the item in the
listbox revealing all details for that record in the
data
entry form located directly above the listbox. By
clicking on the "add activity" button a new data entry
screen is revealed to allow the user to enter data.
When
completed the user clicks the "update screen" button to
add the new record to the top of the list in the
listbox.

What is very strange is that sometimes the record is
added
and I notice the ActivityID (autonumber) is a number 340
when it should be 513. I'll try to add another new
record
for the salesrep and then I'll get the following error
message:

"The changes you requested to the table were not
successful because they would create duplicate entries
in
the index, primary key or relationship...."

When I look at my Activity table, I notice that the
ActivityID (autonumber) has huge gaps in the numerical
order of the numbers. I'm not sure why this is
occurring.
I have 272 records in the table. My highest ActivityID
is
512 but when I go to add a record in the raw data table,
it assigns 316 not 513; however, 316 is already assigned
and hence the above error message. Is there some sort
of
error handling code I need included?

I'm about ready to go crazy ..... especially when I'm
new
at this.

-----Original Message-----
Jody,

What is the value being stored in the listbox field?
You
display the users name, but is the field actually
storing
an ID number? Could 270 be some id value corresponding
to
the users name? The autonumber field, is that an (I am
guessing now) activity ID? I am not real clear on your
database structure.

Answer back tonight if you get this (Monday)and let me
study on it a little more.

Kevin

-----Original Message-----
No I never did get the "copy" option to work. Then
the
autonumber problem occurred.

I have two forms: the first form called SelectName
contains only an unbound combo box called SalesRep.
The
user selects their name from the combo box then clicks
a "Continue" button. The "On Click" event on
my "continue"
button runs a macro. This macro opens a form
called "Schedule Data Entry" where SlsRepID = [Forms]!
[Schedule Data Entry]![SalesRep].

The list box located at the bottom of form "Schedule
Data
Entry" lists all the travel/activities for the user.
If
the user clicks on any one of the records listed in
the
listbox called "list53" the individual record can be
edited on the form (upper half of form). The user has
command buttons on this form that add a record, delete
a
record, update/refresh, cancel.

The next autonumber in the Activity table is 513;
however,
the "add new activity" button is trying to assign an
available autonumber (270) to the record based on the
filtered records in the listbox. I need it to assign
the
next autonumber (513) based on the table NOT the
listbox.

My users like the way the form works - ability to see
all
their entries in the list box, edit, update and add
records all on one screen.

Here's my code associated with this form:

Private Sub cboDate_MouseDown(Button As Integer, Shift
As
Integer, X As Single, Y As Single)
With Calendar1
.Visible = Not .Visible
If .Visible Then
.SetFocus
.Value = Date
Else
cboDate.SetFocus
End If
End With
End Sub
---------------------------------
Private Sub Calendar1_Click()
With Calendar1
cboDate.Value = .Value
cboDate.SetFocus
.Visible = False
End With
End Sub
-----------------------------------
Private Sub cmdUndo_Click()
' same action as clicking Undo from the Edit menu
DoCmd.RunCommand acCmdUndo
Requery
End Sub
Private Sub Form_Current()
Me!CmdUndo.Enabled = False
End Sub
Private Sub Form_Dirty(Cancel As Integer)
Me!CmdUndo.Enabled = True
End Sub
-----------------------------
Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me! [List53])
Me.Bookmark = RS.Bookmark
End Sub
-------------------------
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

----------------------------
Private Sub CmdDelActivity_Click()
On Error GoTo Err_CmdDelActivity_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70

Exit_CmdDelActivity_Click:
Exit Sub

Err_CmdDelActivity_Click:
MsgBox Err.Description
Resume Exit_CmdDelActivity_Click

Requery

End Sub
-------------------------
Private Sub cmdUpdateScreen_Click()
On Error GoTo Err_cmdUpdateScreen_Click

DoCmd.GoToRecord , , acLast
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

Exit_cmdUpdateScreen_Click:
Exit Sub

Err_cmdUpdateScreen_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateScreen_Click

End Sub



-----Original Message-----
Jody,

Did you get that problem fixed last week? I hope I
was
able to help!

It sounds to me like when they enter their name, the
form
is trying to create a new record. Not sure why
without
more information.

Is the form bound? If so, you need another form that
will
allow the user to enter their name, then open the
form
filtered or apply a filter if it's already open based
on
the choice made in the other form.

I have also handled this by use of a hidden form. In
the
after update event I set the value of a field on a
hidden
form to the vaslue of the field and apply the filter
based
on the field on the hidden form.

If the form is not bound, I would need more
information
on
how your retrieving records.

Hope that helps!

Kevin
-----Original Message-----
I posted my question earlier but I need a response
ASAP -
-

my users are up in arms! Any suggestions please?

-------------------------------------------

I ran compact/repair but it still doesn't work. It
may
be
the way I have my data entry forms set up.

When the user first opens the data entry screen they
are
prompted to enter their name from a drop down box.
They
then click a "next" button to take them to the main
data
entry screen. This screen will show all their
travel/activity entries in a list box at the bottom
of
the
screen. When they click on any item in the list
box,
they
can edit it the form at the top of the screen. I
have
a
button that will allow them to add a new activity.

What may be happening is that it tries to assign the
next
autonumber based on the records in the listbox
rather
than
the entire table. How do I get the "add new
activity"
to
work properly by taking out the next autonumber in
the
table yet still show only the selected users
activities
in
the listbox?

Thanks for your help.

Here's my listbox code:

Private Sub List53_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[Activity_ID] = " & Str(Me!
[List53])
Me.Bookmark = RS.Bookmark
End Sub


-----Original Message-----
Sounds like a corruption problem to me.
Make a copy of your back end database just in case,
then run compact/repair on the original.
Chances are good that will take care of it, but
if
it
doesn't, post
back.

HTH
- Turtle

in
message
My database has linked tables. I have 10 users
that
have
a copy of the database on each of their
computers.
The
main tables reside in a shared department area on
our
network. We "split" the database in order to
allow
more
than one individual to have the data entry forms
open
at a
time.

It seemed to be working but I recently noticed
that
when a
new record is added, instead of the system
assigning
the
next autonumber in the table (Activity_ID is my
primary
key and autonumber field) which in this case
should
be
513, it tries to assign a 270 which already
exists
and
I
get the following error message:

"The changes you requested to the tabe were no
successful
because they would create duplicate values in the
index,
primary key or relationship...."


I thought it should automatically put the next
number
on
the record.

Here's my code to add the new record.
Private Sub CmdAddActivity_Click()
On Error GoTo Err_CmdAddActivity_Click


DoCmd.GoToRecord , , acNewRec

Exit_CmdAddActivity_Click:
Exit Sub

Err_CmdAddActivity_Click:
MsgBox Err.Description
Resume Exit_CmdAddActivity_Click

End Sub

I fairly new to VB and not sure what's
happening ....
any
suggestions?


.

.

.

.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top