Selasa, 31 Desember 2013

[belajar-access] Selamat Tahun Baru 2014

 

Dear Belajar-access member yang setia dan kreatif,

Sejak May 2001 milis ini sudah berjalan dan selalu saja terisi dengan kasus-kasus menarik yang membutuhkan solusi dan pemikiran kreatif. Tidak melulu hanya dari teks book, tetapi juga dari berbagai macam pengalaman yang bisa disaring dari member expert atau pun pemula.

Bukan saja pertukaran ilmu pengetahuan atau pun inti sari pengalaman, tetapi juga peluang bisnis dan pengembangan diri yang lebih bermanfaat telah ternaungi di milis ini. Sekian kali kita telah melaksanakan kopi darat, ikut dengan komunitas access yang lebih luas dalam seminar atau pun temu komunitas yang lebih besar.

Di tahun 2014, tentu saja tantangan semakin beragam, perkembangan ms access sendiri semakin jauh ke depan, merambah dari desktop hingga cloud. Permasalahan teknis semakin kompleks, dan pastinya membutuhkan penanganan yang lebih tepat sasaran.

Mari sambut dan siap kita isi 2014 dengan solusi-solusi bermanfaat. Ajang bisnis dan pertukaran pengetahuan Access terus kita bina dan pelihara, semoga menjadikan yang lebih baik dan bermanfaat bagi kita semua.

Happy New Year.

Aksan Kurdin
(Moderator)

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
SPAM IS PROHIBITED
.

__,_._,___

RE: [MS_AccessPros] IF and SELECT CASE statements coding methods.

 

In my experience, the If Then Else If structure is more susceptible to logical errors than the Select Case structure. That makes If Then more difficult to  more difficult to debug than the case structure. My general preference when dealing with a series of related alternatives is to use the Select Case structure. From an operational point of view, Select Case also executes more efficiently.

 

Glenn

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of saigonf7q5@yahoo.com
Sent: Tuesday, December 31, 2013 3:49 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] IF and SELECT CASE statements coding methods.

 

 

I have an IF statement and a SELECT CASE statement that were coded in 2 different ways as shown. One takes less coding then the other. So far there is no error occurr. Is it always the case, either method is correct.

 

If Me!fraNoteOpts.Value = 1 Then
   sqltext = "Select Distinct id_rssd, [nm_short] From [tbl_A] WHERE [nm_short] Like '" & Chr$(Me![fraButtonFrame]) & "*';"
ElseIf Me!fraNoteOpts.Value = 2 Then
   sqltext = "SELECT Distinct RSSD, BkName FROM tblNotes WHERE BkName Like '" & Chr$(Me![fraButtonFrame]) & "*';"
ElseIf Me!fraNoteOpts.Value = 3 Then
   sqltext = "SELECT Distinct RSSD, BkName FROM tblNotes WHERE BkName Like '" & Chr$(Me![fraButtonFrame]) & "*';"
ElseIf Me!fraNoteOpts.Value = 4 Then
   sqltext = "SELECT Distinct RSSD, BkName FROM tblNotes WHERE BkName Like '" & Chr$(Me![fraButtonFrame]) & "*';"
End If

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

If Me!fraNoteOpts.Value = 1 Then
          sqltext = "Select Distinct id_rssd, [nm_short] From [tbl_A] WHERE [nm_short] Like '" & Chr$(Me![fraButtonFrame]) & "*';"
ElseIf Me!fraNoteOpts.Value = 2 Or Me!fraNoteOpts.Value = 3 Or Me!fraNoteOpts.Value = 4 Then
          sqltext = "SELECT Distinct RSSD, BkName FROM tblNotes WHERE BkName Like '" & Chr$(Me![fraButtonFrame]) & "*';"
End If

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Select Case fraNoteOpts
    Case 1
          sqltext = "select distinct left(nm_short,1)from [tbl_A];"
          fraButtonFrame = Null
    Case 2
          sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;"
          fraButtonFrame = Null
    Case 3
          sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;"
          fraButtonFrame = Null
    Case 4
          sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;"
          fraButtonFrame = Null
    Case Else
          MsgBox Err.Number & vbCrLf & Err.Description
          GoTo ExitProcedure
End Select

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Select Case fraNoteOpts
    Case 1
          sqltext = "select distinct left(nm_short,1)from [tbl_FINAL_A];"
          fraButtonFrame = Null
    Case 2, 3, 4
          sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;"
          fraButtonFrame = Null
    Case Else
          MsgBox Err.Number & vbCrLf & Err.Description
          GoTo ExitProcedure
End Select

 

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)
Recent Activity:
.

__,_._,___

RE: [MS_AccessPros] IF and SELECT CASE statements coding methods.

 

I would use the method that is easiest to maintain and understand. 

IMO, it's this one:

Select Case fraNoteOpts 
 Case 1 
  sqltext = "select distinct left(nm_short,1)from [tbl_FINAL_A];" 
  fraButtonFrame = Null 
 Case 2, 3, 4 
  sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;" 
  fraButtonFrame = Null 
 Case Else 
  MsgBox Err.Number & vbCrLf & Err.Description 
  GoTo ExitProcedure  
End Select 

However, I would probably qualify fraNoteOpts with Me.
Select Case Me.fraNoteOpts 

I'm not sure where the Err.Number and Err.Description would come from.
Also, I typically don't care for GoTo statements.

Duane Hookom MVP
MS Access

________________________________
> From: saigonf7q5@yahoo.com 
>
>
> I have an IF statement and a SELECT CASE statement that were coded in 2
> different ways as shown. One takes less coding then the other. So far
> there is no error occurr. Is it always the case, either method is
> correct.
>
>
>
> If Me!fraNoteOpts.Value = 1 Then
> sqltext = "Select Distinct id_rssd, [nm_short] From [tbl_A] WHERE
> [nm_short] Like '" & Chr$(Me![fraButtonFrame]) & "*';"
> ElseIf Me!fraNoteOpts.Value = 2 Then
> sqltext = "SELECT Distinct RSSD, BkName FROM tblNotes WHERE BkName
> Like '" & Chr$(Me![fraButtonFrame]) & "*';"
> ElseIf Me!fraNoteOpts.Value = 3 Then
> sqltext = "SELECT Distinct RSSD, BkName FROM tblNotes WHERE BkName
> Like '" & Chr$(Me![fraButtonFrame]) & "*';"
> ElseIf Me!fraNoteOpts.Value = 4 Then
> sqltext = "SELECT Distinct RSSD, BkName FROM tblNotes WHERE BkName
> Like '" & Chr$(Me![fraButtonFrame]) & "*';"
> End If
>
> ----------------------------------------------------------
>
> If Me!fraNoteOpts.Value = 1 Then
> sqltext = "Select Distinct id_rssd, [nm_short] From [tbl_A]
> WHERE [nm_short] Like '" & Chr$(Me![fraButtonFrame]) & "*';"
> ElseIf Me!fraNoteOpts.Value = 2 Or Me!fraNoteOpts.Value = 3 Or
> Me!fraNoteOpts.Value = 4 Then
> sqltext = "SELECT Distinct RSSD, BkName FROM tblNotes WHERE
> BkName Like '" & Chr$(Me![fraButtonFrame]) & "*';"
> End If
>
> ----------------------------------------------------------
> Select Case fraNoteOpts
> Case 1
> sqltext = "select distinct left(nm_short,1)from [tbl_A];"
> fraButtonFrame = Null
> Case 2
> sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;"
> fraButtonFrame = Null
> Case 3
> sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;"
> fraButtonFrame = Null
> Case 4
> sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;"
> fraButtonFrame = Null
> Case Else
> MsgBox Err.Number & vbCrLf & Err.Description
> GoTo ExitProcedure
> End Select
>
> ----------------------------------------------------------
>
> Select Case fraNoteOpts
> Case 1
> sqltext = "select distinct left(nm_short,1)from [tbl_FINAL_A];"
> fraButtonFrame = Null
> Case 2, 3, 4
> sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;"
> fraButtonFrame = Null
> Case Else
> MsgBox Err.Number & vbCrLf & Err.Description
> GoTo ExitProcedure
> End Select

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)
Recent Activity:
.

__,_._,___

[MS_AccessPros] IF and SELECT CASE statements coding methods.

 

I have an IF statement and a SELECT CASE statement that were coded in 2 different ways as shown. One takes less coding then the other. So far there is no error occurr. Is it always the case, either method is correct.

 

If Me!fraNoteOpts.Value = 1 Then
   sqltext = "Select Distinct id_rssd, [nm_short] From [tbl_A] WHERE [nm_short] Like '" & Chr$(Me![fraButtonFrame]) & "*';"
ElseIf Me!fraNoteOpts.Value = 2 Then
   sqltext = "SELECT Distinct RSSD, BkName FROM tblNotes WHERE BkName Like '" & Chr$(Me![fraButtonFrame]) & "*';"
ElseIf Me!fraNoteOpts.Value = 3 Then
   sqltext = "SELECT Distinct RSSD, BkName FROM tblNotes WHERE BkName Like '" & Chr$(Me![fraButtonFrame]) & "*';"
ElseIf Me!fraNoteOpts.Value = 4 Then
   sqltext = "SELECT Distinct RSSD, BkName FROM tblNotes WHERE BkName Like '" & Chr$(Me![fraButtonFrame]) & "*';"
End If

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

If Me!fraNoteOpts.Value = 1 Then
          sqltext = "Select Distinct id_rssd, [nm_short] From [tbl_A] WHERE [nm_short] Like '" & Chr$(Me![fraButtonFrame]) & "*';"
ElseIf Me!fraNoteOpts.Value = 2 Or Me!fraNoteOpts.Value = 3 Or Me!fraNoteOpts.Value = 4 Then
          sqltext = "SELECT Distinct RSSD, BkName FROM tblNotes WHERE BkName Like '" & Chr$(Me![fraButtonFrame]) & "*';"
End If

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Select Case fraNoteOpts
    Case 1
          sqltext = "select distinct left(nm_short,1)from [tbl_A];"
          fraButtonFrame = Null
    Case 2
          sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;"
          fraButtonFrame = Null
    Case 3
          sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;"
          fraButtonFrame = Null
    Case 4
          sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;"
          fraButtonFrame = Null
    Case Else
          MsgBox Err.Number & vbCrLf & Err.Description
          GoTo ExitProcedure
End Select

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Select Case fraNoteOpts
    Case 1
          sqltext = "select distinct left(nm_short,1)from [tbl_FINAL_A];"
          fraButtonFrame = Null
    Case 2, 3, 4
          sqltext = "SELECT distinct left(BkName,1)FROM tblNotes;"
          fraButtonFrame = Null
    Case Else
          MsgBox Err.Number & vbCrLf & Err.Description
          GoTo ExitProcedure
End Select

 

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
.

__,_._,___

[MS_AccessPros] RE: Issue with closing form

 

Good Morning John,  I tried the command with the same results.  It appears to be a timing issue.  When I set a break point and step through the commands one at a time everything works fine.  I have tried changing the order of the two form commands with the same results.  Stepping through the code works but real time both forms close.

There is no code in the LOAD or OPEN events of SySMaint.  SysMaint consist of multiple command buttons to initiate different forms to allow for configuring Combo Box list used throughout the total application.  The one associated with Duplicate Record View contains the following:

Dim rptFilter As Variant
On Error GoTo DuplicateRecordsErrorHandler
  rptFilter = ""
  'rptFilter = "(lngUserID = " & GetLoggedUser() & ")"
  DoCmd.Close acForm, "frmSysMaint", acSaveNo
  DoCmd.OpenForm "frmDuplicateLicense", acNormal, "", rptFilter
DuplicateRecords_Exit:
  Exit Sub
DuplicateRecordsErrorHandler:
   MsgBox Err.Description, vbCritical
   Call LogError(Err.Number, Err.Description, "frmSysMaint-Cmd_DuplicateRecords_Click()")
   Resume DuplicateRecords_Exit

Currently rptFilter is not be used.

 

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)
Recent Activity:
.

__,_._,___

Re: [MS_AccessPros] Replacing e-mail addresses with names

 

Thank you very much.  The second option worked great.  I had to write a line for each e-mail address but that will make it much easier in the future when we get new employees.  You help is greatly appreciated.
 
Happy New Year,
 
Dan

From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, December 30, 2013 4:09 PM
Subject: Re: [MS_AccessPros] Replacing e-mail addresses with names
 
Dan-

Try this:

DoCmd.RunSQL "Update tblOne SET [User List] ='Daniel Jones' WHERE InStr([User List], mailto:'danieljones@email.com') <> 0"

However, if there are multiple email addresses, you'll wipe out the others.

Consider also:

DoCmd.RunSQL "UPDATE tblOne SET [User List] = Replace([User List], 'danieljones@email.com', 'Daniel Jones')

That will simply replace the email with the name, but leave any other email in the field intact.
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 Dec 30, 2013, at 9:31 PM, Daniel Daniel <dancg86@yahoo.com> wrote:
John,
 
Here is the Update SQL that works for single entries:
 
DoCmd.RunSQL "Update tblOne SET [User List] ='Daniel Jones WHERE [User List] = 'danieljones@email.com'"
 
If the field is: danieljones@email.com I get Daniel Jones
If the field is: danieljones@email.com, mikesmith@email.com Nothing is changed.
I would like the second example to be: Daniel Jones, Mike Smith
 
Thank you,
 
Dan
 
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, December 30, 2013 3:01 PM
Subject: Re: [MS_AccessPros] Replacing e-mail addresses with names
 
Dan-

It would help to see the SQL of your UPDATE query and perhaps some example contents of the fields you're trying to change and what you expect as a result.
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 Dec 30, 2013, at 7:40 PM, dancg86@yahoo.com wrote:
I have a field in a table that contains e-mail addresses.  Sometimes there is one e-mail address sometimes multiple.  I created SQL to change the e-mail to the persons name.  It works great as long as there is only one e-mail address in the field.  I have searched the Microsoft websites as well as several others and they have been no help. I tried using "Application.SetOption "Default Find/Replace Behavior",1" and it does not do anything to help me.  I am currently using a update query with separate table to complete this task, but as more people join the company the table is getting very bulky. Is it possible for the REPLACE function to search "any part of field" in SQL? Also if anyone can suggest another way to accomplish this I am open to new way of accomplishing it.
 
I am  using Access 2007 with Windows 7 professional.
 
Thank you,
 
Dan

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)
Recent Activity:
.

__,_._,___

Senin, 30 Desember 2013

Re: [MS_AccessPros] Issue with closing form

 

Rod-


Do you have any code in the Open or Load events of frmSysMaint?  I assume this code is running in frmDuplicateLicense.  When I want to close the form where the code is running, I use:

DoCmd.Close acForm, Me.Name

That makes it clear that I'm closing the form where the code is running.

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 Dec 30, 2013, at 10:59 PM, <desertscroller@cox.net> <desertscroller@cox.net> wrote:

Hi All - Happy New Year
I am having an issue with closing a form.  The following code works great in all other uses but does work the same here.

On Error GoTo Form_Close_Err
    DoCmd.OpenForm "frmSysMaint", acNormal, "", "", , acNormal
    On Error Resume Next
    DoCmd.Close acForm, "frmDuplicateLicense"
    Exit Sub
Form_Close_Exit:
    Exit Sub
Form_Close_Err:
    MsgBox Error$
    Resume Form_Close_Exit

The SysMaint form launches just fine but when the close form command is used to close the current form -- both forms close.  I have tried close the DuplicateLicense form first then open SysMaint but get same results.
Any suggestions.   Access 2010 on a Windows 8.1 computer.
Rod

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)
Recent Activity:
.

__,_._,___

[MS_AccessPros] Issue with closing form

 

Hi All - Happy New Year
I am having an issue with closing a form.  The following code works great in all other uses but does work the same here.

On Error GoTo Form_Close_Err
    DoCmd.OpenForm "frmSysMaint", acNormal, "", "", , acNormal
    On Error Resume Next
    DoCmd.Close acForm, "frmDuplicateLicense"
    Exit Sub
Form_Close_Exit:
    Exit Sub
Form_Close_Err:
    MsgBox Error$
    Resume Form_Close_Exit

The SysMaint form launches just fine but when the close form command is used to close the current form -- both forms close.  I have tried close the DuplicateLicense form first then open SysMaint but get same results.
Any suggestions.   Access 2010 on a Windows 8.1 computer.
Rod

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
.

__,_._,___

Re: [MS_AccessPros] Replacing e-mail addresses with names

 

Dan-


Try this:

DoCmd.RunSQL "Update tblOne SET [User List] ='Daniel Jones' WHERE InStr([User List], 'danieljones@email.com') <> 0"

However, if there are multiple email addresses, you'll wipe out the others.

Consider also:

DoCmd.RunSQL "UPDATE tblOne SET [User List] = Replace([User List], 'danieljones@email.com', 'Daniel Jones')

That will simply replace the email with the name, but leave any other email in the field intact.

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 Dec 30, 2013, at 9:31 PM, Daniel Daniel <dancg86@yahoo.com> wrote:

John,
 
Here is the Update SQL that works for single entries:
 
DoCmd.RunSQL "Update tblOne SET [User List] ='Daniel Jones WHERE [User List] = 'danieljones@email.com'"
 
If the field is: danieljones@email.com I get Daniel Jones
If the field is: danieljones@email.com, mikesmith@email.com Nothing is changed.
I would like the second example to be: Daniel Jones, Mike Smith
 
Thank you,
 
Dan
 
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, December 30, 2013 3:01 PM
Subject: Re: [MS_AccessPros] Replacing e-mail addresses with names
 
Dan-

It would help to see the SQL of your UPDATE query and perhaps some example contents of the fields you're trying to change and what you expect as a result.
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 Dec 30, 2013, at 7:40 PM, dancg86@yahoo.com wrote:
I have a field in a table that contains e-mail addresses.  Sometimes there is one e-mail address sometimes multiple.  I created SQL to change the e-mail to the persons name.  It works great as long as there is only one e-mail address in the field.  I have searched the Microsoft websites as well as several others and they have been no help. I tried using "Application.SetOption "Default Find/Replace Behavior",1" and it does not do anything to help me.  I am currently using a update query with separate table to complete this task, but as more people join the company the table is getting very bulky. Is it possible for the REPLACE function to search "any part of field" in SQL? Also if anyone can suggest another way to accomplish this I am open to new way of accomplishing it.
 
I am  using Access 2007 with Windows 7 professional.
 
Thank you,
 
Dan

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)
Recent Activity:
.

__,_._,___

Re: [MS_AccessPros] Replacing e-mail addresses with names

 

John,
 
Here is the Update SQL that works for single entries:
 
DoCmd.RunSQL "Update tblOne SET [User List] ='Daniel Jones WHERE [User List] = 'danieljones@email.com'"
 
If the field is: danieljones@email.com I get Daniel Jones
If the field is: danieljones@email.com, mikesmith@email.com Nothing is changed.
I would like the second example to be: Daniel Jones, Mike Smith
 
Thank you,
 
Dan
 
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, December 30, 2013 3:01 PM
Subject: Re: [MS_AccessPros] Replacing e-mail addresses with names
 
Dan-

It would help to see the SQL of your UPDATE query and perhaps some example contents of the fields you're trying to change and what you expect as a result.
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 Dec 30, 2013, at 7:40 PM, dancg86@yahoo.com wrote:
I have a field in a table that contains e-mail addresses.  Sometimes there is one e-mail address sometimes multiple.  I created SQL to change the e-mail to the persons name.  It works great as long as there is only one e-mail address in the field.  I have searched the Microsoft websites as well as several others and they have been no help. I tried using "Application.SetOption "Default Find/Replace Behavior",1" and it does not do anything to help me.  I am currently using a update query with separate table to complete this task, but as more people join the company the table is getting very bulky. Is it possible for the REPLACE function to search "any part of field" in SQL? Also if anyone can suggest another way to accomplish this I am open to new way of accomplishing it.
 
I am  using Access 2007 with Windows 7 professional.
 
Thank you,
 
Dan

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)
Recent Activity:
.

__,_._,___