Check Boxes On Form

[Iain]

This is difficult to explain so appolgies in advance, so here goes.

I have an Access2007 form that displays information of certain files, including whether the file is 'live' or 'archived'.

The form gathers the file information from several tables and displays the selected record on the top half of the form and the information on the other files in a table in the bottom half.



By selecting a check box for desired records in the table section, the form can be re-queried and refreshed to update a file’s status from ‘live’ to ‘archived’.

I would like to transfer archived files to an archived folder at the same time but cannot find an example of the code I should use to act only on the selected check boxes.

I was thinking along the lines of something simple like:

Where the check box has been selected
Copy selected file to new folder
Else
MsgBox “No files selected”
Next check box

I have code that works for setting the file name, path name and moving the files but I cannot get the construct right for the loop part.

Any suggestions?

[June7]

Need a recordset to loop through. If the form is open can loop through RecordsetClone. Here is example of looping RecordsetClone http://www.blueclaw-db.com/access_em...nd_outlook.htm

Assume checkbox is bound to field of table.
In your case code would be to get value from Yes/No field then perform copy/delete files if appropriate.

[Iain]

Thanks for that.
I'm a complete novice and already this has gone over my head.
The record source for the form is a query.
The check box is a field in a table but I don't know if it is bound. How can I tell?
The user can scroll through the table section of the form, ticking the check box to select records. There is a button when clicked, sets the status field of the record to 'archived'.
This button simply re-runs the query to update the tables then refreshes the form.
I guess what I'm trying to achieve is your last suggestion using the Yes/No value to execute the copy/delete code but I can't even get it to print a simple message box?

[June7]

The form has a RecordSource so it is bound.

If data control is bound, its ControlSource property will be a field of the form's RecordSource. This means entry/edit in the control passes directly to the table.

Post your code for analysis.

[Iain]

Thanks that makes a little bit more sense.
I don't have any code yet but I've listed what I'm trying to construct below.
I've disabled all new code so that the original code still works.
If I tick three check boxes and click on the button the status of the three records changes to archived.
If I close down the form before clicking on the button these three records have ticks in the check boxes of the table when it is opened.
If I knew the basic process to follow I could look up a text book but I don't even know how to get started on this.
It's not the file copy part that is the sticking point it is how to detect that the check box has been ticked in the first place then use it to execute some other code. Any code!




'Runs when 'Set Selected Frames - Deleted' button is clicked.
Private Sub cmdStatDel_Click()
On Error GoTo MyError
Dim frm As Form
'Dim FileNo As String
'Dim FromPath As String
'Dim ToPath As String
'Dim OldName As String
'Dim NewName As String
'Dim Retval As Integer

'FromPath = "J:\Image Files\Live Files\"
'ToPath = "J:\Image Files\Archived Files\"
'Rem:Program mod required
'Rem:Where the DigitalImageSelect check box has been selected
'If DigitalImageSelect Is Then
'Rem:get file name from the query used as a record source and update the text variable
'Rem:set FileNo to the same as txtImageFileNumbe
'FileNo = "Test.jpg" 'ok
'Rem: FileNo = [textImageFileName]
'OldName = [FromPath] & [FileNo] 'ok
'NewName = [ToPath] & [FileNo] 'ok
'Retval = 0 'ok
'Dim objFSO As Object 'ok
'Set objFSO = CreateObject("Scripting.FileSystemObject") 'ok
'Rem:copy file to new folder
'Retval = objFSO.CopyFile(OldName, NewName, True) 'ok
'Set objFSO = Nothing 'ok
'Rem:Confirm copy
'MsgBox "Copy/paste complete" 'ok
'MsgBox "If Statement ok"
'Else
'Stop
'End If
'Rem:get next file name

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDigitalImagesDeleted"
DoCmd.SetWarnings True

Set frm = Forms!frmNewLibraryImageCamera
frm.Requery
frm.Refresh

MyErrorExit:
Exit Sub

[June7]

Incorporate the recordset and looping structure from example. The Yes/No field will be in the recordset. Does True mean 'live'? So within the loop check status of the field:

If rst!checkfield = True Then

[Iain]

Ok thanks I'll have a go at the record set.

True is archived so I assume

If rst!checkfield = False Then ...

[Iain]

Tried to incorporate the example into my code but the Select Case line does not run whether a check box is selected or not.


E.g. This doesn’t work:

Select Case Me.DigitalImageSelect
Case 1
MsgBox "Case 1 executed"
…
Case 2
End Select
Stop

The stop line is the first line to be executed in both instances whereas this alternative code does run:

If Me.DigitalImageSelect = True Then
MsgBox "If Statement ok"
Else
MsgBox "No check box selected"
End If

[June7]

The Case probably doesn't work because the numeric values for True/False are -1/0.

With only two possible values, Case is a little overboard, the If Then structure is just as good in this situation.

[Iain]

Ok ignore previous post I've managed to make some stuttering progress with the following code.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Private Sub cmdStatDel_Click()
On Error GoTo MyError
Dim frm As Form
Set frm = Forms!frmNewLibraryImageCamera
Select Case Me.DigitalImageSelect
Case True
MsgBox "Case 1 executed"
Dim FileNo As String
Dim FromPath As String
Dim ToPath As String
Dim OldName As String
Dim NewName As String
Dim Retval As Integer
Dim rst As DAO.Recordset

FromPath = "J:\Image Files\Live Files\"
ToPath = "J:\Image Files\Archived Files\"

Set rst = frm.RecordsetClone
rst.MoveFirst
Do While Not rst.EOF
Stop
If rst!DigitalImageSelect = True Then
MsgBox "If Statement ok"
FileNo = [txtFile]
OldName = [FromPath] & [FileNo]
NewName = [ToPath] & [FileNo]
'Retval = 0
'Dim objFSO As Object
'Set objFSO = CreateObject("Scripting.FileSystemObject")
'Rem:copy file to new folder
'Retval = objFSO.CopyFile(OldName, NewName, True)
'Set objFSO = Nothing
'Rem:Confirm copy
'MsgBox "Copy/paste complete"
'Else
'Stop
'MsgBox "No check box selected"
End If
Stop
'If IsNull(rst!DigitalImageSelect) Then
'MsgBox "If Statement ok"
'Stop
'GoTo skip_deleted
'End If
Stop
Loop
End Select

MsgBox "No check boxes selected"
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I think I've messed up the recordset because it only assigns variable values if the check box in record one is selected and the program loops continuously on record one in subsequent passess.

[June7]

Problems for sure:
Not referencing recordset for FileNo
No MoveNext line
Variables enclosed in []
Why Case structure referencing form?

Dim FileNo As String
Dim FromPath As String
Dim ToPath As String
Dim OldName As String
Dim NewName As String
Dim Retval As Integer
Dim rst As DAO.Recordset
FromPath = "J:\Image Files\Live Files\"
ToPath = "J:\Image Files\Archived Files\"
Set rst = Me.RecordsetClone
If rst.RecordCount > 0 Then
    rst.MoveFirst
    Do While Not rst.EOF
        Stop
        If rst!DigitalImageSelect = True Then
            MsgBox "If Statement ok"
            FileNo = rst!File
            OldName = FromPath & FileNo
            NewName = ToPath & FileNo
            Retval = 0
            Dim objFSO As Object
            Set objFSO = CreateObject("Scripting.FileSystemObject")
            'Rem:copy file to new folder
            Retval = objFSO.CopyFile(OldName, NewName, True)
            Set objFSO = Nothing
            'Rem:Confirm copy
            MsgBox "Copy/paste complete for file: " & FileNo
        Else
            Stop
            MsgBox "Check box not selected for file: " & FileNo
        End If
        rst.MoveNext
    Loop
Else
    Stop
    MsgBox "No check boxes selected"
End If

Is FileNo reference to a folder or a file? If a file is the suffix included (.pdf, .doc, .xls, etc.)?

[Iain]

I applaud your patience with a numptie like me but sorry, this code doesn't execute and it can't beak out of the loop just as with the previous code.

I don't understand what you are trying to achieve, so I can can't make comparisons.

I don't understand your questions:

Why Case structure referencing form?:
Don't know what you are asking here, so can't give a meaningful answer.

Is FileNo reference to a folder or a file? If a file is the suffix included (.pdf, .doc, .xls, etc.)?
FileNo is the name of a field in a table that contains the file number. It is alphanumeric and is complete with extension.(all manner of image files)

In plain english what I'm trying to do is:

open the form
tick the check box for desired records
filter out all the selected records where the checkbox has been ticked
go to the first record in the filtered list where the check box has been ticked
get the file name for that record
add it to the ToPath
Copy the file
Go to the next record where the CheckBox has been ticked - get FileNo, add to ToPath, copy etc
Repeat until the last check box is reached
Exit the loop
Execute the rest of the code that requeries the database and updates the status of the records (which it presently does without any problems)

The recordset thing doesn't seem to be working at all. If I understand things correctly, the recordset should not be all the records in the databas but only the ones that match the desired criteria (i.e. check box ticked), so if I tick 10 boxes, the record set will be a vitual table with 10 records. Correct?

[June7]

Your code shows Select Case structure pulling value from DigitalImageSelect on form. This does not make sense to me so I changed it in my suggested code.

You clarified what the FileNo field contains.

No, the RecordsetClone replicates the set of records displayed on the form, ALL of the records on the form, checked or not checked. That's the reason for the line: If rst!DigitalImageSelect = True Then

The alternative is to open a recordset from the table with filter criteria. I would use ADODB recordset because that is what I am familiar with.

I don't understand why code doesn't leave the loop.

What you describe is exactly what my suggestion should do.

Do you want to provide project for analysis? Follow instructions at bottom of my post.

[Iain]

Finally I understand what you have been trying to get through to me.

I've added some message boxes to help me follow the flow of the program and it is working.

No problems with the loop.

Now that I know how this works I can read up on it and make more sense of the books I have.

Thank you very much for all your help and patience.

I presume there is a way of updating the status of this thread to solved or complete.

[June7]

Glad you have it working.

See choices under Thread Tools at top of the thread. I marked 'Solved'.