How to run a code?

[ppataki]

Hi,

I am a bit newbie, could anyone please tell me how I can run the following code in Access?

Dim rs As DAO.Recordset
Dim strLastChar As String

strLastChar = ""

Set rs = "Select * from table1 Order By Nr"
WHile Not rs.EOF And Not rs.BOF
rs.Edit
If rs!Field1 = "" Then
rs!Field2 = strLastChar
ElseIF rs!Field1 = strLastChar Then
rs!Field2 = "Undefined."
Else
rs!Field2 = rs!Field1 & " " rs!Field1
End If
rs.Update
rs.MoveNext
wend

Many thanks in advance!!!

 

[AndySuk]

Hi

As long as you have a table (table1) then you should be able to paste the code into a module and run it from there.

If you're using Access 2003 then go to the Tools menu from your main database screen. Follow the menu down to "Macro" and click on the item labelled "Visual Basic Editor".

From there click the Insert menu and click on Module.

A procedure needs a start and end to run so type "sub mycode()" (without the quotation marks) at the top of the blank module page. You'll see an "End Sub" appear. Paste the code between the sub and end sub.

To run it just double-click "mycode" and press F5.

A

 

[ppataki]

Hi,

I did what you suggested in Access 2000 and I got the following error message (pls see screenshot attached)

Could you please advise?

Many thanks!!!

 

[AndySuk]

Is it me or are you missing an ampersand???

Stick a "&" (without the quotes) so that
rs!Field2 = rs!Field1 & " " rs!Field1
reads
rs!Field2 = rs!Field1 & " " &rs!Field1

 

[ppataki]

Hi,

Now the red row is fixed Thank you!

But if I press F5 I still get an error message
(isnt there a problem with the DAO.recordset part, as Access highlights it?)

I attach new screenshot

Many thanks in advance!

 

[Kiwiman]

Howzit

You missed the openrecordset..

Try...

Public Function mycode()

Stop
Dim rs As DAO.Recordset
Dim strLastChar As String, strSQL As String

strSQL = "Select * from table1 Order By Nr"

strLastChar = ""

Set rs = CurrentDb.OpenRecordset(strSQL)

While Not rs.EOF And Not rs.BOF
rs.Edit
If rs!Field1 = "" Then
    rs!Field2 = strLastChar
ElseIf rs!Field1 = strLastChar Then
    rs!Field2 = "Undefined."
Else
    rs!Field2 = rs!Field1 & " " & rs!Field1
End If

rs.Update
rs.MoveNext

Wend

rs.close
set rs = nothing

End Function

 

[ppataki]

Maybe I do something incorrectly but I still get the same error message

 

[Kiwiman]

Howzit

Hmm bizarre - code runs through just fine on mine.

Can you check to see if you have a reference to the DAO library.

From the Visual Basic Window:

Tools >> References

It will be something like

Microsoft DAO 3.6 Object Library

If you haven't scroll down until you find it, check it and try again

 

[ppataki]

Hi,

Now it works fine without error message!
Thank you!

However now I got an issue that the result is not what I expected
I attach a screenshot of the current result and the result that I would like to have

Your help on this would be very much appreciated

 

[Kiwiman]

Howzit

Try...

Public Function Test()

'Stop
Dim rs As DAO.Recordset
Dim strLastChar As String, strSQL As String

strSQL = "Select * from table1 Order By Nr"

strLastChar = ""

Set rs = CurrentDb.OpenRecordset(strSQL)

While Not rs.EOF And Not rs.BOF
rs.Edit
' If field1 is empty or null, insert strLastChar
If rs!field1 = "" Or IsNull(rs!field1) Then
    rs!Field2 = strLastChar
'ElseIf rs!field1 = strLastChar Then
 '   rs!Field2 = "Undefined."
Else                            ' Otherwise insert the value in field 1
    rs!Field2 = rs!field1
    strLastChar = rs!field1     ' set the strLastChar as the value in field1
End If

rs.Update
rs.MoveNext

Wend


End Function

 

[ppataki]

It works perfectly!!!!

Thank you very much for your help!