My code is messy, because I have been trying to understand how to use recordset.clone. I have a continuous form. There is a filter in the header, which filters a partial string to a form control in the detail. It limits all "assets" in a project to a project id, the project ID being the prefix of all suffixes.
I also have a "temp" checkbox in the table, so that as assets are created, a user can check multiple assets, and generate custom metadata for each of them.
So this is what I want to do. Check which ever checkboxes I need. Click a command button in the header. The code will grab the current records in the filtered (or not filtered) form, and then for each record that has a checked checkbox, it will run a union query filtered to that record's asset/batch name, and save it as a csv to the specified directory, using the batch name as the file name.
Then it will move to the next, and so on, until all checked batches have a csv made. Once done, it will clear all checkboxes.
Make sense? I know my code is wrong, I just don't know what I am doing, and am having the hardest time searching this. I have been searching recordset, continuous forms, filters, checkboxes, etc.
Here is the code I have been working back and forth with. It is including msgbox's because I am trying to figure out what information the code is actually getting...
Any help would be greatly appreciated!
misscrf
It is never too late to become what you could have been ~ George Eliot
I also have a "temp" checkbox in the table, so that as assets are created, a user can check multiple assets, and generate custom metadata for each of them.
So this is what I want to do. Check which ever checkboxes I need. Click a command button in the header. The code will grab the current records in the filtered (or not filtered) form, and then for each record that has a checked checkbox, it will run a union query filtered to that record's asset/batch name, and save it as a csv to the specified directory, using the batch name as the file name.
Then it will move to the next, and so on, until all checked batches have a csv made. Once done, it will clear all checkboxes.
Make sense? I know my code is wrong, I just don't know what I am doing, and am having the hardest time searching this. I have been searching recordset, continuous forms, filters, checkboxes, etc.
Here is the code I have been working back and forth with. It is including msgbox's because I am trying to figure out what information the code is actually getting...
Code:
Private Sub cmdGenerateProjectMetadata_Click()
On Error GoTo Err_cmdGenerateProjectMetadata_Click
Dim frm As Form
Dim strTarget As String
Dim strDoc As String
Dim strFileName As String
Dim strPath As String
Dim strExportFile As String
Dim rs As DAO.Recordset
'Dim strSql As String
Dim strSql2 As String
'Set frm = Forms!frmProcessingTracking
'Set rs = frm.RecordsetClone
strSql2 = Forms!frmProcessingTracking.RecordSource
'& " Where " & Me.Filter
Me.RecordSource = strSql2
'Me.FilterOn = True
MsgBox Me.RecordSource
strPath = InputBox("Please Enter a UNC Path for the Custom Metadata files to be saved to", _
"Project Files Custom Metadata Location") & "\"
If strPath = "" Then
MsgBox "Must set path for metadata to generate to", vbOKOnly, "Custom Metadata Must Be Generated to a Folder"
DoCmd.CancelEvent
Else
rs.MoveFirst
MsgBox (rs.RecordCount)
For i = 1 To rs.RecordCount
If Me.chkGen = 1 Then
MsgBox ("IN THE LOOP")
strDoc = "qryUnionCustomMetadata"
strFileName = Forms![frmProcessingTracking].Form![ProjectEvidenceFileBatch]
strExportFile = strPath & strFileName & ".csv"
DoCmd.TransferText acExportDelim, strDoc, strExportFile, False
'ProjectMetadataExport
End If
rs.MoveNext
Next i
End If
rs.Close
Set rs = Nothing
DoCmd.SetWarnings False
strSql = "UPDATE tblDiscoveryProcessing SET GenerateMetadatatmp = 0;"
DoCmd.RunSQL strSql
Me.Requery 'show changes in form
DoCmd.SetWarnings True
MsgBox "All custom Metadata has been generated", vbOKOnly, "Custom Metadata Generated"
Exit_cmdGenerateProjectMetadata_Click:
Exit Sub
Err_cmdGenerateProjectMetadata_Click:
MsgBox Err.Description
Resume Exit_cmdGenerateProjectMetadata_Click
End SubAny help would be greatly appreciated!
misscrf
It is never too late to become what you could have been ~ George Eliot
) try Debug.print for code debugging.