Solved

MS Access details form

Posted on 2004-04-29
8
694 Views
Last Modified: 2012-08-13
I have an acces form,with a subform to display the details.
In the subform i hava command button to open anothe form to enter notes.
There willbe multiple records for thr details.
so i made the default view to Continuous Forms for the sub form
My proble is how do i set the color of the command button,( which opens the notes editor)
when there is any notes are available?If not, set it to default color?

here is the code i wrote in Form load
  Dim db As Database, objRs As Recordset
  Set db = CurrentDb()
  Dim sql As String
 
  sql = "select * from tblCustOrderDetails where JobNumber='" & Me![JobNumber] & "' "
   Set objRs = db.OpenRecordset(sql)
'
   
   Do Until objRs.EOF
        If (IsNull(objRs("TextNotes"))) Or (Len(objRs("TextNotes")) = 0) Then
          ' Me![Notes].ForeColor = vbBlue
                  Else
          ' Me![Notes].ForeColor = 255
                  End If
  objRs.MoveNext
 Loop

The problem is that it set the color of all the button depending up on the last record status.
Because the NAME is same for all the command buttons in the details screen.
Any help will be appreciated
 
0
Comment
Question by:aio419
  • 3
  • 3
  • 2
8 Comments
 
LVL 4

Expert Comment

by:cg_medistox
ID: 10953807
Why dont you use the DAO.recordset function and dim the table with the notes in and if there is any record then show one colour and if there isnt show another instead of using SQL? If you want i will try and recreate it in code?
0
 
LVL 4

Expert Comment

by:cg_medistox
ID: 10953831
In what way do you relate the notes table and the main form or is the notes part of the same table that the main form is on?
0
 
LVL 1

Expert Comment

by:dapcom
ID: 10954701
that's a problem i came accross some time ago.

you can't change a single detail element property in a continuous form.

I found a smart solution on the net. The purpose here was to hilight the selected row, but It can be adapted to your need. I can help later if u need. Try to dig if you can get something out of  that unconventional technique.

here it goes with the original explanations, hope it can help:

Changing the Background Color of the Current Record in a Continuous Form
Changing the Background Color in a continuous form is an often asked question in the Access Newsgroups. The following document shows how this can be done, and the methodology behind it.
This procedure will highlight the current row in a continuous form. Please see the notes at the end to learn what this procedure DOES NOT do.

This sample is based on the "Products" table from the Northwinds Database. Changes will be noted so you may use this code in your own form.

1. Create a new form based on the "Products" table.
2. Add all fields from the products table to the form.
3. Create the following Controls to the form

Name: CtlBack
Control Source: =IIf([SelTop]=[ctlCurrentLine],"ÛÛÛÛÛÛÛÛÛÛÛÛ",Null)

"Û" is character 0219, the easiest way to enter this is to copy and paste from here. Format the font of this control as Terminal.
Place this control on your form so that it is sized to cover the entire area where you would like the background to be. Experiment with the number of "Û" characters as well as the font height to get complete coverage.
Set the background to transparent. Set the foreground to whatever color you want your highlight color to be. Make sure the section background color is different from the highlight color.

Next, for all the controls that will have the background highlight, select them all, change the background color to the highlight color, then change the background color to transparent. (Yes, this step is necessary).

The following two controls can be placed anywhere, and be hidden. You may want to leave them visible to help in seeing how this works, then hide them when done.

Name: ctlCurrentLine
Control Source:=GetLineNumber()

Name: ctlCurrentRecord
Control Source: unbound

Add the following code behind the form:

Function GetLineNumber()
'The function "GetLineNumber" is modified from the Microsoft Knowledge Base
' (Q120913), the only difference here is that the following items have been hard
'coded:F, KeyName, KeyValue. This was done to add a slight performance
'increase. Change KeyName and KeyValue to reflect the key in your table.

Dim RS As Recordset
Dim CountLines
Dim F As Form
Dim KeyName As String
Dim KeyValue

Set F = Form
KeyName = "productid"
KeyValue = [ProductID]

         On Error GoTo Err_GetLineNumber
         Set RS = F.RecordsetClone
         ' Find the current record.
         Select Case RS.Fields(KeyName).Type
            ' Find using numeric data type key value.
            Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
            DB_DOUBLE, DB_BYTE
               RS.FindFirst "[" & KeyName & "] = " & KeyValue
            ' Find using date data type key value.
            Case DB_DATE
               RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
            ' Find using text data type key value.
            Case DB_TEXT
               RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
            Case Else
            MsgBox "ERROR: Invalid key field data type!"
               Exit Function
               End Select
         ' Loop backward, counting the lines.
         Do Until RS.BOF
            CountLines = CountLines + 1
            RS.MovePrevious
            Loop
Bye_GetLineNumber:               ' Return the result.
         GetLineNumber = CountLines
         Exit Function
Err_GetLineNumber:
      CountLines = 0
      Resume Bye_GetLineNumber
End Function

Private Sub Form_Click()
Me!ctlCurrentRecord = Me.SelTop

End Sub

Private Sub Form_Current()
Me!ctlCurrentRecord = Me.SelTop
End Sub

How the code works:
When you open the form, the GetLineNumber function gets the record number for each record and assigns its value to "ctlCurrentLine". As you move from record to record, the code "Me!ctlCurrentRecord = Me.SelTop" changes the value of "ctlcurrentRecord" to the current record number (See Access help for more on "SelTop"). The code in "ctlBack" compares "SelTop" to the "ctlCurrentLine" value and only changes the highlight if the two are equal. These values will only be equal for the current record. The highlighting works by formatting the Terminal Font character 219 (a box, you can use any font box character, but Terminal is more likely to be on any machine) to the highlight color. Because all the controls on the form are transparent, this formatting shows through as a background to the controls.

Notes:
Although at first glance it may look like the control "ctlCurrentRecord" is not needed, it actually is to force the record numbers to update as you move from record to record.
The code as written will detect movement to a row from the following: a mouse or keyboard click, clicking the record selector (through Form_Click), and using the Record Navigation bar. If you move to rows through code, you may need to make modifications to highlight the row.
Performance is affected by the speed of the machine. On slower machines you will note that the individual cell highlight shows first, then the whole row becomes highlighted.
The are many things that can be done with a continuous form, such as allowing deletions, edits, additions, etc. This code generically handles navigation through the form only. To account for how your form is actually set up, you would need to modify your form to detect each of the above events, and refresh accordingly. Anytime you change the underlying records of the form, you will need to call "GetLineNumber" (one way to do this is through Form.Refresh). Since there is a performance hit in doing this, only use this for those operations that are allowed on your form.  

0
 
LVL 1

Expert Comment

by:dapcom
ID: 10954778
hem,

i'm sorry, 80% of this is junk for you, I should have read it before.

all u need is :

----------
Name: CtlBack
Control Source: =IIf(test if note exist(),"ÛÛÛÛÛÛÛÛÛÛÛÛ",Null)

"Û" is character 0219, the easiest way to enter this is to copy and paste from here. Format the font of this control as Terminal.
Place this control on your form so that it is sized to cover the entire area where you would like the background to be. Experiment with the number of "Û" characters as well as the font height to get complete coverage.
Set the background to transparent. Set the foreground to whatever color you want your highlight color to be. Make sure the section background color is different from the highlight color.

Next, for all the controls that will have the background highlight, select them all, change the background color to the highlight color, then change the background color to transparent. (Yes, this step is necessary).
-----------------
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:aio419
ID: 10979596
Hi Dapcom,
  I did not get that.
"Name:     CtlBack
Control Source: =IIf(test if note exist(),"ÛÛÛÛÛÛÛÛÛÛÛÛ",Null)
Place this control on your form "

Which control you are talking about?Is  the syntax is for Access database ?
kind of confused with this name:ctlBack   syntax  for the  control.
Can you explain a bot more?
Thanks
0
 

Author Comment

by:aio419
ID: 10979666
in my earlier comment
Can you explain a bot more?  is    Can you explain a bit more?


Basically i am just making  a change to an existing form, i just do not want to recreate the  form inorder to highlight a small note button, because the form is quite complex in nature.
You know what i am talking about.
Thanks
0
 
LVL 1

Accepted Solution

by:
dapcom earned 135 total points
ID: 10988515
Hi aio419.

First, realise that my proposition don't really do what you need. You want to change the forecolor of your button, while I propose to change the backcolor of the row. As you've seen, if you change a property of a control displayed in a continuous form, the property value change for all rows. (as you have with the forecolor of your button)

(NB:See after the explanation another way more close to what you want, I found it when trying to explain...)

The trick is to allow you to highlight each row depending on any test you want by placing a colored control behind the existing ones, that will be empty or filled with squares of the color you want. Others control being transparent. And the result is a row that seem to have a different back color.

To have a good effect, you must play with the numbers of Û in your control and the font size.

So, the workaround is to add a control (named here ctlBack, but as you want), wich is filled with squares if your condition is true, or empty of not. Place this control where you want the highlight to be visible (behind your other controls)

you have to replace the test with something suitable for you in the controlSource of your now control (ctlBack).

might be something like:

=iif(not isNull([TextNote]) and [textNote]<>"","ÛÛÛÛÛÛÛÛÛÛÛÛ",Null)

so the content of this field will be ÛÛÛÛÛÛÛÛÛÛÛÛ (wich is displayed as filled squares with the Terminal font) if textNote is null or empty, and it will be empty otherwise.

Now, follow the rest of the instructions (transparency...)

The thing is to undesrtand the technique, maybe you want to try something else, based on same technique but look more as you want:
like using 2 controls: one with forecolor blue, one with forecolor 255, back style=transparent, Special effect flat, place both of them above your button. one control Source to
=iif(not isNull([TextNote]) and [textNote]<>"","Note","")
the other one to
=iif(not isNull([TextNote]) and [textNote]<>"","","Note")
and remove the caption of your button

So either one of your text controls will hace the Text "Note" in it in the corresponding color. The other will be blank. This actually should be exactly what you need.

sorry if i confused you
dapcom





0
 

Author Comment

by:aio419
ID: 10997114
Hi dapcom
Yes .I accept your answer.Thank you very much.

thanks to cg_medistox too for his reply.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
maxMirror challenge 10 89
commonTwo  challenge 63 101
Arduino EDI - Programming language 3 64
Recommendation vb6 to vb.net or others 14 44
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
A short article about problems I had with the new location API and permissions in Marshmallow
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now