Solved

One Module and four forms

Posted on 2015-02-06
12
152 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
12 Comments
 
LVL 30

Accepted Solution

by:
hnasr earned 250 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 250 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 30

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 30

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

734 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