One Module and four forms

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

Jass SainiAsked:
Who is Participating?
 
hnasrConnect With a Mentor Commented:
Upload a sample database, and tell us what to do and what output to expect.
0
 
IrogSintaConnect With a Mentor Commented:
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
 
Jass SainiAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
hnasrCommented:
Try to recreate the issue, in a sample database, and upload..
0
 
Jass SainiAuthor Commented:
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
 
IrogSintaCommented:
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
 
Jass SainiAuthor Commented:
Ok..Thank You!
0
 
hnasrCommented:
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
 
Jass SainiAuthor Commented:
I figured it out..Thank you
0
 
Jass SainiAuthor Commented:
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
 
IrogSintaCommented:
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
 
Jass SainiAuthor Commented:
For me it works and I may have to change how it writes to the Table.
0
All Courses

From novice to tech pro — start learning today.