'Error 91 - Object Variable Or With Block Variable Not Set'

Thanks to people that have been helping with other parts of my database.
This was working fine purely in access, but now i am using access and sql i am having the "Error 91 - Object variable or With block variable not set" fault

The line that seems to be causing me the issue is emboldened in the code below but here it is anyway

I initially thought it must be due to an if-statement partially inside a loop or something of that nature, but I don't notice anything offhand. What happens when you press F8 repeatedly and try to step through it? Does the error come up immediately?

If you've done the change that Alun suggested then the only thing left in your line that is a VBA value (and therefore not have its object set) is the CurrentDB part of the statement. I know it sounds crazy but is that valid?

Apologies.
Replies appear to get parsed incorrectly which confuses the issue, the Set statement looked wrong to me but when I sent what I thought it should be, it got parsed to exactly what I thought was wrong!!

That is, one actual quotation mark appeared as a quotation mark but the second appeared as an ampersand followed by the string quot.

Executing the sql directly (in the ctrl-g box) i get
Compile error, expected :end of statement.

To Len,
Set db = CurrentProject.Connection was added because in other script sample it was there, and i thought it was important to reinforce the connection to the sql database if it doesn't do what i thought it did then i'll remove it.

To Martin,
I am unsure of what you mean by use "CurrentProject.Connection ", or the syntax to use in the ado connection.

To Ken,
Thanks for pointing out the next bit, but at the moment i am having trouble with the " Insert Into line", a couple of lines before i start my crawl through the database looking for my matches.. and then doing my final set of inserts. Due to the formatting issues i am unable to understand what you mean.. do you mean?
1 Set rs = CurrentDb.OpenRecordset("nm_inc_tests_master& quot;, dbOpenSnapshot)
or 2 Set rs = CurrentDb.OpenRecordset('nm_inc_tests_master', dbOpenSnapshot)

It is obvious from what all 3 of you are saying there is much that i need to do on this form.
It is quite important that i do get a final solution, as there are many other forms, which use similar scripts to analyse and create sets of data. Once i have got it right i know i can propogate that knowledge elsewhere.

I really do appreciate the assistance that people are offering please do not mistake my misunderstanding for rudeness, this is the first time i created something of this magnitude. Normally a bog standard 1 user Db has been more than enough.

Martin,
CurrentDB is an object reference to the current database connect in Access if you're using DAO (Data Access Objects). CurrentProject.Connection in an object reference to the the current database connect if you're using ADO (Active Data Objects). The DAO object reference CurrentDB doesn't work with an Access Data Project (adp) because it's built on ADO not DAO.

@Len: An ADP 'contains/maintains' (if that's the right word) the connections when you create the actual file. i.e. you effectively set up the DSN when you create the file and then use CurrentProject etc to access the data.

It automatically includes the ADO reference so you don't have to (theoretically anyway) have to precede the properties etc. with ADODB

Martin Walke,
An Access Data Project uses an ADO connection. A Data Source Name (DSN) is a name ODBC connection. The reason martingriffiths is getting an Object variable or with block variable not set is CurrentDB returns Nothing in an Access Project. If one wiches to connect using DAO rather than ADO one must declare and set the required properties and open the connection. DAO has some advantages when working with an Access Database but they are'nt availabe with an Access Project? From Office XP on the default data connection layer has been ADO.
The following links may help to clear up the "muddied waters"

My errors are fixed, my weird match is due to somehow during my debugging session i had somehow flipped the record to another one.. the code was still trying to execute but continued pulling information from the data on a different record... V strange. I really do thank everybody that not only has helped me, but also given some educational background to the differences between ado, dao and the perils of adp.

My proper job is that of an IT teacher, who has managed large ms/mysql databases, it is evident now that just being able to query from the command line and run the odd update query is not enough to "cut it" in the wider world of industry.

Normally my databases that i have created have worked quite well, been small and functional, and were normally being shared across a corporate network, with 1 or 2 people accessing at the same time.

What this has taught me is enough to build on my skills and make me adamant to get a teaching job asap, redundancy is no fun.

This has taught me enough though to show my (potential) students what can be achieved with the access databases they create and a simple sql server.