Senin, 31 Desember 2018

Re: [MS_AccessPros] Please help with VBA code

 

Ken,
If you are always adding ".jpg" then there is no reason to store the SKU with the .jpg. Just use [Sku Field] & ".JPG" when ever needed.

If you really think you need to store the full value, consider using the after update event of the SKU control to store the full value in FieldB.

Regards,
Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of kenaf42@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, December 31, 2018 2:57 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Please help with VBA code
 


Access 2010, Windows 7

Text filed "A" contains products SKU (Identification Number), Text filed "B" should copy filed A's data (that is easy) but add ".jpg" to the end of the SKU that has been copied from filed A in order to import the image from independent file on hard drive not within Database to the Image filed of the form of the product . How could I do this? Happy New Year.

Ken




__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


SPONSORED LINKS
.

__,_._,___

[MS_AccessPros] Please help with VBA code

 

Access 2010, Windows 7

Text filed "A" contains products SKU (Identification Number), Text filed "B" should copy filed A's data (that is easy) but add ".jpg" to the end of the SKU that has been copied from filed A in order to import the image from independent file on hard drive not within Database to the Image filed of the form of the product . How could I do this? Happy New Year.

Ken


__._,_.___

Posted by: kenaf42@yahoo.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


SPONSORED LINKS
.

__,_._,___

Minggu, 30 Desember 2018

[MS_AccessPros] Re: Some tables are not deleted

 

Sorry all - I forgot to "Show message history". Mea maxima culpa :)

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

Hi Kevin

The biggest problem with your code is in your error handler...

ErrHandle:
Resume ErrExit


If any error occurs, your procedure will just silently exit and you will have no indication that anything has gone wrong, much less what the error was, or where it occurred.

At the very least, you should have your error handler display a message:

ErrHandle:
MsgBox Err.Description
Resume ErrExit

Also, instead of declaring a different string variable for every SQL statement, I would reuse the same variable, and include that in your error message so that you can see exactly which SQL statement is failing, and why:

Dim strSQL As String

strSQL = "DELETE * FROM tblGroupRoom" & _
" WHERE GroupID = " & Me.GroupID
db.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM tblPart" & _
" WHERE GroupID = " & Me.GroupID
db.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM tblPartBooking" & _
" WHERE PBGroupID = " & Me.GroupID
db.Execute strSQL, dbFailOnError

... etc - then in your error handler:

ErrHandle:
MsgBox Err.Description & vbCrLf & strSQL
Resume ErrExit

Best wishes,
Graham [Access MVP 1996-2016]


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

Dear All,
I was trying to delete all the data from different tables with link to "GroupID". Sometimes it worked when all tables contain data, other times won't work when there's no data in some of the tables. Here's the code. Please help. Thanks.
------------------------------------------------------------------
On Error GoTo ErrHandle
Dim strPasswd
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQLGroupRoom As String
Dim strSQLPart As String
Dim strSQLPartBooking As String
Dim strSQLPartItinerary As String
Dim strSQLDayCity As String
Dim strSQLCityBooking As String
Dim strSQLCityPlaceToVisit As String
Dim strSQLCityBookingRoom As String
Dim strSQLHotelRoom As String
Dim strSQLRoomingList As String
Dim strSQLSubRoomingList As String
Dim strSQLClientPayment As String
Dim strSQLCoachPayment As String
Dim strSQLHotelPayment As String
Dim strSQLHotelBooking As String
Dim strSQLCoachBooking As String

strSQLGroupRoom = "DELETE * FROM tblGroupRoom" & _
" WHERE GroupID = " & Me.GroupID

strSQLPart = "DELETE * FROM tblPart" & _
" WHERE GroupID = " & Me.GroupID

strSQLPartBooking = "DELETE * FROM tblPartBooking" & _
" WHERE PBGroupID = " & Me.GroupID

strSQLPartItinerary = "DELETE * FROM tblPartItinerary" & _
" WHERE PIGroupID = " & Me.GroupID

strSQLDayCity = "DELETE * FROM tblDayCity" & _
" WHERE DCGroupID = " & Me.GroupID

strSQLCityBooking = "DELETE * FROM tblCityBooking" & _
" WHERE CBGroupID = " & Me.GroupID

strSQLCityPlaceToVisit = "DELETE * FROM tblCityPlaceToVisit" & _
" WHERE CPTVGroupID = " & Me.GroupID

strSQLCityBookingRoom = "DELETE * FROM tblCityBookingRoom" & _
" WHERE CBRGroupID = " & Me.GroupID

strSQLHotelRoom = "DELETE * FROM tblHotelRoom" & _
" WHERE HRGroupID = " & Me.GroupID

strSQLRoomingList = "DELETE * FROM tblRoomingList" & _
" WHERE GroupID = " & Me.GroupID

strSQLSubRoomingList = "DELETE * FROM tblSubRoomingList" & _
" WHERE SRLGroupID = " & Me.GroupID

strSQLClientPayment = "DELETE * FROM tblClientPayment" & _
" WHERE ClientPaymentGroupID = " & Me.GroupID

strSQLCoachPayment = "DELETE * FROM tblCoachPayment" & _
" WHERE CPaymentGroupID = " & Me.GroupID

strSQLHotelPayment = "DELETE * FROM tblHotelPayment" & _
" WHERE HPaymentGroupID = " & Me.GroupID

strSQLHotelPayment = "DELETE * FROM tblHotelBooking" & _
" WHERE HBGroupID = " & Me.GroupID

strSQLHotelPayment = "DELETE * FROM tblCoachBooking" & _
" WHERE CBookingGroupID = " & Me.GroupID


Set db = CurrentDb
strPasswd = InputBox("Enter Temporary Password", "Restricted Form")

If strPasswd = "" Or strPasswd = Empty Then
MsgBox "No Input Provided", vbInformation, "Required Temporary Password"
Exit Sub
End If

If strPasswd = ELookup("TemporaryPass", "tblSettings", "SettingsID=" & 1) Then

If vbYes = MsgBox("Are you sure you want to delete this Group?", vbQuestion + vbYesNo + vbDefaultButton2) Then
db.Execute strSQLGroupRoom, dbFailOnError
db.Execute strSQLPart, dbFailOnError
db.Execute strSQLPartBooking, dbFailOnError
db.Execute strSQLPartItinerary, dbFailOnError
db.Execute strSQLDayCity, dbFailOnError
db.Execute strSQLCityBooking, dbFailOnError
db.Execute strSQLCityPlaceToVisit, dbFailOnError
db.Execute strSQLCityBookingRoom, dbFailOnError
db.Execute strSQLHotelRoom, dbFailOnError
db.Execute strSQLRoomingList, dbFailOnError
db.Execute strSQLSubRoomingList, dbFailOnError
db.Execute strSQLClientPayment, dbFailOnError
db.Execute strSQLCoachPayment, dbFailOnError
db.Execute strSQLHotelPayment, dbFailOnError
db.Execute strSQLHotelBooking, dbFailOnError
db.Execute strSQLCoachBooking, dbFailOnError

End If

Me.Requery

If Not (fDelCurrentRec(Me)) Then
MsgBox "An Error occurred!"
End If




Else
MsgBox "Sorry, you do not have access to this information", vbOKOnly, "Important Information"
Exit Sub
End If

ErrExit:
Exit Sub

ErrHandle:
Resume ErrExit
--------------------------------------------------------------------------------------

Zhao LiQing

Be adventurous, be bold, be careful, be a star !

__._,_.___

Posted by: graham@mandeno.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (8)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


SPONSORED LINKS
.

__,_._,___

[MS_AccessPros] Re: Some tables are not deleted

 

Hi Kevin


The biggest problem with your code is in your error handler...

ErrHandle:
Resume ErrExit


If any error occurs, your procedure will just silently exit and you will have no indication that anything has gone wrong, much less what the error was, or where it occurred.

At the very least, you should have your error handler display a message:

ErrHandle:
MsgBox Err.Description
Resume ErrExit

Also, instead of declaring a different string variable for every SQL statement, I would reuse the same variable, and include that in your error message so that you can see exactly which SQL statement is failing, and why:

Dim strSQL As String

strSQL = "DELETE * FROM tblGroupRoom" & _
" WHERE GroupID = " & Me.GroupID
db.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM tblPart" & _
" WHERE GroupID = " & Me.GroupID
db.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM tblPartBooking" & _
" WHERE PBGroupID = " & Me.GroupID
db.Execute strSQL, dbFailOnError

... etc - then in your error handler:

ErrHandle:
MsgBox Err.Description & vbCrLf & strSQL
Resume ErrExit

Best wishes,
Graham [Access MVP 1996-2016]

__._,_.___

Posted by: graham@mandeno.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (7)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


SPONSORED LINKS
.

__,_._,___

Re: [MS_AccessPros] Some tables are not deleted

 

hi Kevin,

between each db.Execute, do:
db.Tabledefs.refresh
DoEvents

~crystal

VBA code you can use in your projects
http://msaccessgurus.com/code.htm

On 12/30/2018 5:38 AM, qingqinga qingqinga@yahoo.com [MS_Access_Professionals] wrote:
Dear Duane,
I'll try. Thanks a lot.
Best Regards,
Kevin

发自 WPS邮箱客戶端
在 "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>,2018年12月30日 14:44写道:

 

I would try to set a break point and step through the code to check the results of  each Execute. 

Also, many of us don't typically delete. We set a status field to inactive or deleted.

Regards,
Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of qingqinga qingqinga@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Sunday, December 30, 2018 12:33 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Some tables are not deleted
 
Dear Duane,
Yes, I did delete from the lowest level up to the top. But there are several tables in the middle level which don't necessarily connect to one another, except for sharing the same GroupID. In this case,  if one of them has no data, but if it's deleting code is ahead of those tables which contain data, then these tables will not be able to start deleting. Thanks.
Best Regards,
Kevin

发自 WPS邮箱客戶端
在 "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>,2018年12月30日 14:16写道:



 

Zhao LiQing,
You should be more descriptive than "won't work".

Are you deleting from the lowest level of the relationships first and then stepping up to higher levels?

Regards,
Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Qingqing qingqinga@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, December 29, 2018 9:35 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Some tables are not deleted
 
Dear All,
I was trying to delete all the data from different tables with link to "GroupID". Sometimes it worked when all tables contain data, other times won't work when there's no data in some of the tables. Here's the code. Please help. Thanks.
------------------------------------------------------------------
On Error GoTo ErrHandle
Dim strPasswd
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQLGroupRoom As String
Dim strSQLPart As String
Dim strSQLPartBooking As String
Dim strSQLPartItinerary As String
Dim strSQLDayCity As String
Dim strSQLCityBooking As String
Dim strSQLCityPlaceToVisit As String
Dim strSQLCityBookingRoom As String
Dim strSQLHotelRoom As String
Dim strSQLRoomingList As String
Dim strSQLSubRoomingList As String
Dim strSQLClientPayment As String
Dim strSQLCoachPayment As String
Dim strSQLHotelPayment As String
Dim strSQLHotelBooking As String
Dim strSQLCoachBooking As String

        strSQLGroupRoom = "DELETE * FROM tblGroupRoom" & _
                   " WHERE GroupID = " & Me.GroupID

           strSQLPart = "DELETE * FROM tblPart" & _
                   " WHERE GroupID = " & Me.GroupID

        strSQLPartBooking = "DELETE * FROM tblPartBooking" & _
                   " WHERE PBGroupID = " & Me.GroupID

        strSQLPartItinerary = "DELETE * FROM tblPartItinerary" & _
                   " WHERE PIGroupID = " & Me.GroupID

        strSQLDayCity = "DELETE * FROM tblDayCity" & _
                   " WHERE DCGroupID = " & Me.GroupID

        strSQLCityBooking = "DELETE * FROM tblCityBooking" & _
                   " WHERE CBGroupID = " & Me.GroupID

        strSQLCityPlaceToVisit = "DELETE * FROM tblCityPlaceToVisit" & _
                   " WHERE CPTVGroupID = " & Me.GroupID
 
        strSQLCityBookingRoom = "DELETE * FROM tblCityBookingRoom" & _
                   " WHERE CBRGroupID = " & Me.GroupID
 
        strSQLHotelRoom = "DELETE * FROM tblHotelRoom" & _
                   " WHERE HRGroupID = " & Me.GroupID

        strSQLRoomingList = "DELETE * FROM tblRoomingList" & _
                   " WHERE GroupID = " & Me.GroupID

         strSQLSubRoomingList = "DELETE * FROM tblSubRoomingList" & _
                   " WHERE SRLGroupID = " & Me.GroupID

        strSQLClientPayment = "DELETE * FROM tblClientPayment" & _
                   " WHERE ClientPaymentGroupID = " & Me.GroupID
 
        strSQLCoachPayment = "DELETE * FROM tblCoachPayment" & _
                   " WHERE CPaymentGroupID = " & Me.GroupID
 
        strSQLHotelPayment = "DELETE * FROM tblHotelPayment" & _
                   " WHERE HPaymentGroupID = " & Me.GroupID

        strSQLHotelPayment = "DELETE * FROM tblHotelBooking" & _
                   " WHERE HBGroupID = " & Me.GroupID
 
        strSQLHotelPayment = "DELETE * FROM tblCoachBooking" & _
                   " WHERE CBookingGroupID = " & Me.GroupID
 
   
Set db = CurrentDb
strPasswd = InputBox("Enter Temporary Password", "Restricted Form")

If strPasswd = "" Or strPasswd = Empty Then
MsgBox "No Input Provided", vbInformation, "Required Temporary Password"
Exit Sub
End If

If strPasswd = ELookup("TemporaryPass", "tblSettings", "SettingsID=" & 1) Then

       If vbYes = MsgBox("Are you sure you want to delete this Group?", vbQuestion + vbYesNo + vbDefaultButton2) Then
        db.Execute strSQLGroupRoom, dbFailOnError
        db.Execute strSQLPart, dbFailOnError
        db.Execute strSQLPartBooking, dbFailOnError
        db.Execute strSQLPartItinerary, dbFailOnError
        db.Execute strSQLDayCity, dbFailOnError
        db.Execute strSQLCityBooking, dbFailOnError
        db.Execute strSQLCityPlaceToVisit, dbFailOnError
        db.Execute strSQLCityBookingRoom, dbFailOnError
        db.Execute strSQLHotelRoom, dbFailOnError
        db.Execute strSQLRoomingList, dbFailOnError
        db.Execute strSQLSubRoomingList, dbFailOnError
        db.Execute strSQLClientPayment, dbFailOnError
        db.Execute strSQLCoachPayment, dbFailOnError
        db.Execute strSQLHotelPayment, dbFailOnError
        db.Execute strSQLHotelBooking, dbFailOnError
        db.Execute strSQLCoachBooking, dbFailOnError
       
        End If
       
        Me.Requery

        If Not (fDelCurrentRec(Me)) Then
        MsgBox "An Error occurred!"
        End If




Else
MsgBox "Sorry, you do not have access to this information", vbOKOnly, "Important Information"
Exit Sub
End If

ErrExit:
    Exit Sub
   
ErrHandle:
    Resume ErrExit
--------------------------------------------------------------------------------------

Zhao LiQing

Be adventurous, be bold, be careful, be a star !


------------------------------------
Posted by: Qingqing <qingqinga@yahoo.com>
------------------------------------


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

Yahoo Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/MS_Access_Professionals/join
    (Yahoo! ID required)

<*> To change settings via email:
    MS_Access_Professionals-digest@yahoogroups.com
    MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
    https://info.yahoo.com/legal/us/yahoo/utos/terms/



__._,_.___

Posted by: crystal 8 <strive4peace2008@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


SPONSORED LINKS
.

__,_._,___