Copy Previous record

  • Thread starter Thread starter J.J.
  • Start date Start date
J

J.J.

I am using an Append Query run by a Macro which does successfuly copy the old
record over. However SetWarnings to No in the macro is not supported by
Access 2007 which creates compatiblity issues.
I tried Allen Browne's code but it is too complex for me to edit. I am still
tinkering with the following code that won't run. I hate to be a pain but I
have something wrong. Any ideas please?

Dim strSearch As String
Dim ctl As Control
' form is open with data source set to secondary table "IntakeData"
Set rst = Me.RecordsetClone
' set string value for match up where ClientID is primary key of "Client"
table linked
' to secondary table "IntakeData"
strSearch = str(Me!ClientID)
' find a match preferably the last record ( try FindLast?) that has the same
' ClientID as current form
rst.FindFirst "ClientID= " & strSearch
If rst.NoMatch Then
' There is no record match & nothing happens so tell user no cigar
MsgBox "No Previous Record Exists. Unable to Duplicate."
Else
' copy all the data over from previous record to current record
For Each ctl In Me.Controls
' need to copy all records except of course the old IntakeID
' there must be a more effcient means to copy all but the
' IntakeID besides having to tag all other fields?
If ctl.Tag = "RepeatPrevious" Then
' copy old field value to new field value
ctl.Value = .Fields(ctl.IntakeDta)
End If
Next
End If
' All records are copied now but still need code here to refresh open
form?
' this lets user add or edit before moving on
Exit_Dupli_Click: etc etc.
 
I am using an Append Query run by a Macro which does successfuly copy the
old
record over. However SetWarnings to No in the macro is not supported by
Access 2007 which creates compatiblity issues.
Click to expand...

How about ditching your code below and bringing the Append query into VBA?
In that case, it's a one-liner:

CurrentDb.Execute "YourQuery"


And for the warnings, it turns into 3 lines (I'm pretty sure this still
works in 07, but I use 03)

DoCmd.SetWarnings(False)
CurrenDB.Execute "YourQuery"
DoCmd.SetWarnings(True)


Just be sure to put a SetWarnings(True) in your exit code, just in case
something goes wrong and the error handler is called, in which case the line
after your Execute may not run. In fact, I make it a standard to put
SetWarnings(True) right in my error handler, just in case. (This may be the
reasoning behind Access pulling this feature out from macros in '07 - there's
no way to catch it using a macro).

Also, check into the vbFailOnError constant as an argument to Currendb.Execute

This should be all you need to accomplish what you are looking for.
Expecially easy if the query already runs from a macro... just move the query
to VB.

Only, the only macro I will ever use is AutoExec. They just plain don't
have the versitility that vb does.

If changing the macro calls to vb functions calls will be troublesome
throughout the entire db, you can 'cheat' a little, and leave the macro, but
change it to RunCode() and then put your code for setwarnings/append query in
a function called by the macro. If you've got say 20 places in a
pre-existing db this might save a little bit of headache, though it isn't
very elegant.

hth

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill
 
Thank you Jack. I haven't tried it yet but this ought to work. I didn't
know you could handle query warnings through VB. That's why I come to this
site and learn from the pros.
One question does remain:
What code will refresh the form to display the copied over data that was
being handled by the macro action - Requery.

Again, your help is very much appreciated
 
What code will refresh the form to display the copied over data that was
being handled by the macro action - Requery.
Click to expand...

As long as you are in the Form's module

Me.Requery

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill
 
My Query ran fine without VB which seems to have more pitfalls than ... I
tried running the following code but get a 3061 error message - "too few
parameters: 2 expected." I looked up other posts with this problem and the
resposnses are varied and only confuse me more. What I suspect is happening
is that there are two items in my query needing to be updated from outside
the "IntakeData" table. OrderID is populated from Forms! and the RecordDate
from =Date(). The SQL statement is too long but in part reads ...SELECT
IntakeData.ClientID, [Forms]![AssessmentMain].[OrderID] AS Expr1, Date() AS
Expr2, IntakeData.FirstInstance...

Some of the responses to the error problem suggest that that outside
parameters need to be declared before the query runs. The problem is that
the suggested syntax in the more complex examples has me stumped. Here is my
code. How would I reference them?

' run Query to copy over last matching record based on ClientID
Private Sub Assess1_Click()
On Error GoTo Err_Assess1_Click
Dim db As Database
Dim rst As Recordset

' turn off allwarnings before query runs
'DoCmd.SetWarnings (False)
Set db = CurrentDb()
CurrentDb.Execute "AssessmentQuery"
' refresh form
Me.Requery
DoCmd.SetWarnings (True)
rst Nothing

Exit_Assess1_Click:
Exit Sub
etc.

Thank you
 
Sorry about that... db.Execute does not evaluate parameters. Use this
instead:

DoCmd.RunSQL



<Quoting Allen Browne>
However, Execute is not as easy to use if the action query has parameters
such as [Forms].[Form1].[Text0]. If you run that query directly from the
Database Window or via RunSQL, the Expression Service (ES) in Access resolves
those names and the query works. The ES is not available in the Execute
context, so the code gives an error about "parameters expected."
</Quoting Allen Browne>

Here's the link that came from if you want some more info...
http://allenbrowne.com/ser-60.html



--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill
 
Hi Jack,
The RunSql won't do it but the DoCmd makes 'er work. Here is what I tinkered
with and got it to run without a hicup.
stDocName = "AssessmentQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Thank you once again!
 
Back
Top