Advertisement

05.11.2008 at 12:20PM PDT, ID: 23392989
[x]
Attachment Details

Access 2007 -  Field Increment and record copy

Asked by SirTKC in VB Script, Access Forms, Access Coding/Macros

Hi, I am back !

Here my scenario

I have form (source:Query Q_Design) With Subforms (3 total) Lets call them Sub1, Sub2 and Sub3.
ON my Main form I have a special numbering sequence made of Project_Number, Prototype_Number and Revision_Number. None of them are AutoNumbers

I need to create a new revision by copying the Project_Number and the Prototype_Number and increment ONLY the Revision_Number (+1 after the highest revision available for this particular Prototype_Number and Project_Number)
And at the same time, copy the exact same content from the main form and its sub forms in order to create a duplicate where only the Revision_Number will have increased from 1

I thought this one would be easy but after many consideration and trials, it failed...

I have started with the code below but it keep returning "Item not found in this collection". And I did'nt reach the task of integrating the subform :-/

A little help is needed.

Thanks

Here is the code...

Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

Private Sub Command309_Click()
On Error GoTo ErrorInfo
 
Dim RS As DAO.Recordset
Dim strSQL As String
 
strSQL = "SELECT Design.*, Design.PPro " & _
            "FROM Design " & _
            "WHERE (((Design.PPro)= " & [Forms]![Concepts]![PPro] & " ) AND ((Design.PNum)= " & [Forms]![Concepts]![Design.PNum] & " ));"
 
Set RS = CurrentDb.OpenRecordset(strSQL)
Dim LineNo As Integer
 
    RS.AddNew
    LineNo = Me.PRev + 1
    RS!ProjID = Me.[Design.ProjID]
    RS!PPro = Me.PPro
    RS!PRev = LineNo
    RS.Update
 
RS.Close
 
Me.Requery: Me.Refresh: Me.Recordset.MoveLast
 
ErrorInfo:
MsgBox Error$
 
 
End Sub
[+][-]05.11.2008 at 02:54PM PDT, ID: 21543631

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.11.2008 at 03:18PM PDT, ID: 21543716

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.11.2008 at 04:36PM PDT, ID: 21543928

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.11.2008 at 04:36PM PDT, ID: 21543930

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.11.2008 at 05:10PM PDT, ID: 21544013

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.12.2008 at 01:50AM PDT, ID: 21545371

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: VB Script, Access Forms, Access Coding/Macros
Sign Up Now!
Solution Provided By: koutny
Participating Experts: 1
Solution Grade: B
 
 
[+][-]05.12.2008 at 11:01AM PDT, ID: 21549156

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.12.2008 at 12:31PM PDT, ID: 21549839

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]05.12.2008 at 01:36PM PDT, ID: 21550311

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.12.2008 at 02:27PM PDT, ID: 21550671

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628