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

Sabtu, 24 Oktober 2015

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

Hi Crystal,

I have already done that while adding Auto Number field.

Regards,

Khalid

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

Hi Khalid,

be sure to create a UNIQUE INDEX on the AutoNumber int the Table Design by setting the Index property on the AutoNumber to --> Yes (no Duplicates).

take the time to understand the code -- so you can ask for help with specific issues

if you need help understanding specific code, post what you have and what you don't understand.

thank you

warm regards,
crystal

On 10/24/2015 4:15 PM, khalidtanweerburrah@... [MS_Access_Professionals] wrote:

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

(Message over 64 KB, truncated)