Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

One Module and four forms

Posted on 2015-02-06
12
Medium Priority
?
155 Views
Last Modified: 2015-02-10
Hello,

I have a module that I copied the coding to all my subform "On Click" button.  The module works for the first form and not the second. I copied the coding the same way to all the subform respect code sheeting.

It's suppose to take the value that is in one table (As all my forms write to one table) and transpose the values as my boss would like to see them into another table.

Please let me know if you want more info..
Sub Transposer()

Dim db As DAO.Database
Dim tdfNewDef As DAO.TableDef
Dim fldNewField As DAO.field
Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
Dim i As Integer, j As Integer, k As Integer

Set db = CurrentDb()

db.Execute "delete from tblFinal_Transposed where Change <> 'Initial'"

For k = 11 To 38 Step 3
    Call AddRecord(k)
Next k
  
db.Close

End Sub

Private Sub AddRecord(field As Integer)

Set db = CurrentDb()
Set rstSource = db.OpenRecordset("Final_Table")
Set rstTarget = db.OpenRecordset("tblFinal_Transposed")

rstSource.MoveLast
rstTarget.MoveLast

rstSource.MoveFirst

For j = 0 To rstSource.RecordCount - 1
        If rstSource.Fields(field) <> 0 Then
            rstTarget.AddNew
            rstTarget.Fields(0) = rstSource.Fields(1)
            rstTarget.Fields(1) = rstSource.Fields(2)
            rstTarget.Fields(2) = rstSource.Fields(3)
            rstTarget.Fields(3) = rstSource.Fields(4)
            rstTarget.Fields(4) = rstSource.Fields(field).Name
            rstTarget.Fields(5) = rstSource.Fields(field + 1)
            rstTarget.Fields(6) = rstSource.Fields(field + 2)
                For i = 7 To rstTarget.Fields.Count - 1
                    If rstTarget.Fields(i).Name = rstSource.Fields(5) Then
                        rstTarget.Fields(i) = rstSource.Fields(field)
                    Else: rstTarget.Fields(i) = 0
                    End If
                Next i
            rstTarget.Update
        End If
    rstSource.MoveNext
Next j

db.Close
End Sub

Open in new window

0
Comment
Question by:Jass Saini
  • 6
  • 3
  • 3
12 Comments
 
LVL 31

Accepted Solution

by:
hnasr earned 750 total points
ID: 40595108
Upload a sample database, and tell us what to do and what output to expect.
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 750 total points
ID: 40595204
I don't see anything in your code that would pertain to any subform in particular so why are you inserting this same code in 4 subforms?  You could just have the code in a public module and call the function from each subform.  Or put the code and the button on the main form.  

Also what is making you think that it works in one form and not the other.  You have a line to empty the target table before running the process.  Were you expecting the other forms to append to your target table?  

Ron
0
 

Author Comment

by:Jass Saini
ID: 40598610
Will it work on the Main form..even though it's checking for the fields that are on the subform?  And yes I was expecting it to append the other forms to my target table...How do I make it a public module and call the function?  I am still very new to Access. I know how to call...but this looks like a Private module and not a Public one
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 31

Expert Comment

by:hnasr
ID: 40598850
Try to recreate the issue, in a sample database, and upload..
0
 

Author Comment

by:Jass Saini
ID: 40598920
Sorry..I would have a hard time trying to recreate the issue.  I will ask someone in the office. Thank you for your help!
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40598993
even though it's checking for the fields that are on the subform

Can you point out where in your code that you think it's checking for anything in your subform.  It only looks like it's transposing fields from one table into another table.  As it stands, doesn't matter where you put this code.

Ron
0
 

Author Comment

by:Jass Saini
ID: 40599021
Ok..Thank You!
0
 
LVL 31

Expert Comment

by:hnasr
ID: 40599352
You don't need to do a lot of retyping: Copy exisiting database, remove any not relevant object (tables or reports), remove sensitive data and leave few representative records, then upload.

Explain what to do and what to expect.

I am happy to finish this with you.

The code can be written in a public procedure and called with proper arguments.

If you resolved the issue, please put here how the presented comments had helped you.

For example, recreating the issue, can trigger an idea to solve a problem.
0
 

Author Comment

by:Jass Saini
ID: 40599663
I figured it out..Thank you
0
 

Author Comment

by:Jass Saini
ID: 40599667
The guy who wrote the code was only looking at a certain number of fields

For k = 11 To 38 Step 3
    Call AddRecord(k)

Open in new window


I changed it to include all my fields that were needed..changed 38 to 100.  Thus covering all the fields in my source table
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40599758
I still don't see the need to have this code in each of your subforms since there is nothing in the code that pertains to each subform.  You can click the button on each subform and all it does is repeat the process, wiping out the records and adding them in again.  So whether you click the button on one subform or you click all four buttons, you will end up with the same transposed table.

Ron
0
 

Author Comment

by:Jass Saini
ID: 40601310
For me it works and I may have to change how it writes to the Table.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

876 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