Code will only run once unless i close and reopen form

[Blakey300]

Hi

I am using Access 2007 and have the following code in a module which i call
using a macro:

Function CreateStudentFolders()

Dim rs As DAO.Recordset

Set rs = Forms!Students.RecordsetClone
If Not rs.BOF And Not rs.EOF Then
Do While Not rs.EOF
CreateFolder rs![LastName] & " " & rs![FirstName] & " " &
rs![MiddleNames] & " " & rs![StudentID]
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing



End Function

Private Sub CreateFolder(ASubFolder As String)

Dim strDesktop As String
Dim strSubfolder As String

strDesktop = DLookup("StudentFileLocation", "File Locations")

If Len(Dir(strDesktop & ASubFolder, vbDirectory)) = 0 Then
MkDir strDesktop & ASubFolder
End If

End Sub


This code creates a folder for every student in the database, which works
great except: If i run the code then add another student and try to run the
code again it will not work unless I close and reopen the form.

Is there a way around this?

Regards


Dave

 

[Carl Rapson]

The first thought I have is, how are you adding a new student? If it's
through the form, then it looks like it should work. If it's outside of the
form, you'll need to reload the form's Record Source to get the new record
loaded into the form. Since you're using RecordsetClone, that would explain
why you have to close and reopen the form.

An alternative would be to create a recordset directly from the underlying
table instead of using the form's recordset.

Carl Rapson

 

[Blakey300]

Hi Carl

Thanks for your reply, I confirm that I am creating the new record within
the form

The first thought I have is, how are you adding a new student? If it's
through the form, then it looks like it should work. If it's outside of the
form, you'll need to reload the form's Record Source to get the new record
loaded into the form. Since you're using RecordsetClone, that would explain
why you have to close and reopen the form.

An alternative would be to create a recordset directly from the underlying
table instead of using the form's recordset.

Carl Rapson

Hi

I am using Access 2007 and have the following code in a module which i
call
using a macro:

Function CreateStudentFolders()

Dim rs As DAO.Recordset

Set rs = Forms!Students.RecordsetClone
If Not rs.BOF And Not rs.EOF Then
Do While Not rs.EOF
CreateFolder rs![LastName] & " " & rs![FirstName] & " " &
rs![MiddleNames] & " " & rs![StudentID]
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing



End Function

Private Sub CreateFolder(ASubFolder As String)

Dim strDesktop As String
Dim strSubfolder As String

strDesktop = DLookup("StudentFileLocation", "File Locations")

If Len(Dir(strDesktop & ASubFolder, vbDirectory)) = 0 Then
MkDir strDesktop & ASubFolder
End If

End Sub


This code creates a folder for every student in the database, which works
great except: If i run the code then add another student and try to run
the
code again it will not work unless I close and reopen the form.

Is there a way around this?

Regards


Dave

 

[Carl Rapson]

Well then, it's not clear to me why it wouldn't work. My suggestion then
would be to create your recordset directly from the underlying table instead
of using the form's recordset clone.

Carl Rapson

Hi Carl

Thanks for your reply, I confirm that I am creating the new record within
the form

The first thought I have is, how are you adding a new student? If it's
through the form, then it looks like it should work. If it's outside of
the
form, you'll need to reload the form's Record Source to get the new
record
loaded into the form. Since you're using RecordsetClone, that would
explain
why you have to close and reopen the form.

An alternative would be to create a recordset directly from the
underlying
table instead of using the form's recordset.

Carl Rapson

Hi

I am using Access 2007 and have the following code in a module which i
call
using a macro:

Function CreateStudentFolders()

Dim rs As DAO.Recordset

Set rs = Forms!Students.RecordsetClone
If Not rs.BOF And Not rs.EOF Then
Do While Not rs.EOF
CreateFolder rs![LastName] & " " & rs![FirstName] & " " &
rs![MiddleNames] & " " & rs![StudentID]
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing



End Function

Private Sub CreateFolder(ASubFolder As String)

Dim strDesktop As String
Dim strSubfolder As String

strDesktop = DLookup("StudentFileLocation", "File Locations")

If Len(Dir(strDesktop & ASubFolder, vbDirectory)) = 0 Then
MkDir strDesktop & ASubFolder
End If

End Sub


This code creates a folder for every student in the database, which
works
great except: If i run the code then add another student and try to
run
the
code again it will not work unless I close and reopen the form.

Is there a way around this?

Regards


Dave

 

[Blakey300]

Sorry for being a complete spanner

To do that would i just need to change

Set rs = Forms!Students.RecordsetClone
to

Set rs = Tabless!Students.RecordsetClone

I am not a coding type person as you may be able to tell.

Many thanks for your advice

Dave

Well then, it's not clear to me why it wouldn't work. My suggestion then
would be to create your recordset directly from the underlying table instead
of using the form's recordset clone.

Carl Rapson

Hi Carl

Thanks for your reply, I confirm that I am creating the new record within
the form

The first thought I have is, how are you adding a new student? If it's
through the form, then it looks like it should work. If it's outside of
the
form, you'll need to reload the form's Record Source to get the new
record
loaded into the form. Since you're using RecordsetClone, that would
explain
why you have to close and reopen the form.

An alternative would be to create a recordset directly from the
underlying
table instead of using the form's recordset.

Carl Rapson

Hi

I am using Access 2007 and have the following code in a module which i
call
using a macro:

Function CreateStudentFolders()

Dim rs As DAO.Recordset

Set rs = Forms!Students.RecordsetClone
If Not rs.BOF And Not rs.EOF Then
Do While Not rs.EOF
CreateFolder rs![LastName] & " " & rs![FirstName] & " " &
rs![MiddleNames] & " " & rs![StudentID]
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing



End Function

Private Sub CreateFolder(ASubFolder As String)

Dim strDesktop As String
Dim strSubfolder As String

strDesktop = DLookup("StudentFileLocation", "File Locations")

If Len(Dir(strDesktop & ASubFolder, vbDirectory)) = 0 Then
MkDir strDesktop & ASubFolder
End If

End Sub


This code creates a folder for every student in the database, which
works
great except: If i run the code then add another student and try to
run
the
code again it will not work unless I close and reopen the form.

Is there a way around this?

Regards


Dave

 

[Carl Rapson]

No, you'll need to do a bit more to load the recordset from the table.
Something like:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM Students")

(assuming the name of your table is Students). Read up on the OpenRecordset
method for more details and other options, but this is usually enough to get
started. You can also add WHERE and ORDER BY clauses to the SQL code used to
open the table, if you want to filter the records and/or sort them in the
Recordset.

Carl Rapson

Sorry for being a complete spanner

To do that would i just need to change

Set rs = Forms!Students.RecordsetClone
to

Set rs = Tabless!Students.RecordsetClone

I am not a coding type person as you may be able to tell.

Many thanks for your advice

Dave

Well then, it's not clear to me why it wouldn't work. My suggestion then
would be to create your recordset directly from the underlying table
instead
of using the form's recordset clone.

Carl Rapson

Hi Carl

Thanks for your reply, I confirm that I am creating the new record
within
the form

:

The first thought I have is, how are you adding a new student? If it's
through the form, then it looks like it should work. If it's outside
of
the
form, you'll need to reload the form's Record Source to get the new
record
loaded into the form. Since you're using RecordsetClone, that would
explain
why you have to close and reopen the form.

An alternative would be to create a recordset directly from the
underlying
table instead of using the form's recordset.

Carl Rapson

Hi

I am using Access 2007 and have the following code in a module which
i
call
using a macro:

Function CreateStudentFolders()

Dim rs As DAO.Recordset

Set rs = Forms!Students.RecordsetClone
If Not rs.BOF And Not rs.EOF Then
Do While Not rs.EOF
CreateFolder rs![LastName] & " " & rs![FirstName] & " " &
rs![MiddleNames] & " " & rs![StudentID]
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing



End Function

Private Sub CreateFolder(ASubFolder As String)

Dim strDesktop As String
Dim strSubfolder As String

strDesktop = DLookup("StudentFileLocation", "File Locations")

If Len(Dir(strDesktop & ASubFolder, vbDirectory)) = 0 Then
MkDir strDesktop & ASubFolder
End If

End Sub


This code creates a folder for every student in the database, which
works
great except: If i run the code then add another student and try to
run
the
code again it will not work unless I close and reopen the form.

Is there a way around this?

Regards


Dave