ACCESS FEED: Re: [MS_AccessPros] Re: Current record or row colour be highlighted

Kamis, 22 Oktober 2015

Re: [MS_AccessPros] Re: Current record or row colour be highlighted

Hi Khalid,

I only have Access 2003, and it only allows one Primary Key per table. I used your new information to create a form and subform, and I can enter data into the subform (which immediately generates a new empty record). I cannot enter anything into your two text boxes on the main form which both display #Name?, and I get an error saying "Control can't be edited; it's bound to ...". Does your main form have a record source? I have not added any event procedures.

I have not tried to add highlighting yet, but an indicator pointer appears in the record-selector of the record I have selected, but maybe you don't have record-selectors enabled.

Regards,

Dave

----- Original Message -----

From: khalidtanweerburrah@yahoo.com [MS_Access_Professionals]

To: MS_Access_Professionals@yahoogroups.com

Sent: Thursday, October 22, 2015 3:20 PM

Subject: Re: [MS_AccessPros] Re: Current record or row colour be highlighted

Hi Dave,

I am sorry there was a slight mistake in the following line in my reply:

~~~~~~~~~~~

Main form "Collection Voucher" has two fields

===== >Consignment -> Unbound -> Row Source ->

SELECT [Consignment Number].ConsignmentNo, [Consignment Number].ExportDocs FROM [Consignment Number] ORDER BY [Consignment Number].ConsignmentNo DESC;

cmbExportDocs -> its Control Source -> =ConsignmentNo.Column(1)

~~~~~~~~~~~~

Read as:

~~~~~~~~~~~

Main form "Collection Voucher" has two fields

ConsignmentNo -> Unbound -> Row Source ->

SELECT [Consignment Number].ConsignmentNo, [Consignment Number].ExportDocs FROM [Consignment Number] ORDER BY [Consignment Number].ConsignmentNo DESC;

cmbExportDocs -> its Control Source -> =ConsignmentNo.Column(1)

~~~~~~~~~~~

Sorry for inconvenience.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :

Hi Dave,

In my table "CollectionVoucher" 4 fields are assigned as PK

Field 1 = ConsignmentNo its Row Source is:

SELECT [Consignment Number].ConsignmentNo, [Consignment Number].ExportDocs FROM [Consignment Number];

Field 2 = ClientCIN its Row Source is:

SELECT Clients.ClientCIN, Clients.ClientName FROM Clients ORDER BY Clients.ClientCIN;

Field 3 = CartonNo

Field 4 = CartonSuffix

Table "Products" has PK -> ProductNameEnglish

~~~~~~~~~~~

Main form "Collection Voucher" has two fields

Consignment -> Unbound -> Row Source ->

SELECT [Consignment Number].ConsignmentNo, [Consignment Number].ExportDocs FROM [Consignment Number] ORDER BY [Consignment Number].ConsignmentNo DESC;

cmbExportDocs -> its Control Source -> =ConsignmentNo.Column(1)

~~~~~~~~~~~~

There is Before Update event on sub form to check duplicate CartonNo for same Consignment and same Client, that is why

4 fields were assigned in table CollectionVoucher (with the instructions and guidance of John)

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim stLinkCriteria As String

Dim rsc As DAO.Recordset

' Don't check if not on a new row

If Not Me.NewRecord Then Exit Sub

stLinkCriteria = "[CartonSuffix]='" & [CartonSuffix] & "' and [CartonNo]=" & [CartonNo] & " and [ClientCIN]=" & [cmbClientCIN] & _

" and [ConsignmentNo]='" & [ConsignmentNo] & "'"

Debug.Print stLinkCriteria



'Check CollectionVoucher table for duplicate CartonNo

If DCount("*", "CollectionVoucher", stLinkCriteria) > 0 Then

'Message box warning of duplication

MsgBox "This Carton Number: " & [CartonNo] & "-" & CartonSuffix & " has already been allotted" & vbCrLf & _

"in Consignment No.'" & [ConsignmentNo] & "'," & vbCrLf & _

"vide Contract No.'" & [cmbDeliveryVr] & "' for Client CIN: " & [cmbClientCIN] & "." _

, vbInformation, "PCTL - Duplicate Entry"

'Undo duplicate entry

Me.Undo

CartonNo.SetFocus

'Go to record of original CartonNo

Set rsc = Me.RecordsetClone

rsc.FindFirst stLinkCriteria

Me.Bookmark = rsc.Bookmark

Set rsc = Nothing

CartonNo.SetFocus

End If

'---------------------------------------------

There are some checks more in this event before End Sub, like

'---------------------------------------------

Me.ExportDocs.Value = Parent.cmbExportDocs

'-------------------

' If ClientCIN is empty,

If IsNull(Me.ClientCIN) Then

' Copy it from cmbClientCIN

Me.ClientCIN = cmbClientCIN

End If

'-------------------

and so on...

End Sub

~~~~~~~~~~~

I do data entry in sub form. I hope i have described your question to some extent.

I want to highlight the new record while doing data entry in it (in the sub form).

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <davewillgmale@...> wrote :

Khalid,

As you have not said what your tables are, I tried to reconstruct them from your subform SQL. I see that CartonNo and CartonSuffix are fields only in table CollectionVoucher, so they can't both be Primary Keys as you claim, unless there is another table that includes one of these fields as a primary key. Perhaps the Products table has HSCode as a primary key?

What is the SQL for your parent form "Collection Voucher"? Is this where you do your data entry, and you want to highlight this record in the subform?

Regards,

Dave W

----- Original Message -----

From: khalidtanweerburrah@... [MS_Access_Professionals]

To: MS_Access_Professionals@yahoogroups.com

Sent: Tuesday, October 20, 2015 7:57 PM

Subject: [MS_AccessPros] Re: Current record or row colour be highlighted

Hi Crystal,

First of all sorry that i was unable to reply to you and John, as i do not have access to internet.

Crystal,

I tried with all your instructions but am unable to highlight the row on my sub form which is continuous form. The only thing which i assume is that i have 4 fields set as PK

ConsignmnetNo

ClientCIN

CartonNo

CartonSuffix

You wrote:

WHERE

RecordID is the name of your primary key field

So in my case what would be RecordID, i did tried different patterns but could not get the result.

Please help me more. May be i have not got your point.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :

Hi Khalid,

the row can be highlighted using conditional formatting and a control to keep track of the primary key (PK), which is set on the form current event.

TO HIGHLIGHT THE CURRENT RECORD IN A CONTINUOUS FORM

put the following textbox control on your form (I usually put it in the footer since there is often more unused space)

name --> RecordID_current
visible --> no

make the following textbox control in the detail section of your form:

name --> HighlightBox
left --> 0
top --> 0
width --> width of detail section
height --> height of detail section, like 0.2
enabled --> false
locked --> true
tabstop --> false

send this control to the back so it is behind all the other controls in the section

in the design view of the form, select HighlightBox

since HighlightBox is behind everything, you may need to select it using the object drop-down (combo box in the Properties window at the top)

conditional formatting
condition 1 --> Expression Is
--> [RecordID] = [RecordID_current]

example to also highlight a new record:
    [RecordID_current] = nz([RecordID],RecordID_current)

change fill/back color to LIGHT YELLOW or light gray
or whatever color you want for your highlight

If my detail section background is White, I like to use light yellow for a highlight

WHERE
RecordID is the name of your primary key field

then, in the form OnCurrent event, assign a value to the unbound RecordID_current
being unbound, it will have the same value on every record

'~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Current()
   Me.RecordID_current = IIf([NewRecord], 0, [RecordID])
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~~~~~~~~~~~~~~~~~~~~~~~
'needed since Access 2007
Private Sub Form_BeforeInsert(Cancel As Integer)
   Me.RecordID_current = me.RecordID
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~

where [RecordID] is the controlname (or fieldname) corresponding to your primary key.

~~~
Me.NewRecord is true if the form is on a new record. Throwing this in means that a new record will also be highlighted ... otherwise, since 2007, it will not. I had to create the BeforeInsert event to make it work as well ... maybe this was a temporary bug and the extra steps are no longer necessary

I used to use
nz(RecordID,0)

but I changed to
IIf([NewRecord], 0, [RecordID])
since 2007

~~~
I also put a click event on the highlight box to SetFocus to another control -- because, even though it is not enabled, it will come to the front and cover everything up if it is clicked on!

... however, you want the Click event to ALSO open another form (first setFocus, then open the form)

since HighlightBox spans your entire detail section, you can put a click event on that control.

be sure to save the record (if it needs it) before setting focus somewhere else.

~~~~~~~~~

If you are using a datasheet, I believe you will need to set up conditional formatting for each control as you can't use a control that spans the whole line as you can in a continuous form

warm regards,
crystal

Have a suggestion to improve Access?
http://access.uservoice.com/

~ have an awesome day ~

On 10/9/2015 7:24 AM, John Viescas JohnV@... [MS_Access_Professionals] wrote:

Khalid-

Choose some field in CollectionVoucher that is Required but that does not have a default value. Write Conditional Formatting for all the controls based on the value of that field. If the field is empty (Null or blank), then change the background color. As soon as the user fills in the value, the row should change back to its normal color.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 9, 2015, at 12:48 PM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi All,

I have a form "Collection Voucher" on its sub form "CollectionVouchersubform" when i am doing data entry on a new record I need that current row should be highlighted or to a custom colour defined.

Sub forms SQL is as under:

SELECT CollectionVoucher.ConsignmentNo, [Consignment Number].ExportDocs, CollectionVoucher.Route, CollectionVoucher.ClientCIN, Clients.ClientName, CollectionVoucher.Destination, CollectionVoucher.DeliveryVr, CollectionVoucher.DeliveryVrDate, CollectionVoucher.CartonNo, CollectionVoucher.CartonSuffix, CollectionVoucher.TripNo, CollectionVoucher.UnitOfCarton, CollectionVoucher.ProductNameEnglish, Products.ProductNameRussian, Products.HSCode, CollectionVoucher.BrandName, CollectionVoucher.ProductQty, CollectionVoucher.UnitOfQty, CollectionVoucher.WeightOfCarton, CollectionVoucher.UnitOfGrossWeight, CollectionVoucher.[Net Weight], CollectionVoucher.UnitOfNetWeight, CollectionVoucher.UnitOfValue, CollectionVoucher.Rate, CollectionVoucher.Amount

FROM Products INNER JOIN ([Consignment Number] INNER JOIN (Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN) ON [Consignment Number].ConsignmentNo = CollectionVoucher.ConsignmentNo) ON Products.ProductNameEnglish = CollectionVoucher.ProductNameEnglish;

Help required please.

Regards,

Khalid