You will either have to Dim strSaveName as a Variant (so it can hold null
values) Or you will have to skip assigning nulls to strSaveName
Public Function signoff()
Dim dbAny as DAO.Database
Dim rst As DAO.Recordset
Dim strSaveName as Variant
'Dim strSaveName As String
SET Dbany = CurrentDB()
Set rst = DbAny.OpenRecordset("Sheet3", dbOpenDynaset)
With rst
IF .RecordCount > 0 then 'Test to see if any records are in
.MoveLast
.MoveFirst
strSaveName = ![Field2]
Do While Not .EOF 'Missing space in Do While
If IsNull(![Field2]) Then
.Edit
![Field2] = strSaveName
.Update
Else
'If you don't change strSave Name to a variant then you
'Can add the two lines that are commented out
'If IsNull(!Field2) = False Then '******
strSaveName = ![Field2]
'End if '******
End If
.MoveNext
Loop
End if 'Records existed in recordset
End With 'This was missing
End Function
bill ch said:
I have it set to the DAO now -this is great!
Only I get a Run Time Error 94 'Invalid Use of Null'.
If I debug it highlights line -- strSaveName = ![Field2]
Any ideas on a fix? I highly appreciate your help.
Thanks
John Spencer said:
Do you have a library reference set to the Microsoft DAO 3.x Object
Library?
When I tried to compile your posted code I got a few errors.
Public Function signoff()
Dim dbAny as DAO.Database
Dim rst As DAO.Recordset
Dim strSaveName As String
SET Dbany = CurrentDB()
Set rst = DbAny.OpenRecordset("Sheet3", dbOpenDynaset)
With rst
IF .RecordCount > 0 then 'Test to see if any records are in
recordset
.MoveLast
.MoveFirst
strSaveName = ![Field2]
Do While Not .EOF 'Missing space in Do While
If IsNull(![Field2]) Then
.Edit
![Field2] = strSaveName
.Update
Else
strSaveName = ![Field2]
End If
.MoveNext
Loop
End if 'Records existed in recordset
End With 'This was missing
End Function
Compile error: Variable not defined
and it highlights the 'Public Function signoff()' and
'dbOpenDynaset)'.
I tried Public instead of Dim but that didn't work. Hoping someone
could
help!
Public Function signoff()
Dim rst As Recordset
Dim strSaveName As String
Set rst = CurrentDb.OpenRecordset("Sheet3", dbOpenDynaset)
With rst
.MoveLast
.MoveFirst
strSaveName = ![Field2]
DoWhile Not .EOF
If IsNull(![Field2]) Then
.Edit
![Field2] = strSaveName
.Update
Else
strSaveName = ![Field2]
End If
.MoveNext
Loop
End Function
:
PRIVATE function means that it cannot be seen outside the module.
Change
it
to PUBLIC function so that the macro can see it.
Error: 'The expression you entered has a function name that
Microsoft
Access
can't find'.
The code below is what is in my standard module (not a class module)
named
'Module1' and my AUTOEXEC Macro is a Run Code Action with signoff ()
in
the
Function Name box. Not understanding why it can't find the function
name.
What am I messing up? Thanks
Private Function signoff()
Dim rst As Recordset
Dim strSaveName As String
Set rst = CurrentDb.OpenRecordset("Sheet3", dbOpenDynaset)
With rst
.MoveLast
.MoveFirst
strSaveName = ![Field2]
DoWhile Not .EOF
If IsNull(![Field2]) Then
.Edit
![Field2] = strSaveName
.Update
Else
strSaveName = ![Field2]
End If
.MoveNext
Loop
End Function
:
Yes, you can make it run when the database is opened.
What you will need to do to make that happen is to put it in a
standard
module as a function, not a sub. Then create a Macro and name it
Autoexec.
In the Macro, use the RunCode action and put the name of the
function
in
the
text box labeled Command.
Access always looks for a macro named Autoexec when it starts up
and
if
it
finds it, it executes it.
:
Sounds like it is what I need only, not sure how to get it to
run.
Is
it
possible to run this as a module when the database opens. My
skills
are
probably not up to this programming posting section.
Thanks again
:
The code below is the basics. It does not include checking to
see
if
there
is data or closing the recordset. There is also an issue where
if
the first
record in the table has no name, it will stay Null, because
there
is
nothing
to put in it. This will stay true until you hit a record that
has
a
value.
Dim rst as Recordset
Dim strSaveName as String
Set rst = CurrentDb.OpenRecordset("MyTableName",
dbOpenDynaset)
With rst
.MoveLast
.MoveFirst
strSaveName = ![Field1]
DoWhile Not .EOF
If IsNull(![Field1]) Then
.Edit
![Field1] = strSaveName
.Update
Else
strSaveName = ![Field1]
End If
.MoveNext
Loop
:
Large file brought into Access. To simplify, I have a table
that
looks
basically like this:
Field1 Field2 Field3
------- -------- --------
name
yes/no number
name
yes/no number
yes/no number
I would like to know how to get 'Field1' to fill in all the
nulls
with the
name above it.
Thanks
