control as a constant (Access 2000)

Topic

New Reply

I want to use the value in the control as a constant in an expression.How can i do it ? The expression is
Dim rate As Control
Set rate = Forms!frmSetUp![rate].Value
CurrentDb.Execute “ALTER TABLE Castings ALTER COLUMN CastingID COUNTER(” & rate & “,1)”

and the control is the field rate in the table affiliates.
How could i use the value for the afiliate 1 in the expression? I have entered this value to be 200.

Reply | Quote

Replies

You don’t want to use rate as a control. You want to use its value which is numeric (long integer).

So use

Dim rate As Long
rate = Forms!frmSetUp![rate].Value

BTW, rate is a variable, not a constant, since its value can change.

Reply | Quote

Thank you. I have done so,but still i get the error object required.This is so, because i have diffenet rates for the affiliates,as you can see in the attachement and Access should know that i am choosing the fiirst option.
I wonder how could i design the table as to choose the first option, or some other option from the table, and that rate to be referred to that choise ?

Reply | Quote

Are you still using Set rate = …? As you can see in my previous reply, you shouldn’t use the word Set here.

Reply | Quote

yes you are right.Now i removed it.I get now another error 91 object variable not recognized.But i wonder how should Access recognize if i click on the seocnd option, for affiliate 2,Madrid, where i set the rate to 400? I cannot find a way to choose between the different rates

Reply | Quote

The code works with the changes I proposed. Click anywhere in the record with the rate you want to use, then click the command button.

But I don’t understand what you want to accomplish. Changing the AutoNumber seed to a value below the current maximum field value is very dangerous. It can easily lead to errors because of duplicate values.

Reply | Quote

Dear Hans,

Just to confirm that wht you said was true. This time i copied your code,pasted it and it worked without problems.I will use this function as a basis for a further function
Thank you !

Reply | Quote

Dear Hans
How can i automate the process ? I want to import the blank table and make a number for the first affiliate that is Lisbon,send the table, delete it, then go to the next affiliate that is
Madrid, perform the autonumber function , send the table then delete it, then go to the next affiliate and perform the same actions ? I cannot use the GoTo control method since there is only one control.

Reply | Quote

> I want to import the blank table

Which table? Import from where?

Reply | Quote