Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Nested Do While Loops

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
Kev
Asked:
Kev
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
AHMKC1Commented:
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
 
KevAuthor Commented:
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
 
KevAuthor Commented:
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
 
AHMKC1Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now