Are C:\file1 and D:\file2 folders that include files named file1 and file2?
I'm going to make the assumption that this is correct, based on what you
wrote.
So, I *think* you want to update the folder location for records that you
have checked true with a Yes/No boolean checkbox. Then, you want to turn
around and update the boolean field back to it's unchecked (false) state. Is
this correct? I'd suggest adding a field, if you do not already have one,
that tracks the date that a given file was last backed-up, perhaps named
dteLastBackup (the dte in Hungarian naming convention indicating a Date/Time
data type). If this looks right to you, you need to run two separate update
queries. You can do this in one procedure (function or subroutine).
Let's try working through an example together. First, create a new table
with the following fields:
tblMyFiles
pkFileID (Autonumber / primary key)
FilePath (Text / 255)
FileName (Text / 255)
DiskNumber (Text / 20)
dteLastBackup (Date/Time)
blnUpdateMe (Yes/No) Note: bln stands for "boolean"
Add some test records. Let's assume that none of the files have ever been
backed up yet, so the DiskNumber and dteLastBackup fields are null. Suppose
that we want to update the FilePath for the odd-numbered records, ie. records
1 and 3 to the D drive:
pkFileID FilePath FileName DiskNumber dteLastBackup blnUpdateMe
1 C:\File1 FileA Yes
2 C:\File1 FileB No
3 C:\File1 FileC Yes
4 C:\File1 FileD No
Create an unbound (no recordsource) form that includes two text boxes and
one command button:
txtNewPath --->Text box that allows you to specify a new path.
txtDiskNumber --->Text box that allows you to specify a disk ID.
cmdUpdate --->Command button for executing a subroutine.
Have the command button wizards deactivated. Name this form frmUpdateInfo.
Set it's Default View = Single Form, Allow Form View = Yes, and Allow all
other views = No.
Create a query that is based on the tblMyFiles table:
qryMyFiles:
SELECT FilePath, FileName, DiskNumber, dteLastBackup, blnUpdateMe
FROM tblMyFiles
ORDER BY FileName;
Create a form (Autoform: Datasheet) that is based on this query. Name it
frmMyFilesSubform. The Default View should be Datasheet. Set Allow Form View
= No, Allow Datasheet View = Yes, and Allow Pivot / Chart Views both = No.
Add this form as a subform to your new unbound form. You can use this form to
easily pick which records to update. Set the default value for the checkbox
on the form to 0 to avoid the rather ugly greyed out appearance due to null.
If you are using Themed Controls setting, you can skip doing that, because
Themed Controls does not display this ugly greyed out look.
Open frmUpdateInfo in design view. Add frmMyFilesSubform as a subform (you
can re-activate the wizards on the toolbox, if you want wizard assistance).
Name the subform container (the object on your main form that holds the
subform) "MyFilesSubform". To both forms: Set the Allow Design Changes
property to Design View Only.
Add the following event procedure for the command button on the main form:
Option Compare Database
Option Explicit
Private Sub cmdUpdate_Click()
'This procedure uses DAO for the optional dbFailOnError parameter, so you
'must have a reference set to the DAO object library for this code to work.
On Error GoTo ProcError
Dim intResponse As Integer
Dim strSQL As String
intResponse = MsgBox("Do you want to update the selected records?", _
vbQuestion + vbYesNo, "Update Records...")
If intResponse = vbYes Then
'Check for an entry in each text box
If Len(Me.txtNewPath) = 0 Then
MsgBox "Please enter the path to update to.", _
vbCritical, "Unknown Update Path..."
Me.txtNewPath.SetFocus
GoTo ExitProc
End If
If Len(Me.txtDiskNumber) = 0 Then
MsgBox "Please enter the path to update to.", _
vbCritical, "Unknown Update Path..."
Me.txtDiskNumber.SetFocus
GoTo ExitProc
End If
'Update Records
strSQL = "UPDATE tblMyFiles SET FilePath = '" & Me.txtNewPath & "',
" _
& "DiskNumber = '" & Me.txtDiskNumber & "', " _
& "dteLastBackup = #" & Date & "#, blnUpdateMe = 0 " _
& "WHERE tblMyFiles.blnUpdateMe<>0;"
CurrentDb.Execute strSQL, dbFailOnError
MsgBox "All records updated." & vbCrLf & _
"Subform will be refreshed when you click OK.", _
vbInformation, "Done."
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdUpdate_Click..."
Resume ExitProc
End Sub
Save the form. Click on Debug > Compile ProjectName, where ProjectName is
the name of your VBA project (likely the same name as your database). Then
open the frmUpdateInfo form in view mode. Test it out.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
yes it was an update i missed that select.
UPDATE [location Query]
SET [location Query]![Location] = IIf ( [location Query]![updateme] =
True , Forms![Series]![NewLocation] AND [location Query]![updateme] =
False , [location Query]![Location])
FROM [location Query];
that it what it should have been.
as for what I am trying to do. i have a true false column of data for
every record in my database. Each record in this instance is a file
and it's location IE File1 is on c:\file1 and file2 is on D:\file2 now
lets say that I want to burn those 2 files off onto disk and delete
them from my hdds. since the location has changed i would like to be
able to go through all of the files that I am going to burn off and
check updateme then put thier new location (probably a disk number) in
the text box Forms![Series]![NewLocation] and update all of those
entries new locations then set the update me back to false.
apologies for the length
thank you very much for your time
