Solved

Nested Do While Loops

Posted on 2006-07-08
5
227 Views
Last Modified: 2008-03-17
Hi,

I am trying to build an automatic function to export the data of multiple Trainees from multiple tables based on the selection on a form.

This is what I have so far:
This function cycles through the tables listed in tblTables and exports ALL data to another DB.
*********************
Function DataExportTrainees()
'Exports data for Trainee tables to Exportable DB
Dim rs As DAO.Recordset
Dim sExportPath As String
DoCmd.SetWarnings False
sExportPath = Nz([Forms]![frmMntExportData]![ImportPathName], "\\lpnrsn01\lcl_apps\data\riflemanwing\database\120241_Exportable_Blank.mdb")
If Dir$(sExportPath) = "" Then
    MsgBox "Cannot find " & sExportPath
Else
        Set rs = CurrentDb.OpenRecordset("SELECT tblTables.* FROM tblTables WHERE (((tblTables.Export)=True));")
        Do While Not rs.EOF = True
            sSql = "INSERT INTO [" & rs!tbl & "]  IN '" & sExportPath & "' SELECT * FROM [" & rs!tbl & "] "
            DoCmd.RunSQL sSql
            rs.MoveNext
        Loop
End If
DoCmd.SetWarnings True
MsgBox "Data Transfer Complete"
End Function
*********************

I want to be able to export the data for only selected trainees. Every table has field EID which is the employeeID number. I want to be able to transfer the data where
tblPersDetailsTrainee.EID = [Forms]![frmMntExportData]![SessionID]

I basically need to amend the above function to only transfer data for the EID's captured in the above recordset

Thanks in advance. Kev
0
Comment
Question by:budorat
  • 2
  • 2
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17064404
Like this:

'Exports data for Trainee tables to Exportable DB
Dim rs As DAO.Recordset
Dim sExportPath As String
DoCmd.SetWarnings False
sExportPath = Nz([Forms]![frmMntExportData]![ImportPathName], "\\lpnrsn01\lcl_apps\data\riflemanwing\database\120241_Exportable_Blank.mdb")
If Dir$(sExportPath) = "" Then
    MsgBox "Cannot find " & sExportPath
Else
        Set rs = CurrentDb.OpenRecordset("SELECT tblTables.* FROM tblTables WHERE (((tblTables.Export)=True));")
        Do While Not rs.EOF = True
            sSql = "INSERT INTO [" & rs!tbl & "]  IN '" & sExportPath & "' SELECT * FROM [" & rs!tbl & "] where EID = [Forms]![frmMntExportData]![SessionID] "
            DoCmd.RunSQL sSql
            rs.MoveNext
        Loop
End If
DoCmd.SetWarnings True
MsgBox "Data Transfer Complete"
End Function
0
 
LVL 6

Accepted Solution

by:
AHMKC1 earned 500 total points
ID: 17064418
write your sSql as
if EID is Number format then
 sSql = "INSERT INTO [" & rs!tbl & "]  IN '" & sExportPath & "' SELECT * FROM [" & rs!tbl & "]  Where EID =" & [Forms]![frmMntExportData]![SessionID] & ""

or if EID is text format then
 sSql = "INSERT INTO [" & rs!tbl & "]  IN '" & sExportPath & "' SELECT * FROM [" & rs!tbl & "]  Where EID ='" & [Forms]![frmMntExportData]![SessionID] & "'"
0
 
LVL 5

Author Comment

by:budorat
ID: 17064505
Hi,

Thanks for the help so far. I may have not explained myself clearly enough above, in fact, after looking at it I made a mistake.

I wrote
tblPersDetailsTrainee.EID = [Forms]![frmMntExportData]![SessionID]

This is incorrect. I have created a unbound combobox [Forms]![frmMntExportData]![SessionID]
I want to use this combobox to filter for all records in
tblPersDetailsTrainee where tblPersDetailsTrainee.SessionID = [Forms]![frmMntExportData]![SessionID]

This will return a list of approx 50 records, I then want to cycle through that recordset using the EID for each record as the criteria to select what records are exported in the function above.
0
 
LVL 5

Author Comment

by:budorat
ID: 17064523
Hi AHMKC1,

I have used what you have given me and adapted the function to successfully export the data from all tables for an individual trainee. Code as follows:

**********
'Exports data for Trainee tables to Exportable DB
Dim rs As DAO.Recordset
Dim sExportPath As String
DoCmd.SetWarnings False
sExportPath = Nz([Forms]![frmMntExportData]![ImportPathName], "\\lpnrsn01\lcl_apps\data\riflemanwing\database\120241_Exportable_Blank.mdb")

If Dir$(sExportPath) = "" Then
    MsgBox "Cannot find " & sExportPath
Else
        Set rs = CurrentDb.OpenRecordset("SELECT tblTables.* FROM tblTables WHERE (((tblTables.Export)=True));")
        Do While Not rs.EOF = True
             sSql = "INSERT INTO [" & rs!tbl & "]  IN '" & sExportPath & "' SELECT * FROM [" & rs!tbl & "]  Where EID ='" & [Forms]![frmPersData1]![EID] & "'"

            DoCmd.RunSQL sSql
            rs.MoveNext
        Loop
End If
DoCmd.SetWarnings True
MsgBox "Data Transfer Complete"
**********

The function was added to a cmdbtn on form frmPersData1 The problem is then it would have to be pressed for each trainee. While I would accept this as a last resort, it would be much quicker to be able to transfer the data in bulk.

Kev
0
 
LVL 6

Expert Comment

by:AHMKC1
ID: 17064953
OK you have unbound combobx 'SessionID'

Place it on form  frmPersData1 select a sessionID then click cmdbtn sSql is as follows

 sSql = "INSERT INTO [" & rs!tbl & "]  IN '" & sExportPath & "' SELECT * FROM [" & rs!tbl & "]  Where EID ='" & [Forms]![ frmPersData1]![SessionID].Value & "'"

Thanks

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DBF to ... Converter 5 40
MS Access Order Smallest to Biggest Query Help 13 41
Format Meeting Request through VBA 5 18
append to an ms access field 6 24
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

910 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

24 Experts available now in Live!

Get 1:1 Help Now