How to change properties of a certain cell in a continuous form

[mercapto]

I am working in a continuous form.



I want to change the background of a cell after clicking on it (to "mark" the cell). The code is simple:

Private Sub Ctl1_Click()

    Me.Ctl1.BackColor = vbBlue

End Sub

But what I get is the whole column changing to blue, while I only want the clicked cell to change. Is it possible?

Another question:
I am able to run this sub with the textbox "Enabled", but I want no cursor to be blinking, no text selected... it's not an input box. How could I do it?

Thank you in advance.

[June7]

No, not possible with VBA and Continuous/Datasheet form.

Must use Conditional Formatting and must be some condition other than just clicking on control, such as: if Quantity > 5 backcolor is red. I don't know if condition can be if the control has Focus, never tried.

Access doesn't have 'cells' - has fields in table records and textboxes on forms/reports.

[mercapto]

Thanks, june7.

I tried several things with conditional formatting, unsuccessfully.

The point is that once I click the cell I store the data and "do something" (not relevant now), so I want to mark the cell somehow to confirm. How would you do it??

[June7]

It is not a 'cell'. That is spreadsheet terminology. Spreadsheet concepts cannot be applied to relational database structure.

Conditional Formatting in form Continuous or Datasheet views is only way to dynamically set a control's backcolor property.

Store what data and 'do something' what?

[mercapto]

Thanks. I think I am going to do it totally different.

It's an employee timetable in a crosstab query.
I want to switch values (shifts) between """cells""". I regarded that by clicking on the textbox I am able to debug. print the value of the unique row and column, so I was confidence that I was going to be able to do the updating. But I won't do it "blind".

Do you know any method to edit data in a form based in a crosstab query?

[June7]

Cannot edit data via CROSSTAB. Period.

If you can get unique identifier from the CROSSTAB that will allow selection of specific record(s) from the source data then open form to those records.

[mercapto]

I did as you suggested.

This code allows opening a form with the unique record that I want to edit. It works well with all textboxes for the 7 days in the week .

Issue: I want to open just in front of the editing """cell""" but I don't know how to set location, because Top is always the same. How could I do it??

(just the last impulse, please)

Private Sub AbrirFormularioEdicion()
Dim strFecha As String
Dim strLinkCriteria As String
Dim intDiaQueAbre As Integer
Dim ctrl As Control
Dim strOpenArgs As String
Dim intTop As Integer

Set ctrl = Screen.ActiveControl

'Location of new form.
strOpenArgs = ctrl.Left & "|" & ctrl.Top

'Where condition
intDiaQueAbre = Right(ctrl.Name, 1)
strFecha = Forms!frmCuadrante.FechaLunes + (intDiaQueAbre - 1)

strLinkCriteria = "FechaJornada = #" & strFecha & "# AND NombreEmpleado = '" & Me.NombreEmpleado & "'"

DoCmd.OpenForm "frmEditarcuadrante", , , strLinkCriteria, , acDialog, strOpenArgs
End Sub


Private Sub Ctl1_Click()
AbrirFormularioEdicion
End Sub

Private Sub Ctl2_Click()
AbrirFormularioEdicion
End Sub

...

In the new form:

Private Sub Form_Open(Cancel As Integer)
Dim strArgs() As String
Dim intLeft As Integer
Dim intTop As Integer
   
strArgs = Split(Me.OpenArgs, "|")

intLeft = strArgs(0)
intTop = strArgs(1)

DoCmd.MoveSize intLeft, intTop

End Sub

[mercapto]

I want the new form to pop-up in the same place in the screen where the editing cell is. So I try to pass the XY location through openargs. The ctrl.left is OK, but the ctrl.top is always the same number, no matter if I am in the first row or in the third, or in the twentieth. It seems that the system doesn't makes the difference.

So I want to know whether is possible to open a form just in the same place in the screen where a textbox is.

Perhaps if somehow I know the row number I could multiply it to get the coordinates I want.

[June7]

Still need to abandon the idea of 'cells'. A spreadsheet has rows and each row is a structure independent of other rows. Access doesn't work that way. Each 'row' in a form is really just the same set of controls repeated for each record in the table.

Position of controls is relative to the form and position of forms is relative to the application window, not the screen. What you want will not be easy. I've never tried and have no interest in pursuing (not even curious). Search web http://bytes.com/topic/access/answer...-form-position

Good Luck.

[mercapto]

Thanks, june7.

I say cell just because I don't know other word to express it.

Okay, is there any way to get in which row of the spreadsheet we are clicking??

[June7]

The short answer is no. Read post 10 again. There is no 'row' to identify.

[ssanfu]

Issue: I want to open just in front of the editing """cell""" but I don't know how to set location, because Top is always the same. How could I do it??

Short answer is as June has stated: You can't.

The longer answer: (bear with me....)
A table stores data.
A table has fields.
A table has records.

Important: a table does not have a "order". It is a bit bucket. If you enter 100 records into an empty table, close, then open the table, chances are that the records will be displayed in the same order that they were entered. But it is not guaranteed to be or remain in any order. Now you delete records, add new records, delete more records and add more new records. The order you entered the records into the table will probably be different that what you would see after opening the table.

To have order to records, you must use a query with a sort order set.

Tables can be unbound or bound. A bound table has a record source - it can be a table or a query.

Forms have controls. Controls can also be unbound or bound.
To display data from a table/query on a form, the form must be bound and the controls must be bound to a field in the table/query.

Let's say the form is set to "Continuous View" and has one bound control.
In forms view, you will see the data displayed from the table/query. If there were 10 records in the record source, you would see 10 records in the form.
If you change to "Design view', how many controls would you see? 10?? Nope you see only one. There is only one control to display the data for one or more records.

The control top location is relative to the top of the section the control is in (header, detail, footer).
Since there is only one control, there is only one value for the top of the control that can be returned.
If you have code to display (message box or debug window) and the top of the text box is 0.25, no matter how many records there are, the top value will be 0.25.


This is one of the reasons why you (meaning anybody) should forget anything you know about Excel until you are past the beginner stage and well into the intermediate stage in Access.....or any other database.

--------------------------------------------------------------------
I used to do a lot in Excel - didn't even know about Access. Now I have been working in Access so long, I struggle with programming in Excel. Even though it is still VBA, the methods are different. Having to think about how to loop through "cells" takes a while to remember.

[mercapto]

Well... this worked...

intTop = 3600 + (Me.Recordset.AbsolutePosition + 1) * ctrl.Height

Obviously 3600 is a factor and it works only for me. But it could be adapted.

Anyway, thank you guys for the lesson.

[June7]

And we learned from you. Very nice. I have used AbsolutePosition only once and unfortunately did not make connection for this requirement.

[ssanfu]

Nice.... I too forgot about the AbsolutePosition property.