-----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
[email protected]
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?
.
.
.
.
.
.
.
