Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    control as a constant (Access 2000)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: control as a constant (Access 2000)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: control as a constant (Access 2000)

    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 ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: control as a constant (Access 2000)

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

  5. #5
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: control as a constant (Access 2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: control as a constant (Access 2000)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: control as a constant (Access 2000)

    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 !

  8. #8
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: control as a constant (Access 2000)

    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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: control as a constant (Access 2000)

    > I want to import the blank table

    Which table? Import from where?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •