Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Corect the records in control (Access 2000)

    In the OnOpen event of the form FCustomers i want to correct the control
    named "afid" to show the number of the option control named "Office":
    Forms!FCustomers!afid = Forms!FCustomers!office
    I am doing it with the new customer sucessfully.
    All the controls afid in this form must correspond to the number of the office.
    Most of the controls have it, but some of them are blank or show another number, because i was not able to make my function work properly.
    Anyway, i will be happy if i succeed to do the following:
    for all afid in the form
    if afid is not = Forms!FCustomers!office
    then make it = Forms!FCustomers!office
    and save it.

    If i can make it, then i will correct the right number of the control afid and my database will
    work properly.Shall i use updating ?
    Can you help me do it ?

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

    Re: Corect the records in control (Access 2000)

    Do you really need to do this in a form? Why not create and run an update query that sets the value of afid to that of office?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corect the records in control (Access 2000)

    Thank you so much for your so quick reply.Yes,on second thoughts it will be much better to create and run an update query.
    Thank you

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corect the records in control (Access 2000)

    Dear Hans,

    I used the following expression:
    CurrentDb.Execute "UPDATE Customers SET Me!afid = Me!office "

    And i got the error "Too few parameters.Expected 2".

    Will be grateful for your comments
    regards

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

    Re: Corect the records in control (Access 2000)

    Me! is for use in the module for a form. You cannot use it in a query. You can simply omit it:

    CurrentDb.Execute "UPDATE Customers SET afid = office"

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corect the records in control (Access 2000)

    I have to thank you once again.I see how it could be done so simple and elegant

    kind regards

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corect the records in control (Access 2000)

    Dear Hans,
    Could you please have a look at my form? The default value of the office is 4,but i get
    the error Too few parameters.enter1.
    I am afraid i commit some mistake somewhere

    kind regards

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

    Re: Corect the records in control (Access 2000)

    I don't understand what you are trying to do. Office is an unbound group box on the form, not a field in the table, so the SQL statement does not recognize office.

    If you want to set afid to 4 in all records in the table, you do not need the form at all. You can create an update query in the Queries section of the database window and run it.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corect the records in control (Access 2000)

    I have omitted to place the fields on the table,but my problem remains.Of course i could set the afid to 4 with an upadate query,but it does not solve my
    problem.Differemt users are using the form, and each user has different office number.For example, for on user the office number is 3 and for another 5.
    My question is:Can i set all the records in the OnOpen event on the control afid to the number of the office?
    If i do not solve my problem it will be due to my inability to describe reasonably what i am trying to do.

    kind regards

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

    Re: Corect the records in control (Access 2000)

    You can, if you really want to. You can refer to Me!office, but you must do that outside the quotes:

    Private Sub Form_Open(Cancel As Integer)
    CurrentDb.Execute "UPDATE Customers SET afid = " & Me!office
    End Sub

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corect the records in control (Access 2000)

    Dear Hans,

    My database is flying now i am very proud of it but i am fair enough to tell my friends
    that i have done it with the help of this excellent Forum.

    kind regards

Posting Permissions

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