423,832 Members | 1,902 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,832 IT Pros & Developers. It's quick & easy.

Update an unbound form

P: n/a
I have an unbound form. I have an Add New procedure that saves a new
record to the database. What is the best way to update an existing
record? In other words, clicking my "Submit record" button creates
another new instance of the record when all I want is to update a
field or two and save over the existing record.

Thanks in advance.

Troy
Sep 24 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Why are you using an unbound form?

It is possible to create an interface where you provide a mechanism for the
user to select from the choices that define an existing record, and then
populate your unbound boxes with the values from those records, and then
keep track of whether any of them changed, and provide some way for the user
to instuct you to commit or cancel the changes, and then execute an Update
query to modify the existing record.

But WHY? When a bound form does exactly that for you, without any coding?
It's also possible to use paper index cards instead of a database, and it
probably makes as much sense.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<tr******@comcast.netwrote in message
news:3a**********************************@y38g2000 hsy.googlegroups.com...
>I have an unbound form. I have an Add New procedure that saves a new
record to the database. What is the best way to update an existing
record? In other words, clicking my "Submit record" button creates
another new instance of the record when all I want is to update a
field or two and save over the existing record.

Thanks in advance.

Troy
Sep 24 '08 #2

P: n/a
<tr******@comcast.netwrote in message
news:3a**********************************@y38g2000 hsy.googlegroups.com...
>I have an unbound form. I have an Add New procedure that saves a new
record to the database. What is the best way to update an existing
record? In other words, clicking my "Submit record" button creates
another new instance of the record when all I want is to update a
field or two and save over the existing record.
Don't use "AddNew". You'll probably have something like this currently:

With rs
.AddNew
![MyField] = "Whatever"
.Update
End with

Just lose the ".AddNew" command. Be sure you're pointing at the correct
record first of course!

Keith.
www.keithwilby.com

Sep 24 '08 #3

P: n/a
"Keith Wilby" <he**@there.comwrote in message
news:48********@glkas0286.greenlnk.net...
<tr******@comcast.netwrote in message
news:3a**********************************@y38g2000 hsy.googlegroups.com...
>>I have an unbound form. I have an Add New procedure that saves a new
record to the database. What is the best way to update an existing
record? In other words, clicking my "Submit record" button creates
another new instance of the record when all I want is to update a
field or two and save over the existing record.

Don't use "AddNew". You'll probably have something like this currently:

With rs
.AddNew
![MyField] = "Whatever"
.Update
End with

Just lose the ".AddNew" command. Be sure you're pointing at the correct
record first of course!

Keith.
www.keithwilby.com
Actually, I think you'll want to replace .AddNew with .Edit
Fred Zuckerman
Sep 24 '08 #4

P: n/a
"Fred Zuckerman" <Zu********@sbcglobal.netwrote in message
news:Ow***************@nlpi069.nbdc.sbc.com...
>
Actually, I think you'll want to replace .AddNew with .Edit
Fred Zuckerman

Whoops! Yes, thanks Fred. Although I do take Allen's point about using a
bound form.

Regards,
Keith.

Sep 24 '08 #5

P: n/a
I agree with Allen about bound forms. I have also read Allen's
tutorial on locking bound forms. My problem with a bound form in this
case is this:

Form B is being opened from Form A, a bound form. There is one field
on Form A whose value is assigned to a field on Form B. This field is
the PK from Form A's table. I am using this field as an index for Form
B's table, but it is not part of a composite key (referential
integrity is not that important for this). This field is the link for
the one to many relationship from Table A to Table B.

My question is how can I bind this field to Form B if I pass its value
from Form A? I want to pass it because I do not want the user to
manually input this field. But, if it is unbound it can't provide its
value to Form B's underlying table without VBA code. I know I'm
missing something here, but I can't quite clarify what it is.

Hope this makes sense.

Thanks in advance.

Troy Lee

Sep 24 '08 #6

P: n/a
Pass the value in OpenArgs, e.g.:
DoCmd.OpenForm "B", DataMode:=acFormAdd, OpenArgs:=Me.[ID]

Then use the Load event of form B to assign the value to the field if it was
passed in OpenArgs and the form is at a new record. That should work even if
you open B in dialog mode.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<tr******@comcast.netwrote in message
news:3f**********************************@p25g2000 hsf.googlegroups.com...
>I agree with Allen about bound forms. I have also read Allen's
tutorial on locking bound forms. My problem with a bound form in this
case is this:

Form B is being opened from Form A, a bound form. There is one field
on Form A whose value is assigned to a field on Form B. This field is
the PK from Form A's table. I am using this field as an index for Form
B's table, but it is not part of a composite key (referential
integrity is not that important for this). This field is the link for
the one to many relationship from Table A to Table B.

My question is how can I bind this field to Form B if I pass its value
from Form A? I want to pass it because I do not want the user to
manually input this field. But, if it is unbound it can't provide its
value to Form B's underlying table without VBA code. I know I'm
missing something here, but I can't quite clarify what it is.

Hope this makes sense.

Thanks in advance.

Troy Lee
Sep 25 '08 #7

P: n/a
I just wanted to chime-in on Allen's reply -- as a long-time Access/
SQL developer currently dealing with a couple huge Access applications
that use 90% unbound forms, I say say for sure that bound forms should
be used whenever possible.
On Sep 24, 10:12*am, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
Why are you using an unbound form?
Sep 25 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.