Solved

Duplicate record Wizard not producing correct results.

Posted on 2014-03-01
6
220 Views
Last Modified: 2014-04-05
I have attached my sample database.  If you click on a record on the main screen it will bring you to the Case Details screen.  When you click on "create Additional Review" button it creates a new record however the fileno (automatically created) does not change, which is causing a problem. The fileno is the text box to the right of the Act 6 check box.  I want that number to change when I execute the macro "create additional Review".

The database does not contain any confidential information.
DMC-Backup-Backup.accdb
0
Comment
Question by:seamus9909
  • 3
  • 2
6 Comments
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
ID: 39898172
I could not download your db so I will take a shot in the dark.

If I understand you correctly, you want to update the file number when you do a revision. EG file 123 becomes file 123.1.

If so I suggest that you create an additional field call fldversionnumber. The you can add the following code in the after update event of the form

Me.fldversionnumber=Me.fldversionnumber + 1
0
 
LVL 84
ID: 39898400
Your database won't open - it's looking for "C:\DMCACS\DMC_Backpu_Backup_be.accdb", which of course does not exist on anyone else's machine.

Make all your tables local, then upload it again.

Also, be sure to Compact your database before uploading.

I can't see the Nav Pane either, and I cannot resize it. That may be because of the path error, but be sure that the database is easily usable, and accessible, before uploading it again.
0
 

Author Comment

by:seamus9909
ID: 39898578
I attached both the FE and BE versions of the database so you can link them locally.  

Thanks for all your help
DMC-Backup-Backup.accdb
DMC-Backup-Backup-be.accdb
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 84
ID: 39898625
You use this code to write a FileNo in the Form's BeforeUpdate event:

If Me.NewRecord Then
    Me.FileNo = Year(Date) & "1" & Format(Me.InvoiceID, "00000")
End If

You could use the same code in your "Create Additional Review" button to write a new fileno. Of course, the code above does not take into account whether the calculated value would already exist, so you may need to change that if you want unique FileNo values.

I'd also suggest you get away from macros. VBA is the language to use unless you're creating apps for your use only (and you can handle the errors that inevitably occur).
0
 

Author Comment

by:seamus9909
ID: 39898655
Scott

I already have the Beforeupdate event set to that if statement.  SO when I execute the Wizard it copies the FileNo and then doesn't think its a new record so the beforeUpdate event does not run.  So I have a duplicate number created

How would I Put that command into the macro itself?
0
 
LVL 84
ID: 39899066
then doesn't think its a new record so the beforeUpdate event does not run
The Before Update event runs every time you add or update a record, so I think you mean that the "If Me.NewRecord" test does not pass.

If your goal is to create a new record, then get rid of the macro, and do it the right way:

1) Open a recordset that contains the values you want to use for the copy - for example:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM YourTable WHERE IDField=" & Me.IDField)

2) Now create a new record:

DoCmd.RunCommand acCmdRecordsGoToNew

3) Now insert values:

Me.txControl1 = rst("Col1")
Me.txControl2 = rst("Col2")

When you do this, you could then run your code that creates the FileNo.

You should never use copy and paste to create records in a database.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now