Extracting contents from memo fields (1 Viewer)

[murgatroyd]

The table is created manually.

OK thanks. In my database, I created an output table the same as your example, and I created a form with a Run button and put your code in the OnClick event. In the code, I replaced the names of the input table and the fields with the actual ones from my database.

When I tried it, I got an error: "The expression On Open you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name." I tried removing "Cancel As Integer" from the first line, and the error went away.

When I tried it again, I got a VBA error: "Compile error: User-defined type not defined", with ", rst As DAO.Recordset" highlighted (end of second line).

 

[JHB]

In the code - did you remove the line?

Private Sub Report_Open(Cancel As Integer)

Also check if you've 2 lines with:

End Sub

Post your database with some sample data in it!

 

[murgatroyd]

Yes, I replaced your first line with "Private Sub Run_Click()".

No, I only have one (last) line with "End Sub".

I'm afraid I can't post a sample database, as the tables are in a linked SQL Server database.

I discovered that I needed to select the DAO object library, and when I did that, the compile error went away, but then I got another error: "Run-time error 3622. You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column." The debugger highlighted this line: "Set rst = CurrentDb.OpenRecordset("MyInputFile")".

 

[JHB]

Try the below:

Set rst = CurrentDb.OpenRecordset("MyInputFile", dbOpenDynaset, dbSeeChanges)

 

[murgatroyd]

Thanks, that works now.

However, when I changed the "Item, IsWhere, TheColor" field names (in both the code and the table) to "Id, Level, Area", and the "Back" and "Front" value names to "A" and "B", I got an error: "Run-time error 3134. Syntax error in INSERT INTO statement." Are there restrictions on what these labels can be?

Edit: After some experimentation, I found that the error was caused by the "Level" field name. Why is that label not allowed?

 

[JHB]

..
Edit: After some experimentation, I found that the error was caused by the "Level" field name. Why is that label not allowed?

Level is a keyword in MS-Access, did you try to surround it by [] =[Level].
Here is a link to keywords in MS-Access.
https://support.microsoft.com/en-us/kb/286335

 

[murgatroyd]

OK thanks, that explains it. Thanks again for your help with this. The solution works fine, and I learned a lot.

 

[JHB]

You're welcome, good luck.
Only one question - did the [] solved it or did you rename the field?

 

[murgatroyd]

I used a different name for the field (but FYI, I tried [Level] just now, and that works too.