Update a table using a form

[Guest]

I need to update certain fields in a table using a form. How do I associate
the new data with the proper record. I'm already using Auto Numbering but the
form will not let me enter a number, it always goes to record # 1. I need to
update the last record in the table by entering the current time. Can you
Record a Macro in Access like you can in Word?

 

[fredg]

I need to update certain fields in a table using a form. How do I associate
the new data with the proper record. I'm already using Auto Numbering but the
form will not let me enter a number, it always goes to record # 1. I need to
update the last record in the table by entering the current time. Can you
Record a Macro in Access like you can in Word?

Not sure of what you mean by "it always goes to record # 1", but make
sure the forms Allow Additions property is Yes and it's Data Entry
property is No. They're both on the Form's property sheet Data tab.

In Access 'Macro' has a different meaning than it does in Excel and
Word.
No, you cannot record a Macro, nor it's Access equivalent of VBA code
as you can in Excel or in Word. You'll need to write the VBA code
yourself.

 

[Guest]

Thanks for the info. I have the properties set as you advised. The problem
is, when you open the form the "reference number" (Auto number of the record)
always goes to record 1 and you cannot type anything in this field. You can
use the "next record" or "Last record" controls at the bottom of the form.
I'm trying to eleminate this step because if you forget to go to the last
record you will update the first record by mistake..

 

[John Vinson]

Thanks for the info. I have the properties set as you advised. The problem
is, when you open the form the "reference number" (Auto number of the record)
always goes to record 1 and you cannot type anything in this field. You can
use the "next record" or "Last record" controls at the bottom of the form.
I'm trying to eleminate this step because if you forget to go to the last
record you will update the first record by mistake..

You're misunderstanding how forms work, it seems. The "Reference
Number" isn't "going to 1". The Form is a window, which displays the
data in the table; if you don't do something otherwise, it simply is
displaying the first record in the Form's recordsource.

If you want to ALWAYS go to the new, empty record to enter new data,
set the Form's DataEntry property to True. This has the disadvantage
that you cannot review or edit existing records.

If you want to do both, you'll need just a line of VBA code. Open the
Form in design view; view its Properties; select the Event tab, and
find the Open event. Click the ... icon by this event and choose "Code
Builder". Access will give you a Sub and End Sub line; edit it to

Private Sub Form_Open(Cancel as Integer) <<< Access gives you this
DoCmd.GoToRecord acNewRecord
End Sub

This will move the "window" to the new record when you open the form,
but you'll still be able to use the navigation buttons to view and
edit other records if you wish.

John W. Vinson[MVP]

 

[fredg]

Thanks for the info. I have the properties set as you advised. The problem
is, when you open the form the "reference number" (Auto number of the record)
always goes to record 1 and you cannot type anything in this field.

Why would you want to 'enter' the AutoNumber field?
That field is given it's autonumber value by Access when you add the
new record and cannot be changed.

You can use the "next record" or "Last record" controls at the bottom of the form.
I'm trying to eleminate this step because if you forget to go to the last
record you will update the first record by mistake..

If you always want to open the form at a new record, code the form's
Open event:
DoCmd.RunCommand acCmdRecordsGoToNew
** snipped **

 

[Guest]

This may be a hopeless case. I have the form set up to modify a field in a
table (not enter a new record). I use the Auto Number as a reference to find
the record I want to modify (usually the last record). When the form opens it
always starts at the first record (#1). If I enter data into the form it
modifies record #1. What I need to do is modify the last existing record in
the table. This form is accessed thru a Main Menu I have set up. When I enter
the code you suggested in the Open field it causes a "Compile Error:
Ambigious name detected" and refers to another form. I tried changing the
code to "DoCmd.GoToRecord acLastRecord" but this didn't help.
I know how difficult it is to trouble shoot something you can't get your
hands on. Thanks for the help.

 

[John Vinson]

This may be a hopeless case. I have the form set up to modify a field in a
table (not enter a new record). I use the Auto Number as a reference to find
the record I want to modify (usually the last record). When the form opens it
always starts at the first record (#1). If I enter data into the form it
modifies record #1. What I need to do is modify the last existing record in
the table. This form is accessed thru a Main Menu I have set up. When I enter
the code you suggested in the Open field it causes a "Compile Error:
Ambigious name detected" and refers to another form. I tried changing the
code to "DoCmd.GoToRecord acLastRecord" but this didn't help.
I know how difficult it is to trouble shoot something you can't get your
hands on. Thanks for the help.

Not at all hopeless - just need to figure out what the real problem
is!

Open the Form in design view.
View its Properties.
On the Events tab find the Open event.
Click the ... icon by it.
Choose Code Builder. Access will open a VBA editor window with the Sub
and End Sub lines below. Edit it to:

Private Sub Form_Open(Cancel as Integer)
DoCmd.GoToRecord acLast
End Sub

Two questions:

- How can you be sure that the LAST record is the correct one to edit?
- How are you opening the form? Might it better be based on a Query
which returns only the desired record, so the problem of navigating
doesn't arise?

John W. Vinson[MVP]

 

[Guest]

Thanks for hanging in there with me John. I tried your last suggestion and
got the following, "Run-Time error 2487 The Object Type argument for the
action or method is blank or invalid".
A little background - I'm putting together a dispatch system for the TTU
Police Dept. This is a volunteer effort and sorely needed. They normally only
dispatch one call at a time, so 95% of the time they will be updating the
last record. On those times when more than one call is active they can go
back one call from the end. The dispatchers are not proficient with Access
and it is essential that they not "monkey around" in the table itself. I
thought if all data entry was accomplished using forms it would be safer.

 

[John Vinson]

Thanks for hanging in there with me John. I tried your last suggestion and
got the following, "Run-Time error 2487 The Object Type argument for the
action or method is blank or invalid".

Please copy and paste the actual code to a message. This CAN be fixed.
Where did you put the code? Does the form control property show [Event
Procedure], or did you try to copy the code into the property itself?

A little background - I'm putting together a dispatch system for the TTU
Police Dept. This is a volunteer effort and sorely needed. They normally only
dispatch one call at a time, so 95% of the time they will be updating the
last record. On those times when more than one call is active they can go
back one call from the end. The dispatchers are not proficient with Access
and it is essential that they not "monkey around" in the table itself. I
thought if all data entry was accomplished using forms it would be safer.

Well, you're absolutely right about that! I've actually developed a
police dispatching application (which, alas, didn't get implemented
for a variety of reasons). It was for a state-police department and
would have had upwards of twelve dispatchers juggling a dozen calls at
once - it had forms with search facilities to easily find a previous
call. Do you have a CallNumber or some such field? It should be easy
enough to put a combo box on the form to select a call by some unique
identifier.

John W. Vinson[MVP]

 

[Guest]

John,
WOW! A dispatch system for an call center of that size must have been a huge
undertaking. I've been involved in training and helping to refine a system of
that size, I can't imagine coding it though! It wasn't in PA was it?

Below is the code from the Form in question. 10-97 is their code for the
officer arriving on-scene. The 10-97 screen's function is to place the time
when the officer arrived in the Table . I have the same problem with the form
I'm using when the officer goes back in-service. The solution should be the
same though.
I am simply using Auto Number to give each call a unique identifier.

I followed your instruction and placed an [Event Procedure] in "On Open" in
the properties of the form. This is the code:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord acLast
End Sub

With this code in place I get the error msg when I try to open it.

"Run-Time error 2487 The Object Type argument for the
action or method is blank or invalid".


Thanks,
Dave

Thanks for hanging in there with me John. I tried your last suggestion and
got the following, "Run-Time error 2487 The Object Type argument for the
action or method is blank or invalid".

Please copy and paste the actual code to a message. This CAN be fixed.
Where did you put the code? Does the form control property show [Event
Procedure], or did you try to copy the code into the property itself?

A little background - I'm putting together a dispatch system for the TTU
Police Dept. This is a volunteer effort and sorely needed. They normally only
dispatch one call at a time, so 95% of the time they will be updating the
last record. On those times when more than one call is active they can go
back one call from the end. The dispatchers are not proficient with Access
and it is essential that they not "monkey around" in the table itself. I
thought if all data entry was accomplished using forms it would be safer.

Well, you're absolutely right about that! I've actually developed a
police dispatching application (which, alas, didn't get implemented
for a variety of reasons). It was for a state-police department and
would have had upwards of twelve dispatchers juggling a dozen calls at
once - it had forms with search facilities to easily find a previous
call. Do you have a CallNumber or some such field? It should be easy
enough to put a combo box on the form to select a call by some unique
identifier.

John W. Vinson[MVP]

 

[John Vinson]

John,
WOW! A dispatch system for an call center of that size must have been a huge
undertaking. I've been involved in training and helping to refine a system of
that size, I can't imagine coding it though! It wasn't in PA was it?

Idaho, actually. The main reason it failed is that the client (over my
objections) tried to set it up to run over a WAN (with a client
database in Pocatello connected to a backend in Meridian). SQL/Server
would have worked but... oh well... :-{(

Below is the code from the Form in question. 10-97 is their code for the
officer arriving on-scene. The 10-97 screen's function is to place the time
when the officer arrived in the Table . I have the same problem with the form
I'm using when the officer goes back in-service. The solution should be the
same though.
I am simply using Auto Number to give each call a unique identifier.

I'd really suggest using a programmatically assigned IncidentID.
Autonumbers always will have gaps, can become random (if you replicate
so that remote users can enter data), and in general aren't suited for
fields which will be human-visible.

I followed your instruction and placed an [Event Procedure] in "On Open" in
the properties of the form. This is the code:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord acLast
End Sub

Very odd. Try the form's Load event instead, though either ought to
work!

With this code in place I get the error msg when I try to open it.

"Run-Time error 2487 The Object Type argument for the
action or method is blank or invalid".

John W. Vinson[MVP]

 

[Guest]

John,
I got the on-scene form to work using the following:

Option Compare Database

Private Sub DTM1097_GotFocus()
'If DTM1097.Text = Null Then
DTM1097 = Time
'End If
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acLast
End Sub

However, the same code will not work on the call completed form! It goes to
the last record but will not enter the time.

Option Compare Database

Private Sub DTM1098_Enter()
If DTM1098.Text = Null Then
DTM1098.Text = Time
End If
End Sub

Private Sub DTM1098_GotFocus()
If DTM1098.Text = Null Then
DTM1098.Text = Time
End If
End Sub



Private Sub Form_Load()

End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acLast
End Sub

Thanks,
Dave

I'd really suggest using a programmatically assigned IncidentID.
Autonumbers always will have gaps, can become random (if you replicate
so that remote users can enter data), and in general aren't suited for
fields which will be human-visible.

I followed your instruction and placed an [Event Procedure] in "On Open" in
the properties of the form. This is the code:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord acLast
End Sub

Very odd. Try the form's Load event instead, though either ought to
work!

With this code in place I get the error msg when I try to open it.

"Run-Time error 2487 The Object Type argument for the
action or method is blank or invalid".

John W. Vinson[MVP]

 

[John Vinson]

However, the same code will not work on the call completed form! It goes to
the last record but will not enter the time.

I'm not at all sure what you're trying to accomplish here. Do you want
the record timestamped every time the user looks at the record, or
opens the form, or what?? That seems too much a "hair trigger".

I'd suggest just putting a command button on the form, or - as I did
for the ISP - put code in the time field's Doubleclick event:

Private Sub txtTime_DoubleClick()
Me!txtTime = Time()
End Sub


Also, rather than using Time(), which gives the time of day on
December 30, 1899, I'd suggest using Now() which stores the date and
time in one field. I presume (hope anyway!) that you're also storing
the date somewhere; but storing incident times using Now() has the
advantage that you can handle times across midnights without having to
keep two different fields in synch.

John W. Vinson[MVP]

 

[Guest]

John,
I really appreciate all of your help and I don’t want to abuse your
kindness. If you have a few minutes to elaborate on a couple of things it
would be helpful. I have never done anything like this dispatch system before
so I’m learning as I go along. I’ve used Access in the past but only for
queries and reports.

I'd really suggest using a programmatically assigned IncidentID.

How do I do this? I didn’t see anything in Help that refers to it.

I'm not at all sure what you're trying to accomplish here. Do you want
the record timestamped every time the user looks at the record, or
opens the form, or what?? That seems too much a "hair trigger".

I don’t like the way this works either but I didn’t know how else to go
about it. I’m using a Switchboard that opens the desired forms. I’m trying to
allow the dispatcher to enter a date/time that the officer called on-scene.
It needs to be entered into the appropriate record (usually, but not always
the last).

I'd suggest just putting a command button on the form, or - as I did
for the ISP - put code in the time field's Doubleclick event:

I’m not sure what you mean.


Thanks for everything
Dave

 

[John Vinson]

John,
I really appreciate all of your help and I don’t want to abuse your
kindness. If you have a few minutes to elaborate on a couple of things it
would be helpful. I have never done anything like this dispatch system before
so I’m learning as I go along. I’ve used Access in the past but only for
queries and reports.


How do I do this? I didn’t see anything in Help that refers to it.

It isn't in the Help, though it turns up a lot on the newsgroups.
Basically, you would use a simple Long Integer field and increment it
using VBA code when a new incident is added. If the IncidentID is the
*only* required field in the table, you could use code like this in
the Form's BeforeInsert event (which fires when a new record is first
created):

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtIncidentID = DMax("[IncidentID]", "[Incidents]") + 1
Me.Dirty = False
End Sub

This will look up the largest existing IncidentID; add one, and write
the new number to the textbox txtIncidentID (which you would have
bound to the IncidentID field in the table); and immediately save the
record to disk by setting the Form's Dirty property to False.

I don’t like the way this works either but I didn’t know how else to go
about it. I’m using a Switchboard that opens the desired forms. I’m trying to
allow the dispatcher to enter a date/time that the officer called on-scene.
It needs to be entered into the appropriate record (usually, but not always
the last).


I’m not sure what you mean.

Well... just what I say.

The dispatcher should - in my opinion - have more control over the
"date/time that the officer called onscene" than can be provided by
using the Form's Open or GotFocus events. Think about it: the form
might already be open when the officer calls in; the dispatcher might
need to enter records from a couple of hours ago, typing in the time;
etc.

It may be simplest for the dispatcher to a) get a call from the
officer; b) open the Form (if it's not already open) and select the
incident - perhaps it can default to the most recent but you do need
the option; and c) doubleclick in the date/time field indicating when
the officer arrived. The code I posted can be copied and pasted into
the Event Procedure for that textbox, and will fill in the current
system clock date and time when the dispatcher doubleclicks.

Maybe I'm still not understanding what specific difficulty you are
encountering! Is it the form navigation, or what?

John W. Vinson[MVP]

 

[Guest]

John,
I think you are thinking over my head. What little I know about Access I’ve
learned on my own by playing with it. The things that are simple for you are
sometime things I’ve never heard of.
The form to place the officer on-scene has a Text Box for the Incident #
(Auto number) and a Text Box for the date/time. I’ve gotten to the point that
the Form opens with the last Incident # and the current date/time filled in.
I’ve placed a Save button on the form to save and close it. The dispatcher
can change the date/time if necessary. The only problem I’m having with it
now is this – If you move back to a previous record it places the current
date/time in EVERY record you move to. So if I go back 2 calls to put an
officer on-scene I have just placed the on-scene date/time in all of them.
The second issue I have is the Form for call completed. I am trying to do
the same with this form with 2 additional boxes to record if a report was
written (check box) and if arrests were made (number). I’ve been able to have
this box open to the last call but I can’t get it to enter the current
date/time. It too changes every record I go to.

Dave

John,
I really appreciate all of your help and I don’t want to abuse your
kindness. If you have a few minutes to elaborate on a couple of things it
would be helpful. I have never done anything like this dispatch system before
so I’m learning as I go along. I’ve used Access in the past but only for
queries and reports.


How do I do this? I didn’t see anything in Help that refers to it.

It isn't in the Help, though it turns up a lot on the newsgroups.
Basically, you would use a simple Long Integer field and increment it
using VBA code when a new incident is added. If the IncidentID is the
*only* required field in the table, you could use code like this in
the Form's BeforeInsert event (which fires when a new record is first
created):

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtIncidentID = DMax("[IncidentID]", "[Incidents]") + 1
Me.Dirty = False
End Sub

This will look up the largest existing IncidentID; add one, and write
the new number to the textbox txtIncidentID (which you would have
bound to the IncidentID field in the table); and immediately save the
record to disk by setting the Form's Dirty property to False.

I don’t like the way this works either but I didn’t know how else to go
about it. I’m using a Switchboard that opens the desired forms. I’m trying to
allow the dispatcher to enter a date/time that the officer called on-scene.
It needs to be entered into the appropriate record (usually, but not always
the last).


I’m not sure what you mean.

Well... just what I say.

The dispatcher should - in my opinion - have more control over the
"date/time that the officer called onscene" than can be provided by
using the Form's Open or GotFocus events. Think about it: the form
might already be open when the officer calls in; the dispatcher might
need to enter records from a couple of hours ago, typing in the time;
etc.

It may be simplest for the dispatcher to a) get a call from the
officer; b) open the Form (if it's not already open) and select the
incident - perhaps it can default to the most recent but you do need
the option; and c) doubleclick in the date/time field indicating when
the officer arrived. The code I posted can be copied and pasted into
the Event Procedure for that textbox, and will fill in the current
system clock date and time when the dispatcher doubleclicks.

Maybe I'm still not understanding what specific difficulty you are
encountering! Is it the form navigation, or what?

John W. Vinson[MVP]

 

[John Vinson]

If you move back to a previous record it places the current
date/time in EVERY record you move to. So if I go back 2 calls to put an
officer on-scene I have just placed the on-scene date/time in all of them.
The second issue I have is the Form for call completed. I am trying to do
the same with this form with 2 additional boxes to record if a report was
written (check box) and if arrests were made (number). I’ve been able to have
this box open to the last call but I can’t get it to enter the current
date/time. It too changes every record I go to.

What are the Control Source properties of these textboxes? It sounds
like you've set the date field's Control Source to

=Date()

(or perhaps =Now()) instead of the name of the table field in which
you wish to store the data.

The form's Control Source will be either (a) the name of the Table
field into which the data should be stored or (b) an expression which
will NOT be stored but will instead be displayed (no matter which
record is on display).

You want the OfficerOnScene field (whatever it's named) in the Control
Source. However the date gets entered - from the control's Default
Value property, VBA code, or from the user typing data in manually, it
will be stored and then displayed when you navigate to the record.

John W. Vinson[MVP]

 

[Guest]

John,
The Control Source is set to the name of the Table
field into which the data should be stored (1097).
The date/time comes from the Event Procedure in On Got Focus
I'm trying to set it up so it will only enter the date/time in the call#
selected and only when this field is blank.

Option Compare Database

Private Sub DTM1097_Enter()
If DTM1097.Text = Null Then
DTM1097 = Now()
End If
End Sub

Private Sub DTM1097_GotFocus()
'If DTM1097.Text = Null Then
DTM1097 = Now()
'End If
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acLast
End Sub

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim stDocName As String

stDocName = "Save 1097"
DoCmd.RunMacro stDocName

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

 

[John Vinson]

John,
The Control Source is set to the name of the Table
field into which the data should be stored (1097).

You have a *FIELD* named 1097!? I'd avoid numeric fieldnames.

The date/time comes from the Event Procedure in On Got Focus
I'm trying to set it up so it will only enter the date/time in the call#
selected and only when this field is blank.

Why not set its Default proprty to Now() instead? The Enter() and
GotFocus() events fire whenever you click or tab into the control;
they will not fire if you just open the form. What do you mean by
"selected" in this case? How are you "selecting" a call number? And
what on earth are you doing running a Macro (there's NOTHING that a
macro can do that VBA can't), and what does Save 1097 do?

It looks like you're using five procedures and one macro to do what
could be done with a simple Default property. Clearly I'm mistaken,
but I don't see what you're trying to do!

John W. Vinson[MVP]

 

[Guest]

John,
Thanks for all of your time and effort trying to help me with this. I'm
sorry I wasn't articulate enough to explain what I am trying to do. As I
said, I'm trying to learn this thing as I go along. I think I have it working
well enough for now though.
Thanks again,
Dave