Minggu, 25 Oktober 2015

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

 



John,
 
Thanks very much for that - I had no idea that several fields could have one primary key.
 
Khalid,
 
No need to explain your primary keys to me now.
 
Regards,
Dave W
 
----- Original Message -----
Sent: Saturday, October 24, 2015 4:23 PM
Subject: Re: [MS_AccessPros] Re: Current record or row colour be highlighted

 

Dave-


Khalid has a compound Primary Key composed of four fields, and he depends on that to avoid duplicates across the four field values.  

Kahlid- 

If you are under the impression that an AutoNumber must be a Primary Key, that is not the case.  You can keep your current Primary Key.

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 
(Paris, France)




On Oct 24, 2015, at 5:21 PM, 'Dave Williams' davewillgmale@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


Hi Khalid,
 
Please explain what you mean by "PK with the combination of four fields". As I said before, each table can only have one PK field. Thats what "Primary" means. I don't see why checks depend on whether a field is a PK or not. You can index a field with no-duplicates without it having to be a PK.
 
Actually, the added ID field does not have to be a PK, so you don't have to change your existing fields at all.
 
Regards,
Dave
 
----- Original Message -----
Sent: Saturday, October 24, 2015 1:11 AM
Subject: Re: [MS_AccessPros] Re: Current record or row colour be highlighted

 
Hi Dave,

Thanks for your reply and work done.

As you are suggesting to:
Add "ID" AutoNumber field as Primary Key to CollectionVoucher table.

I am worried because i have already a PK with the combination of four fields, if i remove them my lot of checks would not be working and then i have to do a lot of work for them.

Any how i am very much thankful to you for getting the solution which you tested and did worked.

I will have to think over to your advise, but i am not implementing it right now.

Waiting for your comments & suggestions about my concern.

Regards,
Khalid


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


Khalid,
 
I have managed to implement highlighting on my experimental form, based on Crystal's method. Your long module is just for assisting the user to fill in the form, and should not affect matters. I can't get my textboxes on the main form to work, but it doesn't matter.
 
Here is what I did:
 
Add "ID" AutoNumber field as Primary Key to CollectionVoucher table.
 
On CollectionVoucherssubform add:
IDs as a hidden unbound textbox.
ID1 as a hidden textbox with Control Source ID.
 
A Textbox filling the subform record and behind all other controls.
Conditional Format: (option under "Format" on Menu bar)
Condition 1 Expression is ([IDs]=[ID]) Or (IsNull([IDs] And IsNull([ID])))
I used light yellow fill for highlighting.
 
Enabled: No
Locked: Yes
Name: Highlight
 
Form Event Procedure On Current:
 
Private Sub Form_Current()
If IsNull(Me.ID1) Then
 Me.IDs = Null
Else
 Me.IDs = Me.ID1
End If
End Sub
 
The highlighting appears on any record that is selected, even the blank one waiting for input.
 
Before arriving at this solution, I got the same effect without using conditional formatting, based on an example quoted by Bill, in the Files section, in a folder called 1_Samples, filename cont_form_bkColor.zip, which highlights a row based on a control's value.
 
Regards,
Dave W
 
----- Original Message -----
Sent: Thursday, October 22, 2015 9:19 PM
Subject: Re: [MS_AccessPros] Re: Current record or row colour be highlighted

 

Hi Dave,

I also do use Access 2003.

Record Source of Main form is:
SELECT [Consignment Number].ConsignmentNo, [Consignment Number].ExportDocs
FROM [Consignment Number]
ORDER BY [Consignment Number].ConsignmentNo;

I'm copying full module of Main Form "Collection Voucher"
========================
Option Compare Database

Private Sub ConsignmentNo_NotInList(NewData As String, Response As Integer)
    MsgBox "PCTL could not recognize this entry." & vbCrLf & _
        "Please choose an item from the drop down list." _
        , vbExclamation, "PCTL Management - Guide"
        Response = acDataErrContinue
End Sub

Private Sub Form_Open(Cancel As Integer)
    DoCmd.Maximize
End Sub
Private Sub CmdCloseFrmCollectionVr_Click()
On Error GoTo Err_CmdCloseFrmCollectionVr_Click
    DoCmd.Close
    DoCmd.OpenForm "Data Entry Menu"
    DoCmd.Restore

Exit_CmdCloseFrmCollectionVr_Click:
    Exit Sub

Err_CmdCloseFrmCollectionVr_Click:
    MsgBox Err.Description
    Resume Exit_CmdCloseFrmCollectionVr_Click
End Sub

Private Sub CmsUndoCollectionVr_Click()
On Error GoTo Err_CmsUndoCollectionVr_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_CmsUndoCollectionVr_Click:
    Exit Sub

Err_CmsUndoCollectionVr_Click:
    MsgBox Err.Description
    Resume Exit_CmsUndoCollectionVr_Click
    
End Sub
Private Sub CmdDelRcrdCollectionVr_Click()
On Error GoTo Err_CmdDelRcrdCollectionVr_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_CmdDelRcrdCollectionVr_Click:
    Exit Sub

Err_CmdDelRcrdCollectionVr_Click:
    MsgBox Err.Description
    Resume Exit_CmdDelRcrdCollectionVr_Click
    
End Sub

Private Sub SortOrder_NotInList(NewData As String, Response As Integer)
    MsgBox "This entry could not be found." & vbCrLf & _
        "Please select an item from the drop down list" _
        , vbExclamation, "PCTL Management - Guide"
        Response = acDataErrContinue
End Sub
=======================
First Control on Main form combo box "ConsignmentNo"
Control Source --> Unbound
Row Source --> SELECT [Consignment Number].ConsignmentNo, [Consignment Number].ExportDocs FROM [Consignment Number] ORDER BY [Consignment Number].ConsignmentNo DESC;
ExportDocs --> Date field

Second Control on Main form "cmbExportDocs"
Control Source --> =ConsignmentNo.Column(1)
Locked --> Yes
=======================
This is full module of CollectionVouchersubform
Option Compare Database
Option Explicit
Function RateWithNonBranded()
    'Amount = Rate * WeightOfCarton
End Function
Function RateWithBranded()
    'Me.Rate = Rate + 0.5
    'Amount = Rate * WeightOfCarton
End Function

Private Sub Amount_GotFocus()
    If IsNull(Rate) Then
        MsgBox "Oops you forget to enter Rate." & vbCrLf & _
        "Please enter Rate." _
        , vbExclamation, "PCTL - Missing Entry"
        Rate.SetFocus
    End If
    Me.Amount.Value = WeightOfCarton * Rate
End Sub

Private Sub BrandName_AfterUpdate()
    If Me.NewRecord Then
        BrandName.DefaultValue = Chr(34) & BrandName & Chr(34)
    End If
End Sub

Private Sub BrandName_GotFocus()
  If IsNull(CartonNo) Then
        MsgBox "Oops you forget to enter Carton No." & vbCrLf & _
        "Please enter Carton No." _
        , vbExclamation, "PCTL - Missing Entry"
        CartonNo.SetFocus
    End If
End Sub

Private Sub BrandName_NotInList(NewData As String, Response As Integer)
 Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("Brand Name " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "PCTL")
    If intAnswer = vbYes Then
         strSQL = "INSERT INTO [Branded Items] ([BrandName])" & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new Brand Name: " & Chr(34) & NewData & Chr(34) & " has been added to the list." _
            , vbInformation, "PCTL"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose a Brand Name from the list." _
            , vbInformation, "PCTL Management - Guide"
        Response = acDataErrContinue
    End If
End Sub

Private Sub CartonSuffix_AfterUpdate()
    If Me.NewRecord Then
        CartonSuffix.DefaultValue = Chr(34) & CartonSuffix & Chr(34)
    End If
End Sub

Private Sub CartonSuffix_GotFocus()
  If IsNull(CartonNo) Then
        MsgBox "Oops you forget to enter Carton No." & vbCrLf & _
        "Please enter Carton No." _
        , vbExclamation, "PCTL - Missing Entry"
        CartonNo.SetFocus
    End If
End Sub

Private Sub cmbClient_AfterUpdate()
    If Not IsNull(Me.cmbClient) Then
        ' Apply a filter
        Me.Filter = "ClientCIN = " & Me.cmbClient
        Me.FilterOn = True
    End If
End Sub

Private Sub cmbClient_NotInList(NewData As String, Response As Integer)
    MsgBox "PCTL could not recognize this entry." & vbCrLf & _
        "Please choose an item from the drop down list." _
        , vbExclamation, "PCTL Management - Guide"
        Response = acDataErrContinue
End Sub

Private Sub cmbDestination_GotFocus()
  If IsNull(cmbClient) Then
        MsgBox "Oops you forget to select any Client" & vbCrLf & _
        "Please enter Client CIN." _
        , vbExclamation, "PCTL - Missing Entry"
        cmbClient.SetFocus
    End If
End Sub

Private Sub cmbDestination_NotInList(NewData As String, Response As Integer)
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("Destination: " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "PCTL")
    If intAnswer = vbYes Then
         strSQL = "INSERT INTO Destination ([Destination])" & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new Destination: " & Chr(34) & NewData & Chr(34) & " has been added to the list." _
            , vbInformation, "PCTL"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose a Destination Name from the list." _
            , vbInformation, "PCTL"
        Response = acDataErrContinue
    End If
End Sub

Private Sub cmbSetRoute_GotFocus()
  If IsNull(cmbDestination) Then
        MsgBox "What is the Destination of this cargo?" & vbCrLf & _
        "Please enter Destination." _
        , vbExclamation, "PCTL - Missing Entry"
        cmbDestination.SetFocus
    End If
End Sub

Private Sub cmbSetRoute_NotInList(NewData As String, Response As Integer)
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("Route " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "PCTL")
    If intAnswer = vbYes Then
         strSQL = "INSERT INTO Route ([Route])" & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new Route " & Chr(34) & NewData & Chr(34) & " has been added to the list." _
            , vbInformation, "PCTL"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose a Route from the list." _
            , vbInformation, "PCTL"
        Response = acDataErrContinue
    End If
End Sub

Private Sub cmbTripNo_AfterUpdate()
DoCmd.GoToRecord , , acLast
CartonNo.SetFocus
End Sub

Private Sub cmbTripNo_NotInList(NewData As String, Response As Integer)
    MsgBox "PCTL could not recognize this entry." & vbCrLf & _
        "Please choose an item from the drop down list." _
        , vbExclamation, "PCTL Management - Guide"
        Response = acDataErrContinue
End Sub


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
'---------------------------------------------
    Me.ExportDocs.Value = Parent.cmbExportDocs
    '-------------------
    ' If ClientCIN is empty,
    If IsNull(Me.ClientCIN) Then
    ' Copy it from cmbClientCIN
        Me.ClientCIN = cmbClientCIN
    End If
    '-------------------
    ' If ClientName is empty,
    If IsNull(Me.ClientName) Then
    ' Copy it from cmbClientName
        Me.ClientName = cmbClientName
    End If
    '-------------------
    ' If Destination is empty,
    If IsNull(Me.Destination) Then
    ' Copy it from cmbDestination
        Me.Destination = cmbDestination
    End If
    '-------------------
    ' If TripNo is empty,
    If IsNull(Me.TripNo) Then
    ' Copy it from cmbTripNo
        Me.TripNo = cmbTripNo
    End If
    '-------------------
    ' If is empty BrandName
    'If IsNull(BrandName) Then
    '    RateWithNonBranded
    'End If
    '-------------------
    'If Not IsNull(BrandName) Then
    '    RateWithBranded
    'End If
    '-------------------
    ' If HSCode is empty,
    If IsNull(Me.HSCode) Then
    ' Copy it from cmbHSCode
        Me.HSCode = cmbHSCode
    End If
    '-------------------
    ' If UnitOfCarton is empty,
    If IsNull(Me.UnitOfCarton) Then
    ' Copy it from UnitOfCarton
        Me.UnitOfCarton = cmbUnitOfCarton
    End If
    '-------------------
    ' If UnitOfGrossWeight is empty,
    If IsNull(Me.UnitOfGrossWeight) Then
    'Copy it from cmbUnitOfGrossWeight
        Me.UnitOfGrossWeight = cmbUnitOfGrossWeight
    End If
    '-------------------
    ' If UnitOfNetWeight is empty,
    If IsNull(Me.UnitOfNetWeight) Then
    'Copy it from cmbUnitOfGrossWeight
        Me.UnitOfNetWeight = cmbUnitOfNetWeight
    End If
    '-------------------
    ' If UnitOfQty is empty,
    If IsNull(Me.UnitOfQty) Then
    ' Copy it from cmbUnitOfQty
        Me.UnitOfQty = cmbUnitOfQty
    End If
    '-------------------
    ' If UnitOfValue is empty,
    If IsNull(Me.UnitOfValue) Then
    ' Copy it from cmbUnitOfQty
        Me.UnitOfValue = cmbUnitOfValue
    End If
    '-------------------
    ' If Route is empty,
    If IsNull(Me.Route) Then
    ' Copy it from cmbSetRoute
        Me.Route = cmbSetRoute
    End If
    '-------------------
    ' If DeliveryVr is empty,
    If IsNull(Me.DeliveryVr) Then
    ' Copy it from cmbSetDeliveryVr
        Me.DeliveryVr = cmbDeliveryVr
    End If
    '-------------------
    ' If DeliveryVrDate is empty,
    If IsNull(Me.DeliveryVrDate) Then
    ' Copy it from cmbDvDate
        Me.DeliveryVrDate = cmbDvDate
    End If
    '-------------------
    ' If ProductNameRussian is empty
    If IsNull(Me.ProductNameRussian) Then
    ' Copy it from cmbProductNameRussian
        Me.ProductNameRussian = cmbProductNameRussian
    End If
    '-------------------
End Sub

Private Sub Form_Current()
   Me.RecordID_current = IIf([NewRecord], 0, [CartonNo]) ' Added this using Crystal mail for highlighting row
End Sub

Private Sub NetWeight_GotFocus()
    If IsNull(WeightOfCarton) Then
        MsgBox "Oops you forget to enter Weight Of Carton." & vbCrLf & _
        "Please enter Weight." _
        , vbExclamation, "PCTL - Missing Entry"
        WeightOfCarton.SetFocus
    End If
End Sub

Private Sub ProductNameEnglish_AfterUpdate()
    Me.ProductNameRussian = cboProductNameRussian
    If Me.NewRecord Then
        ProductNameEnglish.DefaultValue = Chr(34) & ProductNameEnglish & Chr(34)
        cboProductNameRussian.DefaultValue = Chr(34) & cboProductNameRussian & Chr(34)
    End If
End Sub

Private Sub ProductNameEnglish_NotInList(NewData As String, Response As Integer)
    MsgBox "PCTL could not recognize this entry." & vbCrLf & _
        "Please choose an item from the drop down list." _
        , vbExclamation, "PCTL Management - Guide"
        Response = acDataErrContinue
End Sub

Private Sub ProductQty_AfterUpdate()
    If Me.NewRecord Then
        ProductQty.DefaultValue = Chr(34) & ProductQty & Chr(34)
    End If
End Sub

Private Sub ProductQty_GotFocus()
  If IsNull(ProductNameEnglish) Then
        MsgBox "Oops you forget to select Product Name." & vbCrLf & _
        "Please enter Product Name." _
        , vbExclamation, "PCTL - Missing Entry"
        ProductNameEnglish.SetFocus
    End If
End Sub

Private Sub Rate_GotFocus()
    If IsNull(WeightOfCarton) Then
        MsgBox "Oops you forget to enter Weight Of Carton." & vbCrLf & _
        "Please enter Weight." _
        , vbExclamation, "PCTL - Missing Entry"
        WeightOfCarton.SetFocus
    End If

    Dim varRate As Variant

    varRate = DLookup("DestinationRate", "tblDestinationRates", _
        "Destination = '" & Me.cmbDestination & _
        "' AND ProductNameEnglish = '" & Me.ProductNameEnglish & "'")
    Me.Rate = Nz(varRate, 0)
End Sub

Private Sub WeightOfCarton_AfterUpdate()
    Me.NetWeight.Value = WeightOfCarton - 1.5
End Sub

Private Sub WeightOfCarton_GotFocus()
    If IsNull(ProductQty) Then
        MsgBox "Is this Carton empty?" & vbCrLf & _
        "Please enter Product Quantity." _
        , vbExclamation, "PCTL - Missing Entry"
        ProductQty.SetFocus
    End If
End Sub
===========================

Does above information gives you some help.

Regards,
Khalid


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


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 -----
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 -----
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







__._,_.___
Posted by: "Dave Williams" <davewillgmale@gmail.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (22)

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)