Updating a record; saving the old one

  • Thread starter Thread starter sara
  • Start date Start date
S

sara

I am new to VBA and forms. I could use some direction
here, if possible - sort of an outline of what I should be
doing, I think.

I have a form and subform. Enter Employee Number and see
all the charge records for that employee. The records
come into us from an outside source. I have a field on
the record "Ignore" which defaults to "NO". If I have to
correct a value on the record, I first change "NO"
to "YES" (so my queries can ignore the 'wrong' record, but
so I don't lose it - in case there's an error somewhere),
and save the original record with "YES", and then change
the value(s) as needed, and save it with Ignore = NO.

I would like the user to choose the record to change from
the sub-form (I have been trying "On Double Click" Event),
and open up a new form to edit this one record. I have to
be able to save the original record with Ignore changed
to "YES" and the record with the user's changes with
Ignore "No".

I don't know what code to write - and the order to write
it - I'm not asking for all the code, just what to do. On
what event(s) do I attach code? What commands do I use?
I can do the DoCmd.DoMenuItem SaveRecord for the change,
but I don't know what code to add - and where to add it -
to save the original record. I also have an "Undo"
button, and I want to make sure it "Undoes" both the new
changes and deletes the record I added for Ignore = Yes.

I have thought about being able to write the record to
save to a new table (Make Table Query), then append it to
the original table, and save the new record. But I can't
even get the chosen record to show up on the Form to Edit
when I double-click.

Thans so much. I am learning, and just need some
guidance - what events, what commands, rather than
detailed code. Then I'll try to figure the code out
myself.

Sara
..
 
Hi Sara

If I have understood correctly, what you are trying to do is make a copy of
your record and make the changes on the copy, while leaving the original
alone (but with the 'ignore' flag switched on). Is that right?

If so, then the first thing is probably to make sure that that is really
what you want to do. I don't know exactly how you are going to be using
your database, so I don't know whether your strategy is a good one, but it
will have various implications for how you build and use your database, so
do make sure it is the best way. Try and think about alternative ways of
doing it, and see if any of them look like being easier.

One other possibility that you could consider is to write original values to
a separate table. This would mean that your table of charge records is
correct and that you don't have a mixture of current and out of date records
in it. Just a thought.

Anyway, if you really want to keep original and new records in the same
table, then you will need to add a new record to the table when you open up
your form into which you are going to make the corrections. There are
probably many ways to do this: you could either first add a new record to
the underlying table (either with an INSERT SQL statement or with the AddNew
method of a DAO recordset object), or you could simply open the form in Add
mode (see the online help for DoCmd.OpenForm) and populate the fields on the
form, which will automatically add a new record. There is probably even a
command you can use that would copy the old record, although I'm not sure
off the top of my head what that is.

As for the event to use, it depends on how much you want the user to do to
trigger this process. You could include a button on your main form, and then
open the form on the button's click event. If you just want the process to
fire when a user clicks on the 'ignore' field of an individual record, then
you could use the Click event for the checkbox corresponding to your
'ignore' field.

I hope these thoughts are of some use. Feel free to post back here with any
more questions.

Adam
 
Adam -
Thanks - I'm working on this. First, I think we'll pull
all the "ignore records" off the table and put them in a
separate table, as you said. If we want, we can link the
tables to show the orignial records in table view if we
need that. Great idea -thanks. You see I am the only
person at our company doing this work (I am training
someone to write simple queries so I can go on vacation!),
so I am desperate for thoughts and ideas from this
community.

Now for my current stumper. I have the form/subform
working - select the employee by name or number, see all
their charges. I want the user to double click to edit
the chosen record, and pull that record to a new form, so
I can save the changed record and write the old one to the
original table (the "Yes" record) when the form to edit
the record opens.

Two questions:
1. How do I get the record the user selected to show up
on the new form? I just can't make that happen.
2. Is there a better way to do this? I am open to ideas.

And an etiquette question:
I'm working on this for a few days, then on vacation, so
if I have questions when I get back from vacation (a few
weeks from now) should I start a new thread or go back to
this one, which will be "buried" several pages in by then?

Thanks -
Sara
 
sara said:
Now for my current stumper. I have the form/subform
working - select the employee by name or number, see all
their charges. I want the user to double click to edit
the chosen record, and pull that record to a new form, so
I can save the changed record and write the old one to the
original table (the "Yes" record) when the form to edit
the record opens.

Two questions:
1. How do I get the record the user selected to show up
on the new form? I just can't make that happen.
Click to expand...

Here is a short bit of code I have, which opens a form which shows the
record currently selected in a subform.

Private Sub ContactDetails_Click()

Dim stLinkCriteria As String
Dim ContactID As Long

ContactID = Me.Contacts_Subform.Form!ID
stLinkCriteria = "[ContactID]=" & Str(ContactID)
DoCmd.OpenForm ContactsFormName, , , stLinkCriteria

End Sub

The ContactDetails button is on the main form that contains the subform. You
should be able to tweak the code for your own purposes. You'll probably need
to add a similar button to your form.

2. Is there a better way to do this? I am open to ideas.
Click to expand...

There's almost always another way to do anything. I can't think of a better
way off the top of my head, but that doesn't mean that there isn't one.
Having said that, I think this way is perfectly OK.
And an etiquette question:
I'm working on this for a few days, then on vacation, so
if I have questions when I get back from vacation (a few
weeks from now) should I start a new thread or go back to
this one, which will be "buried" several pages in by then?
Click to expand...

I'd start a new thread. I doubt whether many people look at threads that are
more than a couple of days old.

HTH

Adam
 
Adam -
Moving on...
I got this code to work - sort of. For each employee,
there are MANY charge records. I select one and update
it, close the Update form, and see the original form.

1. I think I should "refresh" the original form to see
the changed data. I know it's a requery, but I can't get
it to work. Even tho I know the form is loaded, I tried
this...

Private Sub cmdSaveOSRecord_Click()
On Error GoTo Err_cmdSaveOSRecord_Click

' Save the record when the user presses "SAVE" and Close
the form - with Requery (requery doesn't work)
DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70
DoCmd.Close

' If F_Cashier form is loaded,
' select it, requery cboFindbyNum combo box,
' and set value of cboFindbyNum combo box.
Dim ctl As Control

If IsLoaded("F_Cashier") Then
Set ctl = Forms![F_Cashier]![cboFindbyNum]
DoCmd.SelectObject acForm, "F_Cashier"
ctl.Requery

End If

Exit_cmdSaveOSRecord_Click:
Exit Sub

Err_cmdSaveOSRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveOSRecord_Click

End Sub

Also, when I'm back on the original form, if I select A
DIFFERENT record to udpate, I still get the first record
that I selected in the form to Update.

2. I tried expanding the String expression to include all
key fields, but I keep getting an error.
Error: Syntax Error (comma) in query Expression '[Cashier]
=113, [SaleDate]=4/23/2004'.

Code: (works with only Cashier declared and in String)
Private Sub cmdChangeOSRecord_Click()
Dim stLinkCriteria As String
Dim Cashier As Long
Dim SaleDate As Date
Dim StNum As Long
Dim VarType As String

Cashier = Me.SF_CashierOS.Form!sfCashier
SaleDate = Me.SF_CashierOS.Form!sfSaleDate
StNum = Me.SF_CashierOS.Form!sfStNum
VarType = Me.SF_CashierOS.Form!sfVarType

stLinkCriteria = "[Cashier]=" & Str(Cashier) & ",
[SaleDate]=" & Str(SaleDate)

DoCmd.OpenForm "F_UpdateOSRecord", , , stLinkCriteria

End Sub

Thanks. I'll work on this right up until the plan leaves!
I feel I'm close....
Sara
-----Original Message-----

Now for my current stumper. I have the form/subform
working - select the employee by name or number, see all
their charges. I want the user to double click to edit
the chosen record, and pull that record to a new form, so
I can save the changed record and write the old one to the
original table (the "Yes" record) when the form to edit
the record opens.

Two questions:
1. How do I get the record the user selected to show up
on the new form? I just can't make that happen.
Click to expand...

Here is a short bit of code I have, which opens a form which shows the
record currently selected in a subform.

Private Sub ContactDetails_Click()

Dim stLinkCriteria As String
Dim ContactID As Long

ContactID = Me.Contacts_Subform.Form!ID
stLinkCriteria = "[ContactID]=" & Str(ContactID)
DoCmd.OpenForm ContactsFormName, , , stLinkCriteria

End Sub

The ContactDetails button is on the main form that contains the subform. You
should be able to tweak the code for your own purposes. You'll probably need
to add a similar button to your form.

2. Is there a better way to do this? I am open to
Click to expand...
ideas.

There's almost always another way to do anything. I can't think of a better
way off the top of my head, but that doesn't mean that there isn't one.
Having said that, I think this way is perfectly OK.
And an etiquette question:
I'm working on this for a few days, then on vacation, so
if I have questions when I get back from vacation (a few
weeks from now) should I start a new thread or go back to
this one, which will be "buried" several pages in by then?
Click to expand...

I'd start a new thread. I doubt whether many people look at threads that are
more than a couple of days old.

HTH

Adam


.
Click to expand...
 
Hi Sara

I'm afraid I can't think of an obvious answer to your first problem. I
suggest you try a new post to see if anyone else can suggest an answer.

As for the syntax error, I think your code

stLinkCriteria = "[Cashier]=" & Str(Cashier) & ", [SaleDate]=" &
Str(SaleDate)

needs to changed to

stLinkCriteria = "[Cashier]=" & Str(Cashier) & " AND [SaleDate]=" &
Str(SaleDate)

In other words, the two clauses need to be combined with AND instead of a
comma.

HTH

Adam

sara said:
Adam -
Moving on...
I got this code to work - sort of. For each employee,
there are MANY charge records. I select one and update
it, close the Update form, and see the original form.

1. I think I should "refresh" the original form to see
the changed data. I know it's a requery, but I can't get
it to work. Even tho I know the form is loaded, I tried
this...

Private Sub cmdSaveOSRecord_Click()
On Error GoTo Err_cmdSaveOSRecord_Click

' Save the record when the user presses "SAVE" and Close
the form - with Requery (requery doesn't work)
DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70
DoCmd.Close

' If F_Cashier form is loaded,
' select it, requery cboFindbyNum combo box,
' and set value of cboFindbyNum combo box.
Dim ctl As Control

If IsLoaded("F_Cashier") Then
Set ctl = Forms![F_Cashier]![cboFindbyNum]
DoCmd.SelectObject acForm, "F_Cashier"
ctl.Requery

End If

Exit_cmdSaveOSRecord_Click:
Exit Sub

Err_cmdSaveOSRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveOSRecord_Click

End Sub

Also, when I'm back on the original form, if I select A
DIFFERENT record to udpate, I still get the first record
that I selected in the form to Update.

2. I tried expanding the String expression to include all
key fields, but I keep getting an error.
Error: Syntax Error (comma) in query Expression '[Cashier]
=113, [SaleDate]=4/23/2004'.

Code: (works with only Cashier declared and in String)
Private Sub cmdChangeOSRecord_Click()
Dim stLinkCriteria As String
Dim Cashier As Long
Dim SaleDate As Date
Dim StNum As Long
Dim VarType As String

Cashier = Me.SF_CashierOS.Form!sfCashier
SaleDate = Me.SF_CashierOS.Form!sfSaleDate
StNum = Me.SF_CashierOS.Form!sfStNum
VarType = Me.SF_CashierOS.Form!sfVarType

stLinkCriteria = "[Cashier]=" & Str(Cashier) & ",
[SaleDate]=" & Str(SaleDate)

DoCmd.OpenForm "F_UpdateOSRecord", , , stLinkCriteria

End Sub

Thanks. I'll work on this right up until the plan leaves!
I feel I'm close....
Sara
-----Original Message-----

Now for my current stumper. I have the form/subform
working - select the employee by name or number, see all
their charges. I want the user to double click to edit
the chosen record, and pull that record to a new form, so
I can save the changed record and write the old one to the
original table (the "Yes" record) when the form to edit
the record opens.

Two questions:
1. How do I get the record the user selected to show up
on the new form? I just can't make that happen.
Click to expand...

Here is a short bit of code I have, which opens a form which shows the
record currently selected in a subform.

Private Sub ContactDetails_Click()

Dim stLinkCriteria As String
Dim ContactID As Long

ContactID = Me.Contacts_Subform.Form!ID
stLinkCriteria = "[ContactID]=" & Str(ContactID)
DoCmd.OpenForm ContactsFormName, , , stLinkCriteria

End Sub

The ContactDetails button is on the main form that contains the subform. You
should be able to tweak the code for your own purposes. You'll probably need
to add a similar button to your form.

2. Is there a better way to do this? I am open to
Click to expand...
ideas.

There's almost always another way to do anything. I can't think of a better
way off the top of my head, but that doesn't mean that there isn't one.
Having said that, I think this way is perfectly OK.
And an etiquette question:
I'm working on this for a few days, then on vacation, so
if I have questions when I get back from vacation (a few
weeks from now) should I start a new thread or go back to
this one, which will be "buried" several pages in by then?
Click to expand...

I'd start a new thread. I doubt whether many people look at threads that are
more than a couple of days old.

HTH

Adam


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