Corect the records in control (Access 2000)

Topic

New Reply

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 ?

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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”

Reply | Quote

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

kind regards

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote