Setting starting value of COUNTER via a textbox 1

[MrMode]

I have a query that renumbers a field for all records in a temporary table

ALTER TABLE tblRanking ALTER COLUMN Rank COUNTER

This works perfectly.

What I would like to do is dictate what the starting number should be. I understand that you can add additional information for the COUNTER (x,y) for starting value and increment amount e.g. (5, 1) would start the series at 5 and increment each record by 1, therefore values would be 5, 6, 7, 8, etc.

My issue is that I need the starting value for a renumbering exercise to be determined each time I run the query based on a value I capture in a textbox.

Something like this:

ALTER TABLE TEMPRanking ALTER COLUMN NewClassRank COUNTER (me.text15, 1);

This just creates an error... How do I push the value in the textbox into the statement?

 

[1DMF]

me.text15 is not valid within a query, I assume you are trying to access a textbox from an open form?

The syntax is

[Forms]![frmName].[fldName]

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MrMode]

**embarrassed**

Of course, dumping something into a SQL statement doesn't work! However, breaking into the statement and inserting the variable does.

This works.

"ALTER TABLE tblRanking ALTER COLUMN Rank COUNTER(" & me.text15 & ", 1);"

 

[1DMF]

not in the query it wouldn't, which is what you stated

I have a query

But of course, if you have VBA creating a SQL statement on the form itself...

CurrentDB.Execute("ALTER TABLE tblRanking ALTER COLUMN Rank COUNTER(" & me.text15 & ", 1)", dbSeeChanges)

That'll do the job also.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MrMode]

Thank. It's Friday, I must be getting tired and not paying attention to even the basics

 

[1DMF]

No problem, always glad to help, I shudder at some of the stupid questions I have asked here on TT over the years, when I really did know better.

Brain freeze happens to us all, not just on a Friday!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MrMode]

Thanks