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

Hi Dave,

Sorry to say i could not resolve the mystery yet...

Regards,

Khalid

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

Khalid,

Maybe my description was not clear. I added one field named ID to the table, and three textboxes, named ID1, IDs, and Highlight to the form. The name Highlight is not necessary, but the other three names have to be the same as in my code.

Regards,

Dave W

----- Original Message -----
From: khalidtanweerburrah@... [MS_Access_Professionals]
To: MS_Access_Professionals@yahoogroups.com
Sent: Saturday, October 24, 2015 10:15 PM
Subject: Re: [MS_AccessPros] Re: Current record or row colour be highlighted

John,

I added field "ID" in table "CollectionVoucher" but not made it PK. As if you remembered with your suggestion we made PK with the combination of four fields:
ConsignmentNo
ClientCIN
CartonNo
CartonSuffix

Now i did all as Dave explained, I put both fields on the Detail section under other controls.

Dave is saying:
ID1 as a hidden textbox with Control Source ID.

and then he wrote:
Name: Highlight

On sub form i also set Record Selector to "Yes"

However, i kept it as "ID1". Yet i could not see any row highlighted.

Where i am wrong now?

Regards,
Khalid

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

Crystal-

Mea culpa. I was thinking of the case where you're trying to convert a Long Integer field to AutoNumber. That requires the procedure noted below.

KHALID-

Just add the AutoNumber field to your table. After you save the table, Access will automatically populate each row with unique integer numbers.

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 24, 2015, at 7:56 PM, crystal 8 strive4peace2008@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John, with all due respect, I believe that you can always add an AutoNumber ... you can just have only one.

warm regards,
crystal

On 10/24/2015 10:28 AM, John Viescas JohnV@... [MS_Access_Professionals] wrote:
Crystal-

It's not possible to add an AutoNumber field to a table that has data in it.

Khalid-

To create the AutoNumber field, do this:

1) Copy the table to the clipboard
2) Paste the table with a new name, but paste Structure Only
3) Add the AutoNumber field to the new table.
4) Paste again, but choose to append to existing table.

You can now delete (or rename) your old table and rename the new one to the original name. You might have to delete some Referential Integrity relationships to do that. Be sure to rebuild the relationships after you have the new table.

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 24, 2015, at 6:00 PM, crystal 8 strive4peace2008@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Actually, a Primary key is a field or combination of fields that is unique. AutoNumbers are a convenient way to do this.

Add an AutoNumber field to the table and set the Indexed property to --> Yes (No Duplicates)

Then put the AutoNumber field in a control (Visible can be No) and use it to do the highlighting

warm regards,
crystal

~ have an awesome day ~

On 10/24/2015 9:21 AM, 'Dave Williams' davewillgmale@... [MS_Access_Professionals] 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 -----
From: khalidtanweerburrah@... [MS_Access_Professionals]
To: MS_Access_Professionals@yahoogroups.com
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 -----
From: khalidtanweerburrah@... [MS_Access_Professionals]
To: MS_Access_Professionals@yahoogroups.com
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
&

(Message over 64 KB, truncated)