Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

One Module and four forms

Posted on 2015-02-06
12
Medium Priority
?
154 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

688 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