Solved

One Module and four forms

Posted on 2015-02-06
12
148 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 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 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

12 Experts available now in Live!

Get 1:1 Help Now