Issues with Query/SQL String

[mission2java_78]

Kevin try using the

[code tags]

it helps me read your code. Also why are you wasting space creating a string strRole. Just base your select on Forms!frmYourForm!fieldYourField.

Jon

 

[Kevin_S]

I hate to admit it but I don't know how to use code tags (grins sheepishly ) but I would if I knew how!

I am using strRole just because I didn't want to have to type:
Forms!frm....!.... everytime I wanted to reference the field in the Select Case Statement - If you think I should take it out I can do that!

also - what parameter do I need to add to the OpenRecordSet..?

Thanks Jon,
Kev

 

[mission2java_78]

I hate to admit it but I don't know how to use code tags (grins sheepishly ) but I would if I knew how!

I am using strRole just because I didn't want to have to type:
Forms!frm....!.... everytime I wanted to reference the field in the Select Case Statement - If you think I should take it out I can do that!

also - what parameter do I need to add to the OpenRecordSet..?

Thanks Jon,
Kev

to use code tags just put in [ c o d e ] around your code and then end it with [ / c o d e ] without all the spaces.

Also try this:
http://www.databasejournal.com/features/msaccess/article.php/1490571

For finding the parameter to openrecordset...or use ADO if you have to.

Jon

 

[mission2java_78]

I hate to admit it but I don't know how to use code tags (grins sheepishly ) but I would if I knew how!

I am using strRole just because I didn't want to have to type:
Forms!frm....!.... everytime I wanted to reference the field in the Select Case Statement - If you think I should take it out I can do that!

also - what parameter do I need to add to the OpenRecordSet..?

Thanks Jon,
Kev

You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.
I'm somewhat lazy when it comes to using recordsets within VBA Code. I sometimes didn't bother differentiating between read only recordsets and updatable recordsets because the performance difference was immaterial most of the time. However SQL Server really likes those options to be set. And Access gives you the above error message. Thus use the following options as appropriate.

File I/O type type, options
Read-only dbOpenSnapshot
Updating dbOpenDynaset, dbSeeChanges

E.g.. Set rs = db.OpenRecordset("TestTable") should now be Set rs = db.OpenRecordset("TestTable", dbOpenDynaset, dbSeeChanges)

Note: You can no longer use dbAppendOnly but must use dbOpenDynaset, dbSeeChanges.

 

[Kevin_S]

Got it working now once I set dbOpenDynaset and dbSeeChanges... thanks for your help on the recordset and I'll have a look at the article.

Also - Thanks to everyone who helped, I appreciate the time and effort!

Take Care,
Kevin

 

[dcx693]

Well, alrighty then Jon, I really did not want to post this since it just wasn't relevant to the thread. I apologize to the forum in general because I probably would not want to see this either. But, at Jon's request here is a copy of the private message I sent him. I think the message speaks for itself, so I will not post further responses here.


After I wrote:

I meant for the user to replace the last parameter in the Dcount expression. I think he understood because I think he got it to work with the final expression I sent him.

You then replied with

Apparently not the first time:

Then when I wrote

Dude, I wrote the final expression. I never wrote the first .

You replied with

Dude no one said you did.

So, I believe it was you who accused me of not getting it right the first time.

Jon, I don't want to start a flame war, that's why I took this off the message board and decided to send you a private message.

In the end, the user had a working (if not perfect) solution and I'm glad that you're willing to help him find a better one. I apologized to him for not getting the syntax correct in the first place, though I think he understands that you, like I, am not paid to provide perfect solutions on this forum. We volunteer our time.