Separating information in a table

[orange]

Yes that is incorrect.

Create a new Module ( I have acc2003 so I can't tell you how it is in 2007 or 2010), but go to the screen with the Tables, Queries, Forms etc.
What you need to do is
- in a new standard module
- copy and paste the procedure SplitAdviceGiven
- save the module with a name of your choice.

Then we'll deal with the form and button.

[Lupson2011]

ok, have created module and saved as AdviceGiven

Thanks for your patience with this

Marc

[orange]

Ok,
now for your form

Here is the code I have for my button which I called btnRunSplit

Your code should have similar format/syntax.

Private Sub btnRunSplit_Click()
'This is the code behind the button
'It is private to this Form
On Error GoTo Err_btnRunSplit_Click

SplitAdviceGiven 'This is the call to the procedure
                 'The procedure runs to completion (or error)
                 'And returns here to finish this Click event code
    

Exit_btnRunSplit_Click:
    Exit Sub

Err_btnRunSplit_Click:
    MsgBox Err.Description
    Resume Exit_btnRunSplit_Click
    
End Sub

Note:

You could put a msgbox "SplitAdvice has completed",vbOkOnly at the end of either the standard module procedure, or the Click event code.

[Lupson2011]

Hi

Did everything like that, decided to make button name same as yours. But when ran got error 3265 (see below). I checked all the fields names, they all match

[orange]

You could have another button to run the query to get your counts. Or you could just have a query that you run from the List of Queries.

I am going out for a few hours but will check back later.

Good luck with your project.

If you are testing your set up and have to run the procedure a few times you may want to add the following line, to keep your output data "clean" (not duplicates from multiple runs).


...Set db = CurrentDb
Set rs = db.OpenRecordset("MarcAdviceGiven")
Set rsTemp = db.OpenRecordset("TempTableForQuery")
'Delete existing records from theTempTable
db.Execute "Delete * from TempTableForQuery", dbFailOnError

Do While Not rs.EOF
arrHold() = Split(rs!adviceGiven, ",")....

[orange]

I was sending my last message when yours arrived.

Did the code run?
You don't have a module called "AWF_Related" but I don't think the error is there.

[orange]

If you do a Ctrl-G (cntrl key and G together), you will open the immediate window.
What info is there?
The debug.print statements in the procedure will write info to the immediate window for debugging purposes.

Using debug.print statements is a good debugging technique

[Lupson2011]

When did Ctrl-G this is my screen, any help? with the 3265 error?

[orange]

You're still using the MarcAdviceGiven table in this one???

The data in the immediate window shows the code has executed

Open your temp table and you'll see the records.

Am heading out, but will check in.

[Lupson2011]

I changed it back to original table, still the same. The data in the immediate window only has records ofr the same URN and the same advice given (see below)

MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application
MLF000128 - Developing your application

Thats all that was executed I am afraid. I have checked all the fields and they are all spelt the same etc...

Marc

[orange]

The only thing I can think of that we didn't discuss was the temp table.

You will have to manually create a table that has the layout shown in the attached jpg.
And you'll have to name it the same as in the procedure "TempTableForQuery".

(I thought I sent this info, but can not find it in the thread --maybe I sent it somewhere else.
Anyway, try this. If it doesn't resolve the issue, we'll get at it tomorrow.

[Lupson2011]

hi

Sorted..It was the field name in the TempTable. I have created a TempTable, but I had called the field AdviceGiven, not AdviceString. I had a bit work tidying up the data as some of the strings were very long. But I got there in the end.

many thanks for your patience in this. I have learned some more access through this.

thanks

Marc

[orange]

In all procedures it is advisable to use error handling. Check things for a value, file exists etc.
Glad you got it sorted out.
Make use of the forum; we are all here to help. And we all learn.